Transactions, ACID, isolation and locks

40 minIntermediate

Transactions, ACID, isolation and locks

Two cashiers sell the last two cartons at the same second. Both sales succeed, four cartons leave the shelf, and the stock count says zero when it should say minus two. This lesson shows exactly how that happens, and the small number of patterns that stop it.

By the end you can

Explain atomicity, consistency, isolation and durability in plain words
Name the anomalies each isolation level allows, with a shop example for each
Fix a lost update with an atomic conditional UPDATE, a row lock, or a version column
Write a Prisma interactive transaction with an isolation level and a safe retry
Avoid deadlocks and the long transactions that cause outages

The everyday picture

You send $20 from your mobile wallet (M-Pesa, UPI, bKash, GCash or similar) to a friend. Two things must happen: $20 leaves your wallet, and $20 arrives in hers. If the app crashed between the two steps, you would lose money that nobody received. So the wallet treats the transfer as one unit. Either both steps happen, or neither does. If the network drops halfway, the whole transfer is cancelled and your balance is untouched.

Now picture two transfers from your wallet at the same moment, one from your phone and one from your laptop, each for $20, with only $30 in the wallet. Each one checks "is there enough?" and sees $30. If both go through, the wallet goes negative. The system must make the second transfer wait for the first, then check again.

A database transaction gives you both guarantees. It is all or nothing, and it has a defined answer for two transactions running at once.

ACID in plain words

Atomicity: all or nothing. The order row and the stock change are saved together. If any statement fails, or the app crashes before COMMIT, Postgres undoes everything in the transaction.
Consistency: the rules hold. Every constraint from Modeling data in tables (CHECK (qty >= 0), foreign keys, UNIQUE) is true before and after every transaction. A transaction that would break one is rejected.
Isolation: concurrent transactions do not trip over each other. How strictly this holds depends on the isolation level, and this is where most real bugs live.
Durability: committed means saved. Once COMMIT returns, the change survives a crash or power cut, because Postgres wrote it to its log on disk first (Replication and read replicas explains that log, the WAL).

In SQL a transaction looks like this (the partner_order table is created in the Build it section). Every statement you send outside BEGIN is its own one-statement transaction.

SQL

BEGIN;
UPDATE stock SET qty = qty - 2 WHERE site_id = 3 AND article_id = 1;
INSERT INTO partner_order (site_id, article_id, qty) VALUES (1, 1, 2);
COMMIT;      -- both changes become visible together; ROLLBACK would undo both

Quick check

The app runs the UPDATE stock above, then the server crashes before the INSERT and the COMMIT. What is in the database after the restart?

Atomicity means nothing from a transaction counts until COMMIT returns. Postgres discards the half-finished work, so stock and orders still agree. Without BEGIN, the UPDATE would have been its own committed transaction, and you would get the first answer.