Transactions

When things must happen together

A bank transfer takes 500 from Alice and adds 500 to Bob. That is two UPDATE statements. If the server crashes after the first, 500 has vanished. If two transfers run at the same moment, they may read the same balance and overwrite each other. A transaction solves both problems by treating a group of statements as one indivisible unit.

ACID in plain words

Transactions are described by four guarantees:

• Atomicity: all the statements take effect, or none do.
• Consistency: a transaction moves the database from one valid state to another. Constraints still apply.
• Isolation: concurrent transactions do not see each other's unfinished work (to a degree you can choose).
• Durability: once committed, data survives a crash or power failure.

MySQL's default engine, InnoDB, supports transactions. The older MyISAM engine does not.

A first transaction

Create a small accounts table:

CREATE DATABASE IF NOT EXISTS practice;
USE practice;
DROP TABLE IF EXISTS bank_accounts;
CREATE TABLE bank_accounts (
  id      INT PRIMARY KEY,
  owner   VARCHAR(30) NOT NULL,
  balance DECIMAL(10,2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO bank_accounts VALUES (1, 'Alice', 1000.00), (2, 'Bob', 300.00);

Now the transfer, as one transaction:

START TRANSACTION;
UPDATE bank_accounts SET balance = balance - 500 WHERE id = 1;
UPDATE bank_accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;
SELECT * FROM bank_accounts;

+----+-------+---------+
| id | owner | balance |
+----+-------+---------+
|  1 | Alice |  500.00 |
|  2 | Bob   |  800.00 |
+----+-------+---------+

START TRANSACTION (or BEGIN) opens it. COMMIT makes both changes permanent at once. Until the commit, other sessions still see the old balances.

ROLLBACK

If something goes wrong, ROLLBACK undoes everything since the start:

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

+-------+---------+
| owner | balance |
+-------+---------+
| Alice |  300.00 |
+-------+---------+
+-------+---------+
| owner | balance |
+-------+---------+
| Alice |  500.00 |
+-------+---------+

Inside the transaction you saw the reduced balance. After the rollback it is back to normal. Applications use this on errors: if any step fails, roll back and report the problem. The CHECK constraint helps too. A transfer that would overdraw an account fails, and the rollback keeps the data consistent:

START TRANSACTION;
UPDATE bank_accounts SET balance = balance + 900 WHERE id = 2;
UPDATE bank_accounts SET balance = balance - 900 WHERE id = 1;
ROLLBACK;
SELECT * FROM bank_accounts;

ERROR 3819 (HY000): Check constraint 'bank_accounts_chk_1' is violated.
+----+-------+---------+
| id | owner | balance |
+----+-------+---------+
|  1 | Alice |  500.00 |
|  2 | Bob   |  800.00 |
+----+-------+---------+

The second UPDATE failed with a constraint error (it would have made Alice's balance negative), so we rolled back the first. Bob's extra 900 did not survive.

Autocommit

By default MySQL runs in autocommit mode: every statement is its own transaction, committed immediately. That is why a plain UPDATE cannot be undone. START TRANSACTION suspends autocommit until COMMIT or ROLLBACK. You can also turn it off for the whole session:

SELECT @@autocommit AS autocommit_is_on;

+------------------+
| autocommit_is_on |
+------------------+
|                1 |
+------------------+

Remember that closing a connection with an open transaction rolls it back. Also note that some statements, mainly DDL such as CREATE TABLE, ALTER TABLE and TRUNCATE, cause an implicit commit: they end the current transaction and cannot be rolled back.

Savepoints

A SAVEPOINT marks a point inside a transaction that you can return to without discarding everything:

START TRANSACTION;
UPDATE bank_accounts SET balance = balance + 10 WHERE id = 1;
SAVEPOINT after_bonus;
UPDATE bank_accounts SET balance = balance + 9999 WHERE id = 1;
ROLLBACK TO SAVEPOINT after_bonus;
COMMIT;
SELECT owner, balance FROM bank_accounts WHERE id = 1;

+-------+---------+
| owner | balance |
+-------+---------+
| Alice |  510.00 |
+-------+---------+

Only the second update was undone; the first was kept and committed.

Isolation levels

When several transactions run at once, what may one see of another? SQL defines four isolation levels:

A dirty read sees data another transaction has not committed. A non-repeatable read gets a different value when it reads the same row twice because someone committed in between. A phantom is a new row that appears in a repeated range query. InnoDB's default, REPEATABLE READ, gives each transaction a consistent snapshot as of its first read.

SELECT @@transaction_isolation AS isolation_level;

+-----------------+
| isolation_level |
+-----------------+
| REPEATABLE-READ |
+-----------------+

Higher isolation means more safety and less concurrency. Many applications choose READ COMMITTED for fewer locks; know what your framework sets.

Locking reads

Consider two staff who each read a stock of 10, subtract 1 and write back 9. One sale is lost. Locking the row on read prevents that:

START TRANSACTION;
SELECT balance FROM bank_accounts WHERE id = 2 FOR UPDATE;
UPDATE bank_accounts SET balance = balance - 50 WHERE id = 2;
COMMIT;

+---------+
| balance |
+---------+
|  800.00 |
+---------+

SELECT ... FOR UPDATE locks the matching rows until the transaction ends, so a second session trying to lock the same row has to wait. (FOR SHARE allows other readers but blocks writers.) You can see this by opening two terminals: run the first two statements in one, then try the same FOR UPDATE in the other and watch it wait.

The cleanest approach for simple counters is still an atomic update: SET balance = balance - 50 WHERE id = 2 AND balance >= 50.

Deadlocks

If transaction A locks row 1 then wants row 2, while transaction B locks row 2 then wants row 1, they wait for each other forever. InnoDB detects this, rolls one back and reports ERROR 1213: Deadlock found when trying to get lock; try restarting transaction. The remedies: touch rows in the same order everywhere, keep transactions short, and let your application retry on error 1213.

Good habits

• Keep transactions as short as possible. Do not wait for a user's input inside one.
• Group only statements that must succeed together.
• Handle failure: always roll back on errors.
• Do not mix long reports with updates in one transaction.

Common mistakes

• Assuming a transaction is open while autocommit has already committed each statement.
• Forgetting to COMMIT, leaving locks held and other sessions waiting.
• Putting DDL in a transaction and expecting a rollback.
• Long transactions that hold locks and bloat the undo log.
• Not retrying after a deadlock.

Exercise

Write a transaction that moves 100 from Bob to Alice, then verify the totals. Next, start a second transaction that adds 1,000,000 to Bob and roll it back, and confirm Bob's balance is unchanged.

START TRANSACTION;
UPDATE bank_accounts SET balance = balance - 100 WHERE id = 2;
UPDATE bank_accounts SET balance = balance + 100 WHERE id = 1;
COMMIT;
SELECT SUM(balance) AS total_money FROM bank_accounts;

START TRANSACTION;
UPDATE bank_accounts SET balance = balance + 1000000 WHERE id = 2;
ROLLBACK;
SELECT owner, balance FROM bank_accounts ORDER BY id;

+-------------+
| total_money |
+-------------+
|     1260.00 |
+-------------+
+-------+---------+
| owner | balance |
+-------+---------+
| Alice |  610.00 |
| Bob   |  650.00 |
+-------+---------+