Transactions and locking

Why it matters

A database serves many users at once. Two people buy the last ticket, two jobs update the same account balance, a report reads a table while it is being changed. Transactions and locks decide what each of them sees and who waits. When they are misunderstood you get lost updates, impossible report totals, lock pile-ups and deadlock errors, which are some of the hardest production incidents to explain.

Transactions and ACID

A transaction groups statements into one unit:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- or ROLLBACK; to undo both

ACID describes the guarantees:

• Atomicity: all statements take effect, or none do.
• Consistency: constraints hold before and after.
• Isolation: concurrent transactions do not see each other's half-finished work (to a configurable degree).
• Durability: once committed, the change survives a crash (it has been written to the write-ahead log).

Isolation levels and anomalies

Full isolation is expensive, so SQL defines levels that allow some anomalies:

• Dirty read: you see another transaction's uncommitted change, which may be rolled back.
• Non-repeatable read: you read a row twice in one transaction and get different values, because someone committed in between.
• Phantom read: you run the same WHERE twice and new rows appear.
• Lost update / write skew: two transactions read, then both write based on what they read, and one effect is lost or a rule across rows is broken.

Real engines differ from the table. PostgreSQL defaults to read committed, treats read uncommitted as read committed, and its repeatable read (snapshot isolation) also prevents phantoms; serializable adds checks that abort one transaction with SQLSTATE 40001 if the result could not have happened serially. MySQL InnoDB defaults to repeatable read. SQL Server defaults to read committed with locking unless snapshot options are enabled. Always check the documentation for your engine and version.

How it works: MVCC and locks

PostgreSQL, MySQL InnoDB and Oracle use MVCC (multi-version concurrency control): an update writes a new version of the row, and each transaction reads the versions visible to its snapshot. The big benefit is that readers do not block writers and writers do not block readers.

Writers still block other writers on the same row. When transaction A updates row 1, transaction B's update of row 1 waits until A commits or rolls back. You can take the lock deliberately when you read:

BEGIN;
SELECT stock FROM tickets WHERE id = 7 FOR UPDATE;  -- lock the row
-- check stock > 0 in the app, then:
UPDATE tickets SET stock = stock - 1 WHERE id = 7;
COMMIT;

Without FOR UPDATE (or a single atomic UPDATE ... SET stock = stock - 1 WHERE id = 7 AND stock > 0), two buyers at read committed can both read stock = 1 and both sell the last ticket. Schema changes take stronger table-level locks; that is covered in the migrations lesson.

Deadlocks

A deadlock happens when two transactions each hold a lock the other needs:

T1: UPDATE accounts ... WHERE id = 1;   -- T1 locks row 1
T2: UPDATE accounts ... WHERE id = 2;   -- T2 locks row 2
T1: UPDATE accounts ... WHERE id = 2;   -- T1 waits for T2
T2: UPDATE accounts ... WHERE id = 1;   -- T2 waits for T1: deadlock

Neither can proceed, so the database detects the cycle and aborts one of them. PostgreSQL checks after deadlock_timeout (1 second by default) and raises SQLSTATE 40P01; MySQL reports error 1213. The survivor continues normally.

How to reduce deadlocks:

• Lock in a consistent order everywhere, for example always update the lower account id first.
• Keep transactions short: no user think-time or network calls inside BEGIN ... COMMIT.
• Index the columns used in UPDATE ... WHERE so fewer rows are locked.
• Retry the aborted transaction in application code. Deadlocks and serialization failures are expected events, not bugs, when retried safely.

What a DBA watches

Long-running and "idle in transaction" sessions hold locks and stop cleanup. In PostgreSQL, pg_stat_activity shows them and pg_locks with pg_blocking_pids(pid) shows who blocks whom. Settings such as idle_in_transaction_session_timeout, lock_timeout and statement_timeout put limits on the damage.

Common mistakes

• Assuming the default isolation level is serializable.
• Read-then-write logic without a lock or an atomic update (lost updates).
• Long transactions that wrap slow work, holding locks for minutes.
• Not retrying on 40001 / 40P01.

Quick recap

• Transactions give ACID; isolation levels trade anomalies for concurrency, and engines implement them differently.
• MVCC lets readers and writers avoid blocking each other; writers on the same row still queue.
• Deadlocks are detected and one transaction is aborted. Consistent lock order, short transactions and retries keep them rare and harmless.

Try it

The Do tab quiz walks through isolation and deadlock scenarios.