HowToCodePage

Transactions and isolation levels

BEGIN, COMMIT and ROLLBACK, what ACID actually promises, the read phenomena isolation levels prevent, and how to recognise a deadlock before your users report it.

Transactions and ACID

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- or ROLLBACK to undo every statement in the block

-- savepoints let you undo part of a transaction
BEGIN;
  INSERT INTO audit (note) VALUES ('start');
  SAVEPOINT after_audit;
  UPDATE accounts SET balance = -1 WHERE id = 1;   -- violates a CHECK
  ROLLBACK TO SAVEPOINT after_audit;               -- the audit row survives
COMMIT;
PropertyMeansCommonly lost to
AtomicityEvery statement commits, or none doesA crash midway through a non-transactional batch
ConsistencyConstraints hold before and afterDeferred checks that never run, or validation done only in the application
IsolationConcurrent transactions do not see each other's half-finished workReading uncommitted rows, or read-modify-write without a lock
DurabilityCommitted data survives a crashDisabling fsync for speed, or a replica that has not caught up

Every single statement you write is already a transaction unless you opened one — the implicit commit is why an UPDATE without a WHERE cannot be talked back.

Isolation levels and read phenomena

LevelDirty readNon-repeatable readPhantom read
READ UNCOMMITTEDPossiblePossiblePossible
READ COMMITTEDNoPossiblePossible
REPEATABLE READNoNoPossible in the standard; InnoDB prevents most
SERIALIZABLENoNoNo
  • A dirty read sees another transaction's uncommitted change — the one phenomenon every engine forbids by default.
  • A non-repeatable read is reading the same row twice and getting different values because someone committed in between.
  • A phantom read is running the same range query twice and getting new rows the second time.
  • Defaults differ: PostgreSQL and Oracle are READ COMMITTED, MySQL InnoDB is REPEATABLE READ, SQLite is SERIALIZABLE.
-- per transaction, not globally
BEGIN ISOLATION LEVEL REPEATABLE READ;
  SELECT * FROM accounts WHERE id = 1;   -- the same answer even after another commit
COMMIT;

-- SERIALIZABLE turns anomalies into errors, so it needs a retry loop
BEGIN ISOLATION LEVEL SERIALIZABLE;
  UPDATE counters SET n = n + 1 WHERE id = 1;
COMMIT;   -- may fail with a serialization error: catch it and run the block again
⚠️
Stronger isolation is not a free upgrade: it converts silent wrong answers into explicit errors that your code must retry. Any SERIALIZABLE transaction, and any read-modify-write without an atomic UPDATE, needs a bounded retry loop or it will fail under real concurrency.

Locking and deadlocks

-- transaction A                      -- transaction B
BEGIN;                                 BEGIN;
UPDATE accounts SET balance = 0
  WHERE id = 1;                        UPDATE accounts SET balance = 0
                                         WHERE id = 2;
                                       UPDATE accounts SET balance = 0
                                         WHERE id = 1;   -- waits for A
UPDATE accounts SET balance = 0
  WHERE id = 2;                        -- now neither can proceed: deadlock

-- take the row you are about to change
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- queue workers: claim rows nobody else holds
SELECT id FROM jobs WHERE status = 'queued'
ORDER BY created_at
LIMIT 10
FOR UPDATE SKIP LOCKED;
  • Acquire locks in a consistent order — most deadlocks are two transactions disagreeing about the order in which to take the same rows.
  • Keep transactions short: never wait on a network call, a file upload or a user click inside one, because every lock is held for the whole wait.
  • Lock only the rows you will change; a wide FOR UPDATE blocks unrelated work and is the usual cause of sudden timeouts.
  • SKIP LOCKED is the standard way to build a queue without two workers taking the same job.

FAQ

Which isolation level should I use?
Start with your engine's default. Raise it for the specific transactions that read data and then write based on it, and add a retry loop at the same time — higher isolation reports conflicts as errors rather than hiding them.
How do I deal with a long-running transaction?
Find it in the engine's activity view (pg_stat_activity, SHOW PROCESSLIST, sys.dm_exec_sessions) and terminate it. Prevention is better: set a statement_timeout and an idle_in_transaction_session_timeout so nothing holds locks indefinitely.

Query tuning and execution plans Views, functions, procedures and triggers

Last refreshed 2026-09-18.