Why should you care?

PostgresVerse wallet transfer from A→B and B→A simultaneously can deadlock if each locks accounts in opposite order.

See it live — copy this example

Run in pgAdmin or psql.

-- Session A                    -- Session B
BEGIN;                           BEGIN;
UPDATE accounts SET balance=balance-10 WHERE account_id=1;
                                 UPDATE accounts SET balance=balance-10 WHERE account_id=2;
UPDATE accounts SET balance=balance+10 WHERE account_id=2;
                                 UPDATE accounts SET balance=balance+10 WHERE account_id=1;
-- one session gets: ERROR: deadlock detected

What happened?

• A locks account 1 then waits for 2.
• B locks 2 then waits for 1.
• Postgres picks a victim and rolls back that transaction.

Practice next

1. Open two psql sessions on PostgresVerse.
2. Run steps in parallel as shown.
3. Read deadlock detected message and which pid was victim.
4. Fix by always updating lower account_id first in both sessions.
5. Query pg_locks during staged deadlock to see wait edges.

Remember

Deadlock is normal under contention — detect and retry. Lock resources in consistent global order. Keep lock-holding transactions short.

PostgresVerse P2P transfer

Mobile app retries transfer once on 40P01; consistent lock order on account ids.

Outcome: Deadlock rate drops from 0.3% to negligible.