Transactions and isolation

A backend serves many requests at once. When two of them touch the same data, the order of their reads and writes decides whether your data stays correct. Transactions are the database’s tool for that.

ACID in one line each

Atomicity: all statements in a transaction take effect, or none do.
Consistency: constraints (foreign keys, unique, check) hold before and after.
Isolation: concurrent transactions don’t see each other’s half-finished work.
Durability: once committed, data survives a crash.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

If the server dies between the two updates, atomicity guarantees that neither is kept.

Isolation levels

Full isolation is expensive, so databases offer levels that allow some anomalies in exchange for speed.

Level	Prevents	Still allows
Read uncommitted	little in practice	dirty reads
Read committed	dirty reads	non-repeatable reads, lost updates, phantoms
Repeatable read	dirty and non-repeatable reads	write skew; lost updates and phantoms in some databases
Serializable	all of the above	nothing: behaves as if transactions ran one at a time

Behaviour at the same level differs between databases. PostgreSQL’s repeatable read aborts one of two conflicting updates; MySQL’s lets a read-then-write lost update through.

PostgreSQL defaults to read committed; MySQL’s InnoDB defaults to repeatable read. Many bugs come from assuming a stronger level than you actually have.

The lost update

The classic concurrency bug: read a value, change it in application code, write it back.

// Two requests run this at the same time for the same product
const { stock } = await db.get("SELECT stock FROM products WHERE id = ?", id);
await db.run("UPDATE products SET stock = ? WHERE id = ?", stock - 1, id);

Both read stock = 10, both write 9. One sale vanished. Three fixes, from simplest:

Let the database do the arithmetic. UPDATE products SET stock = stock - 1 WHERE id = ? AND stock > 0 is atomic, and the stock > 0 guard prevents overselling.
Pessimistic locking. SELECT ... FOR UPDATE inside a transaction locks the row until you commit. Others wait.
Optimistic locking. Keep a version column and make the write conditional:

UPDATE products SET stock = 9, version = version + 1
WHERE id = 42 AND version = 7;

If another request already bumped the version, zero rows change. The application notices and retries or reports a conflict. This avoids holding locks and suits workloads where conflicts are rare.

Write skew

Two transactions each read overlapping data, make decisions based on it, and write different rows. Example: a rule says at least one doctor must be on call. Two doctors both check “someone else is on call”, both go off call, and now nobody is. No single row was updated twice, so repeatable read doesn’t catch it. Serializable isolation does, as does an explicit lock or a constraint that encodes the rule.

Keep transactions short

A transaction holds locks and resources until it ends. Never wait for a network call, a user, or a slow external API inside one. Do slow work first, then open the transaction, write, and commit.

Unique constraints are concurrency control too

To stop duplicate sign-ups for the same email, a “check then insert” in code has the same race as the lost update. A UNIQUE constraint makes the database reject the second insert, however the requests interleave. Catch the constraint error and turn it into a friendly message.