Module P-3·21 min read

BEGIN, COMMIT, ROLLBACK, isolation levels, and safe atomic operations — what every production application must understand.

P-3 — Transactions and ACID in Practice

Q: You are designing a job queue system in PostgreSQL where multiple background workers concurrently claim and process pending jobs from a `tasks` table. If multiple workers frequently attempt to claim the exact same job simultaneously, which concurrency control mechanism is the most efficient and robust choice to prevent locking contention and ensure high throughput?

Use `SELECT ... FOR UPDATE SKIP LOCKED` to allow workers to instantly bypass rows that are already locked by other transactions. — Using `SELECT ... FOR UPDATE SKIP LOCKED` is the standard and most efficient pattern for implementing job queues in PostgreSQL. It allows a worker to claim the next available row while instantly skipping over any rows that are currently locked by other concurrent workers. This eliminates blocking and prevents the database from wasting time resolving lock contention. Option A (`SERIALIZABLE`) would lead to massive serialization failures and retries. Option B (`FOR UPDATE` without `SKIP LOCKED`) would cause workers to form a queue waiting for the same row lock, defeating concurrency. Option D (Table locks) would destroy throughput by allowing only one worker to claim a job at any given time.

Q: During a complex financial reconciliation process, an application uses the default `READ COMMITTED` isolation level. The application executes a `SELECT COUNT(*)` on a ledger table, then performs some internal logic, and executes the exact same `SELECT COUNT(*)` again within the same transaction. The second query returns a different result than the first. What is the fundamental reason for this behavior?

A concurrent transaction committed a new ledger entry between the first and second query, and `READ COMMITTED` fetches a fresh snapshot for each individual query within the transaction. — In the `READ COMMITTED` isolation level, each individual query sees a fresh snapshot of the database as it existed at the exact moment that specific query began. Therefore, if a concurrent transaction commits new data between two queries in your transaction, the second query will see those new changes (this is known as a "non-repeatable read"). Option C is incorrect because PostgreSQL never allows "dirty reads" (reading uncommitted data from other transactions), even in `READ UNCOMMITTED` mode. To ensure the second query sees the exact same data as the first, the transaction would need to use the `REPEATABLE READ` isolation level, which freezes the snapshot for the duration of the entire transaction.

Q: A backend developer has written an API endpoint that opens a database transaction, queries a `pending_orders` table, makes an HTTP request to a third-party payment gateway that takes 4 seconds, and finally updates the order status to `confirmed` before committing. From a database performance perspective, why is this an anti-pattern?

The transaction holds locks on the modified rows for the entire 4 seconds, blocking other concurrent operations and allowing dead tuples to accumulate by preventing autovacuum from cleaning them up. — Keeping transactions open during slow external operations (like network requests) is a classic anti-pattern. While the transaction is open waiting for the HTTP response, it continues to hold any locks acquired earlier in the transaction. This blocks other concurrent transactions trying to modify those rows. Furthermore, long-running transactions prevent `autovacuum` from cleaning up dead tuples across the entire database, leading to table bloat and performance degradation. The correct pattern is to query the data, close or pause the transaction, make the external HTTP request, and then open a short, separate transaction to write the final result.

Who this module is for: You can write queries and design schemas. Now you need to understand how to group multiple operations into an atomic unit — so that either all of them succeed or none of them do. This is the mechanism that prevents your application from leaving the database in a half-updated, inconsistent state.

The Problem Transactions Solve

Imagine a bank transfer: debit $100 from Alice, credit $100 to Bob.

sql

UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- Alice
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- Bob

What happens if the server crashes, the network drops, or an error occurs between the two statements? Alice has lost $100 and Bob never received it. The money has vanished.

Transactions prevent this by grouping operations: either both updates happen, or neither does.

`BEGIN`, `COMMIT`, `ROLLBACK`

sql

BEGIN;  -- start a transaction

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

COMMIT;  -- make both changes permanent

If anything goes wrong between BEGIN and COMMIT:

sql

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Something goes wrong (application error, network failure, etc.)

ROLLBACK;  -- undo the debit — Alice gets her $100 back

Without a BEGIN, every statement is its own transaction — it auto-commits immediately. This is fine for isolated statements, but dangerous for multi-step operations.

With a BEGIN, nothing is visible to other connections until COMMIT. If the session disconnects before COMMIT, PostgreSQL automatically rolls back the transaction.

ACID: What It Actually Means

A — Atomicity: All operations in a transaction succeed, or none do. There is no partial success.

sql

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Crash here → the UPDATE is rolled back. Alice keeps her $100.

C — Consistency: A transaction brings the database from one valid state to another. Constraints (NOT NULL, FOREIGN KEY, CHECK) are enforced at commit time.

sql

BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;  -- balance would go to -500
-- CHECK (balance >= 0) fires at COMMIT
COMMIT;  -- ERROR: check constraint violated → automatic rollback

I — Isolation: Transactions run as if they are the only transaction in the system. Other transactions' uncommitted changes are invisible.

sql

-- Session A:
BEGIN;
UPDATE tasks SET status = 'in_progress' WHERE id = 1;
-- NOT committed yet

-- Session B (concurrent):
SELECT status FROM tasks WHERE id = 1;
-- Returns 'todo' — Session A's change is not visible until committed

D — Durability: Once committed, changes survive crashes. PostgreSQL's Write-Ahead Log (WAL) ensures this.

Isolation Levels in Practice

PostgreSQL supports four isolation levels. The default — and the one you will use for almost everything — is READ COMMITTED.

`READ COMMITTED` (default)

Each query in a transaction sees a fresh snapshot of committed data at the time that query starts. This means a transaction can see different data at the beginning vs. the end if other transactions commit in between.

sql

-- Session A:
BEGIN;
SELECT COUNT(*) FROM tasks;  -- returns 100

-- Session B (commits a new task while A is open):
INSERT INTO tasks (...) VALUES (...);
COMMIT;

-- Session A continues:
SELECT COUNT(*) FROM tasks;  -- returns 101 — sees B's committed change
COMMIT;

This is acceptable for most application reads. Each query gets a consistent view; the transaction as a whole does not.

`REPEATABLE READ`

The entire transaction sees the same snapshot of data as of when the transaction started. New inserts/updates by other transactions are invisible for the duration.

sql

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;

SELECT COUNT(*) FROM tasks;  -- returns 100

-- Session B inserts and commits a task while we wait

SELECT COUNT(*) FROM tasks;  -- still returns 100 — snapshot is frozen
COMMIT;

Use this when your transaction needs a consistent view across multiple queries — for example, generating a report where all queries must see the same data point in time.

`SERIALIZABLE`

The strongest level. Transactions execute as if they were serialised one after another. PostgreSQL detects situations where concurrent transactions could produce results different from any serial execution and fails one of them.

sql

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Used for financial operations where concurrent updates to related rows
-- could produce incorrect results even if each individual update is correct

Use this sparingly — it adds overhead and can cause transaction failures that need retrying.

Rule for most applications: use the default READ COMMITTED. Move to REPEATABLE READ only when you need a consistent multi-query snapshot. Use SERIALIZABLE only for complex financial operations where non-serialisable anomalies are a real risk.

`SAVEPOINT` — Partial Rollback

A SAVEPOINT marks a point within a transaction you can roll back to without abandoning the entire transaction.

sql

BEGIN;

INSERT INTO orders (customer_id, total) VALUES (1, 150.00);

SAVEPOINT before_items;  -- mark this point

INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 1, 2);
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 999, 1);
-- ↑ This fails: product 999 doesn't exist (foreign key violation)

-- Roll back to the savepoint — the order INSERT is preserved,
-- but the order_items INSERTs are undone
ROLLBACK TO SAVEPOINT before_items;

-- Try again with correct data
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 2, 1);

COMMIT;  -- order + 1 item committed successfully

Transaction Best Practices

Keep transactions short

A transaction holds locks on the rows it modifies until it commits. Long-running transactions block other operations and accumulate dead tuples (see Phase 3, autovacuum).

sql

-- ❌ SLOW: fetching from an external API inside a transaction
BEGIN;
SELECT * FROM orders WHERE status = 'pending';
-- ... application calls payment API (100ms to 5 seconds) ...
UPDATE orders SET status = 'confirmed' WHERE id = $id;
COMMIT;
-- Locks held for 5+ seconds

-- ✅ FAST: do external work outside the transaction
-- 1. Fetch pending orders (no transaction needed for reads in most cases)
SELECT * FROM orders WHERE status = 'pending';
-- 2. Call payment API outside the transaction (no locks held)
-- 3. Open transaction only for the write
BEGIN;
UPDATE orders SET status = 'confirmed' WHERE id = $id;
COMMIT;

Never ignore transaction errors

In application code, check for errors after every database call. In PostgreSQL, once an error occurs inside a transaction, the transaction is aborted — all subsequent statements fail until you ROLLBACK.

sql

BEGIN;
INSERT INTO tasks (...) VALUES (...);

-- If this INSERT fails (e.g., foreign key violation):
INSERT INTO invalid_table (...) VALUES (...);  -- ERROR

-- This subsequent SELECT will also fail:
SELECT * FROM tasks;
-- ERROR: current transaction is aborted, commands ignored until end of transaction block

ROLLBACK;  -- must rollback before doing anything else

In application code (Node.js example):

javascript

const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [100, 1]);
  await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [100, 2]);
  await client.query('COMMIT');
} catch (err) {
  await client.query('ROLLBACK');
  throw err;  // re-throw so the caller knows something went wrong
} finally {
  client.release();  // always return the connection to the pool
}

`SELECT ... FOR UPDATE` — Pessimistic Locking

When multiple transactions need to read and then modify the same row, you need to prevent another transaction from changing that row between your SELECT and your UPDATE.

sql

BEGIN;

-- Lock the row immediately — no one else can update it until we commit
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

-- Application logic: check if balance >= amount
-- (the row is locked, so the balance cannot change while we check)

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

COMMIT;

Without FOR UPDATE, two sessions could read the same balance, both decide they can proceed, and both subtract — leading to a negative balance.

FOR UPDATE SKIP LOCKED — useful for job queues:

sql

-- Claim the next available task without blocking on locked rows
BEGIN;
SELECT id, title
FROM tasks
WHERE status = 'todo'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;  -- skip rows locked by other workers

-- Update the claimed task
UPDATE tasks SET status = 'in_progress' WHERE id = $id;
COMMIT;

Multiple workers can run this concurrently — each claims a different task because SKIP LOCKED skips rows already locked by another worker.

Common Transaction Patterns

Safe inventory deduction

sql

BEGIN;

-- Check and lock in one step
UPDATE products
SET stock = stock - $quantity
WHERE id = $product_id AND stock >= $quantity
RETURNING stock;

-- If no rows returned, stock was insufficient — rollback
-- Application checks: if rowCount === 0, rollback

COMMIT;

Atomic counter increment

sql

-- This is safe — the entire operation is atomic
UPDATE page_views
SET count = count + 1
WHERE page_id = $page_id;
-- No transaction needed — a single UPDATE is always atomic

sql

BEGIN;

INSERT INTO orders (customer_id, status)
VALUES ($customer_id, 'pending')
RETURNING id AS order_id;

-- Use the returned order_id
INSERT INTO order_items (order_id, product_id, quantity)
VALUES ($order_id, $product_id, $quantity);

-- Update inventory
UPDATE products SET stock = stock - $quantity WHERE id = $product_id;

COMMIT;

Practical Exercise: Safe Bank Transfers

sql

CREATE TABLE bank_accounts (
  id      BIGSERIAL PRIMARY KEY,
  owner   TEXT      NOT NULL,
  balance NUMERIC(15,2) NOT NULL CHECK (balance >= 0)
);

INSERT INTO bank_accounts (owner, balance) VALUES
  ('Alice', 1000.00),
  ('Bob',   500.00);

-- Write a transfer function
CREATE OR REPLACE FUNCTION transfer(
  from_id   BIGINT,
  to_id     BIGINT,
  amount    NUMERIC
) RETURNS VOID AS $$
BEGIN
  -- Validate amount
  IF amount <= 0 THEN
    RAISE EXCEPTION 'Transfer amount must be positive: %', amount;
  END IF;

  -- Lock both accounts in ID order (prevents deadlock)
  UPDATE bank_accounts SET balance = balance - amount
  WHERE id = LEAST(from_id, to_id)
    AND id = from_id;

  UPDATE bank_accounts SET balance = balance + amount
  WHERE id = LEAST(from_id, to_id)
    AND id = to_id;

  -- Simpler approach:
  UPDATE bank_accounts
  SET balance = balance + CASE WHEN id = from_id THEN -amount ELSE amount END
  WHERE id IN (from_id, to_id);
END;
$$ LANGUAGE plpgsql;

-- Test the transfer
BEGIN;
SELECT transfer(1, 2, 200);  -- Alice sends $200 to Bob
COMMIT;

SELECT owner, balance FROM bank_accounts;
-- Alice: 800.00, Bob: 700.00

-- Test that CHECK constraint prevents overdraft
BEGIN;
SELECT transfer(1, 2, 1000);  -- Alice tries to send $1000 (only has $800)
-- ERROR: new row for relation "bank_accounts" violates check constraint
ROLLBACK;

SELECT owner, balance FROM bank_accounts;
-- Alice still has 800.00 — rollback preserved the balance

Summary

Concept	Key Takeaway
`BEGIN` / `COMMIT` / `ROLLBACK`	Group operations atomically; ROLLBACK undoes all changes since BEGIN
Auto-commit	Without BEGIN, every statement is its own transaction
`READ COMMITTED` (default)	Each query sees the latest committed data; use for most application work
`REPEATABLE READ`	Entire transaction sees same snapshot; use for consistent multi-query reports
`SERIALIZABLE`	Strongest isolation; use for complex financial operations
`SAVEPOINT`	Partial rollback to a named point within a transaction
Keep transactions short	Long-held locks block other transactions and cause dead tuple accumulation
Handle errors	After any error, a transaction is aborted — you must ROLLBACK before continuing
`FOR UPDATE`	Lock rows at read time to prevent concurrent modification
`FOR UPDATE SKIP LOCKED`	Claim rows without blocking — the correct pattern for job queues

Module P-4 covers schema design for real applications — normalisation, the correct data types for money and time, soft deletes, audit fields, and the migration tools that keep schemas manageable as they evolve.

Next: P-4 — Schema Design for Real Applications →

Knowledge Check

You are designing a job queue system in PostgreSQL where multiple background workers concurrently claim and process pending jobs from a tasks table. If multiple workers frequently attempt to claim the exact same job simultaneously, which concurrency control mechanism is the most efficient and robust choice to prevent locking contention and ensure high throughput?

During a complex financial reconciliation process, an application uses the default READ COMMITTED isolation level. The application executes a SELECT COUNT(*) on a ledger table, then performs some internal logic, and executes the exact same SELECT COUNT(*) again within the same transaction. The second query returns a different result than the first. What is the fundamental reason for this behavior?

A backend developer has written an API endpoint that opens a database transaction, queries a pending_orders table, makes an HTTP request to a third-party payment gateway that takes 4 seconds, and finally updates the order status to confirmed before committing. From a database performance perspective, why is this an anti-pattern?

Test your knowledge with more question sets

PreviousModule P-2: Indexes — When and How to Add Them Next Module P-4: Schema Design for Real Applications

Discussion

Join the discussion

Loading comments...