SQL: transactions with COMMIT and ROLLBACK
Move money between two accounts so that both updates happen or neither does.
CREATE TABLE account (id INTEGER PRIMARY KEY, owner TEXT, balance INTEGER CHECK (balance >= 0));
INSERT INTO account VALUES (1, 'Ada', 100), (2, 'Bo', 50);
BEGIN;
UPDATE account SET balance = balance - 30 WHERE id = 1;
UPDATE account SET balance = balance + 30 WHERE id = 2;
ROLLBACK;
SELECT owner, balance FROM account ORDER BY id;
BEGIN;
UPDATE account SET balance = balance - 30 WHERE id = 1;
UPDATE account SET balance = balance + 30 WHERE id = 2;
COMMIT;
SELECT owner, balance FROM account ORDER BY id;Ada|100 Bo|50 Ada|70 Bo|80
Explanation
A transaction groups several statements into one all-or-nothing unit. BEGIN starts it, COMMIT makes all its changes permanent, and ROLLBACK undoes all of them. This is the atomicity of ACID: a transfer that subtracts from one account and adds to another must never be left half done, even if the system fails between the two statements.
In the example, the first transfer is rolled back, so the balances are unchanged afterwards. The second one is committed, so both updates persist together. Other ACID properties are consistency (constraints hold before and after), isolation (concurrent transactions do not see each other's partial work), and durability (committed changes survive a crash).
Explore the dry run
CREATE TABLE account (id INTEGER PRIMARY KEY, owner TEXT, balance INTEGER CHECK (balance >= 0));INSERT INTO account VALUES (1, 'Ada', 100), (2, 'Bo', 50); BEGIN;UPDATE account SET balance = balance - 30 WHERE id = 1;UPDATE account SET balance = balance + 30 WHERE id = 2;ROLLBACK;SELECT owner, balance FROM account ORDER BY id; BEGIN;UPDATE account SET balance = balance - 30 WHERE id = 1;UPDATE account SET balance = balance + 30 WHERE id = 2;COMMIT;SELECT owner, balance FROM account ORDER BY id;Follow the example
Start to inspect the source, values, and output. These prepared steps explain the published sample; grouped loop steps describe multiple iterations.
View the complete dry-run table
| Step / line | What happens | State after step | Output so far |
|---|---|---|---|
| Two accounts with a combined balance of 150. | Ada 100, Bo 50 | — | |
| The first transfer is abandoned. Both updates are undone together. | balances restored | — | |
| After the rollback nothing has changed. | balances restored | Ada|100 Bo|50 | |
| The second transfer is made permanent: both updates take effect together. | balances restored | Ada|100 Bo|50 | |
| After the commit, Ada has 70 and Bo has 80. The total is still 150. | balances restored | Ada|100 Bo|50 Ada|70 Bo|80 |
Cost and common mistakes
Each statement costs O(1) with the primary key index; commit adds a log write for durability.
- Without a transaction, each statement is committed on its own, so a failure between the two UPDATEs would lose or create money.
- Keep transactions short. Long ones hold locks and slow down other users.
- A transaction left open without COMMIT or ROLLBACK keeps its locks until the connection closes.
Try it yourself
What would the balances be if the transfer subtracted 120 from Ada inside a transaction?
Reveal answer and reasoning
The CHECK (balance >= 0) would fail on the first UPDATE, the statement would stop, and a rollback would leave 100 and 50.