Transactions & Isolation

The 30-second version

The classic example is a bank transfer. Moving $50 from Alice to Bob takes two changes to the DatabaseDatabaseA program whose whole job is storing data safely and finding it again quickly — Postgres, MySQL, MongoDB, Redis. Your app usually talks to it over a network connection; embedded databases like SQLite run inside your program instead.: subtract 50 from Alice, add 50 to Bob. Two things can go wrong between them:

• A crash. The server dies, the network drops, or your code throws right after the first change. Now Alice has lost $50 and Bob never got it.
• Someone else, at the same time. A second transfer, or a report adding up all balances, runs in the middle of yours and sees (or overwrites) the half-finished state.

Try it. First run the transfer without a transaction and crash it halfway. Then switch to BEGIN … COMMIT and crash it at the same spot:

1BEGIN;
2UPDATE accounts SET balance = balance - 50 WHERE id = 1; -- Alice
3UPDATE accounts SET balance = balance + 50 WHERE id = 2; -- Bob
4COMMIT;

In SQL (the language for talking to databases; §02 covers just enough of it) a transaction looks like this. BEGIN starts it. COMMIT saves everything. ROLLBACK throws everything away, and a crash before COMMIT counts as a rollback:

BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;  -- Alice
UPDATE accounts SET balance = balance + 50 WHERE id = 2;  -- Bob
COMMIT;

The crash part is easy. The "at the same time" part is the real topic.

Making changes all-or-nothing is something the database just does for you. The harder question is how much transactions running at the same moment can see of, and interfere with, each other. Databases let you choose with a setting called the Isolation levelIsolation levelA database setting for how strictly concurrent transactions are kept apart: from READ UNCOMMITTED (may see others' unfinished changes) through READ COMMITTED and REPEATABLE READ up to SERIALIZABLE (as if transactions ran one at a time). Stricter levels prevent more anomalies but may block or abort more., and the default is looser than most people assume. Here's a preview of what's coming:

Just enough SQL

If you've used databases before, skip to §03. Otherwise, this is all the SQL you need for the rest of the page.

One table, three columns

Here is the table used throughout this page, in PostgreSQLPostgreSQL (Postgres)A popular open-source relational SQL database, known for correctness and features. Used throughout this site's database examples. (the database we'll use for every real example):

CREATE TABLE accounts (
  id      int  PRIMARY KEY,                    -- unique number per row
  owner   text NOT NULL,                       -- must always have a value
  balance int  NOT NULL CHECK (balance >= 0)   -- a rule: never negative
);

INSERT INTO accounts (id, owner, balance)
VALUES (1, 'alice', 100), (2, 'bob', 100);     -- add two rows

Reading it back with SELECT (this is real output from psql, Postgres's command-line client):

postgres=# SELECT id, owner, balance FROM accounts;
 id | owner | balance
----+-------+---------
  1 | alice |     100
  2 | bob   |     100
(2 rows)

Changing one row with UPDATE:

UPDATE accounts SET balance = balance - 50 WHERE id = 1;   -- reply: UPDATE 1

Your app talks to the database over a connection

Your Go or Node program isn't inside the database. It opens a network connection to the database server and sends SQL over it, one statement at a time, waiting for each reply. Because opening a connection is slow, programs keep a few open and reuse them. That set of reusable connections is a Connection poolConnection poolA set of open database (or HTTP) connections that your program keeps and reuses instead of opening a new one per request. Pools usually have a size limit; when every connection is busy, the next caller waits for one to be returned.. It matters a lot for transactions (§04).

One more default to know: if you send a statement without BEGIN first, the database treats it as a tiny transaction of its own and commits it right away. That's called AutocommitAutocommitThe default mode of most database drivers: every statement you send outside an explicit BEGIN is its own tiny transaction, committed as soon as it finishes., and it's why the first version of the transfer in §01 could stop halfway with one change saved and the other missing.

ACID, one letter at a time

A — Atomicity: all or nothing

Failure it prevents: the half-done transfer from §01.

If anything goes wrong before COMMIT — a crash, a lost connection, an error, or your own ROLLBACK — the database undoes every change the transaction made. You never have to write "undo" code yourself.

In PostgreSQL, an error also poisons the rest of the transaction: after one statement fails, every further statement is refused until you roll back. This is real output from psql. Its prompt changes from =# to =*# inside a transaction, and to =!# once the transaction has failed:

postgres=# BEGIN;
BEGIN
postgres=*# UPDATE accounts SET balance = balance - 500 WHERE id = 1;
ERROR:  new row for relation "accounts" violates check constraint "accounts_balance_check"
DETAIL:  Failing row contains (1, alice, -450).
postgres=!# SELECT * FROM accounts;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
postgres=!# COMMIT;
ROLLBACK

(Alice had 50 after §02's UPDATE, so 50 − 500 = −450.) Notice the last line: you typed COMMIT, and Postgres answered ROLLBACK. A failed transaction can't be committed.

C — Consistency: the rules still hold afterwards

Failure it prevents: data that breaks your rules, like a negative balance or an order pointing at a customer who doesn't exist.

Consistency is the odd letter out: it's mostly your job. The database helps in one way. It enforces any constraintsConstraintA rule the database itself enforces on every write: NOT NULL, UNIQUE, CHECK (balance >= 0), FOREIGN KEY. A write that would break it fails with an error, so bad data can't get in through any code path. you declare — NOT NULL, UNIQUE, CHECK, foreign keys — on every single write, whichever code path the write came from:

postgres=# UPDATE accounts SET balance = balance - 500 WHERE id = 1;
ERROR:  new row for relation "accounts" violates check constraint "accounts_balance_check"
DETAIL:  Failing row contains (1, alice, -450).

But the database can't know rules you never told it. The bank's real InvariantInvariantA rule that must always hold true about your data — “an account balance is never negative”, “area equals width × height”. Code (and transactions) must keep it true before and after every change. is "the total amount of money never changes". Watch this transfer to a mistyped account id:

postgres=# BEGIN;
BEGIN
postgres=*# UPDATE accounts SET balance = balance - 30 WHERE id = 1;
UPDATE 1
postgres=*# UPDATE accounts SET balance = balance + 30 WHERE id = 99;   -- no such account
UPDATE 0
postgres=*# COMMIT;
COMMIT

UPDATE 0 isn't an error, so the transaction committed and $30 vanished. Atomicity only helps if your code notices the problem and rolls back. That's why §04's code checks how many rows each UPDATE changed.

I — Isolation: as if you were alone

Failure it prevents: another transaction seeing or overwriting your half-finished work.

The ideal is that every transaction behaves as if it ran alone, one after another, even when hundreds run at the same moment. Perfect isolation is expensive, so databases offer weaker settings, called isolation levels, that allow some interference in exchange for speed. Which interference each level allows is the subject of §05 and §06, and it's where most real bugs live.

D — Durability: committed means committed

Failure it prevents: the database replies "COMMIT", then the power goes out, and the change is gone.

Writing to a disk is slow, so operating systems normally keep "written" data in memory and save it to disk a bit later. A power cut loses whatever was still in memory. To be durableDurabilityThe promise that once the database says “committed”, the data survives a crash or power cut — usually because it was written to disk (and fsynced) before replying., the database writes every change into a Write-ahead logWrite-ahead log (WAL)An append-only file where a database records each change before applying it. After a crash it replays the log to recover; replicas can follow it to copy changes. — an append-only file — and calls fsyncfsyncA system call that forces data the OS has buffered in memory to actually be written to the disk. Slow (often milliseconds), but without it a power cut can lose “written” data. to force it onto the disk before it tells you the commit succeeded:

Appending to one file and syncing it is much faster than rewriting table files all over the disk. That's why nearly every database works this way. After a crash, the database replays the log: changes that have a commit record come back, and changes without one are discarded. So the WAL serves atomicity too.

BEGIN, COMMIT, ROLLBACK in Go and JavaScript

Why one connection? The database remembers "this connection is inside a transaction". BEGIN sent on connection #1 means nothing to connection #2. So the code has to make sure that BEGIN, every statement, and COMMIT or ROLLBACK all travel down the same connection.

Setup: the pool

In both languages, the thing you create at startup is a pool of connections, not one connection:

package main

import (
    "context"
    "database/sql"
    "log"

    _ "github.com/jackc/pgx/v5/stdlib" // registers the "pgx" driver
)

func main() {
    db, err := sql.Open("pgx", "postgres://app:secret@localhost:5432/shop")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close() // db is a POOL of connections, not one connection

    ctx := context.Background()
    if err := transfer(ctx, db, 1, 2, 30); err != nil {
        log.Println("transfer failed:", err)
    }
}

The safe shape

Here's the transfer from §01, written properly. It also fixes the "UPDATE 0 isn't an error" hole from §03 by checking how many rows each update changed. Together with the setup above, each tab is a complete program you can run (in Go as two files in one package; in JS, paste both into one file).

package main

import (
    "context"
    "database/sql"
    "fmt"
)

func transfer(ctx context.Context, db *sql.DB, from, to, amount int) error {
    tx, err := db.BeginTx(ctx, nil) // BEGIN — tx is pinned to ONE connection
    if err != nil {
        return err
    }
    defer tx.Rollback() // safety net: undoes everything unless Commit ran first

    if err := move(ctx, tx, from, -amount); err != nil {
        return err
    }
    if err := move(ctx, tx, to, amount); err != nil {
        return err
    }
    return tx.Commit() // COMMIT
}

func move(ctx context.Context, tx *sql.Tx, id, delta int) error {
    res, err := tx.ExecContext(ctx, // ← tx, not db
        "UPDATE accounts SET balance = balance + $1 WHERE id = $2", delta, id)
    if err != nil {
        return err // e.g. the CHECK (balance >= 0) constraint failed
    }
    n, err := res.RowsAffected()
    if err != nil {
        return err
    }
    if n != 1 {
        return fmt.Errorf("account %d not found", id) // UPDATE 0 → roll back
    }
    return nil
}

Running both against a real Postgres: transferring 30 works, a transfer to account 99 fails with account 99 not found, and a transfer of 500 fails with the constraint error from §03 (SQLSTATE 23514). In both failure cases the balances are unchanged, because the debit was rolled back.

The bug: a transaction on the pool

This version looks almost the same. It's broken:

await pool.query("BEGIN");
await pool.query(
  "UPDATE accounts SET balance = balance - $1 WHERE id = $2",
  [amount, from]);
// … something throws …
await pool.query("ROLLBACK");

const client = await pool.connect();
try {
  await client.query("BEGIN");
  await client.query("UPDATE …", [amount, from]);
  await client.query("COMMIT");
} catch (err) {
  await client.query("ROLLBACK").catch(() => {});
  throw err;
} finally {
  client.release();
}

pool.query() borrows some free connection, runs one statement, and hands the connection straight back. Under load, the three calls can land on three different connections. We ran the broken version against a real Postgres with one other request using the pool at the same time:

server says: WARNING  there is no transaction in progress
[ { id: 1, balance: 90 }, { id: 2, balance: 100 } ]

Step through what happened, connection by connection, then flip to the fixed version:

1try {
2  await pool.query("BEGIN");
3  await somethingSlow();            // any await: other requests use the pool
4  await pool.query(
5    "UPDATE accounts SET balance = balance - 10 WHERE id = 1");
6  throw new Error("crash before the credit");
7} catch (err) {
8  await pool.query("ROLLBACK");
9}

In short:

1. BEGIN ran on connection #1, which went back to the pool still inside an open transaction. The other request then borrowed it, so its queries ran inside our transaction.
2. The UPDATE ran on connection #2 in autocommit mode, so it was committed immediately: Alice lost $10.
3. ROLLBACK landed on a connection with no transaction, so Postgres just printed a warning. Nothing was undone.

It's the worst kind of bug: in development, with one user, all three calls usually get the same idle connection, and the code seems to work.

Six ways concurrent transactions go wrong

An AnomalyAnomaly (concurrency)A wrong or surprising result that can only happen because two transactions ran at the same time — e.g. reading a value that was never committed, or two updates where one disappears. Each isolation level promises to prevent some anomalies. isn't a database bug. Each one is a specific interference that a given isolation level allows. To reason about them, you need a picture of two sessions running side by side. Here are the six you'll meet in practice, as real interleavings. Except for the first, the outcomes were recorded on PostgreSQL 16 at its default level, READ COMMITTED.

The scenarios use the accounts table from §02, plus one more for the last scenario, a hospital's on-call list:

CREATE TABLE doctors (
  name    text    PRIMARY KEY,
  on_call boolean NOT NULL       -- true = currently on call
);
INSERT INTO doctors VALUES ('alice', true), ('bob', true);

1A: BEGIN;
2A: UPDATE accounts SET balance = 0 WHERE id = 1;
3B: SELECT balance FROM accounts WHERE id = 1;
4A: ROLLBACK;

The six, in one table

How they relate

The first three are about reading. They're the three the 1992 SQL standard defines its isolation levels with (§06).

• A dirty read sees data that isn't committed at all.
• A non-repeatable read sees two different committed versions of the same row.
• A phantom sees a different set of rows for the same search, because rows were inserted or deleted.

Read skew is the multi-row version of a non-repeatable read. No single row was read twice, but the two rows came from different moments, so their combination is nonsense.

The last two are about writing based on something you read. That's the pattern behind most real money-losing bugs.

• A lost update is two writers on the same row. You met it in Race conditions §05, along with three fixes: do the arithmetic in one UPDATE … SET x = x + 10, use a version column, or lock with SELECT … FOR UPDATE.
• Write skew is the sneaky cousin. The writers touch different rows, so nothing ever overwrites anything, and locking the rows you write can't catch it. What connects the two transactions is the rows they both read.

Isolation levels: the standard vs reality

What the SQL standard says

The 1992 SQL standard defines four levels by the three reading anomalies each must prevent. Stricter levels prevent more:

SERIALIZABLE has a stronger definition than "prevents those three": the result must be the same as if the transactions had run one at a time, in some order. That rules out every anomaly, including the three the table doesn't mention.

The standard sets a minimum. A database may prevent more than a level requires. It may also implement each level its own way. So "REPEATABLE READ" in PostgreSQL and "REPEATABLE READ" in MySQL behave differently, and you need to know which one you're on.

The matrix

Switch between the standard, PostgreSQL and MySQL. Click any cell. On PostgreSQL, press replay to run that exact interleaving from §05 at that level. Every output you see came from a real PostgreSQL 16 server.

What PostgreSQL actually does

• Default: READ COMMITTED. Each statement takes a fresh SnapshotSnapshotA frozen view of the database as of one moment: “every transaction that had committed by then, and nothing after”. Queries that use a snapshot ignore later changes, even committed ones.: a frozen view of all data committed at the moment the statement started. So you never see uncommitted data, but two statements in the same transaction can see different data. That allows non-repeatable reads, phantoms, read skew, lost updates and write skew.
• READ UNCOMMITTED is just READ COMMITTED. Postgres accepts the name, but it never shows uncommitted data.
• REPEATABLE READ is Snapshot isolationSnapshot isolationAn isolation scheme where each transaction reads from one snapshot taken at its start, and a transaction fails if it tries to change a row someone else changed after that snapshot. PostgreSQL's REPEATABLE READ works this way. It still allows write skew.. The whole transaction uses one snapshot, taken at its first statement (not at BEGIN). Re-reads and re-searches return the same data, so phantoms are prevented too, which is stricter than the standard requires. If you try to update or delete a row that someone else changed and committed after your snapshot, you get ERROR: could not serialize access due to concurrent update (SQLSTATESQLSTATEA standard five-character error code that databases return alongside the message, e.g. 40P01 = deadlock detected, 23505 = unique violation. Check the code, not the message text. 40001). That's how it stops lost updates. It still allows write skew, because write skew never touches the same row twice.
• SERIALIZABLE is snapshot isolation plus tracking of who read what. If a group of transactions couldn't have produced their results one at a time, one of them fails with 40001. §09 shows how.

What MySQL (InnoDB) does

These come from the MySQL 8 manual; we didn't run MySQL for this page.

• Default: REPEATABLE READ. Plain SELECTs read from one snapshot taken at the first read, much like Postgres.
• But locking readsLocking readA read that also locks the rows it reads: SELECT … FOR UPDATE / FOR SHARE, and the reading part of UPDATE and DELETE. In MySQL, locking reads see the latest committed data rather than the transaction's snapshot. see the latest data. SELECT … FOR UPDATE (read and lock rows, covered in §08), UPDATE and DELETE read the current committed rows, not your snapshot. They also take gap locksGap lock (MySQL)In MySQL/InnoDB, a lock on the space between index entries, not just on existing rows. It stops other transactions from inserting new rows into a range you've read with a locking read — InnoDB's way of preventing phantoms at REPEATABLE READ., which stop other transactions from inserting into the range you touched. That's how InnoDB prevents phantoms for locking reads. Mixing plain and locking reads in one transaction can still surprise you.
• Lost updates are not detected at REPEATABLE READ. The deposit race from §05 silently loses an update in MySQL's default level. It fails with an error in Postgres's.
• READ UNCOMMITTED really allows dirty reads. Almost nobody wants this.
• SERIALIZABLE uses locks. Every plain SELECT becomes SELECT … FOR SHARE (a read lock: others can read but not change those rows until you finish, §08), so readers block writers. Conflicts usually end in a DeadlockDeadlockTwo or more tasks each wait for something another one holds, so none can ever continue — e.g. transaction A waits for B's row lock while B waits for A's. Go crashes with “all goroutines are asleep” when every goroutine is stuck; databases detect it and abort one transaction. — two transactions each waiting for the other's lock — and MySQL aborts one with error 1213. You retry it.

Choosing and setting the level

Set it per transaction, at the start:

tx, err := db.BeginTx(ctx, &sql.TxOptions{
    Isolation: sql.LevelRepeatableRead, // or sql.LevelSerializable
    ReadOnly:  true,                    // optional: refuse writes in this tx
})

MVCC: readers and writers don't block each other

That's MVCCMVCC (multi-version concurrency control)The database keeps several versions of a row available at once, so each transaction can read the version that matches its snapshot. PostgreSQL writes each new version next to the old one; MySQL/InnoDB and Oracle update in place but can rebuild old versions from an undo log. Either way, readers never wait for writers and writers never wait for readers., and it's how PostgreSQL, MySQL/InnoDB, Oracle and most modern databases implement the snapshots from §06. They differ in where the old versions live. PostgreSQL writes each new version next to the old one in the table. InnoDB and Oracle update the row in place but keep the old value in an undo log (§03), and rebuild old versions from it on demand. This section uses PostgreSQL, where you can see the versions directly.

Every row version says who created it and who replaced it

In PostgreSQL, each writing transaction gets a number, its transaction IDTransaction ID (xmin / xmax)A number PostgreSQL gives each writing transaction, counting up. Every row version stores xmin (the transaction that created it) and xmax (the one that deleted or replaced it, or 0 if none). Visibility rules compare these with your snapshot., and the numbers count up. Every row versionRow version (tuple)One physical copy of a row inside a table file. With MVCC an UPDATE creates a new version and marks the old one as replaced; several versions of the same row can exist at once. PostgreSQL calls them tuples. carries two hidden columns:

• xmin: the transaction that created this version.
• xmax: the transaction that deleted or replaced it, or 0 if nobody has.

You can select them like normal columns. Real output, before and after one UPDATE (blank lines and row counts trimmed):

postgres=# SELECT xmin, xmax, id, balance FROM accounts WHERE id = 1;
 xmin | xmax | id | balance
------+------+----+---------
  788 |    0 |  1 |     100

postgres=# UPDATE accounts SET balance = 50 WHERE id = 1;
postgres=# SELECT xmin, xmax, id, balance FROM accounts WHERE id = 1;
 xmin | xmax | id | balance
------+------+----+---------
  790 |    0 |  1 |      50      ← a different physical version, created by tx 790

The old version (xmin 788) is still in the table file, now stamped xmax 790. A query just doesn't show it to you any more.

The visibility rule

A SnapshotSnapshotA frozen view of the database as of one moment: “every transaction that had committed by then, and nothing after”. Queries that use a snapshot ignore later changes, even committed ones. is essentially a list: "these transactions had committed when I started; everything else is invisible to me". A row version is visible to you if:

1. the transaction that created it (xmin) had committed in your snapshot — or is your own transaction — and
2. the transaction that replaced it (xmax) had not committed in your snapshot (or there isn't one), and isn't your own transaction.

The "your own" parts are why, inside a transaction, you see your own uncommitted changes (Alice at 50 in §01) while nobody else does.

That's the whole trick. A writer creates a new version that nobody else can see until it commits. A reader keeps seeing the version that matches its snapshot. Nobody has to wait. Play with it:

Things to try:

1. Uncommitted changes are invisible. Press UPDATE: the new version is orange, and everyone still reads the green one. Then ROLLBACK. Nothing gets copied back. The new version is simply ignored from now on, which is why rollback is instant in Postgres.
2. Readers don't block writers. Start the reader, then UPDATE + COMMIT twice. The reader's SELECT still returns its old value, and the writer never waited.
3. Old snapshots hold back clean-up. Start the reader, then UPDATE + COMMIT twice, then press VACUUM. A dashed horizon line appears at the reader's start. VACUUM keeps every version replaced right of that line — even ones the reader can't see — because PostgreSQL only checks "was this replaced after the oldest open snapshot began?", not who needs what. Commit the reader and VACUUM again.

VACUUM: taking out the trash

Every UPDATE and DELETE leaves a dead version behind. VACUUMVACUUMPostgreSQL's clean-up job: it finds row versions that no running or future transaction can see any more (“dead” versions) and frees their space for reuse. Autovacuum runs it automatically in the background. finds versions that no running transaction can see any more and frees their space for reuse. You rarely run it by hand: AutovacuumAutovacuumA PostgreSQL background worker that runs VACUUM on a table automatically once enough of its rows have been updated or deleted. Leave it on; tune it rather than disabling it. runs it in the background once enough rows have changed.

Here's the "old snapshot" experiment run for real. Session B opened a REPEATABLE READ transaction and read Alice's row. Then session A updated that row three times. B still sees the original version (xmax 794 means "replaced by 794, but 794 isn't in my snapshot"):

-- session B, after A's three updates (trimmed):
 xmin | xmax | balance
  793 |  794 |     100

-- session A sees the newest version:
 xmin | xmax | balance
  796 |    0 |      70

Now A runs VACUUM (VERBOSE) accounts while B is still open (output trimmed to the two relevant lines):

tuples: 0 removed, 5 remain, 3 are dead but not yet removable
removable cutoff: 794, which was 3 XIDs old when operation ended

(XID = transaction ID. The "removable cutoff" is the horizon from the widget: B's snapshot was taken before transaction 794 even started, so nothing replaced by 794 or later may go. That covers all three old versions, including two that B can't even see.)

"Dead but not yet removable": B's snapshot might still need them. The server's activity view shows why. B is idle in transaction, holding an old snapshot (backend_xmin is the oldest transaction ID it might still need):

postgres=# SELECT pid, state, backend_xmin FROM pg_stat_activity WHERE pid = 21765;
  pid  |        state        | backend_xmin
-------+---------------------+--------------
 21765 | idle in transaction |          794

After B commits, the same VACUUM reports tuples: 3 removed, 2 remain, 0 are dead but not yet removable.

Locks: FOR UPDATE, FOR SHARE, SKIP LOCKED

Every UPDATE or DELETE locks the rows it changes until the transaction ends. A second transaction that wants to change the same row waits until the first commits or rolls back. Plain SELECTs never wait for these locks; they just read the right version (§07).

You can also take a Row lockRow lock (SELECT … FOR UPDATE)Pessimistic locking (“lock first, then read and write”) in a database: reading a row with FOR UPDATE locks it until your transaction ends, so other transactions that want to lock or change it wait. while reading, which is how you stop someone else from changing the rows your decision depends on. Race conditions §05 introduced SELECT … FOR UPDATE for the lost-update problem. Here's the full toolkit.

All locks are released at COMMIT or ROLLBACK. There's no "unlock" statement.

Fixing write skew with FOR UPDATE

The doctors from §05 broke the rule because both transactions read the same rows but wrote different ones. Lock the rows you read, and the second transaction has to wait for the first:

BEGIN;
SELECT name FROM doctors WHERE on_call FOR UPDATE;   -- lock every on-call doctor
-- app: if fewer than 2 rows came back, ROLLBACK and refuse
UPDATE doctors SET on_call = false WHERE name = 'alice';
COMMIT;

We ran it in two sessions at READ COMMITTED. Session A's SELECT … FOR UPDATE returned alice and bob. Session B's identical SELECT waited. When A committed, B's SELECT finished and returned only bob, because at READ COMMITTED Postgres re-checks the WHERE against the newest committed version of each row it waited for. B saw one doctor and refused. ✓

A job queue with SKIP LOCKED

A Job queueJob queueA list of work items waiting to be done, usually stored outside the program (Redis, a database table, SQS) so that separate worker processes can pick them up, retry them, and survive restarts. in a table is a classic use. Many workers repeatedly grab "the oldest queued job". With plain FOR UPDATE, they all go for the same first row and line up behind each other. SKIP LOCKEDSKIP LOCKEDAn option for SELECT … FOR UPDATE: instead of waiting for rows another transaction has locked, silently skip them and return the next unlocked ones. The standard trick for many workers pulling jobs from one queue table. lets each worker skip rows that another worker has already locked:

You don't even need an explicit transaction. One statement finds a job, locks it, marks it running and returns it. As a single statement, it's atomic on its own:

UPDATE jobs SET status = 'running', started_at = now()
WHERE id = (
  SELECT id FROM jobs
  WHERE status = 'queued'
  ORDER BY id
  LIMIT 1
  FOR UPDATE SKIP LOCKED      -- skip jobs other workers are claiming right now
)
RETURNING id, payload;        -- hand back the claimed row

// claimSQL is the UPDATE … RETURNING statement above, as a string.
// claimJob returns ok == false when the queue is empty.
func claimJob(ctx context.Context, db *sql.DB) (id int, payload string, ok bool, err error) {
    err = db.QueryRowContext(ctx, claimSQL).Scan(&id, &payload)
    if errors.Is(err, sql.ErrNoRows) {
        return 0, "", false, nil // nothing queued (or all locked by other workers)
    }
    if err != nil {
        return 0, "", false, err
    }
    return id, payload, true, nil
}

We ran 8 workers in parallel against 50 jobs with this query: 50 claims, 50 different jobs, no duplicates. The worker then runs the job outside any transaction and marks it done afterwards. If a worker crashes mid-job, the row stays running. So add a reaper that re-queues jobs whose started_at is too old, and make jobs safe to run twice.

Don't wait forever: NOWAIT and lock_timeout

By default, a transaction waits for a lock as long as it takes. Two ways to bound that, both real Postgres output:

-- another session holds job 1 locked
postgres=# BEGIN;
BEGIN
postgres=*# SELECT id FROM jobs WHERE id = 1 FOR UPDATE NOWAIT;
ERROR:  could not obtain lock on row in relation "jobs"
postgres=!# ROLLBACK;
ROLLBACK
postgres=# BEGIN;                         -- a new transaction
BEGIN
postgres=*# SET LOCAL lock_timeout = '200ms';
SET
postgres=*# SELECT id FROM jobs WHERE id = 1 FOR UPDATE;
ERROR:  canceling statement due to lock timeout
postgres=!# ROLLBACK;
ROLLBACK

Both errors have SQLSTATE 55P03, and both abort the transaction, so you must roll back. lock_timeoutlock_timeoutA PostgreSQL setting: if a statement has waited this long for a lock, give up with an error (SQLSTATE 55P03) instead of waiting forever. The transaction is then aborted and must be rolled back. is a good safety net to set for a whole service, a second or two for web requests. SET LOCAL changes a setting for the current transaction only.

Deadlocks

Two transactions that lock the same rows in opposite orders wait for each other forever: a DeadlockDeadlockTwo or more tasks each wait for something another one holds, so none can ever continue — e.g. transaction A waits for B's row lock while B waits for A's. Go crashes with “all goroutines are asleep” when every goroutine is stuck; databases detect it and abort one transaction.. Postgres detects this after about a second and kills one of them with ERROR: deadlock detected (40P01). The fixes are to lock rows in a consistent order (for example ORDER BY id FOR UPDATE) and to retry. Race conditions §10 walks through it.

Or don't lock at all: optimistic concurrency

Locks are pessimistic: they assume a conflict will happen, and make others wait. Optimistic lockingOptimistic lockingDon't lock while reading. When writing, check that nobody changed the row since you read it (usually with a version number); if someone did, re-read and retry. assumes conflicts are rare. Add a version column, and make the write succeed only if nobody changed the row since you read it:

UPDATE docs SET body = $1, version = version + 1
WHERE id = $2 AND version = $3;    -- $3 = the version you read; 0 rows = someone beat you

It holds no lock while the user is thinking. That's why it's the standard choice for "edit form open for five minutes", where a FOR UPDATE held that long would be a disaster. Details and retry code are in Race conditions §05.

SERIALIZABLE in practice: retry the whole thing

That's a good deal. With locks (§08), you have to spot every write-skew shape in your code and lock exactly the right rows. Miss one, and the bug ships. With SERIALIZABLE, every transaction is protected, including the ones you didn't think about. The cost is a retry loop, which you write once.

How PostgreSQL decides (SSI, simplified)

PostgreSQL implements SERIALIZABLE as SSISSI (Serializable Snapshot Isolation)How PostgreSQL implements SERIALIZABLE: transactions run on snapshots as usual, while the database tracks who read what. If a set of concurrent transactions couldn't have produced their result in any one-at-a-time order, it aborts one of them with SQLSTATE 40001.. Transactions run on snapshots exactly as at REPEATABLE READ, so readers never wait. (Two writers on the same row still take turns on the row lock, as in §08.) In addition, the database quietly records what each transaction read, including the searches it ran, not just the rows it got back.

Then it watches for one pattern. "A read something that B later changed, and B read something that A changed" means neither order works:

• If A ran first, B should have seen A's change.
• If B ran first, A should have seen B's.

That's exactly the doctors from §05. A counted the doctors (including Bob's row), and B changed Bob's row. B counted too (including Alice's row), and A changed Alice's. No one-at-a-time order produces "both went off call", so Postgres aborts one of them:

Real output, at B's COMMIT:

ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.

("Pivot" is Postgres's name for the transaction in the middle of the dangerous read/write pattern. You can ignore the reason code; the SQLSTATE is what matters.) The SQLSTATESQLSTATEA standard five-character error code that databases return alongside the message, e.g. 40P01 = deadlock detected, 23505 = unique violation. Check the code, not the message text. is 40001, the same code as the REPEATABLE READ error from §06. The hint is literal advice: retry it.

The retry: run everything again, from BEGIN

The abort rolls back the whole transaction, so retrying just the failed statement makes no sense. Its earlier reads came from a snapshot that's now stale. The unit of retry is "the function that does BEGIN … COMMIT". The cleanest design is a helper that owns BEGIN and COMMIT, so callers can't get it wrong:

import (
    "context"
    "database/sql"
    "errors"
    "math/rand/v2"
    "time"

    "github.com/jackc/pgx/v5/pgconn" // pgx's error type, *pgconn.PgError
)

// inTx runs fn inside a SERIALIZABLE transaction, retrying on 40001 / 40P01.
func inTx(ctx context.Context, db *sql.DB, fn func(tx *sql.Tx) error) error {
    opts := &sql.TxOptions{Isolation: sql.LevelSerializable}
    for attempt := 1; ; attempt++ {
        err := runOnce(ctx, db, opts, fn)
        var pgErr *pgconn.PgError
        retryable := errors.As(err, &pgErr) &&
            (pgErr.Code == "40001" || pgErr.Code == "40P01")
        if !retryable || attempt == 5 {
            return err // nil on success, or a non-retryable error
        }
        wait := time.Duration(attempt*20+rand.IntN(20)) * time.Millisecond
        select {
        case <-time.After(wait): // back off with jitter, then run it ALL again
        case <-ctx.Done():
            return ctx.Err()
        }
    }
}

func runOnce(ctx context.Context, db *sql.DB, opts *sql.TxOptions, fn func(tx *sql.Tx) error) error {
    tx, err := db.BeginTx(ctx, opts)
    if err != nil {
        return err
    }
    defer tx.Rollback()
    if err := fn(tx); err != nil {
        return err
    }
    return tx.Commit() // ← a 40001 can come from COMMIT too
}

And the doctors, written against it. The business rule is a plain if. The helper makes the race impossible:

var ErrLastDoctor = errors.New("you're the last doctor on call")

func goOffCall(ctx context.Context, db *sql.DB, name string) error {
    return inTx(ctx, db, func(tx *sql.Tx) error {
        var n int
        err := tx.QueryRowContext(ctx,
            "SELECT count(*) FROM doctors WHERE on_call").Scan(&n)
        if err != nil {
            return err
        }
        if n < 2 {
            return ErrLastDoctor // not retryable: inTx returns it as is
        }
        _, err = tx.ExecContext(ctx,
            "UPDATE doctors SET on_call = false WHERE name = $1", name)
        return err
    })
}

We ran both against Postgres with Alice and Bob going off call at the same time. One succeeded, and the other got "you're the last doctor on call", sometimes on its first attempt, sometimes after a 40001 and a retry. Six JS requests racing each other needed 10 attempts in total, and exactly one doctor stayed on call every time.

What makes a retry safe

The function you pass in may run several times. Only the database work gets rolled back between attempts. Everything else happens again.

await inTx(pool, async (tx) => {
  const order = await placeOrder(tx, cart);
  await sendReceiptEmail(order);   // sent again on every retry
  await chargeCard(order);         // charged again on every retry!
});

const order = await inTx(pool, (tx) => placeOrder(tx, cart));
await chargeCard(order);           // once, after the commit succeeded
await sendReceiptEmail(order);

• No external calls inside. HTTP requests, emails, payments and queue messages can't be rolled back. Do them after the commit, or record them in an outbox table inside the transaction (§10). If they must be retried, make them IdempotentIdempotentDoing it twice has the same effect as doing it once. “Set balance to 50” is idempotent; “add 50 to balance” is not — so only the first is safe to retry blindly. with an idempotency key.
• No in-memory changes that pile up. total += x or results.push(row) on a variable outside the function gets applied once per attempt.
• Don't retry other errors. A constraint violation (23505, 23514) or "insufficient funds" fails the same way every time.
• Cap the attempts, and use JitterJitterA small random amount added to a delay or timer — a retry wait, a reconnect, a connection's maximum age — so that many clients don't all act at the same instant and stampede a server.. Without the random part, two transactions that collided once retry in lockstep and collide again.

Production: short transactions, pools, savepoints, outbox

Do the arithmetic: why short matters

Each open transaction holds three things:

1. A pooled connection. Say your Connection poolConnection poolA set of open database (or HTTP) connections that your program keeps and reuses instead of opening a new one per request. Pools usually have a size limit; when every connection is busy, the next caller waits for one to be returned. has 10 connections and each request keeps one inside a transaction for 2 seconds. Then the service handles at most 10 / 2 s = 5 requests per second. Request number 11 waits for a free connection, then the request after it, and so on. (Worker pools explains why that queue grows so fast.)
2. Its row locks. If every checkout locks the same hot row (a popular product's stock) for 2 seconds, that row allows at most one checkout every 2 seconds, however many servers you run.
3. Its snapshot. As §07 showed, an open snapshot stops VACUUM from cleaning up anywhere in the database.

The same transaction at 20 ms instead of 2 s gives 500 requests per second from the same pool. The length of your transactions sets your throughput ceiling.

Never wait on the outside world inside a transaction

await client.query("BEGIN");
const { rows } = await client.query(
  "SELECT * FROM orders WHERE id = $1 FOR UPDATE", [id]);
const payment = await paymentApi.charge(rows[0]); // 300 ms… or 30 s
await client.query(
  "UPDATE orders SET status = 'paid' WHERE id = $1", [id]);
await client.query("COMMIT");

// 1. call the API with no transaction open (use an idempotency key)
const payment = await paymentApi.charge(order, { idempotencyKey: order.id });
// 2. a short transaction to record the result (inTx from §09
//    does BEGIN/COMMIT/ROLLBACK/release for you)
await inTx(pool, (tx) => tx.query(
  "UPDATE orders SET status = 'paid', payment_id = $2 WHERE id = $1 AND status = 'pending'",
  [order.id, payment.id]));

On the left, the payment provider's speed now sets your database's lock times and pool usage. When it has a bad day, so does every endpoint that touches orders. The same goes for waiting on a user ("hold the seat while they type their card number") and for slow computation. Do the slow part with no transaction open, then use a short transaction to record the result. A conditional WHERE status = 'pending' stops a double record. The AND status = … trick is the same check-and-act-in-one-statement idea as Race conditions §05.

Guard rails: timeouts the database enforces

Bugs will leave transactions open anyway: a missing release(), an exception path that skips ROLLBACK, a debugger paused on a breakpoint. PostgreSQL can enforce limits for you, per role or per database:

ALTER ROLE app SET idle_in_transaction_session_timeout = '30s'; -- kill forgotten transactions
ALTER ROLE app SET statement_timeout = '5s';                    -- kill runaway queries
ALTER ROLE app SET lock_timeout = '2s';                         -- don't queue forever on a lock

When a session sits Idle in transactionIdle in transactionA database connection that ran BEGIN (and maybe some statements) and is now doing nothing — while still holding its locks, its snapshot and a pooled connection. Usually your app is waiting on something slow, or forgot to COMMIT. past the limit, Postgres ends it. Real output in psql, with the limit set to 1 s (trimmed to the error):

FATAL:  terminating connection due to idle-in-transaction timeout
server closed the connection unexpectedly

Your app sees a broken connection, and the transaction is rolled back. That's much better than six hours of growing bloat. To find the offenders, ask the server:

SELECT pid, now() - xact_start AS open_for, state, left(query, 60) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY open_for DESC;

Savepoints: undo part of a transaction

A SavepointSavepointA named bookmark inside a transaction. ROLLBACK TO SAVEPOINT undoes only the work done after the bookmark; the rest of the transaction carries on and can still commit. is a bookmark inside a transaction. Rolling back to it undoes only the work since the bookmark. It's also the only way to recover from an error in Postgres without losing the whole transaction (remember §03: one error poisons the rest). Real output:

postgres=# BEGIN;
BEGIN
postgres=*# INSERT INTO doctors VALUES ('dave', true);
INSERT 0 1
postgres=*# SAVEPOINT before_import;
SAVEPOINT
postgres=*# INSERT INTO doctors VALUES ('alice', true);
ERROR:  duplicate key value violates unique constraint "doctors_pkey"
DETAIL:  Key (name)=(alice) already exists.
postgres=!# ROLLBACK TO SAVEPOINT before_import;
ROLLBACK
postgres=*# INSERT INTO doctors VALUES ('carol', true);
INSERT 0 1
postgres=*# COMMIT;
COMMIT          -- dave and carol were saved; the failed alice insert was not

Many ORMsORMObject-relational mapper: a library (Prisma, GORM, Hibernate…) that maps database rows to objects in your code and writes much of the SQL for you. implement "nested transactions" this way: the inner "transaction" is really a savepoint. Use them sparingly. Each savepoint is a subtransaction inside Postgres, and transactions that create very many of them (dozens or more) are a known performance cliff.

Changing the database and telling the world: the outbox

A common need: save an order, and publish an "order paid" message to a message broker. Those are two systems, so no single transaction covers both. Publish first and then crash, and the world hears about an order that doesn't exist. Commit first and then crash, and nobody ever hears about it.

The Outbox patternOutbox patternWrite an outgoing event into an outbox table in the same database transaction as the change itself; a separate process reads that table and publishes the events. Either both happen or neither does. fixes this with a transaction you already have. Write the message into an outbox table in the same transaction as the change. A separate relay process reads the outbox and publishes:

BEGIN;
UPDATE orders SET status = 'paid' WHERE id = 42;
INSERT INTO outbox (topic, payload) VALUES ('order.paid', '{"id": 42}');
COMMIT;   -- both rows, or neither

The relay may publish a message twice (if it crashes after publishing, before marking the row sent), so consumers must handle duplicates. Behavioral patterns §10 has an interactive crash demo of exactly this. The relay itself is a job queue, and FOR UPDATE SKIP LOCKED from §08 is a natural fit for it.

Where transactions end

Everything on this page is about one database. Once a business operation spans several databases or services (charge the card in the payments service, reserve stock in the inventory service), there's no shared BEGIN and COMMIT.

• Two-phase commitTwo-phase commit (2PC)A protocol for committing one transaction across several databases: a coordinator first asks everyone to “prepare” (promise they can commit), then tells all of them to commit. Correct but slow, and stuck if the coordinator dies mid-way. (2PC) makes several databases commit together, by having a coordinator ask each one to "prepare" and then to "commit". Postgres supports the building blocks (PREPARE TRANSACTION), though they're switched off by default (max_prepared_transactions = 0). But 2PC is slow, holds locks across the network, and gets stuck if the coordinator dies mid-way. Most teams avoid it.
• Sagas split the operation into local transactions, each with a "compensating" undo step (refund the card if the stock reservation fails). It's a different model: other users can see the in-between states. See Saga.

Pitfalls gallery

tx, _ := db.BeginTx(ctx, nil)
defer tx.Rollback()
tx.ExecContext(ctx, debitSQL, from)
audit(ctx, db, "transfer") // ← runs on db: commits on its own
return tx.Commit()

tx, err := db.BeginTx(ctx, nil)
if err != nil {
    return err
}
defer tx.Rollback()
// … every statement, including helpers, uses tx …
if err := audit(ctx, tx, "transfer"); err != nil {
    return err
}
return tx.Commit()

ERROR:  current transaction is aborted, commands ignored until end of transaction block

Check yourself

Commit to an answer before you click. Each explanation points back to the section that covers it. Q1 is from §04, Q2–Q4 from §05–§06, Q5 from §07, Q6 from §09 and Q7 from §06.

tx, err := db.BeginTx(ctx, nil)
if err != nil {
    return err
}
defer tx.Rollback()

_, err = db.ExecContext(ctx,
    "UPDATE accounts SET balance = 0 WHERE id = 1")
if err != nil {
    return err
}
return errors.New("changed my mind") // Commit never runs

B: BEGIN;
B: SELECT balance FROM accounts WHERE id = 1;   -- 100
A: UPDATE accounts SET balance = 50 WHERE id = 1;
B: SELECT balance FROM accounts WHERE id = 1;   -- ?
B: COMMIT;

A: BEGIN ISOLATION LEVEL REPEATABLE READ;
A: SELECT balance FROM accounts WHERE id = 1;   -- 100
B: BEGIN ISOLATION LEVEL REPEATABLE READ;
B: SELECT balance FROM accounts WHERE id = 1;   -- 100
A: UPDATE accounts SET balance = 110 WHERE id = 1;
A: COMMIT;
B: UPDATE accounts SET balance = 120 WHERE id = 1;  -- ?

-- both sessions: BEGIN ISOLATION LEVEL REPEATABLE READ;
A: SELECT count(*) FROM doctors WHERE on_call;  -- 2
B: SELECT count(*) FROM doctors WHERE on_call;  -- 2
A: UPDATE doctors SET on_call = false WHERE name = 'alice';
B: UPDATE doctors SET on_call = false WHERE name = 'bob';
A: COMMIT;
B: COMMIT;   -- ?

await inTx(pool, async (tx) => {
  const order = await placeOrder(tx, cart);
  await emailReceipt(order);        // sends an email
  await chargeCard(order);          // calls the payment API
});

Cheat sheet

What PostgreSQL prevents, by level

(READ UNCOMMITTED behaves exactly like READ COMMITTED in Postgres.)

Which tool for which race

Error codes worth matching on (PostgreSQL SQLSTATE)