COMMIT & ROLLBACK
In this page:
START TRANSACTION;
-- SQL statements
COMMIT; -- save the changes
-- or
ROLLBACK; -- undo the changesIntroduction to Transactions
COMMIT and ROLLBACK are the two ways a transaction can end: COMMIT locks in every change made since START TRANSACTION as permanent, while ROLLBACK throws all of them away as if the transaction never happened.
Example: Introduction to Transactions
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
COMMIT;Undoing Changes with ROLLBACK
ROLLBACK is your safety net when something goes wrong mid-transaction — a failed validation, an unexpected error, or a business rule violation — letting you cancel every change made so far and leave the data exactly as it was.
Example: Undoing Changes with ROLLBACK
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
-- Validation failed -- cancel everything since START TRANSACTION
ROLLBACK;Auto-Commit Mode in MySQL
Turning off autocommit mode puts you in full manual control of when data becomes permanent, which matters most for multi-step operations like transferring money between two accounts that must both succeed or both fail together.
Example: Auto-Commit Mode in MySQL
SET autocommit = 0;
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;Checking Transaction Status
You can check the current autocommit setting to confirm whether MySQL is saving your statements immediately or waiting for an explicit COMMIT, which is worth verifying before running sensitive multi-step logic.
Example: Checking Transaction Status
SELECT @@autocommit AS autocommit_enabled;Best Practices for Transactions
Keeping transactions short is a real performance concern — a long-running transaction can hold locks on rows or tables for an extended period, blocking other users, so always close it promptly with COMMIT or ROLLBACK.
Example: Best Practices for Transactions
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT; -- close it promptly, don't hold locks longer than needed- Assuming
ROLLBACKworks afterCOMMIT, when committed changes are permanent. - Leaving a transaction open for a long time, which holds locks and blocks other users.
- Turning
autocommitoff withSET autocommit = 0and then forgetting to commit.
Chapter Quiz — Complete all 4 topics to unlock
0/4 topics done
Complete these topics first: