Catalog My learning Blog
PostgreSQL Course: Beginner to Production in 30 Days · Phase 2 · Lesson 12 · 8 min read

PostgreSQL Transactions and ACID Explained

On this page
  1. What a transaction is
  2. ACID explained
  3. Errors inside a transaction, and savepoints
  4. Isolation levels in PostgreSQL
  5. Lost updates, and SELECT ... FOR UPDATE
  6. A job queue with SKIP LOCKED
  7. Advisory locks: locking an idea, not a row
  8. How deadlocks happen and how to prevent them
  9. Check yourself

What a transaction is

You move 200 from Alice's account to Bob's. That's two UPDATEs. If the server crashes after the first one, Alice has lost 200 and Bob never got it. A transaction makes the two statements one unit: both happen, or neither does.

SQL
CREATE TABLE accounts (
  account_id  int PRIMARY KEY,
  owner       text NOT NULL,
  balance     numeric(12,2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 'Alice', 1000), (2, 'Bob', 500);

BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 200 WHERE account_id = 2;
COMMIT;

Until COMMIT, nobody else sees either change. ROLLBACK instead of COMMIT discards both. Without BEGIN, each statement runs in its own transaction and commits as soon as it finishes. That's called autocommit.

ACID explained

The four letters describe what PostgreSQL guarantees for every transaction. Here's what each one means for the transfer:

  • Atomicity. Both updates land, or neither does. A crash halfway leaves no half-done transfer.
  • Consistency. Constraints hold at every commit. The CHECK means no transaction can commit a negative balance.
  • Isolation. Other sessions don't see the transfer half-done. How strictly they're shielded is something you can tune, as you'll see below.
  • Durability. Once COMMIT returns, the transfer survives a crash or power cut, because it's already in the write-ahead log on disk (Day 2).

Errors inside a transaction, and savepoints

This behavior surprises developers who come from other databases. If any statement in a transaction fails, PostgreSQL marks the whole transaction as failed. It won't run anything else:

SQL
BEGIN;
UPDATE accounts SET balance = balance - 5000 WHERE account_id = 1;
-- ERROR: new row for relation "accounts" violates check constraint "accounts_balance_check"
SELECT * FROM accounts;
-- ERROR: current transaction is aborted, commands ignored until end of transaction block
COMMIT;   -- PostgreSQL answers ROLLBACK

Even COMMIT can't save it. PostgreSQL rolls back and says so.

To recover from one failed step without losing the whole transaction, set a savepoint before the risky step:

SQL
BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE account_id = 1;
SAVEPOINT before_credit;
INSERT INTO accounts VALUES (1, 'duplicate', 0);     -- fails
ROLLBACK TO SAVEPOINT before_credit;                 -- undo only the failed part
UPDATE accounts SET balance = balance + 200 WHERE account_id = 2;
COMMIT;                                              -- the debit and credit both commit

When an ORM offers "nested transactions", this is what it does underneath: SAVEPOINT on the way in, then RELEASE or ROLLBACK TO on the way out. PostgreSQL has no true nested transactions.

Isolation levels in PostgreSQL

Two transactions running at the same time can step on each other in a few named ways:

  • Dirty read. You see another transaction's uncommitted change. PostgreSQL never allows this, at any level.
  • Non-repeatable read. You read a row twice in one transaction and get different values, because someone committed a change in between.
  • Phantom read. You run the same query twice and a new matching row appears.
  • Serialization anomaly. Each transaction looks fine alone, but together they produce a result that no one-at-a-time order could have produced.

PostgreSQL offers three behaviors. It also accepts READ UNCOMMITTED, but treats it as READ COMMITTED.

LevelWhat each statement seesPreventsYou must handle
Read committed (default)A fresh snapshot per statementDirty readsValues changing between statements
Repeatable readOne snapshot for the whole transactionAlso non-repeatable and phantom readsSerialization errors on conflicting writes
SerializableOne snapshot, plus conflict trackingEvery anomaly aboveSerialization errors, which need a retry

PostgreSQL's repeatable read is stricter than the SQL standard requires, since it also blocks phantoms.

SQL
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT sum(balance) FROM accounts;   -- the snapshot is taken here
-- ...another session commits a change...
SELECT sum(balance) FROM accounts;   -- same answer as before
COMMIT;

Read committed is the right default for most application code. Reach for repeatable read when a report must see one consistent moment. Use serializable when correctness depends on several rows together, like "at least one doctor must stay on call". The catch with the two stricter levels: PostgreSQL may abort your transaction with a serialization failure (SQLSTATE 40001) rather than allow an anomaly. Your code must catch that and retry the whole transaction.

Lost updates, and SELECT ... FOR UPDATE

UPDATE accounts SET balance = balance - 200 is safe under any isolation level, because the subtraction happens inside the database on the latest row. Application code often does something riskier: read the balance, compute the new value, write it back. Two requests doing that at once lose one of the changes. Tested with two sessions on PostgreSQL 16, both withdrawing 200 from Alice's 1,000:

SQL
Session A                                   Session B
SELECT balance ...;      -- 1000
                                            SELECT balance ...;      -- 1000
UPDATE ... SET balance = 800; COMMIT;
                                            UPDATE ... SET balance = 800; COMMIT;
-- Final balance: 800. One withdrawal vanished.

That's a lost update. The fix is to lock the row when you read it, so the second reader waits:

SQL
BEGIN;
SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE;   -- locks the row
-- the application computes the new balance
UPDATE accounts SET balance = 800 WHERE account_id = 1;
COMMIT;                                                         -- releases the lock

In the same test with FOR UPDATE, session B's SELECT waited until A committed, then read 800, so it would write 600. FOR SHARE is the weaker form: it stops others from changing or deleting the row while you rely on it, but lets other FOR SHARE readers through. Under repeatable read, the second session gets a serialization error instead of a silent lost update, which is another way out, as long as you retry.

A job queue with SKIP LOCKED

Several workers pull jobs from one table. With FOR UPDATE alone, every worker queues behind whichever one locked the first job. SKIP LOCKED tells each worker to skip rows another transaction has locked and take the next free one:

SQL
CREATE TABLE jobs (
  job_id   int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  payload  text NOT NULL,
  status   text NOT NULL DEFAULT 'queued'
);
INSERT INTO jobs (payload) VALUES ('email 1'), ('email 2'), ('email 3');

-- each worker runs this inside its own transaction
WITH next AS (
  SELECT job_id FROM jobs
  WHERE status = 'queued'
  ORDER BY job_id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs j SET status = 'running'
FROM next
WHERE j.job_id = next.job_id
RETURNING j.job_id, j.payload;

Tested with two workers: while the first held job 1 in an open transaction, the second got job 2 at once instead of waiting. Without SKIP LOCKED, the second worker waited the full 2 seconds until the first committed. This pattern is how many PostgreSQL-backed job queues work. Mark a job done (or delete it) in the same transaction, and a crashed worker's job is released automatically when its transaction rolls back.

Advisory locks: locking an idea, not a row

Sometimes the thing to protect isn't a row: "only one nightly report may run at a time". Advisory locks are named locks that mean whatever your application decides. PostgreSQL doesn't tie them to any table:

SQL
BEGIN;
SELECT pg_try_advisory_xact_lock(hashtext('nightly-report'));   -- true: we got it
-- ... run the report ...
COMMIT;                                                        -- released automatically

A second session that tries while the first holds it gets false straight away, and can exit instead of running twice. The _xact_ variants release at the end of the transaction. The session-level versions (pg_advisory_lock) are held until you unlock them or disconnect, which doesn't mix with transaction-mode connection pooling (Day 29).

How deadlocks happen and how to prevent them

Two transfers run at the same moment, in opposite directions:

SQL
Session A                                   Session B
BEGIN;                                      BEGIN;
UPDATE ... WHERE account_id = 1;  -- locks 1
                                            UPDATE ... WHERE account_id = 2;  -- locks 2
UPDATE ... WHERE account_id = 2;  -- waits for B
                                            UPDATE ... WHERE account_id = 1;  -- waits for A

Each waits for the other forever. That's a deadlock. Ordinary waiting, where B waits until A commits, is normal and resolves itself. A deadlock is a cycle. PostgreSQL checks for cycles after a lock wait passes deadlock_timeout (1 second by default). It then cancels one transaction with a "deadlock detected" error, and the other carries on.

You prevent most deadlocks with one habit: lock rows in the same order everywhere. If every transfer updates the lower account id first, the cycle above can't form. Also keep transactions short, and never wait for user input or a slow API call between BEGIN and COMMIT. Then treat the deadlocks that still happen like serialization failures: catch them and retry.

Check yourself

An earlier statement in the same transaction failed, and PostgreSQL refuses to run anything else until the transaction ends. Roll back (or roll back to a savepoint) when an error occurs, instead of carrying on with the same transaction.

No. That's PostgreSQL preventing an anomaly. Catch serialization failures and retry the whole transaction, usually a few times with a short backoff.

Update the two rows in a fixed order, for example lower account_id first, in every code path. Keep a retry around the transaction for the cases that still slip through.

A lost update: both read the same value before either wrote. Lock the row when reading with SELECT ... FOR UPDATE, or do the arithmetic in SQL with UPDATE products SET stock = stock - 1 WHERE ....

Each worker selects a job with FOR UPDATE SKIP LOCKED, which locks the rows it gets and skips rows another worker already locked. Workers never wait on each other or process the same job twice, so a plain table works as a reliable queue.