Transactions: COMMIT and ROLLBACK

A transaction groups several statements into one unit of work: either all of them take effect, or none do.

The classic example is a bank transfer. Moving 100 rupees from Asha to Ben is two updates:

1. Subtract 100 from Asha.
2. Add 100 to Ben.

If the server crashes (or an error occurs) between the two, 100 rupees vanish. A transaction makes the pair atomic.

1
2
3
4
5
6
7

CREATE TABLE accounts (
    id INT PRIMARY KEY,
    owner VARCHAR(20) NOT NULL,
    balance DECIMAL(10,2) NOT NULL,
    CHECK (balance >= 0)
) ENGINE = InnoDB;
INSERT INTO accounts VALUES (1, 'Asha', 500.00), (2, 'Ben', 200.00);

A successful transfer

1
2
3
4
5
6

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

SELECT id, owner, balance FROM accounts ORDER BY id;

1
2
3
4
5
6
7

+----+-------+---------+
| id | owner | balance |
+----+-------+---------+
|  1 | Asha  |  400.00 |
|  2 | Ben   |  300.00 |
+----+-------+---------+
2 rows in set

START TRANSACTION begins the transaction. COMMIT makes all changes permanent, at once. Until COMMIT, other connections do not see your changes (with the default isolation level, see Isolation Levels).

Undoing with ROLLBACK

ROLLBACK throws away everything since START TRANSACTION:

1
2
3
4
5

START TRANSACTION;
UPDATE accounts SET balance = balance - 300 WHERE id = 1;
SELECT owner, balance FROM accounts WHERE id = 1;
ROLLBACK;
SELECT owner, balance FROM accounts WHERE id = 1;

1
2
3
4
5
6
7
8
9
10
11
12
13

+-------+---------+
| owner | balance |
+-------+---------+
| Asha  |  100.00 |
+-------+---------+
1 row in set

+-------+---------+
| owner | balance |
+-------+---------+
| Asha  |  400.00 |
+-------+---------+
1 row in set

Inside the transaction, Asha had 100. After ROLLBACK, her balance is back to 400, as if nothing had happened.

When an error happens

Suppose Ben tries to send more than he has. The CHECK (balance >= 0) constraint rejects the update:

1
2
3

START TRANSACTION;
UPDATE accounts SET balance = balance + 900 WHERE id = 1;
UPDATE accounts SET balance = balance - 900 WHERE id = 2;

1

ERROR 3819 (HY000): Check constraint 'accounts_chk_1' is violated.

The second statement failed (error 3819). The first one did run, and is still pending inside the open transaction. MySQL rolled back only the failing statement, not the whole transaction. It is the application’s job to see the error and issue ROLLBACK:

1
2
3

SELECT owner, balance FROM accounts ORDER BY id;
ROLLBACK;
SELECT owner, balance FROM accounts ORDER BY id;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

+-------+---------+
| owner | balance |
+-------+---------+
| Asha  | 1300.00 |
| Ben   |  300.00 |
+-------+---------+
2 rows in set

+-------+---------+
| owner | balance |
+-------+---------+
| Asha  |  400.00 |
| Ben   |  300.00 |
+-------+---------+
2 rows in set

Before the rollback, Asha seemed to have 1,300 (the half-done transfer). After the rollback, the original values return. Always check for errors and roll back in application code (Transaction Patterns).

Autocommit

By default, MySQL runs in autocommit mode: every single statement is its own transaction, committed immediately. That is why UPDATE changes are permanent without a COMMIT.

1

SELECT @@autocommit AS autocommit_is_on;

1
2
3
4
5
6

+------------------+
| autocommit_is_on |
+------------------+
|                1 |
+------------------+
1 row in set

START TRANSACTION (or BEGIN) suspends autocommit until you COMMIT or ROLLBACK. You can also turn autocommit off for the session: SET autocommit = 0;. Then every statement joins an open transaction, and nothing is saved until COMMIT, which surprises many people. Keep autocommit on, and use START TRANSACTION explicitly.

Which tables support transactions?

Only InnoDB (the default). Old MyISAM tables ignore ROLLBACK: their changes are applied at once and cannot be undone. Check that all tables in a transaction are InnoDB (Storage Engines).

Statements that end a transaction by themselves

DDL statements such as CREATE TABLE, ALTER TABLE, DROP TABLE and TRUNCATE cause an implicit commit in MySQL. They cannot be rolled back, and they commit whatever you had pending (DDL, DML and TCL, Autocommit and Savepoints).

When to use a transaction

Use one whenever several statements must succeed together:

• Money moves between accounts.
• An order and its order lines are created.
• Stock is reduced and an order is confirmed.
• An archive copy is made and the original deleted (INSERT SELECT).
• A parent row and its children are inserted (INSERT Results).

A single INSERT or UPDATE is already atomic by itself. You do not need a transaction around one statement.

Keep transactions short

An open transaction holds locks on the rows it changed, and keeps old row versions alive. Do the work, commit quickly, and never wait for a user, an HTTP call or a slow job inside a transaction (Locking).