Concurrency Control

// Schedule S: R1(A) → R2(A) → W1(A) → R2(B) → W2(A) → W1(B) → C1 → C2

// Check every pair of operations from DIFFERENT transactions:

// R1(A) and R2(A): same item A, different transactions, BUT both are reads → NO CONFLICT
// R1(A) and W2(A): same item A, different transactions, W2 is a write → CONFLICT (R-W)
// R1(A) and R2(B): different items (A vs B) → NO CONFLICT
// R1(A) and W2(A): already noted
// W1(A) and R2(A): R2 reads A, W1 writes A, different transactions → CONFLICT (W-R)
// W1(A) and W2(A): same item A, different transactions, both writes → CONFLICT (W-W)
// R2(B) and W1(B): same item B, different transactions, W1 writes → CONFLICT (R-W)
// W2(A) and W1(A): same as W1(A) and W2(A) — conflict
// W2(A) and W1(B): different items → NO CONFLICT
// W1(B) and W2(A): different items → NO CONFLICT

// CONFLICTING PAIRS in this schedule:
// (R1(A), W2(A)) — T1 reads A before T2 writes A
// (W1(A), R2(A)) — T2 reads A after T1 writes A → but wait, R2(A) comes BEFORE W1(A)
//                  in the schedule: R2(A) is 2nd, W1(A) is 3rd → T2 reads A before T1 writes
//                  so this is: R2(A) before W1(A) → T2 reads A, T1 later writes A
// (W1(A), W2(A)) — T1 writes A, T2 later writes A
// (R2(B), W1(B)) — T2 reads B, T1 later writes B

// For conflict serializability: can we swap non-conflicting adjacent operations
// to transform this schedule into a serial schedule?
// See Precedence Graph method below for the systematic approach.

// ─────────────────────────────────────────────────────────────────
// EXAMPLE 1: Schedule that IS conflict-serializable
// Schedule S1: R1(A) → W1(A) → R2(A) → W2(A) → R1(B) → W2(B) → C1 → C2
// ─────────────────────────────────────────────────────────────────

// Step 1: Find all conflicting pairs:
// W1(A) before R2(A): W-R conflict on A → edge T1 → T2
// W1(A) before W2(A): W-W conflict on A → edge T1 → T2 (already have this)
// R1(B) before W2(B): R-W conflict on B → edge T1 → T2 (already have this)

// Precedence graph: T1 ──→ T2  (one edge, no cycle)

// Is it acyclic? YES — no cycle
// Equivalent serial schedule: T1, T2 (topological order)
// S1 IS conflict-serializable ✓

// ─────────────────────────────────────────────────────────────────
// EXAMPLE 2: Schedule that is NOT conflict-serializable
// Schedule S2: R1(A) → R2(B) → W1(B) → W2(A) → C1 → C2
// ─────────────────────────────────────────────────────────────────

// Step 1: Find all conflicting pairs:
// R1(A) before W2(A): R-W conflict on A → edge T1 → T2
// R2(B) before W1(B): R-W conflict on B → edge T2 → T1

// Precedence graph: T1 ──→ T2
//                  T2 ──→ T1   ← CYCLE!

// T1 must come before T2 (from R1(A)-W2(A) conflict)
// T2 must come before T1 (from R2(B)-W1(B) conflict)
// Contradiction — no valid serial order exists.

// S2 is NOT conflict-serializable ✗
// This schedule can produce results inconsistent with any serial execution.

// ─────────────────────────────────────────────────────────────────
// EXAMPLE 3: Three transactions — GATE level
// Schedule S3: R1(X) → R2(X) → W2(X) → R3(X) → W1(X) → W3(X) → C1 → C2 → C3
// ─────────────────────────────────────────────────────────────────

// Step 1: Find all conflicting pairs (same item, different tx, at least one write):
// R1(X) before W2(X): R-W on X → T1 → T2
// R1(X) before W3(X): R-W on X → T1 → T3
// R2(X) before W1(X): R-W on X → T2 → T1   ← note direction: R2 before W1
// R2(X) before W3(X): R-W on X → T2 → T3
// W2(X) before R3(X): W-R on X → T2 → T3 (already have this)
// W2(X) before W1(X): W-W on X → T2 → T1 (already have this)
// W2(X) before W3(X): W-W on X → T2 → T3 (already have this)
// R3(X) before W1(X): R-W on X → T3 → T1
// W1(X) before W3(X): W-W on X → T1 → T3 (already have this)

// Unique edges:
// T1 → T2 (from R1 before W2)
// T1 → T3 (from R1 before W3)
// T2 → T1 (from R2 before W1) ← T1→T2 AND T2→T1 = CYCLE between T1 and T2
// T2 → T3 (from R2 before W3)
// T3 → T1 (from R3 before W1) ← T1→T3 AND T3→T1 = CYCLE between T1 and T3

// Precedence graph has cycles: T1↔T2 and T1↔T3
// S3 is NOT conflict-serializable ✗

// Schedule: W1(X) → W2(X) → W1(X) → C1 → C2
// (T1 writes X, T2 writes X, T1 writes X again)

// Conflicting pairs:
// W1(X) first and W2(X): W-W on X → T1 → T2
// W2(X) and W1(X) second: W-W on X → T2 → T1  ← CYCLE

// Precedence graph has cycle T1↔T2 → NOT conflict-serializable.

// But check view-serializability against serial schedule (T1, T2):
// Serial (T1 then T2): W1(X), W1(X), W2(X)
//   Final write of X: T2 ✓
// Our schedule: W1(X), W2(X), W1(X)
//   Final write of X: T1 ✗ — T2's write is not the final write!

// Check against serial schedule (T2, T1):
// Serial (T2 then T1): W2(X), W1(X), W1(X)
//   Final write of X: T1
// Our schedule W1(X), W2(X), W1(X):
//   Initial reads: none (no reads in this schedule)
//   Updated reads: none
//   Final write of X: T1 ✓ (matches)
// → View-equivalent to serial (T2, T1)!
// → Schedule IS view-serializable but NOT conflict-serializable.

// WHY: W2(X) is a blind write — T2 writes X without reading it,
// and T1 immediately overwrites it. T2's write is "invisible."
// The presence of blind writes can make a non-conflict-serializable
// schedule still view-serializable.

// PRACTICAL NOTE: Most databases test for conflict serializability
// because it is efficiently checkable (O(n²) precedence graph).
// View serializability testing is NP-complete in general.

-- EXPLICIT ROW-LEVEL LOCKS in SQL:

-- SELECT FOR UPDATE: acquire X-lock on selected rows
-- Blocks other transactions from reading or writing those rows
BEGIN;
SELECT balance FROM accounts WHERE account_id = 'A' FOR UPDATE;
-- Row A is now X-locked. Any other transaction attempting:
-- SELECT ... FOR UPDATE on A → BLOCKS until this transaction commits/rolls back
-- SELECT ... (without FOR UPDATE) → behaviour depends on isolation level
UPDATE accounts SET balance = balance - 500 WHERE account_id = 'A';
COMMIT;  -- lock released

-- SELECT FOR SHARE: acquire S-lock on selected rows
-- Allows other S-locks but blocks X-locks
BEGIN;
SELECT balance FROM accounts WHERE account_id = 'A' FOR SHARE;
-- Row A is S-locked. Other transactions CAN read FOR SHARE.
-- But: SELECT ... FOR UPDATE on A → BLOCKS
-- Good for: "I want to read this and be sure it's not modified while I calculate"
COMMIT;

-- SELECT FOR UPDATE SKIP LOCKED: skip rows that are already locked
-- Instead of waiting, move on to the next available row
-- Useful for job queues: grab the next available job without blocking
BEGIN;
SELECT job_id, payload FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- Multiple workers can run this concurrently — each grabs a different unlocked row
UPDATE job_queue SET status = 'processing' WHERE job_id = :grabbed_id;
COMMIT;

-- SELECT FOR UPDATE NOWAIT: fail immediately if lock not available
-- Instead of waiting, return an error
BEGIN;
SELECT * FROM inventory WHERE product_id = 'P001' FOR UPDATE NOWAIT;
-- If P001 is locked: ERROR: could not obtain lock on row in relation "inventory"
-- Application can catch this and retry later

A transaction must follow a strict two-phase structure regarding lock acquisition and release:

// TRANSACTION T1 — following 2PL:

// GROWING PHASE: acquire locks, do not release any
lock_S(A);    // acquire S-lock on A ← growing phase
read(A);
lock_X(B);    // acquire X-lock on B ← still growing phase
write(B);
lock_S(C);    // acquire S-lock on C ← still growing phase
read(C);
// ← LOCK POINT: this is the moment T1 holds its maximum lock set

// SHRINKING PHASE: release locks, acquire no new ones
unlock(A);    // release S-lock on A ← shrinking phase begins
unlock(B);    // release X-lock on B ← still shrinking
unlock(C);    // release S-lock on C ← still shrinking
commit;

// 2PL VIOLATION (what you must NOT do):
lock_S(A);
read(A);
unlock(A);    // ← released a lock
lock_X(B);    // ← acquiring a NEW lock AFTER releasing → VIOLATES 2PL!
// This sequence is NOT 2PL compliant and may produce non-serializable schedules.

// THEOREM: Any schedule produced by transactions following 2PL is
// conflict-serializable. 2PL is a SUFFICIENT condition for conflict serializability.
// (It is not necessary — there exist serializable schedules not produced by 2PL.)

// WAIT-FOR GRAPH for the deadlock above:
// T1 is waiting for T2 (T1 wants B, which T2 holds) → edge T1 → T2
// T2 is waiting for T1 (T2 wants A, which T1 holds) → edge T2 → T1

// Graph: T1 → T2 → T1  (cycle of length 2 = deadlock involving 2 transactions)

// THREE-TRANSACTION DEADLOCK:
// T1 holds A, wants B
// T2 holds B, wants C
// T3 holds C, wants A

// Wait-for graph:
// T1 → T2 (T1 waits for T2 to release B)
// T2 → T3 (T2 waits for T3 to release C)
// T3 → T1 (T3 waits for T1 to release A)

// Cycle: T1 → T2 → T3 → T1  (deadlock involving 3 transactions)

// DEADLOCK DETECTION PROCESS (performed by DBMS periodically):
// 1. Build current wait-for graph from lock manager state
// 2. Run cycle detection algorithm (DFS or BFS)
// 3. If cycle found: select a victim transaction to abort
// 4. Abort the victim: release all its locks, roll back its work
// 5. The waiting transactions can now proceed
// 6. Repeat detection periodically (every 1 second in PostgreSQL by default)

// HOW POSTGRESQL HANDLES DEADLOCKS:
-- Client A:
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 'A';
-- holds X-lock on account A

-- Client B (at same time):
BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE account_id = 'B';
-- holds X-lock on account B
UPDATE accounts SET balance = balance + 300 WHERE account_id = 'A';
-- BLOCKS: waiting for X-lock on A (held by Client A)

-- Client A continues:
UPDATE accounts SET balance = balance + 500 WHERE account_id = 'B';
-- BLOCKS: waiting for X-lock on B (held by Client B)

-- After deadlock detection timeout:
-- ERROR: deadlock detected
-- DETAIL: Process 12345 waits for ShareLock on transaction 67890;
--         blocked by process 11111.
-- HINT: See server log for query details.
-- Client B's transaction is rolled back (ABORTED).
-- Client A's lock request on B is now granted. Client A proceeds and commits.
-- Client B must retry its transaction.

-- THE #1 PRACTICAL DEADLOCK PREVENTION:
-- Always acquire locks in a consistent global order.

-- BAD: T1 and T2 acquire locks in different orders
-- T1: lock A then lock B
-- T2: lock B then lock A
-- → Deadlock possible (as shown above)

-- GOOD: Both transactions always lock in alphabetical/ID order
-- T1: lock A then lock B (A < B alphabetically)
-- T2: lock A then lock B (same order, even though T2 needs B first)
-- → Deadlock impossible with consistent ordering

-- In SQL: ensure JOINs and UPDATEs touch tables in consistent order

-- BAD (inconsistent order creates deadlock risk):
-- Transaction T1:
UPDATE orders SET status = 'processing' WHERE order_id = 1;   -- locks order 1
UPDATE payments SET status = 'captured' WHERE order_id = 1;  -- then locks payment 1

-- Transaction T2 (concurrent):
UPDATE payments SET status = 'refunding' WHERE order_id = 1; -- locks payment 1
UPDATE orders SET status = 'refunding' WHERE order_id = 1;   -- then locks order 1
-- → Classic deadlock: T1 holds order, wants payment. T2 holds payment, wants order.

-- GOOD: consistent order (always update orders before payments):
-- T1:
UPDATE orders  SET status = 'processing' WHERE order_id = 1;  -- lock order first
UPDATE payments SET status = 'captured'  WHERE order_id = 1; -- then payment

-- T2:
UPDATE orders  SET status = 'refunding'  WHERE order_id = 1;  -- lock order first
UPDATE payments SET status = 'refunding' WHERE order_id = 1; -- then payment
-- T2 will wait for T1 to release the order lock — but no deadlock.

-- ANOTHER PRACTICAL PATTERN: lock all needed rows at the START of the transaction
BEGIN;
-- Lock both rows immediately before doing any work
SELECT * FROM accounts WHERE account_id IN ('A', 'B') ORDER BY account_id FOR UPDATE;
-- ORDER BY ensures consistent lock ordering even for SET operations
-- Now safely update:
UPDATE accounts SET balance = balance - 500 WHERE account_id = 'A';
UPDATE accounts SET balance = balance + 500 WHERE account_id = 'B';
COMMIT;

// Every transaction Ti has timestamp TS(Ti).
// Each data item X has W-TS(X) and R-TS(X).
// Goal: ensure execution is equivalent to serial order by timestamp.

// ─────────────────────────────────────────────
// RULE FOR READ: Ti wants to read X
// ─────────────────────────────────────────────

if TS(Ti) < W-TS(X):
    // Ti is trying to read a value that was written by a YOUNGER transaction Tj.
    // This means Tj already "happened after" Ti in the logical timeline.
    // Allowing Ti to read X would be like reading a value from the future.
    REJECT — abort Ti and restart with a new (later) timestamp.
else:
    // Ti's timestamp is >= the last write timestamp — safe to read.
    Allow the read.
    R-TS(X) = max(R-TS(X), TS(Ti))  // update read timestamp

// ─────────────────────────────────────────────
// RULE FOR WRITE: Ti wants to write X
// ─────────────────────────────────────────────

if TS(Ti) < R-TS(X):
    // A younger transaction Tj already read X.
    // If Ti now writes X, Tj would have read a value it should not have seen.
    // The serial order says Ti should come before Tj, but Tj already read
    // the old value — contradiction. Cannot allow this write.
    REJECT — abort Ti and restart with a new timestamp.
elif TS(Ti) < W-TS(X):
    // A younger transaction Tj already wrote X.
    // Ti's write would be outdated — in serial order, Tj's write should win.
    // Apply Thomas's Write Rule: IGNORE (skip) this write — it is obsolete.
    // Do NOT abort — just skip the write. This is an optimisation.
    Ignore the write (Thomas's Write Rule).
else:
    // Safe to write.
    Write X.
    W-TS(X) = TS(Ti)

// KEY PROPERTIES:
// 1. No locks needed — no deadlocks possible (deadlocks require waiting; TO aborts instead)
// 2. Produces conflict-serializable schedules (equivalent to serial order by timestamp)
// 3. Starvation possible: an unlucky transaction may be repeatedly aborted and restarted
//    if it always conflicts with younger transactions. Addressed by aging (give long-waiting
//    transactions priority).

// Transactions: T1 (TS=1), T2 (TS=2), T3 (TS=3)
// Initial state: W-TS(A)=0, R-TS(A)=0, W-TS(B)=0, R-TS(B)=0

// Schedule (in execution order):
// 1. R1(A): TS(T1)=1, W-TS(A)=0. Is 1 < 0? NO. Allow. R-TS(A) = max(0,1) = 1
// 2. R2(A): TS(T2)=2, W-TS(A)=0. Is 2 < 0? NO. Allow. R-TS(A) = max(1,2) = 2
// 3. W1(A): TS(T1)=1, R-TS(A)=2. Is 1 < 2? YES → ABORT T1!
//           T1 tried to write A, but T2 (younger, TS=2) already read A.
//           Allowing T1's write would mean T2 should have seen T1's write
//           (since T1 logically comes before T2) but T2 already read the old value.
//           Contradiction → reject T1's write.

// T1 is aborted and will restart with a new timestamp TS(T1_new) = 4 (current time)
// T2 and T3 continue.

// ANALYSIS: This prevents the scenario where T2 reads a stale value of A
// that would be inconsistent with T1's write in the serial order T1→T2→T3.

-- PostgreSQL MVCC internals (using system columns)

-- Initial state: account balance = ₹5000
-- Row version 1: (account_id='A', balance=5000, xmin=100, xmax=0)
--   xmin=100: created by transaction 100
--   xmax=0: not yet deleted or updated

-- T1 (transaction ID 200) starts: "Transfer ₹500 from A"
BEGIN;  -- T1 takes a snapshot: sees all committed tx up to ~199
UPDATE accounts SET balance = 4500 WHERE account_id = 'A';

-- MVCC action: does NOT overwrite the existing row.
-- Instead, creates a NEW row version:
-- Row version 1: (account_id='A', balance=5000, xmin=100, xmax=200)
--   xmax=200: T1 has marked this version as superseded
-- Row version 2: (account_id='A', balance=4500, xmin=200, xmax=0)
--   xmin=200: created by T1
--   xmax=0: current version

-- T2 (transaction ID 201) starts at same time as T1 (before T1 commits):
BEGIN;  -- T2 takes a snapshot: sees all committed tx up to ~199
       -- T1 (tx 200) is NOT in T2's snapshot (T1 hasn't committed yet)
SELECT balance FROM accounts WHERE account_id = 'A';
-- T2 evaluates visibility:
-- Row version 2: xmin=200. Is tx 200 committed in T2's snapshot? NO (still running)
--   → Version 2 is INVISIBLE to T2
-- Row version 1: xmin=100, xmax=200. Is tx 100 committed? YES. Is tx 200 committed? NO
--   → The deletion (xmax=200) hasn't committed yet → Version 1 is VISIBLE to T2
-- T2 reads: balance = ₹5000  ← the pre-update value! T2 sees a consistent snapshot.

-- T1 commits:
COMMIT;
-- T1's changes become permanent. Tx 200 is now committed.

-- T3 starts after T1 commits:
BEGIN;  -- T3 snapshot: sees tx 200 as committed
SELECT balance FROM accounts WHERE account_id = 'A';
-- Row version 1: xmax=200, tx 200 is committed → Version 1 is DELETED from T3's view
-- Row version 2: xmin=200, tx 200 is committed → Version 2 IS VISIBLE to T3
-- T3 reads: balance = ₹4500  ← sees T1's committed update

-- KEY OBSERVATION:
-- T2 and T1 ran concurrently. T2 never waited for T1.
-- T2 read a consistent snapshot of the database as of T2's start time.
-- No locks were acquired for T2's read. No blocking occurred.
-- This is why "readers never block writers, writers never block readers" in MVCC.

-- Concurrent UPDATEs on the same row: locking still required

-- T1 (tx 200):
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 'A';
-- T1 acquires X-lock on the row for account A (for write-write conflict prevention)
-- Creates new row version: (balance=4500, xmin=200, xmax=0)

-- T2 (tx 201): attempts to update the SAME row concurrently
BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE account_id = 'A';
-- T2 tries to acquire X-lock on account A's row
-- T1 holds that X-lock → T2 BLOCKS and waits

-- T1 commits:
COMMIT;
-- T1's X-lock released. T2's lock request granted.

-- T2 now executes its UPDATE:
-- T2 re-reads account A: sees T1's committed version (balance=4500)
-- T2 creates new version: (balance=4100, xmin=201, xmax=0)
-- The row previously written by T1 (xmax=0) gets xmax=201
COMMIT;
-- Final balance: ₹4100

-- SUMMARY OF MVCC BLOCKING BEHAVIOUR:
-- Read vs Read: NO BLOCKING (each reads their snapshot)
-- Read vs Write: NO BLOCKING (reader reads old version; writer creates new version)
-- Write vs Write (same row): BLOCKING (row-level X-lock required)
-- This is why MVCC dramatically improves read throughput in read-heavy workloads

-- Manual VACUUM: clean dead tuples from one table
VACUUM accounts;

-- VACUUM ANALYZE: clean + update statistics (run this regularly on busy tables)
VACUUM ANALYZE accounts;

-- VACUUM FULL: reclaim disk space by rewriting table (LOCKS table — use in maintenance)
VACUUM FULL accounts;

-- Check for table bloat — tables that need VACUUM:
SELECT
    schemaname,
    tablename,
    n_dead_tup                              AS dead_tuples,
    n_live_tup                              AS live_tuples,
    ROUND(n_dead_tup * 100.0
          / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
ORDER BY dead_pct DESC NULLS LAST;

-- AUTOVACUUM: PostgreSQL's background process that runs VACUUM automatically
-- Triggered when: dead tuples exceed 20% of live tuples (default threshold)
-- Check autovacuum settings:
SHOW autovacuum_vacuum_threshold;      -- minimum dead tuples before trigger (default: 50)
SHOW autovacuum_vacuum_scale_factor;   -- percentage of table size (default: 0.2 = 20%)

// LOCK TYPES with intention locks:
// S   = Shared lock on the table (reading the whole table)
// X   = Exclusive lock on the table (modifying the whole table)
// IS  = Intention-Shared: intend to acquire S-locks on rows
// IX  = Intention-Exclusive: intend to acquire X-locks on rows
// SIX = Shared + Intention-Exclusive: S on table, but will X-lock some rows

// COMPATIBILITY MATRIX (IS/IX/SIX):
//       IS    IX    S     SIX   X
// IS  [ ✓  ] [ ✓ ] [ ✓ ] [ ✓ ] [ ✗ ]
// IX  [ ✓  ] [ ✓ ] [ ✗ ] [ ✗ ] [ ✗ ]
// S   [ ✓  ] [ ✗ ] [ ✓ ] [ ✗ ] [ ✗ ]
// SIX [ ✓  ] [ ✗ ] [ ✗ ] [ ✗ ] [ ✗ ]
// X   [ ✗  ] [ ✗ ] [ ✗ ] [ ✗ ] [ ✗ ]

// TYPICAL SEQUENCE:
// Transaction wants to update row R in table T:
// 1. Acquire IX lock on table T (intention to X-lock rows within T)
// 2. Acquire X lock on row R

// If another transaction tries to LOCK TABLE T IN SHARE MODE (full table S-lock):
// It tries to acquire S on T. IX and S are incompatible → BLOCKED.
// This correctly prevents the full-table reader from running while rows are being modified.

// POSTGRESQL lock types you'll see in pg_locks:
-- RowShareLock    = IS lock (SELECT FOR SHARE)
-- RowExclusiveLock = IX lock (INSERT, UPDATE, DELETE, SELECT FOR UPDATE)
-- ShareLock        = S lock (CREATE INDEX without CONCURRENTLY)
-- ShareRowExclusiveLock = SIX
-- ExclusiveLock    = exclusive table lock
-- AccessExclusiveLock  = full exclusive (ALTER TABLE, DROP TABLE)

-- View current locks:
SELECT pid, locktype, relation::regclass, mode, granted
FROM pg_locks l
JOIN pg_class c ON l.relation = c.oid
ORDER BY pid, relation;

Approach 1 — No Concurrency Control (WRONG)

-- 500 concurrent transactions all execute this simultaneously:
BEGIN;
SELECT stock FROM inventory WHERE product_id = 'IPHONE15';
-- All 500 read: stock = 1 (available!)
-- All 500 decide: "I can purchase this"
UPDATE inventory SET stock = stock - 1 WHERE product_id = 'IPHONE15';
-- All 500 set stock = 1 - 1 = 0  (reading initial value 1, subtracting 1)
-- Actually: last writer wins → stock = 0 after all 500 commit
-- But: 500 orders created, 500 payments charged
-- RESULT: Flipkart owes 499 iPhones it doesn't have.

Approach 2 — Optimistic with Check-and-Set (PARTIAL FIX)

-- Use the stock value as an optimistic lock condition:
BEGIN;
SELECT stock FROM inventory WHERE product_id = 'IPHONE15';
-- Read stock = 1
UPDATE inventory
SET stock = stock - 1
WHERE product_id = 'IPHONE15' AND stock >= 1;  -- conditional update
-- If stock is still >= 1: update succeeds, affected_rows = 1
-- If stock already = 0: update fails, affected_rows = 0
-- Application checks affected_rows. If 0: ROLLBACK, "out of stock"

-- PROBLEM: Race condition still exists.
-- T1 reads stock=1, T2 reads stock=1 (both pass the check)
-- T1 executes UPDATE: stock = 0, affected_rows = 1, commits
-- T2 executes UPDATE: stock already 0, WHERE stock >= 1 fails, affected_rows = 0
-- T2 rolls back correctly.
-- RESULT: Only one sale completes ✓

-- But: this is a lost update pattern — works but relies on the WHERE clause
-- to catch the race. Not reliable for all scenarios.

Approach 3 — SELECT FOR UPDATE (CORRECT AND RECOMMENDED)

-- Serialise access to the inventory row using pessimistic locking:
BEGIN;

-- Acquire X-lock on the specific iPhone row BEFORE reading
SELECT stock FROM inventory
WHERE product_id = 'IPHONE15'
FOR UPDATE;
-- This is the crucial line.
-- The FIRST transaction to reach this acquires the X-lock.
-- All other 499 transactions BLOCK here, waiting for the lock.
-- No deadlock possible (only one row, all transactions want the same one — 
-- they queue up, not circular).

-- Only the lock holder continues:
IF stock >= 1:
    UPDATE inventory SET stock = stock - 1 WHERE product_id = 'IPHONE15';
    INSERT INTO orders (...) VALUES (...);
    COMMIT;  -- X-lock released. Next queued transaction gets the lock.
ELSE:
    -- stock = 0: someone else got the last one
    ROLLBACK;  -- return "out of stock" to this customer

-- RESULT:
-- 500 transactions queue on the FOR UPDATE lock
-- Transaction 1: reads stock=1, decrements to 0, commits
-- Transaction 2: reads stock=0, rolls back → "out of stock"
-- Transactions 3-500: reads stock=0, all roll back → "out of stock"
-- Exactly 1 iPhone sold. No overselling. Correct.

-- TRADE-OFF: 499 transactions are serialised through one lock.
-- At extreme scale, this creates a bottleneck.
-- For truly massive inventory, use:
-- 1. Distributed queue (Redis SETNX for atomic claim)
-- 2. Database-level atomic decrement with floor check:
UPDATE inventory
SET stock = GREATEST(stock - 1, 0),
    sold  = sold + CASE WHEN stock > 0 THEN 1 ELSE 0 END
WHERE product_id = 'IPHONE15'
RETURNING sold;  -- if sold increased: purchase succeeded; else: out of stock