Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

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.

~5 min readSQL
#SQL#Transactions#Concurrency
3D illustration of atomic transaction capsules protected by escalating isolation level vault walls with deadlock detection monitors

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

sql
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
sql
-- 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 levelDirty readNon-repeatable readPhantom readWrite skewTypical engine default
READ UNCOMMITTEDPossiblePossiblePossiblePossibleRarely used (SQL Server hint)
READ COMMITTEDNoPossiblePossiblePossiblePostgreSQL, Oracle, SQL Server
REPEATABLE READNoNoPossible (standard) / prevented in InnoDBPossibleMySQL InnoDB
SERIALIZABLENoNoNoNoOpt-in; may abort transactions
SymptomWhat happenedCorrect response
deadlock detected (40P01 / MySQL 1213 / SQL Server 1205)Lock cycle; engine killed one transactionRetry the whole transaction with backoff
could not serialize access due to concurrent update (40001)Serializable conflictRetry — this is the level working as designed
canceling statement due to lock timeout (55P03)lock_timeout elapsedInvestigate the blocker; retry or fail fast
Balance changes lost with no errorRead-modify-write raceSELECT ... FOR UPDATE, or a version column
Two transactions both "succeeded", invariant brokenWrite skewRaise isolation, or lock the rows the invariant reads

Practical edge cases and deep dives

1. READ COMMITTED is per statement, not per transaction

sql
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:

sql
-- 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.

sql
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:

sql
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

sql
-- 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:

sql
UPDATE accounts SET balance = balance + delta WHERE id = ANY(ARRAY[1, 2]::int[]) ORDER BY id; -- PostgreSQL: deterministic row order

Not 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:

python
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

sql
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:

sql
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 symptomCauseFix
current transaction is aborted, commands ignored until end of transaction block (25P02)A statement failed and you kept issuing commandsROLLBACK, or guard each statement with a SAVEPOINT
deadlock detected (40P01 / 1213 / 1205)Lock cycleCanonical lock order + retry with backoff
could not serialize access due to concurrent update (40001)Serializable conflictRetry the whole transaction
canceling statement due to lock timeout (55P03)Waited past lock_timeoutFind the blocker (pg_locks), then retry
duplicate key value violates unique constraint after a retry loopRetrying a non-transient errorDo not retry unique violations; resolve the data
DDL inside a transaction committed it silentlyMySQL implicit commit on CREATE/ALTER/DROPKeep DDL in its own migration step
FOR UPDATE cannot be applied to the nullable side of an outer joinLocking through a LEFT JOINLock only the table you need, in a separate statement
Locks held for minutes with no active queryidle in transaction leaked connectionClose transactions in finally; set idle_in_transaction_session_timeout
too many clients alreadyConnection leak or pool sized above max_connectionsPool at the database's limit, not per instance count
NOLOCK "fixes" a blocking problem and returns wrong numbersDirty readsFix 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 tools

Small utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.

All SQL guides

More in SQL

All SQL guides

The guides filed next to this one in SQL, in library order.