PostgreSQL Transactions and Isolation Levels in Practice

Two users buy the last item at the same moment. Without the right transaction design, you sell it twice. These patterns prevent that.

Transactions

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;   -- both happen, or (on error / ROLLBACK) neither does

Isolation levels

• Read Committed (default) - each statement sees data committed before it started. Simple, but read-then-write logic can race.
• Repeatable Read - the whole transaction sees one snapshot; concurrent conflicting updates fail with a serialization error.
• Serializable - behaves as if transactions ran one at a time. Safest, but you must retry on serialization failures.

Pattern 1: atomic updates

-- Race-free: the check and the change are one statement
UPDATE products SET stock = stock - 1
WHERE id = 7 AND stock > 0
RETURNING stock;
-- 0 rows returned = sold out

Pattern 2: lock the row you're about to change

BEGIN;
SELECT balance FROM wallets WHERE user_id = 42 FOR UPDATE;   -- others wait here
-- application checks the balance, computes the new value...
UPDATE wallets SET balance = $1 WHERE user_id = 42;
COMMIT;

Pattern 3: a job queue with SKIP LOCKED

WITH next_job AS (
  SELECT id FROM jobs
  WHERE status = 'queued' AND run_at <= now()
  ORDER BY run_at
  LIMIT 1
  FOR UPDATE SKIP LOCKED      -- workers never grab the same job
)
UPDATE jobs SET status = 'running', started_at = now()
FROM next_job WHERE jobs.id = next_job.id
RETURNING jobs.*;

Pattern 4: serializable with retries

async function withSerializableRetry(fn, attempts = 3) {
  for (let i = 1; ; i++) {
    const client = await pool.connect();
    try {
      await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
      const result = await fn(client);
      await client.query('COMMIT');
      return result;
    } catch (err) {
      await client.query('ROLLBACK');
      if (err.code !== '40001' || i === attempts) throw err; // 40001 = serialization_failure
    } finally {
      client.release();
    }
  }
}

Keep transactions short: never wait for user input or slow external APIs while holding locks.