Transaction isolation & concurrency anomalies

• Atomicity — all of a transaction’s changes apply, or none do (rollback undoes partial work).
• Consistency — a transaction moves the database from one valid state to another (constraints, foreign keys, invariants hold). Partly the application’s job.
• Isolation — concurrent transactions don’t see each other’s intermediate states — to the degree set by the isolation level. This is the part with trade-offs.
• Durability — once committed, data survives crashes (write-ahead log flushed to disk).

A transaction overwrites a value another transaction wrote but has not yet committed.

T1: UPDATE account SET owner = 'Alice' WHERE id = 1;   -- not committed
T2: UPDATE account SET owner = 'Bob'   WHERE id = 1;   -- overwrites T1's uncommitted write
T1: ROLLBACK;   -- roll back to what? T2's change is entangled with T1's

Dirty writes make rollback undefined and let two multi-row updates interleave into an inconsistent mix (an invoice belonging to Alice while the car belongs to Bob). Every isolation level prevents them, even READ UNCOMMITTED — the database takes a row-level write lock held until commit, so T2’s UPDATE waits for T1 to finish.

A transaction reads data another transaction has written but not committed.

T1: UPDATE account SET balance = balance - 100 WHERE id = 1;  -- not committed
T2: SELECT balance FROM account WHERE id = 1;                 -- sees the -100
T1: ROLLBACK;                                                 -- T2 acted on data that never existed

Allowed only at READ UNCOMMITTED; prevented from READ COMMITTED up. (PostgreSQL doesn’t implement READ UNCOMMITTED at all — it silently behaves as READ COMMITTED.)

Quick distinction: dirty read = reading someone’s uncommitted data; dirty write = overwriting someone’s uncommitted data.

Reading the same row twice in one transaction gives different values, because another transaction committed an update in between.

T1: SELECT price FROM product WHERE id = 7;   -- 100
T2: UPDATE product SET price = 120 WHERE id = 7; COMMIT;
T1: SELECT price FROM product WHERE id = 7;   -- 120

Allowed at READ COMMITTED (each statement sees the latest committed data); prevented by REPEATABLE READ and above.

Re-running the same range/predicate query returns a different set of rows, because another transaction committed an insert or delete matching the predicate.

T1: SELECT count(*) FROM booking WHERE room = 4 AND day = '2026-10-05';   -- 0
T2: INSERT INTO booking (room, day) VALUES (4, '2026-10-05'); COMMIT;
T1: SELECT count(*) FROM booking WHERE room = 4 AND day = '2026-10-05';   -- 1

Difference from non-repeatable read: that one is an existing row changing; a phantom is rows appearing or disappearing. Locking the rows you read can’t prevent phantoms — the row didn’t exist to be locked — so it needs predicate/range (gap) locks or snapshots. By the SQL standard only SERIALIZABLE prevents it; in practice PostgreSQL’s and MySQL’s REPEATABLE READ also prevent phantoms for plain reads via snapshots.

Two transactions each read a value, modify it in application code, and write it back; one write silently overwrites the other.

T1: SELECT stock FROM item WHERE id = 1;   -- 10
T2: SELECT stock FROM item WHERE id = 1;   -- 10
T1: UPDATE item SET stock = 9 WHERE id = 1; COMMIT;
T2: UPDATE item SET stock = 9 WHERE id = 1; COMMIT;   -- two items sold, stock dropped by 1

This is the anomaly that bites typical Spring/JPA code (load entity → change field → commit) at the default READ COMMITTED. Prevention:

1. Atomic update in SQL — UPDATE item SET stock = stock - 1 WHERE id = 1 AND stock > 0.
2. Optimistic locking — JPA @Version; the second commit fails and can retry.
3. Pessimistic locking — SELECT … FOR UPDATE (@Lock(PESSIMISTIC_WRITE)); the second reader waits.
4. Higher isolation — PostgreSQL REPEATABLE READ detects it and aborts the second transaction (“could not serialize access due to concurrent update”). MySQL InnoDB’s REPEATABLE READ does not — plain UPDATEs act on the latest committed row.

Read skew — a transaction sees parts of the database at different points in time: it reads account A, another transaction moves 100 from A to B and commits, then it reads B — the total appears 100 short. Prevented by snapshot-based REPEATABLE READ.

Write skew — two transactions read the same set of rows, each checks an invariant, then each writes different rows, and together they break the invariant.

Rule: at least one doctor must be on call.
T1: SELECT count(*) FROM doctor WHERE on_call;  -- 2 → ok for me to leave
T2: SELECT count(*) FROM doctor WHERE on_call;  -- 2 → ok for me to leave
T1: UPDATE doctor SET on_call = false WHERE name = 'Alice'; COMMIT;
T2: UPDATE doctor SET on_call = false WHERE name = 'Bob';   COMMIT;  -- 0 on call

No row was written by both, so row locks and snapshot isolation don’t catch it. Only SERIALIZABLE (e.g. PostgreSQL’s Serializable Snapshot Isolation) prevents it in general — or explicitly locking the rows the decision is based on (SELECT … FOR UPDATE), or materialising the conflict into a row/constraint both transactions must touch.

Level	Dirty write	Dirty read	Non-repeatable read	Phantom	Lost update	Write skew
READ UNCOMMITTED	prevented	possible	possible	possible	possible	possible
READ COMMITTED	prevented	prevented	possible	possible	possible	possible
REPEATABLE READ	prevented	prevented	prevented	possible (std) *	DB-dependent *	possible
SERIALIZABLE	prevented	prevented	prevented	prevented	prevented	prevented

* Implementation-dependent: PostgreSQL’s REPEATABLE READ is snapshot isolation (no phantoms for reads, lost updates detected); MySQL InnoDB’s prevents phantoms via snapshots and next-key locks but doesn’t detect lost updates.

Defaults: PostgreSQL, Oracle and SQL Server → READ COMMITTED; MySQL InnoDB → REPEATABLE READ. In Spring: @Transactional(isolation = Isolation.REPEATABLE_READ); Isolation.DEFAULT uses the database’s.

Higher isolation costs throughput: more blocking (lock-based) or more aborted transactions (snapshot/SSI-based) that the application must retry — serialization failures are an expected outcome, not a bug.

• Lock-based (two-phase locking) — readers take shared locks, writers exclusive locks; readers and writers block each other. Classic SQL Server (without snapshot options).
• MVCC (multi-version concurrency control) — writers create a new version of the row; readers see a consistent snapshot of committed versions, so readers never block writers and writers never block readers (writers still block writers on the same row). PostgreSQL, Oracle, MySQL InnoDB.

Under MVCC, READ COMMITTED takes a fresh snapshot per statement, REPEATABLE READ takes one snapshot per transaction (at the first statement). The cost is old row versions to clean up (PostgreSQL VACUUM, InnoDB undo-log purge) — a long-running transaction keeps old versions alive and bloats tables.

T1 locks row A then wants B; T2 locks B then wants A. The database detects the cycle and aborts one transaction with a deadlock error (Spring: CannotAcquireLockException / DeadlockLoserDataAccessException). Mitigations: acquire locks in a consistent order (e.g. sort by id before updating multiple rows), keep transactions short, add proper indexes (an unindexed UPDATE … WHERE may lock far more rows than intended), and retry the aborted transaction.

A transaction owns one database connection for its entire duration. With HikariCP’s default pool of 10 connections, ten requests sitting in transactions that wait on a slow HTTP call block every other request (Connection is not available, request timed out after 30000ms). Hence: keep transactions short, never do remote calls inside them, beware REQUIRES_NEW (holds two connections per request), and be aware that OSIV holds the connection until the response is written. Pool size should match database capacity, not thread count — a small pool is often faster than a large one.