Transaction Basics
In this page:
START TRANSACTION;
-- SQL statements
COMMIT;What is a Transaction?
A transaction groups several SQL statements into one all-or-nothing unit: either every statement inside it succeeds and is saved together, or the whole group is rolled back as if none of it ever ran.
Example: What is a Transaction?
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;Starting a Transaction
START TRANSACTION marks the beginning of that group, which suspends MySQL's usual behavior of saving each statement immediately so you can control exactly when the whole batch becomes permanent.
Example: Starting a Transaction
CREATE TABLE accounts (id INT, balance INT);
INSERT INTO accounts VALUES (1, 500), (2, 200);
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;Committing Changes
COMMIT makes every change made since the transaction began permanent all at once. Once committed, those changes are durable and can no longer be undone with a rollback.
Example: Committing Changes
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;Rolling Back Changes
ROLLBACK discards every change made since the transaction started, restoring the database to exactly the state it was in before the transaction began — useful the moment something goes wrong partway through.
Example: Rolling Back Changes
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Something went wrong -- undo it
ROLLBACK;Autocommit Mode
By default MySQL runs in autocommit mode, saving each individual statement the instant it executes. Wrapping statements in an explicit transaction temporarily disables that so several changes can be committed together.
Example: Autocommit Mode
SELECT @@autocommit;
SET autocommit = 0;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;- Forgetting
COMMIT, so other sessions never see the changes, and closing the connection rolls them back. - Using a non-transactional table type like
MyISAM, soROLLBACKdoes not undo anything (useInnoDB). - Running a DDL statement like
CREATE TABLEin the middle, which forces an implicit commit.
Chapter Quiz — Complete all 4 topics to unlock
0/4 topics done
Complete these topics first: