Transactions: ACID Without the Textbook | Databases Tutorial

This chapter shows you how transactions keep data consistent under concurrent writes, and which isolation level to pick when.

What a Transaction Is

A transaction is a group of statements that either all succeed or all fail. If anything goes wrong, the database reverts everything to the state before the transaction started.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

If the second UPDATE fails (the account doesn't exist, the machine crashes, the power goes out), the first UPDATE is undone. The money doesn't vanish.

ROLLBACK aborts the transaction explicitly:

BEGIN;
UPDATE orders SET status = 'cancelled' WHERE id = 42;
-- Oops, wrong ID
ROLLBACK;

Without BEGIN, every statement is its own transaction (autocommit). That's fine for single-statement operations. The moment you have two statements that must happen together, wrap them.

The Four Letters: ACID

ACID is the set of properties a transaction gives you.

Atomicity. All or nothing. The example above.
Consistency. After a transaction, all constraints (foreign keys, CHECK, unique) still hold. The database's job.
Isolation. Concurrent transactions don't interfere with each other. The tricky one; see below.
Durability. Once a transaction is committed, it survives crashes. PostgreSQL writes a log (WAL) before acknowledging COMMIT.

In practice, you reason about Atomicity and Isolation. Consistency and Durability are the database's problem unless you disable them on purpose.

Isolation Levels

When two transactions touch the same data concurrently, weird things can happen. Isolation levels describe which weirdness the database prevents.

The SQL standard defines four levels. PostgreSQL implements three (it treats READ UNCOMMITTED the same as READ COMMITTED).

READ COMMITTED (the default)

You see only data that has been committed. But a transaction can see different committed data across its statements, because other transactions keep committing.

BEGIN;
SELECT balance FROM accounts WHERE id = 1;   -- 100
-- Meanwhile another session commits an UPDATE.
SELECT balance FROM accounts WHERE id = 1;   -- 200
COMMIT;

That's a "non-repeatable read". For most applications, fine. You rarely need the same query to return the same result twice inside a transaction.

REPEATABLE READ

A transaction sees a snapshot of the database as of the moment it started. The balance will still read 100 after the other session commits; you're reading a frozen view.

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;   -- 100
-- Another session commits an UPDATE.
SELECT balance FROM accounts WHERE id = 1;   -- still 100
COMMIT;

Good for reports and multi-step reads that need a consistent view. Writes can still conflict (you'll get a serialization error if you try to UPDATE a row another transaction modified).

SERIALIZABLE

The strictest level. The database behaves as if transactions ran one at a time, in some order. It detects dependency cycles between concurrent transactions and aborts one with a serialization error.

BEGIN ISOLATION LEVEL SERIALIZABLE;
-- ...
COMMIT;

Use when correctness matters more than throughput (financial ledgers, inventory). Be prepared to retry transactions that fail with 40001 serialization_failure.

Picking a Level

READ COMMITTED for most application queries.
REPEATABLE READ for multi-statement reads that need a consistent snapshot.
SERIALIZABLE for operations where correctness under concurrency is hard to reason about manually.

Set the default in postgresql.conf (default_transaction_isolation) or per-transaction with BEGIN ISOLATION LEVEL ....

Locks

PostgreSQL uses row-level locks under the hood. You rarely write them explicitly. Two that come up:

SELECT ... FOR UPDATE

"Read this row and take an exclusive lock on it so nobody else can update it until I commit":

BEGIN;
SELECT stock FROM products WHERE id = 1 FOR UPDATE;
-- Do some logic based on stock
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

Without FOR UPDATE, two sessions could read stock = 1, both decide to decrement, and you end up overselling. FOR UPDATE forces them to serialize on that row.

FOR UPDATE SKIP LOCKED is the classic "pull work from a queue" pattern:

SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

Workers grab different jobs without blocking each other.

Table Locks

Commands like ALTER TABLE acquire stronger locks. A careless ALTER TABLE ... ADD COLUMN with a non-constant default used to rewrite the whole table under a full lock; modern PostgreSQL handles constant defaults online, but it's worth knowing when a migration needs scheduling.

Deadlocks

Two transactions, each holding a lock the other needs:

Session A                      Session B
BEGIN;                         BEGIN;
UPDATE orders SET ...          UPDATE products SET ...
  WHERE id = 1;                  WHERE id = 1;
UPDATE products SET ...        UPDATE orders SET ...
  WHERE id = 1;                  WHERE id = 1;
-- waits for B                 -- waits for A

PostgreSQL detects this after a short timeout and aborts one transaction with 40P01 deadlock_detected. The other proceeds.

Fix the pattern: always lock rows in the same order across your codebase. If every transaction locks orders first, then products, no cycle is possible.

Savepoints

A savepoint is a nested rollback point inside a transaction.

BEGIN;
INSERT INTO users (email, name) VALUES ('ada@example.com', 'Ada');
SAVEPOINT before_orders;
INSERT INTO orders (user_id, status, total) VALUES (1, 'bogus', 0);
-- Error: CHECK constraint fails
ROLLBACK TO SAVEPOINT before_orders;
-- The user is still inserted; only the order is rolled back.
COMMIT;

Useful when one step of a larger operation might legitimately fail and you want to retry it differently without aborting the whole transaction.

Idempotency

Retrying is a fact of life: networks drop, clients reconnect, workers crash. Build operations so retries are safe.

Use ON CONFLICT upserts instead of insert-then-fail-on-duplicate.
Use a unique client-provided key on operations that mustn't duplicate (order creation, payment capture).
Wrap multi-step logic in a single transaction so partial states don't linger after a crash.

Common Pitfalls

Forgetting to COMMIT. In psql, if your prompt says learning=*# (note the asterisk), you're inside an open transaction. Until you COMMIT, other sessions can't see your changes, and your locks are still held. Autocommit off plus forgotten COMMIT is one of the top causes of "the database is stuck" tickets.

Long-running transactions. Transactions hold locks and block vacuum. A five-minute transaction can cause table bloat. Break work into small transactions.

Assuming READ COMMITTED prevents lost updates. It doesn't. Use FOR UPDATE or SERIALIZABLE when you need to read-then-modify safely.

Catching and swallowing serialization errors. These are not bugs in your code. They are the database telling you to retry. Retry the whole transaction, don't just retry the failing statement.

Next Steps

Continue to 09-normalization.md to structure your schema so updates happen in one place.