Locks và Deadlock — row lock, table lock, deadlock detection, SKIP LOCKED

-- Session A:
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
-- OK, dang giu lock tren row id=1

-- Session B:
BEGIN;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
-- OK, dang giu lock tren row id=2

-- Session A (tiep):
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
-- ... hang, cho Session B nha lock tren id=2

-- Session B (tiep):
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
-- ... hang, cho Session A nha lock tren id=1

ERROR:  deadlock detected
DETAIL:  Process 12345 waits for ShareLock on transaction 789; blocked by process 12346.
         Process 12346 waits for ShareLock on transaction 788; blocked by process 12345.
HINT:   See server log for query details.
SQLSTATE: 40P01

-- FOR UPDATE: lock row, block UPDATE/DELETE khac cung row
SELECT * FROM tasks WHERE id = 1 FOR UPDATE;
-- Row id=1 bi lock. Tx khac UPDATE id=1 phai cho.

-- FOR SHARE: shared read lock — allow concurrent FOR SHARE, block UPDATE/DELETE
SELECT * FROM tasks WHERE id = 1 FOR SHARE;
-- Nhieu tx co the cung FOR SHARE. UPDATE phai cho tat ca FOR SHARE release.

-- SKIP LOCKED: bo qua row dang bi lock, lay row tiep theo (queue pattern)
SELECT * FROM tasks WHERE status = 'pending' LIMIT 1 FOR UPDATE SKIP LOCKED;
-- Neu row dang lock boi tx khac: SKIP, khong wait. Lay row ke tiep available.

-- NOWAIT: error ngay neu phai wait, khong block
SELECT * FROM tasks WHERE id = 1 FOR UPDATE NOWAIT;
-- ERROR: could not obtain lock on row in relation "tasks" (neu row dang bi lock)

-- Session A:
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
-- UPDATE 1 (thanh cong, giu ROW EXCLUSIVE lock tren id=1)

-- Session B:
BEGIN;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
-- UPDATE 1 (thanh cong, giu ROW EXCLUSIVE lock tren id=2)

-- Session A:
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
-- [HANG -- waiting for Session B to release lock on id=2]

-- Session B:
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
-- [HANG -- waiting for Session A to release lock on id=1]
-- Tao ra cycle: A -> wait B, B -> wait A

ERROR:  deadlock detected
DETAIL:  Process 23456 waits for ShareLock on transaction 1001; blocked by process 23457.
         Process 23457 waits for ShareLock on transaction 1002; blocked by process 23456.
HINT:    See server log for query details.

-- Ai dang wait lock, va ai dang giu lock do
SELECT
  waiting.pid        AS waiting_pid,
  waiting.query      AS waiting_query,
  waiting.state      AS waiting_state,
  blocking.pid       AS blocking_pid,
  blocking.query     AS blocking_query,
  age(now(), waiting.xact_start) AS wait_duration
FROM pg_stat_activity AS waiting
JOIN pg_locks AS wl
  ON wl.pid = waiting.pid AND NOT wl.granted
JOIN pg_locks AS bl
  ON bl.relation = wl.relation
  AND bl.locktype = wl.locktype
  AND bl.granted
JOIN pg_stat_activity AS blocking
  ON blocking.pid = bl.pid
WHERE waiting.pid <> blocking.pid
ORDER BY wait_duration DESC;

-- Tat ca lock dang ton tai, kem theo query va tx age
SELECT
  l.pid,
  l.mode,
  l.granted,
  l.relation::regclass AS relation_name,
  a.query,
  a.state,
  age(now(), a.xact_start) AS tx_age
FROM pg_locks l
JOIN pg_stat_activity a USING (pid)
WHERE l.relation IS NOT NULL
ORDER BY tx_age DESC;
-- granted = false: tx nay dang WAIT
-- granted = true: tx nay dang GIU lock

-- lock_timeout: abort neu phai wait lock qua X thoi gian
-- Scope: co the set cho toan session hoac chi transaction hien tai
SET LOCAL lock_timeout = '5s';
-- Neu UPDATE phai wait lock > 5 giay: ERROR: canceling statement due to lock timeout
-- SQLSTATE: 55P03

-- statement_timeout: abort neu query chay qua X thoi gian (bat ke co wait hay khong)
SET LOCAL statement_timeout = '30s';
-- Bao gom ca thoi gian wait lock. Neu tong thoi gian > 30s: cancel query.

-- deadlock_timeout: khoang thoi gian PG bat dau chay deadlock detector
-- Default: 1s. Set trong postgresql.conf, khong nen doi thuong xuyen.
-- KHONG nen giam deadlock_timeout xuong duoi 1s:
-- deadlock detection algorithm la O(N^2) theo so luong lock -- expensive.
SHOW deadlock_timeout;
-- deadlock_timeout
-- ----------------
--  1s

-- Pattern thuc te cho API endpoint co SLA 2 giay:
BEGIN;
SET LOCAL lock_timeout = '1500ms';  -- fail neu wait lock qua 1.5s
SET LOCAL statement_timeout = '2000ms';  -- fail neu toan query qua 2s
-- ... business logic ...
COMMIT;

-- Optimistic locking: version column
-- Schema: ALTER TABLE tasks ADD COLUMN version INTEGER NOT NULL DEFAULT 1;

-- Doc voi version
SELECT id, title, status, version FROM tasks WHERE id = 42;
-- => version = 5

-- Ghi: chi update neu version chua thay doi
UPDATE tasks
SET status = 'done', version = version + 1
WHERE id = 42 AND version = 5;
-- affected rows = 0: nguoi khac da update (version da tang) -> retry voi version moi
-- affected rows = 1: thanh cong

-- Pessimistic locking: FOR UPDATE
BEGIN;
SELECT id, title, status FROM tasks WHERE id = 42 FOR UPDATE;
-- Row bi lock. Khong ai khac co the UPDATE row nay.
-- ... tinh toan trong app nếu can ...
UPDATE tasks SET status = 'done' WHERE id = 42;
COMMIT;

// Retry exponential backoff + jitter (tranh tight loop -> thrashing)
async function retryOnConflict(fn, maxAttempts = 5) {
  for (let attempt = 0; attempt < maxAttempts; attempt++) {
    try {
      return await fn();
    } catch (err) {
      // 40P01: deadlock detected
      // 40001: serialization failure (Repeatable Read / Serializable)
      if (err.code !== '40P01' && err.code !== '40001') throw err;
      if (attempt === maxAttempts - 1) throw new Error('Max retry exceeded');
      // Exponential backoff: 50ms, 100ms, 200ms, 400ms... cap 2000ms
      // Jitter: cong them random de cac tx khong retry cung luc
      const delay = Math.min(50 * 2 ** attempt, 2000) + Math.random() * 50;
      await new Promise(r => setTimeout(r, delay));
    }
  }
}

// Dung:
// await retryOnConflict(async () => {
//   await client.query('BEGIN');
//   // ... business logic ...
//   await client.query('COMMIT');
// });

-- Worker A pull next task tu queue:
BEGIN;
SELECT id, payload
FROM tasks
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- Worker A lay task id=1 (lock tren id=1)
-- Neu khong co row available: tra ve empty set (khong block)

UPDATE tasks SET status = 'processing' WHERE id = 1;
COMMIT;
-- Task id=1 bay gio la 'processing', lock duoc release

-- Worker B chay dong thoi:
BEGIN;
SELECT id, payload
FROM tasks
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- id=1 dang bi Worker A lock -> SKIP
-- Lay task id=2 (lock tren id=2, khong conflict voi Worker A)

UPDATE tasks SET status = 'processing' WHERE id = 2;
COMMIT;

-- Sau khi worker hoan thanh task:
UPDATE tasks
SET
  status = 'done',
  completed_at = NOW()
WHERE id = ?;

-- Neu worker crash giua chung (khong COMMIT):
-- Lock tu dong release khi connection cua worker bi dong
-- Task van la 'processing' -> can job rieng de reset timeout tasks
-- SELECT * FROM tasks WHERE status = 'processing' AND updated_at < NOW() - INTERVAL '10 minutes';