SQL Transactions: ACID Properties, Isolation Levels, and Deadlock Prevention
Which anomaly each isolation level still allows, why READ COMMITTED permits lost updates, and the lock ordering plus retry loop every deadlock needs.

Quick Summary
A transaction groups statements so they commit together or not at all, and the isolation level decides what concurrent transactions may see of each other while they run. ACID is the promise: atomicity (all or nothing), consistency (constraints hold at commit), isolation (levels below), durability (once committed, survives a crash).
Most engines default to a level weaker than "serializable", so the interesting work is knowing which anomaly your level still permits. PostgreSQL defaults to READ COMMITTED, MySQL/InnoDB to REPEATABLE READ, SQL Server to READ COMMITTED, Oracle to READ COMMITTED with statement-level snapshots. At every level, a deadlock is possible, and every deadlock must be handled by retrying the transaction — not by adding WITH (NOLOCK) and hoping.
Basic syntax and query signatures
BEGIN; -- or START TRANSACTION
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- or ROLLBACK-- Pessimistic lock: hold the row until commit
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- Skip locked rows instead of queueing behind them
SELECT id FROM jobs WHERE state = 'pending' ORDER BY run_at LIMIT 10 FOR UPDATE SKIP LOCKED;The four standard levels are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. SET TRANSACTION applies to the next transaction; SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL ... changes the default for the session. FOR UPDATE needs a transaction — outside one, it locks for microseconds and proves nothing.
Key comparison table
| Isolation level | Dirty read | Non-repeatable read | Phantom read | Write skew | Typical engine default |
|---|---|---|---|---|---|
READ UNCOMMITTED | Possible | Possible | Possible | Possible | Rarely used (SQL Server hint) |
READ COMMITTED | No | Possible | Possible | Possible | PostgreSQL, Oracle, SQL Server |
REPEATABLE READ | No | No | Possible (standard) / prevented in InnoDB | Possible | MySQL InnoDB |
SERIALIZABLE | No | No | No | No | Opt-in; may abort transactions |
| Symptom | What happened | Correct response |
|---|---|---|
deadlock detected (40P01 / MySQL 1213 / SQL Server 1205) | Lock cycle; engine killed one transaction | Retry the whole transaction with backoff |
could not serialize access due to concurrent update (40001) | Serializable conflict | Retry — this is the level working as designed |
canceling statement due to lock timeout (55P03) | lock_timeout elapsed | Investigate the blocker; retry or fail fast |
| Balance changes lost with no error | Read-modify-write race | SELECT ... FOR UPDATE, or a version column |
| Two transactions both "succeeded", invariant broken | Write skew | Raise isolation, or lock the rows the invariant reads |
Practical edge cases and deep dives
1. READ COMMITTED is per statement, not per transaction
BEGIN; -- READ COMMITTED
SELECT balance FROM accounts WHERE id = 1; -- 100
-- another session commits balance = 50 here
SELECT balance FROM accounts WHERE id = 1; -- 50, same transaction
COMMIT;Each statement gets a fresh snapshot, so the same query can return different values inside one transaction. Code that reads a value, decides, and writes later is therefore exposed to a lost update: two workers read 100, both subtract 10, both write 90, and a 20-unit debit becomes 10. Two correct fixes:
-- 1. Atomic update: no read, no window
UPDATE accounts SET balance = balance - 10 WHERE id = 1 AND balance >= 10;
-- 2. Lock the row for the duration of the decision
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
COMMIT;The atomic UPDATE is better whenever the decision can be expressed as a predicate, because it fails by affecting zero rows instead of by blocking. The lock is better when the decision requires application logic that cannot be pushed into SQL.
2. Write skew: the anomaly REPEATABLE READ does not stop
Two doctors are on call. Each transaction checks "are there at least two doctors on call?" and then removes itself. Both see two, both remove one, and nobody is on call — with no deadlock and no constraint violated at commit time.
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM shifts WHERE on_call AND day = DATE '2026-11-14'; -- both see 2
UPDATE shifts SET on_call = false WHERE doctor_id = 42;
COMMIT;Only SERIALIZABLE (PostgreSQL's SSI, or MySQL's locking reads) aborts one of them. The alternative is to materialise the invariant as a row that both transactions must update — a day_on_call row with a count column — which turns write skew into a lock conflict the engine can see:
UPDATE day_on_call SET on_call_count = on_call_count - 1
WHERE day = DATE '2026-11-14' AND on_call_count > 1;That pattern generalises: if an invariant spans rows, give it a row to lock.
3. Deadlocks: order, then retry
-- Transaction A -- Transaction B
BEGIN; BEGIN;
UPDATE accounts SET ... WHERE id=1; UPDATE accounts SET ... WHERE id=2;
UPDATE accounts SET ... WHERE id=2; UPDATE accounts SET ... WHERE id=1;
COMMIT; COMMIT;A waits for B's lock on row 2, B waits for A's lock on row 1, and the engine kills one after deadlock_timeout. The structural fix is a canonical lock order — sort the ids and always update in ascending order:
UPDATE accounts SET balance = balance + delta
WHERE id = ANY(ARRAY[1, 2]::int[])
ORDER BY id; -- PostgreSQL: deterministic row orderNot every dialect supports ORDER BY in UPDATE, so the portable version takes the locks first in sorted order (SELECT ... WHERE id IN (1,2) ORDER BY id FOR UPDATE) and then updates. No ordering discipline removes deadlocks entirely, so the retry loop is part of the design:
for attempt in range(5):
try:
with conn.transaction():
do_work()
break
except (DeadlockDetected, SerializationFailure) as e: # 40P01, 40001
time.sleep((2 ** attempt) * 0.1 + random.random() * 0.1)Retry only errors that are safe to retry: deadlock (40P01), serialization failure (40001), and lock timeout (55P03) when the timeout was your own. Retrying a unique-violation error just burns time — that one needs a different decision.
4. Queue processing with SKIP LOCKED
BEGIN;
SELECT id FROM jobs
WHERE state = 'pending'
ORDER BY run_at
LIMIT 10
FOR UPDATE SKIP LOCKED;
-- process, then
UPDATE jobs SET state = 'done' WHERE id = ANY($1);
COMMIT;SKIP LOCKED lets ten workers each claim different rows instead of serialising behind the first one. Without it, every worker queues on the same first row and the queue throughput collapses to one worker. The transaction must stay short: while it runs, the claimed rows are locked and invisible to other workers, so a slow external call inside the transaction holds ten jobs hostage.
5. Long transactions cost more than they lock
An idle in transaction session blocks vacuum from cleaning dead tuples across the whole database:
SELECT pid, state, now() - xact_start AS age, left(query, 60)
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY age DESC
LIMIT 10;
-- Never let a stuck transaction sit for hours
SET statement_timeout = '30s';
SET lock_timeout = '5s';
SET idle_in_transaction_session_timeout = '60s';lock_timeout fails fast instead of waiting behind a lock for the default (forever, in PostgreSQL). statement_timeout bounds runaway queries. These three settings convert "the site hung for twenty minutes" into an error your code can retry, which is a much cheaper failure.
Common mistakes and runtime errors
| Error or symptom | Cause | Fix |
|---|---|---|
current transaction is aborted, commands ignored until end of transaction block (25P02) | A statement failed and you kept issuing commands | ROLLBACK, or guard each statement with a SAVEPOINT |
deadlock detected (40P01 / 1213 / 1205) | Lock cycle | Canonical lock order + retry with backoff |
could not serialize access due to concurrent update (40001) | Serializable conflict | Retry the whole transaction |
canceling statement due to lock timeout (55P03) | Waited past lock_timeout | Find the blocker (pg_locks), then retry |
duplicate key value violates unique constraint after a retry loop | Retrying a non-transient error | Do not retry unique violations; resolve the data |
| DDL inside a transaction committed it silently | MySQL implicit commit on CREATE/ALTER/DROP | Keep DDL in its own migration step |
FOR UPDATE cannot be applied to the nullable side of an outer join | Locking through a LEFT JOIN | Lock only the table you need, in a separate statement |
| Locks held for minutes with no active query | idle in transaction leaked connection | Close transactions in finally; set idle_in_transaction_session_timeout |
too many clients already | Connection leak or pool sized above max_connections | Pool at the database's limit, not per instance count |
NOLOCK "fixes" a blocking problem and returns wrong numbers | Dirty reads | Fix the transaction length instead |
Durability has its own tier of surprises: COMMIT on PostgreSQL waits for the WAL flush unless synchronous_commit = off, and setting it off for bulk loads is a legitimate trade that becomes data loss on power failure. Know which setting is in force before claiming a commit is durable.
Try matching browser tools
All toolsSmall utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related SQL guides
All SQL guidesSQL Cursors vs Set-Based Operations: Syntax, Memory, and Performance
Why a row-by-row cursor costs minutes where one UPDATE takes seconds, how server cursors spool to disk, and the set-based replacements for each loop.
SQL String Functions: CONCAT, SUBSTRING, REPLACE, and Pattern Matching
The concatenation and slice functions that differ between engines, why LIKE 'x%' uses an index and '%x%' cannot, plus escaping and NULL rules.
SQL Date and Time Functions: INTERVAL Arithmetic, Time Zones, and DST
timestamptz versus timestamp, half-open ranges that keep an index, and the daylight saving gaps that make local scheduling wrong once a year.
SQL Row Locking: SELECT FOR UPDATE, SKIP LOCKED, and NOWAIT
How FOR UPDATE locks rows until commit, why the plan decides the lock scope, and the SKIP LOCKED claim-queue pattern that scales to many workers.
More in SQL
All SQL guidesThe guides filed next to this one in SQL, in library order.