SQL: transactions with COMMIT and ROLLBACK

Before you start

SQL UPDATE and SELECT.

Move money between two accounts so that both updates happen or neither does.
Predict the sample output and explain the state changes.
Complete the practice task and check the boundary cases.

The concept

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).

Code example

SQL

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;

Output

Ada|100
Bo|50
Ada|70
Bo|80

Example details

Follow the code

Open the walkthrough to inspect the example one step at a time.

Show guided walkthrough

Guided walkthroughReady

1CREATE TABLE account (id INTEGER PRIMARY KEY, owner TEXT, balance INTEGER CHECK (balance >= 0));

2INSERT INTO account VALUES (1, 'Ada', 100), (2, 'Bo', 50);

4BEGIN;

5UPDATE account SET balance = balance - 30 WHERE id = 1;

6UPDATE account SET balance = balance + 30 WHERE id = 2;

7ROLLBACK;

8SELECT owner, balance FROM account ORDER BY id;

10BEGIN;

11UPDATE account SET balance = balance - 30 WHERE id = 1;

12UPDATE account SET balance = balance + 30 WHERE id = 2;

13COMMIT;

14SELECT 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

Prepared trace of the published program. Some rows group repeated iterations.
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

Behind the scenes

How SQL processes this code

The database parses and plans the query, accesses the table, and returns the selected rows. Physical execution order may differ from the query’s written order.

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.