Tuan-05-Transactions-Locking

Tuần 05 — Transactions & Locking

“Concurrency bug khó nhất vì nó không xuất hiện trong dev (1 user) và không xuất hiện trong staging (10 user). Nó chỉ xuất hiện trong production khi 1000 user đụng nhau, và lúc đó bạn không thể debug bằng print statement. Phải hiểu transaction để code tránh chúng từ đầu.”

Tags: database postgresql transaction isolation locking mvcc Thời lượng: 7 ngày (5-7h/ngày) Prerequisites: Tuan-01-DB-Internals-Refresh (MVCC mechanics) Liên quan: Tuan-Bonus-Consistency-Models-Isolation (SD course - lý thuyết sâu hơn) · Tuan-16-ORM-CQRS-Multi-Tenancy

---

1. Context & Why

1.1 Concurrency bug = bug đắt nhất

Trong sự nghiệp dev, các bug bạn gặp ranked theo độ khó debug:

graph LR
    A[Syntax error<br/>1 phút] --> B[Logic bug<br/>1 giờ]
    B --> C[Edge case<br/>1 ngày]
    C --> D[Race condition<br/>1 tuần]
    D --> E[Lost update<br/>vô hình đến khi audit<br/>1 quý]

    style A fill:#c8e6c9
    style B fill:#fff9c4
    style D fill:#ffccbc
    style E fill:#ef9a9a

Lost update đặc biệt nguy hiểm: không có error log, không có exception, chỉ là số tiền không khớp 6 tháng sau khi reconcile.

1.2 Mục tiêu tuần

• Hiểu ACID thực chất ý nghĩa gì (không phải định nghĩa thuộc lòng)
• Đọc 4 isolation level chuẩn SQL + biết Postgres implement khác sách
• Phân biệt 4 anomaly: dirty read, non-repeatable, phantom, write skew
• MVCC visibility rules đủ để debug “tại sao SELECT thấy row này nhưng UPDATE update 0 rows”
• Pessimistic locking: SELECT FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE
• Optimistic locking với version column
• Deadlock — diagnose, prevent, recover
• Advisory locks — application-level locks
• Hot row problem + 3 cách fix

1.3 Tham chiếu

• DDIA Ch.7 (Transactions) — Kleppmann
• Postgres docs Ch.13 (Concurrency Control)
• Jepsen testing — Postgres analysis — https://jepsen.io/analyses
• A Critique of ANSI SQL Isolation Levels — Berenson et al. (1995)
• Serializable Snapshot Isolation in PostgreSQL — Ports & Grittner (VLDB 2012)
• The Internals of PostgreSQL Ch.5 — Concurrency Control

---

2. ACID — Thực chất

2.1 A — Atomicity

“Tất cả hoặc không gì cả”. Trong Postgres = transaction.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- Cả 2 hoặc không cái nào

Implement: WAL. Khi commit, WAL có “COMMIT” record. Recovery thấy COMMIT → replay; không thấy → rollback.

2.2 C — Consistency

“DB từ state hợp lệ → state hợp lệ”. Cái này mơ hồ nhất trong ACID, gây tranh luận.

Thực tế: constraints (CHECK, FK, UNIQUE) enforce; application logic responsible cho domain consistency.

→ “C” trong ACID không phải property DB tự đảm bảo. Đó là contract giữa app và DB.

2.3 I — Isolation

“Concurrent transaction trông như chạy tuần tự”. Đây là phần khó nhất, đào sâu section 3.

2.4 D — Durability

“Commit rồi → persist qua crash”. Postgres: fsync WAL trước khi return commit (default synchronous_commit = on).

Trade-off: synchronous_commit = off → faster commits, risk mất ~200ms last data nếu crash.

2.5 ACID không phải tất cả

DDIA: “ACID” trở thành marketing term, mỗi DB define khác. Đáng quan tâm:

• “ACID compliant” có bao nhiều isolation level support?
• Default isolation level là gì?
• Có anomaly nào exist ngay cả ở highest level?

---

3. 4 Isolation Levels — Chuẩn SQL vs Postgres thực tế

3.1 Chuẩn SQL (ANSI/ISO)

graph TB
    subgraph "Standard SQL"
        RU[Read Uncommitted<br/>P0: Dirty Write blocked<br/>P1: Dirty Read possible]
        RC[Read Committed<br/>P1: Dirty Read blocked]
        RR[Repeatable Read<br/>P2: Non-repeatable read blocked]
        SER[Serializable<br/>P3: Phantom read blocked]
    end

    RU --> RC --> RR --> SER

    style RU fill:#ef9a9a
    style RC fill:#ffccbc
    style RR fill:#fff9c4
    style SER fill:#c8e6c9

Level	P1 Dirty Read	P2 Non-Repeatable	P3 Phantom
Read Uncommitted	✓ possible	✓ possible	✓ possible
Read Committed	✗	✓	✓
Repeatable Read	✗	✗	✓
Serializable	✗	✗	✗

3.2 Postgres khác chuẩn

Postgres dùng Snapshot Isolation thay cho 4 level chuẩn:

SQL standard	Postgres thực tế
Read Uncommitted	= Read Committed (Postgres không có dirty read)
Read Committed	Read Committed (default)
Repeatable Read	Snapshot Isolation (SI) — stronger than standard RR
Serializable	Serializable Snapshot Isolation (SSI)

Hệ quả:

• Postgres không có dirty read ở bất kỳ level nào → SET LEVEL READ UNCOMMITTED chỉ là alias
• RR trong Postgres khác RR sách giáo khoa
• Serializable Postgres là SSI (Ports & Grittner 2012), unique implementation

3.3 Anomalies giải thích

Dirty Read — đọc data chưa commit:

T1: UPDATE x = 10 (uncommitted)
T2: SELECT x → 10
T1: ROLLBACK
T2 vừa đọc data ma.

Postgres: ngăn mọi level.

Non-Repeatable Read — đọc 2 lần, value khác:

T1: SELECT x → 5
T2: UPDATE x = 10; COMMIT
T1: SELECT x → 10  (changed!)

Postgres Read Committed: có thể. RR/SER: ngăn (snapshot frozen).

Phantom Read — đọc range, 2 lần khác số rows:

T1: SELECT count(*) FROM orders WHERE total > 100 → 5
T2: INSERT new order total=200; COMMIT
T1: SELECT count(*) → 6

Postgres RR: ngăn (snapshot trùm cả SELECT range).

Write Skew — 2 transactions đọc overlap data, write different rows, kết quả vi phạm constraint:

T1: SELECT count(*) FROM oncall WHERE on_duty=true → 2 (Alice, Bob)
T2: SELECT count(*) FROM oncall WHERE on_duty=true → 2
T1: UPDATE Alice SET on_duty=false; COMMIT  (cần ≥1 oncall, có Bob OK)
T2: UPDATE Bob SET on_duty=false; COMMIT   (cần ≥1 oncall, T2 thấy Alice còn)
Kết quả: 0 oncall! Vi phạm.

Postgres RR: KHÔNG ngăn write skew (chỉ ngăn dirty/non-repeatable/phantom). Postgres Serializable (SSI): ngăn.

3.4 Decision tree — chọn isolation level

flowchart TD
    A[Use case] --> B{Có business invariant<br/>cross-row?}
    B -->|No| C{Cần see latest data?}
    C -->|Yes| C1[Read Committed - default]
    C -->|No - cần consistency snapshot| C2[Repeatable Read]
    B -->|Yes - invariant phức tạp| D{Volume cao?}
    D -->|No| D1[Serializable - SSI]
    D -->|Yes| D2[Read Committed +<br/>SELECT FOR UPDATE +<br/>explicit locks]

    style C1 fill:#c8e6c9
    style D1 fill:#fff9c4
    style D2 fill:#fff9c4

Rule 2024:

• 90% web app: Read Committed (default) đủ
• Reporting, batch jobs cần consistent snapshot: Repeatable Read
• Critical business rules (finance, booking, inventory): Serializable hoặc explicit locks

3.5 Set isolation level

-- Per session
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 
-- Per transaction
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- ... statements ...
COMMIT;
 
-- App-level (libpq)
SET default_transaction_isolation = 'repeatable read';

---

4. MVCC Visibility Deep Dive

4.1 Snapshot — đối tượng quan trọng nhất

Mỗi transaction lấy snapshot khi bắt đầu (RR/SER) hoặc per-statement (RC). Snapshot bao gồm:

• xmin — xid horizon: transactions trước này đều committed
• xmax — current xid: ngưỡng trên
• xip[] — list xid đang active (in-progress)

Tuple visible nếu:

• t_xmin < snapshot.xmax AND t_xmin not in snapshot.xip[] AND t_xmin đã commit
• AND (t_xmax = 0 OR t_xmax not committed OR t_xmax > snapshot.xmax)

4.2 Xem snapshot và xid

SELECT txid_current();           -- current xid
SELECT pg_current_snapshot();    -- snapshot mô tả: 'xmin:xmax:xips'
 
-- PG13+
SELECT pg_current_snapshot_xmin();
SELECT pg_current_snapshot_xmax();

4.3 Read Committed behavior

-- Session A
BEGIN;  -- isolation: read committed (default)
SELECT name FROM users WHERE id=1;  -- 'Alice'
 
-- Session B
BEGIN;
UPDATE users SET name='Bob' WHERE id=1;
COMMIT;
 
-- Session A
SELECT name FROM users WHERE id=1;  -- 'Bob'  ← thấy commit của B!
COMMIT;

Mỗi statement trong A lấy snapshot mới → thấy commit gần nhất.

4.4 Repeatable Read behavior

-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT name FROM users WHERE id=1;  -- 'Alice'
-- Snapshot lock tại đây
 
-- Session B
BEGIN;
UPDATE users SET name='Bob' WHERE id=1;
COMMIT;
 
-- Session A
SELECT name FROM users WHERE id=1;  -- 'Alice'  ← vẫn cũ
COMMIT;
SELECT name FROM users WHERE id=1;  -- 'Bob'  ← giờ mới

4.5 The classic gotcha — UPDATE in RR

-- Session A (RR)
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id=1;  -- 100
 
-- Session B
UPDATE accounts SET balance=200 WHERE id=1;
COMMIT;
 
-- Session A
UPDATE accounts SET balance=balance+50 WHERE id=1;
-- ERROR: could not serialize access due to concurrent update
-- ↑ "Lost update prevention" trong RR

Postgres RR detect: UPDATE đụng vào row đã modified bởi transaction khác (after snapshot) → reject, app retry.

4.6 Serializable - SSI

Serializable Snapshot Isolation: detect read-write conflicts forming cycle → abort 1 transaction.

-- Both T1, T2 in SERIALIZABLE
T1: SELECT count(*) FROM oncall WHERE on_duty=true
T2: SELECT count(*) FROM oncall WHERE on_duty=true
T1: UPDATE Alice SET on_duty=false
T2: UPDATE Bob SET on_duty=false
T1: COMMIT  -- OK
T2: COMMIT  -- ERROR: could not serialize access due to read/write dependencies

SSI overhead:

• Per-tuple SIREAD locks (predicate locks)
• 1-5% throughput cost
• Abort rate phụ thuộc workload — design app để retry

4.7 Retry logic là mandatory cho RR/SER

def transfer(from_id, to_id, amount, max_retries=3):
    for attempt in range(max_retries):
        try:
            with conn.transaction(isolation='repeatable read'):
                # ... transfer logic ...
            return
        except SerializationFailure:
            if attempt == max_retries - 1:
                raise
            time.sleep(0.01 * (2 ** attempt))  # exp backoff

→ Library: PostgreSQL JDBC, psycopg3, sqlalchemy có retry decorator.

---

5. Locks — Hệ thống lock của Postgres

5.1 Hierarchy

graph TB
    subgraph "Postgres Locks"
        TL[Table-level locks<br/>8 levels: ACCESS SHARE → ACCESS EXCLUSIVE]
        RL[Row-level locks<br/>4 modes: FOR SHARE / KEY SHARE / NO KEY UPDATE / UPDATE]
        PL[Page-level latches<br/>internal]
        AL[Advisory locks<br/>application-level]
        DL[Deadlock detector<br/>1s default check interval]
    end

    TL -.weaker→stronger.-> TL
    RL -.weaker→stronger.-> RL

5.2 Table-level locks

8 levels, sorted strength:

Lock mode	Conflicts với	Acquired by
ACCESS SHARE	ACCESS EXCLUSIVE	SELECT
ROW SHARE	EXCLUSIVE, ACCESS EXCLUSIVE	SELECT FOR UPDATE/SHARE
ROW EXCLUSIVE	SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE	INSERT, UPDATE, DELETE
SHARE UPDATE EXCLUSIVE	itself, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE	VACUUM, CREATE INDEX CONCURRENTLY, ANALYZE
SHARE	ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE	CREATE INDEX (without CONCURRENTLY)
SHARE ROW EXCLUSIVE	mọi cái trên + itself	rare
EXCLUSIVE	mọi cái trừ ACCESS SHARE	REFRESH MATERIALIZED VIEW CONCURRENTLY
ACCESS EXCLUSIVE	mọi cái	DROP, ALTER TABLE, TRUNCATE, REINDEX (non-CONCURRENTLY), VACUUM FULL

Key insight: SELECT (ACCESS SHARE) chỉ conflict với ACCESS EXCLUSIVE. Đó là lý do DDL block readers — ALTER TABLE lấy ACCESS EXCLUSIVE.

5.3 ALTER TABLE — silent killer

ALTER TABLE orders ADD COLUMN notes text;
-- Lấy ACCESS EXCLUSIVE → block mọi SELECT/INSERT/UPDATE đang chạy
-- Thường <1s, nhưng nếu có long transaction → ALTER queue → mọi query mới cũng queue
-- Cascading lock wait → app outage

Tuần 08 dạy zero-downtime DDL. Preview pattern:

-- BAD - block tất cả khi backfill
ALTER TABLE orders ADD COLUMN status text NOT NULL DEFAULT 'pending';
 
-- GOOD - 2 steps
ALTER TABLE orders ADD COLUMN status text;          -- fast metadata only PG11+
-- Backfill in chunks
UPDATE orders SET status='pending' WHERE id BETWEEN ... AND ... ;
-- Then add constraint
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;  -- still ACCESS EXCLUSIVE but fast
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'pending';

PG11+ added column with constant default → metadata-only, instant. Default function → still rewrite.

5.4 Row-level locks (4 modes)

Mới trong PG9.3+:

graph LR
    KS[FOR KEY SHARE<br/>weakest]
    S[FOR SHARE]
    NKU[FOR NO KEY UPDATE]
    U[FOR UPDATE<br/>strongest]

    KS --> S --> NKU --> U

Compatibility matrix:

	KEY SHARE	SHARE	NO KEY UPDATE	UPDATE
KEY SHARE	✓	✓	✓	✗
SHARE	✓	✓	✗	✗
NO KEY UPDATE	✓	✗	✗	✗
UPDATE	✗	✗	✗	✗

Khi nào dùng:

• FOR UPDATE — sẽ UPDATE row, lock mạnh nhất
• FOR NO KEY UPDATE — UPDATE column không phải PK/unique → ít conflict với FK check
• FOR SHARE — đọc nhưng prevent UPDATE/DELETE
• FOR KEY SHARE — FK check lock — weakest, dùng tự động cho FK validation

5.5 SELECT FOR UPDATE pattern

-- Pessimistic: lock row, đảm bảo no one else modifies
BEGIN;
SELECT balance FROM accounts WHERE id=1 FOR UPDATE;
-- ... compute new balance ...
UPDATE accounts SET balance = new_balance WHERE id=1;
COMMIT;

Behavior:

• Block khác FOR UPDATE, FOR NO KEY UPDATE trên cùng row
• Block UPDATE/DELETE
• Không block bình thường SELECT (vẫn MVCC visible)

5.6 NOWAIT, SKIP LOCKED

-- Không chờ, fail nếu locked
SELECT * FROM jobs WHERE status='pending' FOR UPDATE NOWAIT;
 
-- Skip locked rows - perfect for queue worker
SELECT * FROM jobs WHERE status='pending' ORDER BY priority LIMIT 1 FOR UPDATE SKIP LOCKED;

SKIP LOCKED (PG9.5+) là superpower cho queue worker pattern:

• 10 workers cùng SELECT jobs
• Mỗi worker pick job khác nhau (skip job worker khác đã lock)
• Không block lẫn nhau

5.7 Advisory Lock — application-level

-- Acquire (blocking)
SELECT pg_advisory_lock(12345);
 
-- Acquire (try, non-blocking)
SELECT pg_try_advisory_lock(12345);
 
-- Release
SELECT pg_advisory_unlock(12345);
 
-- Transaction-scoped (auto-release on commit/rollback)
SELECT pg_advisory_xact_lock(12345);

Use cases:

• Distributed cron lock: chỉ 1 worker chạy job
• Cross-table coordination
• Application semaphore

# Python pattern
def run_critical_job():
    with conn.cursor() as cur:
        if not cur.execute("SELECT pg_try_advisory_lock(%s)", [JOB_ID]).fetchone()[0]:
            return  # another instance holds it
        try:
            # ... critical section ...
        finally:
            cur.execute("SELECT pg_advisory_unlock(%s)", [JOB_ID])

---

6. Deadlock

6.1 Cơ chế

T1: BEGIN; UPDATE accounts SET ... WHERE id=1;  (lock row 1)
T2: BEGIN; UPDATE accounts SET ... WHERE id=2;  (lock row 2)
T1: UPDATE accounts SET ... WHERE id=2;          (wait for T2)
T2: UPDATE accounts SET ... WHERE id=1;          (wait for T1)

→ Cycle. Postgres detect (default deadlock_timeout=1s) → abort one transaction.

ERROR: deadlock detected
DETAIL: Process X waits for ShareLock on transaction Y;
        Process Z waits for ShareLock on transaction X.

6.2 Diagnose

-- Active waits
SELECT pid, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state = 'active' AND wait_event IS NOT NULL;
 
-- Detailed lock info
SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query,
    blocking.state AS blocking_state
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

6.3 Prevention patterns

1. Lock ordering — always lock rows in consistent order:

# BAD: random order
acc1, acc2 = random_pair()
lock(acc1); lock(acc2)
 
# GOOD: sorted order
acc1, acc2 = sorted([id_a, id_b])
lock(acc1); lock(acc2)

2. Short transactions — giảm window deadlock
3. Retry với backoff — code phải resilient

def with_retry(func, max_retries=3):
    for attempt in range(max_retries):
        try:
            return func()
        except DeadlockDetected:
            if attempt == max_retries - 1: raise
            time.sleep(0.01 * (2 ** attempt) + random.uniform(0, 0.01))  # jitter

6.4 Log mọi deadlock

log_lock_waits = on
deadlock_timeout = 1s

Postgres log mỗi deadlock detected. Monitor count → spike = problem area.

---

7. Optimistic Concurrency

7.1 Pattern

Thay vì lock, dùng version column:

CREATE TABLE accounts (
    id bigint PRIMARY KEY,
    balance bigint NOT NULL,
    version int NOT NULL DEFAULT 0
);
 
-- Read
SELECT balance, version FROM accounts WHERE id=1;
-- balance=100, version=5
 
-- App computes new balance
 
-- Write with version check
UPDATE accounts
SET balance=150, version=version+1
WHERE id=1 AND version=5;
 
-- If rowcount=0 → conflict, retry

7.2 Trade-off vs pessimistic

	Optimistic	Pessimistic
Conflict rate cao	Tốn nhiều retry	Lock fairness
Conflict rate thấp	Cực hiệu quả	Lock overhead waste
Latency P99	Variable (retry)	Predictable
Code complexity	Retry loop everywhere	Begin/commit boundary

7.3 Khi nào dùng cái nào

• Optimistic: read-heavy, conflict hiếm (<5%), update bằng app code
• Pessimistic: write-heavy hot rows, conflict thường xuyên, financial precision

7.4 Postgres specific: RR isolation = automatic optimistic

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM accounts WHERE id=1;
-- ... logic ...
UPDATE accounts SET balance=... WHERE id=1;
-- Postgres tự throw serialization failure nếu row đã đổi
COMMIT;

→ Cleaner code, vẫn cần retry logic.

---

8. Hot Row Problem

8.1 Vấn đề

-- Global counter
UPDATE site_stats SET total_views = total_views + 1;

10K requests/sec → 10K UPDATE same row → mỗi UPDATE wait lock → throughput ~100/sec, không phải 10K.

8.2 Solution 1 — Sharded counter

CREATE TABLE site_stats_shards (
    metric text,
    shard smallint,
    value bigint DEFAULT 0,
    PRIMARY KEY (metric, shard)
);
 
-- Insert chọn random shard (vd: 0-9)
UPDATE site_stats_shards
SET value = value + 1
WHERE metric = 'views' AND shard = (random()*10)::smallint;
 
-- Read
SELECT sum(value) FROM site_stats_shards WHERE metric = 'views';

Trade-off: write nhanh, read tốn (sum nhiều shard). OK với metric không cần realtime exact.

8.3 Solution 2 — Batch in app

# Buffer in app, flush mỗi 100 events
batch = []
def increment():
    batch.append(1)
    if len(batch) >= 100:
        execute("UPDATE stats SET views = views + %s", [sum(batch)])
        batch.clear()

Risk: data loss nếu crash trước flush. Acceptable cho analytics.

8.4 Solution 3 — Async with queue

App → Kafka → Aggregator → Postgres batch upsert

Decoupling. Tuần 14 đào sâu OLAP pattern.

8.5 Solution 4 — Materialized view periodic

-- Source: per-event log
INSERT INTO event_log (...) VALUES (...);
 
-- Aggregate periodically (cron)
REFRESH MATERIALIZED VIEW CONCURRENTLY site_stats_mv;

---

9. Real-world Patterns

9.1 Bank transfer — pessimistic

BEGIN;
SELECT balance FROM accounts WHERE id IN (1, 2) FOR UPDATE;
-- Sorted to prevent deadlock
UPDATE accounts SET balance = balance - 100 WHERE id = LEAST(1, 2);
UPDATE accounts SET balance = balance + 100 WHERE id = GREATEST(1, 2);
COMMIT;

Add CHECK constraint cho safety:

ALTER TABLE accounts ADD CONSTRAINT no_negative CHECK (balance >= 0);

9.2 Inventory decrement

-- Atomic decrement with check
UPDATE products
SET stock = stock - 1
WHERE id = $1 AND stock > 0
RETURNING stock;
 
-- If 0 rows → out of stock

No lock needed. Concurrent reservation works correctly because UPDATE locks row atomically.

9.3 Queue worker — SKIP LOCKED

BEGIN;
SELECT id, payload FROM jobs
WHERE status = 'pending' AND scheduled_at <= now()
ORDER BY priority DESC, scheduled_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
 
-- Got job? Process, then:
UPDATE jobs SET status='completed' WHERE id=$1;
COMMIT;

100 workers, no contention, each picks different job.

9.4 Idempotent insert — ON CONFLICT

-- UPSERT pattern
INSERT INTO orders (id, user_id, ...)
VALUES (...)
ON CONFLICT (id) DO UPDATE SET updated_at = excluded.updated_at;
 
-- Get-or-create
WITH ins AS (
    INSERT INTO users (email) VALUES ($1)
    ON CONFLICT (email) DO NOTHING
    RETURNING *
)
SELECT * FROM ins
UNION ALL
SELECT * FROM users WHERE email = $1 AND NOT EXISTS (SELECT 1 FROM ins);

9.5 Booking system — EXCLUDE constraint

CREATE TABLE bookings (
    room_id bigint,
    during tstzrange NOT NULL,
    EXCLUDE USING gist (room_id WITH =, during WITH &&)
);
 
-- No need for explicit lock; constraint enforces
INSERT INTO bookings (room_id, during) VALUES (1, '[2026-05-20 10:00, 2026-05-20 12:00)');
-- Conflicting insert → ERROR: conflicting key value violates exclusion constraint

9.6 Distributed lock — advisory

JOB_LOCK_ID = hash("daily_report") % 2**31
 
with conn.cursor() as cur:
    cur.execute("SELECT pg_try_advisory_lock(%s)", [JOB_LOCK_ID])
    if not cur.fetchone()[0]:
        return  # someone else holds
    try:
        run_daily_report()
    finally:
        cur.execute("SELECT pg_advisory_unlock(%s)", [JOB_LOCK_ID])

9.7 Rate limiter — token bucket trong DB

CREATE TABLE rate_limits (
    user_id bigint PRIMARY KEY,
    tokens int NOT NULL DEFAULT 100,
    refilled_at timestamptz NOT NULL DEFAULT now()
);
 
-- Check + consume in 1 query
UPDATE rate_limits
SET
    tokens = CASE
        WHEN now() - refilled_at > interval '1 hour' THEN 100 - 1  -- refill + consume
        ELSE tokens - 1
    END,
    refilled_at = CASE
        WHEN now() - refilled_at > interval '1 hour' THEN now()
        ELSE refilled_at
    END
WHERE user_id = $1 AND (tokens > 0 OR now() - refilled_at > interval '1 hour')
RETURNING tokens;
-- 0 rows = rate limited

Better: dedicated rate limiter (Redis), nhưng Postgres-only works cho low scale.

---

10. Pitfalls

10.1 Long-running transaction

BEGIN;
SELECT ... ;
-- ... 10 phút app logic ...
COMMIT;

Hậu quả:

• Block vacuum (xmin horizon)
• Bloat table
• Hold row locks → block others

→ Monitor & kill:

SELECT pid, now()-xact_start AS duration, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY duration DESC;
 
SELECT pg_terminate_backend(pid);

Setting:

idle_in_transaction_session_timeout = 30s
statement_timeout = 60s

10.2 Implicit transactions trong ORM

ORM thường auto-begin nếu chưa có. Nếu hold connection mà không commit → “idle in transaction” leak.

→ Always set idle_in_transaction_session_timeout.

10.3 SELECT FOR UPDATE without LIMIT

SELECT * FROM orders FOR UPDATE;
-- Locks ALL rows!

Always combine with WHERE + LIMIT. SKIP LOCKED nếu queue pattern.

10.4 Transaction in HTTP request handler

@app.route('/api/order')
def create_order():
    with conn.transaction():
        # ... 5 seconds work ...
        external_api_call()  # ← may take 30s
        ...

External call inside transaction = transaction hold open 30s = deadlock risk + connection pool exhaustion.

→ Move external call out of transaction. Transaction chỉ wrap DB writes.

10.5 Foreign key without index

FK constraint check fires query: SELECT ... WHERE referencing_col = ?. Không có index → seq scan trên cascade/check.

→ Index FK columns (default trên child).

---

11. Lab

11.1 Setup

Use schema từ Tuần 02. 2 psql sessions song song để reproduce concurrency.

11.2 Day 1 — Isolation levels

Reproduce all 4 anomalies:

-- Session A:
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT name FROM users WHERE id=1;
-- Wait for B
 
-- Session B:
BEGIN;
UPDATE users SET name='X' WHERE id=1;
COMMIT;
 
-- Session A:
SELECT name FROM users WHERE id=1;  -- changed
-- Repeat with REPEATABLE READ

Reproduce write skew (oncall example) under each level.

11.3 Day 2 — Row locks

-- Demo SELECT FOR UPDATE conflict
-- Session A
BEGIN; SELECT * FROM accounts WHERE id=1 FOR UPDATE;
-- hold
 
-- Session B
BEGIN; SELECT * FROM accounts WHERE id=1 FOR UPDATE;
-- blocks
 
-- Session A
COMMIT;
-- Session B continues
 
-- Repeat with FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE
-- Check compatibility matrix

11.4 Day 3 — Deadlock reproduce

import threading, psycopg
 
def t1():
    with conn1.cursor() as c:
        c.execute("BEGIN")
        c.execute("UPDATE accounts SET balance=1 WHERE id=1")
        time.sleep(1)
        c.execute("UPDATE accounts SET balance=1 WHERE id=2")  # waits
        c.execute("COMMIT")
 
def t2():
    with conn2.cursor() as c:
        c.execute("BEGIN")
        c.execute("UPDATE accounts SET balance=1 WHERE id=2")
        time.sleep(1)
        c.execute("UPDATE accounts SET balance=1 WHERE id=1")  # deadlock
        c.execute("COMMIT")
 
threading.Thread(target=t1).start()
threading.Thread(target=t2).start()

Một thread sẽ raise deadlock. Fix bằng sorted locking.

11.5 Day 4 — SKIP LOCKED queue

CREATE TABLE jobs (id bigserial PK, status text, payload text, locked_at timestamptz);
INSERT INTO jobs (status, payload) SELECT 'pending', 'job '||i FROM generate_series(1,1000) i;
 
-- Worker function (run in 10 sessions parallel)
WITH job AS (
    SELECT id FROM jobs
    WHERE status='pending'
    ORDER BY id
    FOR UPDATE SKIP LOCKED
    LIMIT 1
)
UPDATE jobs SET status='processing', locked_at=now()
WHERE id = (SELECT id FROM job)
RETURNING *;

10 workers parallel → each pick different job, no contention.

11.6 Day 5 — Hot row sharded counter benchmark

-- Single counter
CREATE TABLE single_counter (value bigint);
INSERT INTO single_counter VALUES (0);
 
-- Sharded
CREATE TABLE sharded_counter (shard smallint PRIMARY KEY, value bigint);
INSERT INTO sharded_counter SELECT i, 0 FROM generate_series(0,15) i;
 
-- pgbench script
-- script1.sql:
UPDATE single_counter SET value = value + 1;
-- script2.sql:
UPDATE sharded_counter SET value = value + 1 WHERE shard = (random()*16)::smallint;
 
-- Bench
pgbench -c 50 -j 4 -T 30 -f script1.sql labdb
pgbench -c 50 -j 4 -T 30 -f script2.sql labdb
-- Compare TPS

11.7 Day 6 — Optimistic concurrency

-- Account with version
CREATE TABLE accounts (id bigint PK, balance bigint, version int DEFAULT 0);
 
-- App-level retry loop in script
-- Show conflict rate vs row contention level

11.8 Day 7 — Serializable retries

Build banking app: transfer money, hold under SERIALIZABLE, measure retry rate. Tune work patterns.

---

12. Self-check

1. Postgres có dirty read không? Tại sao?
2. RR trong Postgres khác RR chuẩn SQL ở điểm nào?
3. Write skew là gì? Cho ví dụ. Level nào ngăn được?
4. SELECT FOR UPDATE vs FOR NO KEY UPDATE — khác nhau? Khi nào dùng cái nào?
5. SKIP LOCKED ứng dụng tốt nhất ở pattern nào?
6. Deadlock — Postgres detect như thế nào? Sau detect làm gì?
7. Hot row problem là gì? 3 solutions?
8. Optimistic vs pessimistic — khi nào pick mỗi cái?
9. ALTER TABLE ADD COLUMN — tại sao có thể outage?
10. Long-running transaction tác động vacuum thế nào?

---

13. Tiếp theo

Bài tiếp: Tuan-06-Connection-Pooling-PgBouncer — đi từ data layer sang connection layer.

Cross-link: Tuan-Bonus-Consistency-Models-Isolation (SD course) cho lý thuyết distributed.

---

Tuần 05 hoàn thành. Phase 1 (Relational Deep Dive) DONE. Bạn đã có xương sống. Cập nhật: 2026-05-16