Choosing Optimistic or Pessimistic Locking for Payment Writes

Ledger writes concentrate contention in a way most workloads do not — the distribution is extremely skewed: 99% of accounts see one write a day and a handful see thousands a minute. A single concurrency-control choice applied uniformly is therefore wrong for one end of the distribution or the other. This guide picks the strategy from measured contention and sits under optimistic vs pessimistic concurrency control.

Prerequisites. A ledger or balance table with a version column available, and the mechanics from implementing optimistic concurrency with version columns.

---

Step 1 — Measure the conflict rate per row

The decision is per-row, not per-table, and the distribution is almost always heavily skewed.

-- Concurrent write attempts per account in the busiest minute of the last day.
SELECT account_id,
       count(*) AS writes,
       count(*) FILTER (WHERE conflicted) AS conflicts,
       round(100.0 * count(*) FILTER (WHERE conflicted) / count(*), 2) AS conflict_pct
  FROM ledger_write_attempts
 WHERE attempted_at > now() - interval '1 day'
 GROUP BY account_id
 ORDER BY conflicts DESC
 LIMIT 20;

---

Step 2 — Implement both

-- OPTIMISTIC: version predicate in the WHERE clause. No lock is taken.
UPDATE account_balances
   SET balance = balance - :amount,
       version = version + 1
 WHERE account_id = :account
   AND version    = :expected_version
   AND balance   >= :amount
RETURNING balance, version;
-- Zero rows: either a concurrent write bumped the version, or funds are short.
-- Distinguish the two by re-reading; do not conflate them.

-- PESSIMISTIC: take the row lock first, then act on a value nobody can change.
BEGIN;
  SELECT balance FROM account_balances
   WHERE account_id = :account
     FOR UPDATE;                       -- blocks other writers until COMMIT
  UPDATE account_balances
     SET balance = balance - :amount
   WHERE account_id = :account;
COMMIT;

def debit_optimistic(conn, account: str, amount: int, attempts: int = 5) -> int:
    for attempt in range(attempts):
        row = conn.execute(
            "SELECT balance, version FROM account_balances WHERE account_id=%s",
            (account,)).fetchone()
        if row.balance < amount:
            raise InsufficientFunds(account)

        updated = conn.execute(OPTIMISTIC_SQL, {
            "amount": amount, "account": account, "expected_version": row.version,
        }).fetchone()
        if updated is not None:
            return updated.balance

        metrics.inc("ledger_optimistic_conflict_total", account_tier=tier(account))
        time.sleep(random.uniform(0, min(0.2, 0.005 * 2 ** attempt)))   # full jitter
    raise ContentionExhausted(account)

def debit_pessimistic(conn, account: str, amount: int) -> int:
    with conn.transaction():
        row = conn.execute(
            "SELECT balance FROM account_balances WHERE account_id=%s FOR UPDATE",
            (account,)).fetchone()
        if row.balance < amount:
            raise InsufficientFunds(account)
        return conn.execute(
            "UPDATE account_balances SET balance = balance - %s WHERE account_id = %s "
            "RETURNING balance", (amount, account)).fetchone().balance

---

Step 3 — Find the crossover empirically

# Same write, same row, varying concurrency. Report throughput and p99.
def bench(strategy, concurrency: int, seconds: int = 30):
    stop, ok, conflicts, lat = time.monotonic() + seconds, 0, 0, []
    def worker():
        nonlocal ok, conflicts
        while time.monotonic() < stop:
            t0 = time.perf_counter()
            try:
                strategy(conn(), "acct_hot", 1); ok += 1
            except ContentionExhausted:
                conflicts += 1
            lat.append((time.perf_counter() - t0) * 1000)
    run_threads(worker, concurrency)
    lat.sort()
    return ok / seconds, lat[int(len(lat) * 0.99)], conflicts

Representative shape for a single hot row on PostgreSQL — run it yourself, but expect this pattern:

Optimistic wins at low concurrency and collapses at high concurrency, because each retry re-enters a field with more contenders. Pessimistic degrades gracefully: writers queue, throughput stays near the serial ceiling, and latency grows linearly rather than exponentially.

---

Step 4 — The mixed strategy

Most payment systems end up routing by measured contention rather than picking one globally.

HOT_ACCOUNT_CONFLICT_THRESHOLD = 0.10        # from step 1, re-derived nightly

def debit(conn, account: str, amount: int) -> int:
    if contention_oracle.conflict_rate(account) > HOT_ACCOUNT_CONFLICT_THRESHOLD:
        metrics.inc("ledger_strategy_total", strategy="pessimistic")
        return debit_pessimistic(conn, account, amount)
    metrics.inc("ledger_strategy_total", strategy="optimistic")
    return debit_optimistic(conn, account, amount)

-- The oracle: a small table refreshed nightly from the measurement in step 1.
CREATE TABLE account_contention (
    account_id    text PRIMARY KEY,
    conflict_rate numeric(5,4) NOT NULL,
    measured_at   timestamptz  NOT NULL DEFAULT now()
);

A second option for genuinely extreme hot rows is to remove the contention rather than manage it: append immutable ledger entries and derive the balance, so concurrent writes touch different rows entirely.

-- No shared row to contend on. Balance becomes a read-side concern.
INSERT INTO ledger_entries (account_id, amount, idempotency_key, created_at)
VALUES (:account, -:amount, :key, now())
ON CONFLICT (idempotency_key) DO NOTHING;

-- Balance from a rolling snapshot plus recent entries, not a hot mutable row.
SELECT s.balance + coalesce(sum(e.amount), 0) AS balance
  FROM balance_snapshots s
  LEFT JOIN ledger_entries e
    ON e.account_id = s.account_id AND e.created_at > s.as_of
 WHERE s.account_id = :account
 GROUP BY s.balance;

The trade is that overdraft checks become harder: you can no longer read the balance and decide atomically in one statement. For accounts that cannot go negative, pessimistic locking on the hot row remains the simpler correct answer.

---

Step 5 — Verify the invariant

def test_balance_never_goes_negative_under_contention(db):
    seed_account("acct_invariant", balance=1_000)
    barrier, results = threading.Barrier(64), []

    def attempt():
        barrier.wait()
        try:
            results.append(debit(conn(), "acct_invariant", 100))
        except (InsufficientFunds, ContentionExhausted):
            pass

    run_threads(attempt, 64)
    final = db.scalar("SELECT balance FROM account_balances WHERE account_id=%s",
                      ("acct_invariant",))
    assert final >= 0
    assert final == 1_000 - 100 * len(results)      # every success is accounted for

# The invariant that actually matters, checked continuously in production.
psql -c "SELECT count(*) FROM account_balances WHERE balance < 0"     # expect 0
psql -c "SELECT b.account_id FROM account_balances b
          JOIN (SELECT account_id, sum(amount) s FROM ledger_entries GROUP BY 1) e
            ON e.account_id = b.account_id
         WHERE b.balance <> e.s LIMIT 10"                             # expect none

---

Failure scenarios and debugging

---

SRE / observability checklist

1. ledger_optimistic_conflict_total — Counter labelled by account tier. The per-account conflict rate is the input to the routing decision.
2. ledger_strategy_total{strategy} — Counter of optimistic versus pessimistic routing. A sudden shift means the contention profile changed.
3. transaction_duration_seconds — Histogram on the ledger write path. Page above 100 ms at p99; long transactions are what make pessimistic locking expensive.
4. pg_stat_database.deadlocks — Any non-zero rate means lock ordering is inconsistent across code paths.
5. ledger_negative_balance_count — Gauge from a continuous query. Must be exactly zero; it is the invariant everything else exists to protect.
6. ledger_reconciliation_divergence_total — Counter of accounts where the balance disagrees with the sum of entries. Any value is a lost update.

---

Related

• Optimistic vs Pessimistic Concurrency Control — the parent page on the two models and their guarantees.
• Implementing Optimistic Concurrency with Version Columns — the version-column mechanics used throughout this guide.
• Handling Unique-Violation Races Under Serializable Isolation — the database-level analogue of optimistic retry.
• Preventing Race Conditions in Microservices — the wider set of races a ledger write participates in.