Postgres – Transactions, Isolation and Locking

A transaction is a group of statements that either all happen or none do. That much is familiar. What is worth your time is the second promise — what one transaction sees while another is running — because the default is not the strongest option and the difference causes real bugs.

The basics

BEGIN;
UPDATE bookings SET status = 'CANCELLED', cancelled_at = now() WHERE id = 12345;
INSERT INTO outbox (topic, payload, status, attempts, available_at, public_id)
VALUES ('booking.cancelled', '{"bookingId": 12345}'::jsonb, 'PENDING', 0, now(),
        gen_random_uuid());
COMMIT;

Both rows appear together or neither does. A statement outside an explicit transaction gets its own — there is no such thing as a statement running outside one.

Postgres DDL is transactional. CREATE TABLE, ALTER TABLE and DROP TABLE all roll back, which is why a failed migration here leaves nothing half-applied. If you are used to MySQL or Oracle, this is a genuine difference.

Savepoints give partial rollback inside a transaction — and they are what an ORM uses to implement nested transactions, which is worth knowing when you go looking for one in a log:

BEGIN;
INSERT INTO amenities (slug, name) VALUES ('sauna', 'Sauna');
SAVEPOINT before_risky;
INSERT INTO amenities (slug, name) VALUES ('sauna', 'Duplicate');  -- fails
ROLLBACK TO SAVEPOINT before_risky;
COMMIT;   -- the first insert survives

Without the savepoint, that error would poison the whole transaction: every subsequent statement answers current transaction is aborted until you roll back.

Isolation levels

Postgres offers three, and the default is the weakest.

Note that Postgres has no Read Uncommitted — asking for it gives you Read Committed. Dirty reads simply cannot happen.

The default's sharp edge: each statement gets a new snapshot, so two identical queries in one transaction can return different answers.

-- session A
BEGIN;
SELECT count(*) FROM bookings WHERE property_id = 42;   -- 20
-- session B commits an insert here
SELECT count(*) FROM bookings WHERE property_id = 42;   -- 21, in the SAME transaction
COMMIT;

Any logic that reads a value, decides something, and writes based on that decision is unsafe at Read Committed unless it locks. That is the whole of the next section.

The lost update, and SELECT FOR UPDATE

Two sessions read the same row, both compute a new value from it, both write. One update is silently lost:

-- session A                                    -- session B
BEGIN;                                          BEGIN;
SELECT rating_count FROM properties             SELECT rating_count FROM properties
WHERE id = 42;               -- 10              WHERE id = 42;               -- 10
UPDATE properties SET rating_count = 11         UPDATE properties SET rating_count = 11
WHERE id = 42;                                  WHERE id = 42;
COMMIT;                                         COMMIT;                      -- still 11

Two reviews arrived, the count went up by one. Three fixes, in order of preference:

-- 1. best: let the database do the arithmetic. No read, nothing to lose.
UPDATE properties SET rating_count = rating_count + 1 WHERE id = 42;

-- 2. when you genuinely must read first, lock the row while you think
BEGIN;
SELECT rating_count FROM properties WHERE id = 42 FOR UPDATE;
UPDATE properties SET rating_count = 11 WHERE id = 42;
COMMIT;

-- 3. optimistic: fail rather than overwrite, and retry
UPDATE properties SET rating_count = 11, updated_at = now()
WHERE  id = 42 AND rating_count = 10;    -- 0 rows means somebody else got there first

FOR UPDATE holds the row until the transaction ends, and a second session's FOR UPDATE on the same row waits. Two variants matter: FOR UPDATE NOWAIT errors immediately rather than waiting, and FOR UPDATE SKIP LOCKED ignores locked rows — which is how you build a work queue that several workers can pull from without handing anyone the same job:

SELECT id, payload FROM outbox
WHERE  status = 'PENDING' AND available_at <= now()
ORDER  BY available_at
LIMIT  10
FOR UPDATE SKIP LOCKED;

Deadlocks

Two transactions each holding a lock the other wants. Postgres detects it after about a second, kills one, and reports deadlock detected:

-- session A                        -- session B
BEGIN;                              BEGIN;
UPDATE properties SET ... id = 1;   UPDATE properties SET ... id = 2;
UPDATE properties SET ... id = 2;   UPDATE properties SET ... id = 1;
-- waits for B                      -- waits for A: deadlock

The cure is not a retry loop, although you want one anyway. It is touching rows in a consistent order — always ascending by primary key — so the cycle cannot form. Add ORDER BY id to the subquery of any batch update.

The locks that block a deploy

Row locks are rarely the problem. Table-level locks taken by DDL are, because ACCESS EXCLUSIVE blocks even SELECT:

SELECT a.pid, a.state, now() - a.xact_start AS open_for,
       a.wait_event_type, left(a.query, 50) AS query
FROM   pg_stat_activity a
WHERE  a.backend_type = 'client backend' AND a.state <> 'idle'
ORDER  BY a.xact_start;

The state to hunt for is idle in transaction: a connection that ran BEGIN, did something, and then went away without committing. It holds every lock it took and it stops VACUUM from cleaning up anything newer than its snapshot. One of those, left open by a crashed worker, will block your next migration indefinitely.

Set a limit rather than relying on applications to behave:

ALTER DATABASE stayhub_lab SET idle_in_transaction_session_timeout = '60s';
ALTER DATABASE stayhub_lab SET lock_timeout = '5s';
ALTER DATABASE stayhub_lab SET statement_timeout = '30s';

lock_timeout is the one that turns a migration from an outage into a retry: rather than queueing behind a long query — and blocking everything that queues behind it — the DDL gives up after five seconds and you run it again.

When to raise the isolation level

Read Committed plus explicit locks handles nearly everything, and it is what you should reach for first because it never fails with a serialization error. The level worth raising for is write skew, which locking rows does not prevent — because the problem is a row that does not exist yet.

The booking case is the textbook one. Two guests request overlapping dates at the same property simultaneously. Each transaction reads the calendar, finds no conflict, and inserts. Neither read the other's row, because neither row existed at the time, so FOR UPDATE has nothing to lock.

BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT 1 FROM bookings
WHERE  property_id = 42 AND daterange(check_in, check_out, '[)') && daterange('2025-07-01','2025-07-05','[)')
  AND  status IN ('PENDING','CONFIRMED','COMPLETED');
-- decide it is free, then insert
COMMIT;

At SERIALIZABLE, Postgres tracks the read and fails one of the two transactions with could not serialize access due to read/write dependencies. That is not an error to log and forget — it means "retry this transaction", and any code using serializable isolation needs a retry loop around the whole transaction, not around one statement.

The alternative, and the one this schema actually uses, is to make the conflict a constraint — the exclusion constraint from the constraints post. Then Read Committed is sufficient, because the database rejects the second insert whatever order things happened in. Prefer a constraint to an isolation level where you can express it as one: it needs no retry loop, no discipline from callers, and it holds for clients that never heard of your rule.

Advisory locks

A named lock with no row attached, for coordinating things outside the database — one scheduler across several instances, or a nightly job that must not overlap itself:

SELECT pg_try_advisory_lock(4242);   -- true if you got it, false if someone else holds it
SELECT pg_advisory_unlock(4242);

Use pg_try_advisory_lock, which returns immediately, rather than pg_advisory_lock, which waits forever. Session-level advisory locks are released on disconnect but not on commit, so a pooled connection can carry one into the next request — the _xact variants, released at commit, are usually what you want.