Transactions, Isolation and Locking
Know what a transaction guarantees in PostgreSQL, how each isolation level behaves with real two-session timelines, and how to use locks, savepoints and retries to keep concurrent writes correct.
Key points
- 1
Without BEGIN every statement is its own transaction (autocommit). Wrap related changes in BEGIN … COMMIT to make them atomic.
- 2
Any error inside a PostgreSQL transaction aborts the whole block (SQLSTATE 25P02) until ROLLBACK or ROLLBACK TO SAVEPOINT; a COMMIT then rolls back.
- 3
DDL is transactional in PostgreSQL (CREATE, ALTER, DROP, TRUNCATE can be rolled back); MySQL commits implicitly around DDL.
- 4
READ COMMITTED (the default) takes a new snapshot per statement; a waiting UPDATE re-checks its WHERE and applies its SET to the latest committed row version.
- 5
REPEATABLE READ is snapshot isolation: the snapshot starts at the first query, phantoms are not visible, same-row conflicts raise 40001, and write skew is still possible.
- 6
SERIALIZABLE (SSI) aborts dangerous read/write patterns with 40001, sometimes as false positives, so the whole transaction must be retried.
- 7
FOR UPDATE / FOR NO KEY UPDATE / FOR SHARE / FOR KEY SHARE lock rows; SKIP LOCKED builds job queues and NOWAIT fails fast. Plain SELECTs never wait on row locks.
Common traps
Reading a value into the application and writing back a computed literal loses updates; use
SET n = n + 1, a row lock, or a version check.An isolation level can only be set before the transaction's first query, and READ UNCOMMITTED behaves as READ COMMITTED in PostgreSQL.
Long idle-in-transaction sessions, forgotten prepared transactions and DDL waiting for locks can stall a whole database; use idle_in_transaction_session_timeout and lock_timeout.
Test yourself on Transactions, Isolation and Locking
Ten questions, with the answer and explanation after each one.