PostgreSQL Same-Row Concurrent Update Deadlock

Threat/Impact Level: HIGH | Downtime Risk: HIGH | Time to Fix: 15–45 mins depending on transaction complexity

TL;DR

• What broke: Two or more concurrent transactions acquired row-level locks in opposite orders on the same row, causing PostgreSQL to abort one transaction with ERROR: deadlock detected.
• How to fix it: Enforce a consistent lock acquisition order across all transactions using SELECT FOR UPDATE, or implement optimistic locking with a version column to eliminate contention entirely.
• Fast path: Use our Client-Side Sandbox above to auto-refactor your transaction logic — paste your SQL and let it detect the conflicting lock sequence.

ERROR:  deadlock detected
DETAIL:  Process 47382 waits for ShareLock on transaction 9021; blocked by process 47401.
         Process 47401 waits for ShareLock on transaction 9020; blocked by process 47382.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,42) in relation "orders"

Transaction A:  UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- locks row 1
                UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- waits for row 2

Transaction B:  UPDATE accounts SET balance = balance - 50  WHERE id = 2;  -- locks row 2
                UPDATE accounts SET balance = balance + 50  WHERE id = 1;  -- waits for row 1  ← DEADLOCK

How to Fix It

Basic Fix — Enforce Consistent Lock Ordering

Always acquire locks on rows in the same deterministic order (e.g., ascending id) across all transactions.

-- Transaction handling a transfer between two accounts
BEGIN;

- -- Inconsistent order: Transaction A locks id=1 first, Transaction B locks id=2 first
- UPDATE accounts SET balance = balance - 100 WHERE id = :from_account;
- UPDATE accounts SET balance = balance + 100 WHERE id = :to_account;

+ -- Consistent order: always lock the lower ID first
+ UPDATE accounts SET balance = balance - 100
+   WHERE id = LEAST(:from_account, :to_account);
+ UPDATE accounts SET balance = balance + 100
+   WHERE id = GREATEST(:from_account, :to_account);

COMMIT;

For application-layer ordering:

- # Python — no ordering, deadlock-prone
- cursor.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (amount, from_id))
- cursor.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (amount, to_id))

+ # Python — sort IDs before locking
+ ids_in_order = sorted([from_id, to_id])
+ cursor.execute("SELECT id FROM accounts WHERE id = ANY(%s) ORDER BY id FOR UPDATE", (ids_in_order,))
+ cursor.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (amount, from_id))
+ cursor.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (amount, to_id))

Enterprise Best Practice — Optimistic Locking with Version Column

For high-read, low-contention workloads, eliminate pessimistic locking entirely. Use a version column — the UPDATE only succeeds if the row hasn't changed since you read it.

-- Schema change
+ ALTER TABLE orders ADD COLUMN version INTEGER NOT NULL DEFAULT 0;

-- Application UPDATE
- UPDATE orders
-   SET status = 'processed', total = :new_total
-   WHERE id = :order_id;

+ UPDATE orders
+   SET status = 'processed', total = :new_total, version = version + 1
+   WHERE id = :order_id
+   AND version = :expected_version;  -- fails silently if row was modified concurrently
+
+ -- Check rows_affected in application code:
+ -- if rows_affected == 0: raise OptimisticLockException and retry with fresh read

Application-layer retry wrapper (Python):

- def update_order(order_id, new_total):
-     db.execute("UPDATE orders SET total = %s WHERE id = %s", (new_total, order_id))

+ MAX_RETRIES = 5
+ def update_order(order_id, new_total):
+     for attempt in range(MAX_RETRIES):
+         row = db.fetchone("SELECT version FROM orders WHERE id = %s", (order_id,))
+         affected = db.execute(
+             "UPDATE orders SET total = %s, version = version + 1 "
+             "WHERE id = %s AND version = %s",
+             (new_total, order_id, row['version'])
+         )
+         if affected == 1:
+             return  # success
+         time.sleep(0.05 * attempt)  # exponential backoff
+     raise Exception("Optimistic lock failed after max retries")

💡 Tired of pasting proprietary infrastructure configs into public LLMs? Generic AI interfaces log your company's ARNs, connection strings, and private keys. StackEngine enforces client-side zero-trust boundaries—the sandbox above executes 100% locally in your browser. Drop your broken manifest above to redact secrets locally and synthesize deterministic patch drafts with your own API key.

(For CI/CD pipeline automation, install via npm i -g stackengine).

# .pre-commit-config.yaml — custom hook to flag unordered multi-row UPDATEs
- repo: local
  hooks:
    - id: check-lock-order
      name: Flag potential deadlock patterns
      entry: grep -rn "UPDATE.*WHERE id" --include="*.sql" --include="*.py"
      language: system
      pass_filenames: false

- # postgresql.conf (default — too slow to catch in staging)
- deadlock_timeout = 1s

+ # postgresql.conf (staging only — surface deadlocks faster)
+ deadlock_timeout = 100ms
+ log_lock_waits = on
+ lock_timeout = 5s  # fail fast rather than queue indefinitely

# pytest-xdist parallel test — run the same transaction from 10 threads simultaneously
import concurrent.futures

def test_no_deadlock_under_concurrency():
    with concurrent.futures.ThreadPoolExecutor(max_workers=10) as executor:
        futures = [executor.submit(update_order, order_id=1, new_total=i*10) for i in range(10)]
        results = [f.result() for f in concurrent.futures.as_completed(futures)]
    # Assert no exceptions were raised

-- Query to detect current lock waits in pg_stat_activity
SELECT
    pid,
    wait_event_type,
    wait_event,
    state,
    query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY state_change;