Transactions & concurrency

Guarantees

SET TRANSACTION ISOLATION LEVEL … and SHOW transaction_isolation are accepted for driver compatibility and change nothing. The model is fixed: snapshot reads plus a commit-time guard. For a ledger this is sufficient with the pattern shown below; invariants that need phantom-freedom belong in constraints, not in an isolation level.

Read-your-writes

Outside a transaction, a SELECT on any connection sees every write that has been acknowledged, including one made a millisecond ago on a different connection. There is no replica lag to reason about and no freshness setting to choose.

Inside an open transaction, a SELECT sees the transaction’s own staged INSERT/UPDATE/DELETE as well as everything committed before the transaction began. That holds for simple queries and for prepared statements since 0.1.42, so an ORM that reads back a row it just wrote before COMMIT (SQLAlchemy’s Session.flush followed by a query, Prisma’s nested writes) gets the staged value. RETURNING also works on staged DML and returns the row after defaults were applied.

Row counts reported for a staged UPDATE or DELETE are real. The engine evaluates the statement against the current snapshot plus the transaction’s earlier writes and returns the count that COMMIT will apply.

Writes under concurrency

Every table with a PRIMARY KEY is written key by key, and the key is the unit of conflict.

The rule of thumb: a write inside a transaction is guarded on the keys it writes and on the parents it references; a read is guarded only if you ask with FOR UPDATE.

SELECT … FOR UPDATE

FOR UPDATE (and FOR SHARE, FOR NO KEY UPDATE, FOR KEY SHARE, which all behave the same here) does not take a lock and never blocks anyone. What it does:

1. Runs the SELECT as a snapshot read.
2. Records the primary keys of every row returned, on every base table in the FROM clause. A JOIN … FOR UPDATE guards the rows it read from both sides since 0.1.43.
3. At COMMIT, checks that none of those rows was written by another committed transaction since the read. If one was, COMMIT fails with 40001 and nothing from the transaction is applied.

Outside a transaction the clause is a plain snapshot read and a NOTICE tells you so — row-locking clause outside a transaction has no effect: served as a snapshot read — because there is no COMMIT at which anything could be checked.

The WHERE clause should name the primary key (or an equality on a UNIQUE index) so the guard is precise. A FOR UPDATE over a wide range guards every row it returned, which makes the transaction abort on any write to any of them; that is correct but rarely what you want.

ON CONFLICT and upserts

Retrying

Key retry logic on the SQLSTATE, never on message text.

Idempotency: a retried transaction can run after a previous attempt already committed, when the client lost the COMMIT acknowledgement to a dropped connection. Give each business operation an id and make the write conditional on it: an INSERT … ON CONFLICT (txn_id) DO NOTHING into a journal table as the first statement, followed by a check of its row count, turns a replay into a no-op.

import random, time
import psycopg
from psycopg.errors import SerializationFailure, OperationalError
 
def run_in_txn(conn, fn, attempts=8):
    for i in range(attempts):
        try:
            with conn.transaction():
                return fn(conn)
        except (SerializationFailure, OperationalError) as e:   # 40001, 08006
            if i == attempts - 1:
                raise
            time.sleep(min(2.0, 0.02 * 2 ** i) * random.random())

from sqlalchemy import text
from sqlalchemy.exc import OperationalError, DBAPIError
 
def run_in_txn(engine, fn, attempts=8):
    for i in range(attempts):
        try:
            with engine.begin() as conn:
                return fn(conn)
        except DBAPIError as e:
            code = getattr(e.orig, "sqlstate", None)
            if code not in ("40001", "08006") or i == attempts - 1:
                raise
            time.sleep(min(2.0, 0.02 * 2 ** i) * random.random())

The transfer pattern

A balanced posting between two accounts: read both balances under FOR UPDATE, check the invariant in application code, write both legs and the journal rows, commit. Any concurrent write to either account between the read and the COMMIT turns into a 40001 and the loop runs again with fresh balances.

BEGIN;
 
SELECT id, balance
FROM accounts
WHERE id IN (:from_id, :to_id)
FOR UPDATE;                                  -- guards both rows until COMMIT
 
-- application checks: both rows exist, balance(from) >= :amount
 
UPDATE accounts SET balance = balance - :amount WHERE id = :from_id;
UPDATE accounts SET balance = balance + :amount WHERE id = :to_id;
 
INSERT INTO ledger_entries (txn_id, account_id, delta) VALUES
  (:txn_id, :from_id, -:amount),
  (:txn_id, :to_id,    :amount);
 
SELECT SUM(delta) FROM ledger_entries WHERE txn_id = :txn_id;   -- sees the two rows above: 0
 
COMMIT;                                      -- 40001 here → run the block again

from decimal import Decimal
 
def transfer(conn, txn_id, from_id, to_id, amount: Decimal):
    cur = conn.cursor()
    rows = cur.execute(
        "SELECT id, balance FROM accounts WHERE id = ANY(%s) FOR UPDATE",
        ([from_id, to_id],),
    ).fetchall()
    bal = dict(rows)
    if len(bal) != 2:
        raise LookupError("unknown account")
    if bal[from_id] < amount:
        raise ValueError("insufficient funds")
    cur.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (amount, from_id))
    cur.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (amount, to_id))
    cur.executemany(
        "INSERT INTO ledger_entries (txn_id, account_id, delta) VALUES (%s, %s, %s)",
        [(txn_id, from_id, -amount), (txn_id, to_id, amount)],
    )
 
run_in_txn(conn, lambda c: transfer(c, txn_id, "acc-1", "acc-2", Decimal("12.50")))

Declare balance as NUMERIC(18,2) (or another explicit scale): arithmetic on it is exact and rounds the way Postgres does. See Data types. Add CHECK (balance >= 0) so the invariant is enforced by the engine as well as by the application; a violation is 23514 at the write. Declare ledger_entries.account_id REFERENCES accounts (id): an entry for an unknown account is refused 23503 at the insert, and the parent check doubles as a guard on the account row.

Savepoints

SAVEPOINT name, ROLLBACK TO SAVEPOINT name and RELEASE SAVEPOINT name work inside a transaction and nest as a stack. Rolling back to a savepoint discards the statements staged after it; the rest of the transaction continues and commits as one unit. A failed statement inside a transaction does not poison the session: roll back to a savepoint, or ROLLBACK the transaction, and continue on the same connection.

Transactions over HTTP and gRPC

The Execute API has no session, so an interactive BEGIN sent on its own is refused. Send the whole block in one call instead:

BEGIN;
  INSERT INTO ledger_entries (txn_id, account_id, delta) VALUES ('t-9', 'acc-1', -12.50);
  INSERT INTO ledger_entries (txn_id, account_id, delta) VALUES ('t-9', 'acc-2',  12.50);
COMMIT;

The block is applied as one atomic commit under the same guard rules, across as many tables as it writes. Only INSERT, UPSERT, UPDATE and DELETE are allowed inside it; a SELECT, a DDL statement or a nested BEGIN is refused. A ROLLBACK anywhere in the script discards the block.

That script is the only multi-statement shape Execute accepts. Otherwise Execute takes one statement per call: a semicolon-separated batch without BEGIN/COMMIT is refused (Execute takes ONE statement per call — split the batch and issue the statements in order). There is no batch mode, no per-statement result list and no stop-at-first-error semantics. Details in gRPC & HTTP API → Execute.

Next: Errors & SQLSTATEs for the full code table, or the Double-entry ledger recipe for the pattern above end to end.