ACID, isolation and locking

ACID with the last Tatkal berth

At 10 a.m. thousands of people try to book the same few Tatkal berths. A booking system like this must guarantee four things, known together as ACID.

What goes wrong without isolation

When transactions run at the same time, four classic problems, called anomalies, can appear:

• Dirty read: you read a change that another transaction has not committed yet, and it later rolls back. You acted on data that never existed.
• Non-repeatable read: you read a row twice in one transaction and get different values, because someone committed an update in between.
• Phantom read: you run the same WHERE twice and get a new row the second time, because someone inserted a matching row.
• Lost update: two transactions read the same value, both compute a new one, and the second write overwrites the first. Two people read seats_left = 1, both write 0, and two tickets are sold for one seat.

Isolation levels

Full isolation costs speed, so SQL defines four isolation levels. Each one stops more anomalies than the one before.

Defaults differ: PostgreSQL, SQL Server and Oracle use READ COMMITTED; MySQL with InnoDB uses REPEATABLE READ. PostgreSQL treats READ UNCOMMITTED as READ COMMITTED, and its REPEATABLE READ also blocks phantoms. SQLite allows only one writer at a time, so its transactions behave as SERIALIZABLE. At SERIALIZABLE, PostgreSQL may cancel one of two clashing transactions with a "could not serialize access" error, and the app must simply retry it.

-- PostgreSQL
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- ... statements ...
COMMIT;

Locks and SELECT ... FOR UPDATE

Databases protect rows with locks. A row lock taken by an UPDATE makes other writers of the same row wait until the first transaction commits or rolls back. When you must read a row and then decide, lock it while reading with SELECT ... FOR UPDATE (PostgreSQL, MySQL, Oracle). SQLite has no FOR UPDATE; BEGIN IMMEDIATE takes the write lock for the whole database instead. SQL Server uses table hints such as WITH (UPDLOCK).

-- PostgreSQL / MySQL
BEGIN;
SELECT status FROM berths WHERE id = 2 FOR UPDATE;   -- other bookers wait here
UPDATE berths SET status = 'Booked', booked_by = 'Riya' WHERE id = 2;
COMMIT;

Even simpler: make the check part of the UPDATE itself. UPDATE quota SET seats_left = seats_left - 1 WHERE train_no = '12951' AND class = '3A' AND seats_left > 0 is one atomic statement. If it changes 0 rows, the seat is gone.

Optimistic locking with a version column

Locks are pessimistic: they assume a clash and make others wait. Web apps often use optimistic locking instead. Each row has a version number. The app reads the row and its version, and later updates only if the version has not changed, bumping it at the same time.

UPDATE berths
SET status = 'Booked', booked_by = 'Riya', version = version + 1
WHERE id = 2 AND version = 2;
-- changes() = 1: Riya wins, version is now 3
-- Sameer's identical update with version = 2 now changes 0 rows: "Sorry, just booked"

Deadlocks

A deadlock happens when transaction A holds a lock that B needs while B holds a lock that A needs; both wait forever. Databases detect this and cancel one of them (PostgreSQL reports "deadlock detected"). Prevent most deadlocks by always locking rows in the same order, for example the lower account id first, and by keeping transactions short. The app should retry a transaction cancelled by a deadlock.

Common mistakes

• Read-modify-write in app code (read seats_left, subtract 1 in Python, write it back) without a lock or a version check. Use an atomic UPDATE, FOR UPDATE or a version column.
• Not checking the row count of an optimistic update. 0 rows means someone else won; tell the user and reload.
• Assuming every engine has the same default isolation level. Check it.
• Raising everything to SERIALIZABLE without retry logic. Expect serialization errors and retry.
• Locking rows in a different order in different code paths, which invites deadlocks.