Concurrency Control — Locking, MVCC, Deadlock

1. Vì sao cần Concurrency Control?

Ở Ch6 ta đã thấy: nhiều transaction chạy song song có thể tạo ra anomaly (lost update, phantom, write skew). DB cần cơ chế để tuần tự hóa các thao tác xung đột mà vẫn cho phép song song tối đa khi không xung đột.

Có 2 phương pháp lớn:

• Locking — chặn transaction khác đụng vào tài nguyên đang dùng.
• MVCC — tạo nhiều phiên bản dữ liệu, mỗi transaction thấy snapshot riêng.

Postgres kết hợp cả hai: MVCC cho read (không cần lock), row-lock cho write conflict.

2. Two-Phase Locking (2PL) — lý thuyết

Cổ điển. Mỗi transaction có 2 pha:

1. Growing phase — chỉ acquire lock, không release.
2. Shrinking phase — chỉ release, không acquire mới.

Strict 2PL: không release write lock cho đến COMMIT/ROLLBACK. Đảm bảo serializability + recoverability.

2.1. Loại lock

• Shared (S) lock — read lock. Nhiều S cùng tồn tại OK.
• Exclusive (X) lock — write lock. Không tồn tại với bất kỳ S/X khác.

2.2. Hạn chế của 2PL thuần

• Throughput thấp dưới read-heavy workload.
• Read và write block nhau → ứng dụng cảm thấy chậm.
• Dễ deadlock nếu không có thứ tự lock cố định.

Đó là lý do hầu hết DB hiện đại (Postgres, Oracle, MySQL InnoDB) dùng MVCC thay 2PL thuần.

3. Optimistic vs Pessimistic Locking

BEGIN;
SELECT balance FROM accounts WHERE id=1 FOR UPDATE;
-- transaction khác phải chờ ở đây
UPDATE accounts SET balance = balance + 50 WHERE id=1;
COMMIT;

-- Đọc:
SELECT balance, version FROM accounts WHERE id=1;
-- App nhớ version=5

-- Ghi (CAS):
UPDATE accounts
SET balance = balance + 50, version = version + 1
WHERE id = 1 AND version = 5;
-- Nếu rowcount = 0 → ai đó đã update, retry.

Khi nào chọn cái nào?

• Read-heavy + ít update conflict → Optimistic (Stripe, Shopify thường dùng cho update đơn giản).
• Write-heavy + tranh chấp gắt → Pessimistic (đặt vé, queue).

4. Lock trong Postgres — toàn cảnh

Postgres có nhiều granularity lock:

• Row-level — lock 1 dòng (qua FOR UPDATE, UPDATE, DELETE).
• Table-level — lock cả bảng (qua LOCK TABLE, hoặc DDL).
• Page-level — lock 1 page (ít gặp, nội bộ).
• Advisory — lock do app tự định nghĩa, không gắn với object.

Postgres KHÔNG có lock-escalation tự động (khác SQL Server) — bạn lock row 100 cái thì 100 row-lock, không bao giờ "leo" thành table lock.

5. Row-level lock

5.1. Bốn cường độ row lock (Postgres)

5.2. Cú pháp SELECT FOR UPDATE

-- Khóa độc quyền row, transaction khác đụng vào row sẽ chờ:
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- ...thao tác...
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- NOWAIT: lỗi ngay nếu không lock được:
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- ERROR: could not obtain lock on row in relation "accounts"

-- SKIP LOCKED: bỏ qua row đang bị lock (queue worker pattern):
SELECT * FROM jobs WHERE status = 'pending'
ORDER BY id LIMIT 1
FOR UPDATE SKIP LOCKED;

5.3. Pattern: Queue worker với SKIP LOCKED

-- N worker cùng pull job, không tranh chấp:
BEGIN;
SELECT id, payload FROM jobs
WHERE status = 'pending'
ORDER BY priority, created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- App xử lý job
UPDATE jobs SET status = 'done' WHERE id = ?;
COMMIT;

SKIP LOCKED là magic — Postgres bỏ qua row đang bị transaction khác lock, lấy row tiếp theo. Mỗi worker tự nhiên lấy job khác nhau, không cần coordination.

6. Table-level lock

Postgres có 8 mức table lock. Quan trọng nhất:

-- Explicit lock:
LOCK TABLE accounts IN ACCESS EXCLUSIVE MODE;

-- Trong transaction, lock tự release ở COMMIT.

6.1. pg_locks — xem lock đang bị giữ

SELECT
  l.locktype, l.mode, l.granted,
  a.pid, a.query, a.state,
  age(now(), a.query_start) AS age
FROM pg_locks l
JOIN pg_stat_activity a USING (pid)
WHERE NOT a.pid = pg_backend_pid()
ORDER BY age DESC;

Cứu cánh khi debug "tại sao query của tôi treo?".

7. MVCC sâu — xmin, xmax, visibility

Mỗi tuple trong Postgres có 2 cột ẩn:

• xmin — XID của transaction tạo tuple này.
• xmax — XID của transaction xóa/update tuple này (0 nếu chưa).

-- Xem xmin/xmax:
SELECT xmin, xmax, * FROM accounts WHERE id = 1;

7.1. Visibility rule

Một tuple visible với snapshot xid_S khi:

• xmin đã commit và xmin < xid_S và xmin không trong "active list" của snapshot.
• VÀ (xmax = 0 OR xmax chưa commit OR xmax > xid_S).

Nói cách khác: "Tuple này được tạo trước snapshot và chưa bị xóa bởi transaction đã commit trước snapshot."

7.2. UPDATE thực ra là INSERT + mark old DELETE

7.3. Hệ quả: dead tuple, table bloat

Mỗi UPDATE/DELETE để lại "dead tuple". Vacuum (autovacuum) phải dọn. Nếu không kịp → bảng phình to → query chậm.

Workload UPDATE-heavy là kẻ thù của MVCC. Giải pháp: HOT updates (Heap-Only Tuple — UPDATE không đụng index) khi không thay đổi cột indexed, và vacuum thường xuyên.

8. Deadlock — phát hiện và phòng tránh

Deadlock = vòng luẩn quẩn lock:

8.1. Postgres detection

Postgres scan lock graph mỗi deadlock_timeout (mặc định 1s). Khi phát hiện chu trình → abort 1 transaction (thường là ngắn nhất). App nhận lỗi 40P01 deadlock_detected.

-- Demo deadlock:
-- Session A:
BEGIN;
UPDATE accounts SET balance=balance-10 WHERE id=1;
-- (chờ B)
UPDATE accounts SET balance=balance+10 WHERE id=2;

-- Session B (đồng thời):
BEGIN;
UPDATE accounts SET balance=balance-5 WHERE id=2;
-- (chờ A)
UPDATE accounts SET balance=balance+5 WHERE id=1;
-- → 1s sau, 1 trong 2 sẽ nhận:
-- ERROR: deadlock detected

8.2. Phòng tránh — luôn lock theo thứ tự nhất quán

// Bug-prone: app A khóa (id=1, id=2), app B khóa (id=2, id=1)
async function transfer(from, to, amount) {
  // ✓ Sort theo id trước khi lock:
  const [a, b] = [from, to].sort();
  await tx.lock(a);
  await tx.lock(b);
  // ...
}

Quy tắc: luôn acquire lock theo cùng thứ tự (sort by id, lexicographic, …) trong toàn app.

8.3. Lock timeout

SET lock_timeout = '5s';
BEGIN;
SELECT * FROM accounts WHERE id=1 FOR UPDATE;
-- Nếu không lock được trong 5s → ERROR canceled.

Tốt cho UI — không để user chờ vô hạn.

9. Advisory Lock — lock do app định nghĩa

Lock không gắn với object DB cụ thể, chỉ là 1 cặp (key1, key2) integer hoặc 1 bigint. Tự app dùng để serialize logic.

-- Acquire (block nếu đã có ai giữ):
SELECT pg_advisory_lock(12345);

-- Try acquire (không block):
SELECT pg_try_advisory_lock(12345);   -- → true/false

-- Release:
SELECT pg_advisory_unlock(12345);

-- Tự release ở COMMIT:
SELECT pg_advisory_xact_lock(12345);

9.1. Use case

• Cron singleton — đảm bảo chỉ 1 instance chạy job tại 1 thời điểm.
• Migration leader — chỉ 1 process chạy migration trên cluster.
• Distributed mutex — Postgres làm lock manager.

async function runIfLeader(jobName: string) {
  const lockKey = hash(jobName);
  const acquired = await db.query('SELECT pg_try_advisory_lock($1)', [lockKey]);
  if (!acquired.rows[0].pg_try_advisory_lock) return; // ai đó đang chạy
  try { await runJob(); }
  finally { await db.query('SELECT pg_advisory_unlock($1)', [lockKey]); }
}

10. Vacuum & Bloat

Như đã nói, MVCC tạo dead tuple. VACUUM dọn:

• Đánh dấu dead tuple là free space (re-use cho INSERT/UPDATE sau).
• Cập nhật visibility map → cho phép Index Only Scan.
• Cập nhật statistics cho planner.

10.1. Autovacuum

Postgres tự động chạy autovacuum khi % dead tuple vượt threshold. Mặc định OK cho hầu hết workload, nhưng workload nặng có thể cần tune:

ALTER TABLE hot_table SET (
  autovacuum_vacuum_scale_factor = 0.05,    -- 5% thay vì 20%
  autovacuum_vacuum_cost_limit = 1000       -- chạy nhanh hơn
);

10.2. VACUUM vs VACUUM FULL

• VACUUM — không reclaim dung lượng disk, chỉ free space cho re-use. Online, không block.
• VACUUM FULL — viết lại bảng, reclaim disk. Lock toàn bảng (ACCESS EXCLUSIVE). Chỉ dùng khi thực sự cần.

Thay vì VACUUM FULL trên prod, dùng pg_repack — viết lại bảng online.

10.3. Bloat detection

SELECT
  schemaname, relname,
  pg_size_pretty(pg_total_relation_size(relid)) AS size,
  n_dead_tup, n_live_tup,
  ROUND(n_dead_tup::numeric / NULLIF(n_live_tup,0) * 100, 1) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

11. Patterns thực tế

11.1. Update với optimistic version

CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR, price DECIMAL,
  version INT NOT NULL DEFAULT 0
);

-- App đọc:
SELECT id, name, price, version FROM products WHERE id = 1;
-- → version = 5

-- App ghi với CAS:
UPDATE products
SET name = $1, price = $2, version = version + 1
WHERE id = 1 AND version = 5
RETURNING version;
-- Nếu rowcount = 0 → conflict, retry.

11.2. Distributed counter — write contention

-- ❌ Mọi request UPDATE row counter — tất cả tranh nhau lock 1 row.
UPDATE counters SET value = value + 1 WHERE name = 'page_view';

-- ✓ Sharded counter:
INSERT INTO counter_log (name, delta) VALUES ('page_view', 1);
-- (định kỳ aggregate vào counters)

-- HOẶC random shard:
UPDATE counter_shards SET value = value + 1
WHERE name = 'page_view' AND shard = floor(random() * 100);

11.3. SELECT … FOR UPDATE NOWAIT cho UI

BEGIN;
SELECT * FROM seats WHERE id = $1 AND available = TRUE
FOR UPDATE NOWAIT;
-- Nếu LOCK NOT GRANTED → trả 409 Conflict cho user, không block UI

11.4. Worker queue pattern

-- N worker pull job:
DELETE FROM jobs WHERE id = (
  SELECT id FROM jobs
  WHERE status = 'pending'
  ORDER BY priority, created_at
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
RETURNING *;

Khá magic, là pattern queue cơ bản dùng Postgres làm message broker (đủ cho moderate scale).

11.5. Idempotency key

-- Đảm bảo POST /charge không double-charge nếu retry:
INSERT INTO charges (id, idem_key, amount)
VALUES (gen_random_uuid(), $1, 100)
ON CONFLICT (idem_key) DO NOTHING
RETURNING id;
-- Nếu row trả về null → đã xử lý, return cùng response.

12. Bài tập

1. Mở 2 psql, demo deadlock cố ý theo mục 8.1. Quan sát error.
2. Cài đặt queue worker với FOR UPDATE SKIP LOCKED. Test 4 worker đồng thời pull từ bảng 1000 job.
3. Implement optimistic locking với cột version cho bảng products. Viết function update với retry.
4. Đo dead_pct của 1 bảng UPDATE-heavy. Tune autovacuum để giảm.
5. Vì sao SELECT thông thường KHÔNG block UPDATE trong Postgres? (MVCC).
6. So sánh: tại sao MySQL InnoDB cũng MVCC nhưng default REPEATABLE READ dùng "next-key locks", còn Postgres không?

13. Quiz