Many clients use Orbit at the same moment. Two of them may edit the same note, a background worker may process jobs while users create new ones, and a migration may alter a table under all of them. This lesson explains how Postgres keeps all of that correct, and the few tools you need to use it well.
ACID in one table
| Letter | Promise | How Postgres keeps it |
|---|---|---|
| Atomicity | All changes of a transaction happen, or none | Commit is one record in the write-ahead log |
| Consistency | Constraints hold after every transaction | Constraints are checked on every write |
| Isolation | Concurrent transactions do not see each other’s half-done work | MVCC snapshots |
| Durability | Once committed, it survives a crash | The WAL is flushed to disk at commit |
Transactions
Every statement runs in a transaction. Without BEGIN, each statement is its own transaction and commits immediately (autocommit). To group statements:
BEGIN;
INSERT INTO tags (name) VALUES ('ideas');
INSERT INTO note_tags (note_id, tag_id)
SELECT 3, id FROM tags WHERE name = 'ideas';
COMMIT;
BEGIN
INSERT 0 1
INSERT 0 1
COMMIT
If anything fails in between, use ROLLBACK, and neither insert happened. After an error, Postgres refuses further statements in that transaction (current transaction is aborted, commands ignored until end of transaction block) until you roll back. In psql the prompt shows ! in that state.
Savepoints
A savepoint marks a point you can roll back to without losing the whole transaction:
BEGIN;
INSERT INTO tags (name) VALUES ('travel');
SAVEPOINT before_risky;
INSERT INTO tags (name) VALUES ('work'); -- fails: duplicate name
ROLLBACK TO SAVEPOINT before_risky;
COMMIT; -- 'travel' is kept
This is exactly what psql’s ON_ERROR_ROLLBACK setting from lesson 3 does for you automatically.
MVCC: why readers never wait for writers
Postgres uses multi-version concurrency control. An UPDATE does not overwrite a row; it writes a new version of it and marks the old version as expired. Each transaction reads from a snapshot: the set of versions that were committed when its snapshot was taken.
The consequences are worth memorising:
- Readers never block writers, and writers never block readers. A long report keeps reading the old versions while users update the table.
- Only two transactions trying to change the same row wait for each other.
- Old row versions pile up until VACUUM removes them once no snapshot can see them anymore (lesson 17). A transaction left open for hours prevents that cleanup.
Isolation levels
Isolation decides when your transaction takes its snapshot:
| Level | Snapshot | Can see |
|---|---|---|
| Read Committed (default) | New snapshot for each statement | Changes committed by others between your statements |
| Repeatable Read | One snapshot for the whole transaction | Only what was committed when it started |
| Serializable | Like Repeatable Read, plus detection of dangerous patterns | Results as if transactions ran one after another |
Watch the default level with two psql sessions side by side:
-- Session A -- Session B
BEGIN;
SELECT plan FROM users WHERE id = 1; -- free
UPDATE users SET plan = 'pro' WHERE id = 1;
SELECT plan FROM users WHERE id = 1; -- pro
COMMIT;
In Read Committed, the second SELECT sees Session B’s committed change. Start Session A with BEGIN ISOLATION LEVEL REPEATABLE READ; and both SELECTs return free: the transaction keeps its first snapshot. (Set Ana back to free afterwards with UPDATE users SET plan = 'free' WHERE id = 1;.)
Under Serializable, Postgres aborts one of two transactions whose combined effect could not have happened in any serial order:
ERROR: could not serialize access due to read/write dependencies among transactions
Repeatable Read can fail with a similar serialization error when two transactions update the same row. Applications using these levels must retry the whole transaction when they get SQLSTATE 40001.
Lost updates and row locks
Two users open note 4 at the same time, both read the body, both save their edited version. With plain SELECT then UPDATE, the second save silently overwrites the first: a lost update.
The simplest fix is to make the update itself do the change, so it uses the current value:
UPDATE notes SET body = body || E'\nalso: use CONCURRENTLY for indexes' WHERE id = 4;
When you must read, decide in the application and then write, lock the row while you read it:
BEGIN;
SELECT body FROM notes WHERE id = 4 FOR UPDATE;
-- the application edits the text, then sends it back as a parameter
UPDATE notes SET body = $1 WHERE id = 4;
COMMIT;
Another transaction that runs SELECT ... FOR UPDATE on the same row now waits until the first commits, then sees the new version. Ordinary SELECTs are not blocked. For web apps where a user may keep a form open for minutes, prefer optimistic locking instead: add a version column and update with WHERE id = 4 AND version = 7. If the update reports UPDATE 0, someone else saved first.
A job queue with SKIP LOCKED
Background workers often take jobs from a table. With FOR UPDATE SKIP LOCKED, each worker grabs a job that no other worker has locked, without waiting:
CREATE TABLE jobs (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
kind text NOT NULL,
payload jsonb NOT NULL DEFAULT '{}',
status text NOT NULL DEFAULT 'queued',
created_at timestamptz NOT NULL DEFAULT now()
);
-- each worker, in a loop:
BEGIN;
SELECT id, kind, payload FROM jobs
WHERE status = 'queued'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- do the work, then:
UPDATE jobs SET status = 'done' WHERE id = $1;
COMMIT;
If a worker crashes, its transaction rolls back, the lock disappears and another worker picks the job up. Add a partial index ON jobs (id) WHERE status = 'queued' so finding the next job stays fast.
Table locks and migrations
Most statements take light table-level locks that do not conflict with each other. ALTER TABLE, DROP, TRUNCATE and plain CREATE INDEX take strong ones. The dangerous part is the queue: if a long-running query holds a light lock, your ALTER TABLE waits for it, and every new query on the table waits behind your ALTER. A two-second migration can take the site down for minutes.
Always set a lock timeout in migrations, and retry if it fires:
SET lock_timeout = '3s';
ALTER TABLE notes ADD COLUMN archived boolean NOT NULL DEFAULT false;
ERROR: canceling statement due to lock timeout
That error is good news: the migration gave up instead of blocking everyone. Try again a moment later.
Deadlocks
Transaction 1 locks row A and wants row B; transaction 2 locks row B and wants row A. Neither can continue. Postgres detects this after deadlock_timeout (one second by default) and cancels one of them:
ERROR: deadlock detected
Prevent deadlocks by touching rows in a consistent order (for example, always by ascending id) and keeping transactions short. Treat the error like a serialization failure: retry.
Finding who is blocking whom
SELECT pid, usename, state, wait_event_type, now() - xact_start AS xact_age,
pg_blocking_pids(pid) AS blocked_by, left(query, 60) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY xact_age DESC;
pg_blocking_pids lists the sessions holding the locks a session is waiting for. If one of them is idle in transaction, an application opened a transaction and forgot about it. End it with SELECT pg_terminate_backend(pid);, and protect the server with:
ALTER DATABASE orbit SET idle_in_transaction_session_timeout = '5min';
Quick reference
| Task | SQL |
|---|---|
| Group statements | BEGIN; ... COMMIT; |
| Undo | ROLLBACK; |
| Partial undo | SAVEPOINT s; ... ROLLBACK TO SAVEPOINT s; |
| Stricter isolation | BEGIN ISOLATION LEVEL REPEATABLE READ; |
| Lock rows you will change | SELECT ... FOR UPDATE; |
| Queue consumer | ... FOR UPDATE SKIP LOCKED |
| Fail fast on locks | SET lock_timeout = '3s'; |
| Who blocks whom | pg_blocking_pids(pid) |
| Kill forgotten transactions | idle_in_transaction_session_timeout |
Exercise
A nightly report runs a 20-minute SELECT on notes. What happens to users who update notes during that time?
Show answer
Answer: C. With MVCC, readers and writers do not block each other. The long transaction does delay VACUUM cleanup of old row versions, so keep reports on a replica if they run for hours.
Exercise
Several workers take jobs from the same table. Which clause lets each one grab a different job without waiting?
Show answer
Answer: A. SKIP LOCKED ignores rows locked by other transactions, so each worker locks the first job nobody else is holding. A crashed worker’s lock is released on rollback.
Exercise
A migration’s ALTER TABLE hangs and the whole application stops responding. What is the most likely cause?
Show answer
Answer: D. The strong lock request waits for the current holder and blocks everything after it. Set lock_timeout so the migration gives up quickly, then retry.
Key takeaways
- Transactions make groups of changes atomic; savepoints allow partial rollback.
- MVCC keeps row versions so readers and writers never block each other; VACUUM cleans up old versions.
- Read Committed takes a new snapshot per statement; Repeatable Read and Serializable keep one per transaction and may require retries.
- Prevent lost updates with in-place updates,
SELECT ... FOR UPDATEor a version column; build queues withSKIP LOCKED. - Set
lock_timeoutfor migrations andidle_in_transaction_session_timeoutfor forgotten transactions.
FAQ
Which isolation level should my application use?
Read Committed, the default, for most work. Use Repeatable Read for reports that must see one consistent snapshot, and Serializable when correctness depends on complex invariants and you can retry transactions.
Does BEGIN slow things down?
No. Every statement is already a transaction. Grouping many inserts into one transaction is actually faster, because the commit, the expensive part, happens once.
Are DDL statements transactional?
Yes, in Postgres almost all of them are: CREATE TABLE, ALTER TABLE and DROP can be rolled back. A few commands, such as CREATE INDEX CONCURRENTLY, VACUUM and CREATE DATABASE, cannot run inside a transaction block.
What is an advisory lock?
A lock on an application-defined number, not on a row or table. SELECT pg_try_advisory_lock(42); is a simple way to make sure only one instance of a cron job runs at a time.
Why does my connection pooler complain about SET and transactions?
In transaction pooling mode (PgBouncer), consecutive transactions may run on different server connections, so session state such as SET, prepared statements or advisory locks can get lost. Use SET LOCAL inside the transaction, or session pooling for clients that need session state.