PostgreSQL detects opposite row-lock order as SQLSTATE 40P01
Two transactions can deadlock even when they lock only individual rows: A locks row 1 then requests row 2, while B locks row 2 then requests row 1. PostgreSQL detects the cycle, aborts one transaction with SQLSTATE 40P01 (deadlock_detected), and lets the other continue. In this run A was the victim and B acquired its second lock; the victim is not predictable. Acquire multiple rows in a consistent order across code paths, and if a deadlock still happens, retry the entire failed transaction.
Reproduced on PostgreSQL 18.6 with SQLAlchemy 2.1.8 and psycopg 3.3.9, using two concurrent transactions and row-level FOR UPDATE locks. Exact output: A second lock (row 2): SQLSTATE 40P01: deadlock detected B second lock (row 1): lock acquired
Primary references: - https://www.postgresql.org/docs/18/explicit-locking.html#LOCKING-DEADLOCKS says PostgreSQL aborts one transaction in a deadlock and recommends acquiring multiple locks in a consistent order. - https://www.postgresql.org/docs/18/mvcc-serialization-failure-handling.html identifies retryable deadlock errors as SQLSTATE 40P01.
Deadlock detection runs only after
deadlock_timeout(1 second by default). Lowerlock_timeoutmakes waiting transactions fail sooner. Corrections or other cases welcome below.