Heavyweight lock manager (lock modes, conflict table, fast-path, deadlock detector)

# Lab: pgi17 / PostgreSQL 17.10. Table acct(id pk, bal) 2 rows. Two psql sessions A,B (FIFO harness).

=== STAGE 1, fast-path (weak) vs shared lock table (strong) ===
# A: BEGIN; SELECT count(*) FROM acct;   -> AccessShareLock
  who  | locktype |      mode       | granted | fastpath
-------+----------+-----------------+---------+----------
 sessA | relation | AccessShareLock | t       | t          <- weak lock, recorded in per-backend array

# A: LOCK TABLE acct IN SHARE UPDATE EXCLUSIVE MODE;   -> SUE (NOT fast-path eligible)
  who  | locktype |           mode           | granted | fastpath
-------+----------+--------------------------+---------+----------
 sessA | relation | AccessShareLock          | t       | t        <- still on fast path
 sessA | relation | ShareUpdateExclusiveLock | t       | f        <- mode >= SUE -> shared lock table
# Exactly the source boundary: EligibleForRelationFastPath requires mode < ShareUpdateExclusiveLock.

=== STAGE 2, a real blocking wait (AccessExclusive vs AccessShare) ===
# A: BEGIN; LOCK TABLE acct IN ACCESS EXCLUSIVE MODE;   (held)
# B: SELECT count(*) FROM acct;  -> BLOCKS (AccessShare conflicts with AccessExclusive, per the table)
  who  |   locktype    |        mode         | granted | fastpath
-------+---------------+---------------------+---------+----------
 sessA | relation      | AccessExclusiveLock | t       | f
 sessA | transactionid | ExclusiveLock       | t       | f        <- A's own xid lock (always held)
 sessB | relation      | AccessShareLock     | f       | f        <- WAITING (granted=f)

# straight from the server:
 waiter | wait_event_type | wait_event | blocked_by_pids | blocker
--------+-----------------+------------+-----------------+---------
 sessB  | Lock            | relation   | {121}           | sessA   <- pg_blocking_pids names the holder

# A: ROLLBACK;  -> B unblocks and returns (it only ever WAITED, never errored):
  2
  B got result

=== STAGE 3, deadlock detector ===
# A: BEGIN; UPDATE acct ... WHERE id=1;   (A holds row 1)
# B: BEGIN; UPDATE acct ... WHERE id=2;   (B holds row 2)
# A: UPDATE ... WHERE id=2;  -> blocks on B    } cycle
# B: UPDATE ... WHERE id=1;  -> blocks on A    } closes it
# deadlock_timeout = 1s; after it fires, DeadLockCheck runs and aborts ONE side:

### session A log (the VICTIM) ###
UPDATE 1
ERROR:  deadlock detected
DETAIL:  Process 163 waits for ShareLock on transaction 381758; blocked by process 170.
Process 170 waits for ShareLock on transaction 381757; blocked by process 163.
HINT:  See server log for query details.

### session B log (the SURVIVOR) ###
UPDATE 1
UPDATE 1          <- B's second UPDATE succeeded once A was aborted and released row 1

## Reading the result
- The fastpath column flips from t to f EXACTLY at the ShareUpdateExclusive boundary the source macro
  encodes (mode < SUE). Weak read/DML locks never touch the shared hash table; that's the contention
  win the fast path exists for.
- STAGE 2 proves the conflict table is real and asymmetric: a plain SELECT is blocked by a schema-style
  AccessExclusive lock, and pg_blocking_pids points straight at the holder. This is the mechanism behind
  'my SELECTs hang during a migration'.
- STAGE 3's DETAIL is the honest nuance: two row UPDATEs deadlock by each waiting for 'ShareLock on
  transaction <xid>', a writer that hits a locked row waits on the OTHER transaction's xid lock, not a
  table lock. The detector named both xids and aborted the one whose abort breaks the cycle.
- Victim selection here picked A (the process that detected the cycle on its wait); the survivor B
  completed both statements. Which side is chosen is not guaranteed run-to-run.