Transactions, Isolation, and Locking

Why transactions exist#

Moving money means two updates: subtract here, add there. If the server dies in between, you've destroyed money. A transaction groups statements so they either all happen or none do.

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

By default, MySQL runs in autocommit mode: each statement is its own transaction. START TRANSACTION suspends that until COMMIT or ROLLBACK.

ACID#

• Atomicity: all or nothing (undo log).
• Consistency: constraints hold before and after.
• Isolation: concurrent transactions don't see each other's half-finished work.
• Durability: committed data survives a crash (redo log).

Savepoints#

START TRANSACTION;
INSERT INTO orders (customer_id) VALUES (1);
SAVEPOINT after_order;
INSERT INTO order_items (order_id, product_id, qty, unit_price) VALUES (LAST_INSERT_ID(), 99, 1, 5);
-- product 99 doesn't exist, FK fails
ROLLBACK TO SAVEPOINT after_order;
COMMIT;

You can undo part of a transaction and keep the rest.

Isolation levels#

InnoDB's default REPEATABLE READ gives each transaction a consistent snapshot, via MVCC (multi-version concurrency control). Your plain SELECTs read the snapshot from your first read, even if others commit changes meanwhile.

T1: BEGIN; SELECT stock → 10
T2:        UPDATE stock = 5; COMMIT
T1: SELECT stock → 10   (still the snapshot)
T1: COMMIT;  SELECT → 5

Locking reads#

START TRANSACTION;
SELECT stock FROM products WHERE id = 3 FOR UPDATE;
-- row is now locked, other writers wait
UPDATE products SET stock = stock - 1 WHERE id = 3;
COMMIT;

FOR UPDATE takes an exclusive row lock. Without it, two sessions can both read 1 unit in stock and both sell it. Locks are held until the transaction ends, so keep transactions short, and never wait on a user or an HTTP call while holding one.

Under REPEATABLE READ, InnoDB also takes gap locks on ranges, which can block inserts into a range you searched. That's a surprise for many people, and a reason some teams use READ COMMITTED.

Deadlocks#

T1: lock row A ──▶ wants row B
T2: lock row B ──▶ wants row A     ◀ circular wait

InnoDB detects the cycle and kills one transaction with error 1213. That's normal behavior, not a crash. Your application must catch it and retry. Reduce them by touching rows in a consistent order and keeping transactions small. SHOW ENGINE INNODB STATUS\G shows the latest deadlock.

Common mistakes#

• Leaving a transaction open (forgot COMMIT) and blocking others.
• Read, check, write in app code without FOR UPDATE.
• Not retrying on deadlock.
• Mixing in non-transactional tables like MyISAM.

Practice#

Open two terminals. In one, START TRANSACTION; SELECT ... FOR UPDATE on a product, and in the other try to update it, then watch it wait. Then trigger a deadlock by updating two rows in opposite order from each session, and read the result in SHOW ENGINE INNODB STATUS.