1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

-- In SQL, exclusive locks are acquired during write operations
-- or explicitly with FOR UPDATE clause
 
-- PostgreSQL: Row-level exclusive lock for update
SELECT * FROM accounts WHERE id = 1001 FOR UPDATE;
 
-- MySQL/InnoDB: Similar syntax
SELECT * FROM accounts WHERE id = 1001 FOR UPDATE;
 
-- Skip locked rows (useful for queue-like patterns)
SELECT * FROM accounts WHERE id = 1001 FOR UPDATE SKIP LOCKED;
 
-- Wait timeout for lock acquisition
SELECT * FROM accounts WHERE id = 1001 FOR UPDATE NOWAIT;
 
-- SQL Server: Explicit exclusive lock hint
SELECT * FROM accounts WITH (XLOCK, ROWLOCK) WHERE id = 1001;
 
-- Implicit exclusive locks during UPDATE/DELETE
UPDATE accounts SET balance = balance - 100 WHERE id = 1001;
-- ^^ This implicitly acquires X-lock on affected rows
 
DELETE FROM accounts WHERE id = 1001;
-- ^^ Also acquires X-lock before deletion

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83

// Exclusive Lock State Machine Implementation
enum LockMode {
    UNLOCKED = 'UNLOCKED',
    SHARED = 'SHARED',
    EXCLUSIVE = 'EXCLUSIVE'
}
 
interface LockRequest {
    txId: string;
    mode: LockMode;
    timestamp: number;
}
 
class ExclusiveLockManager {
    private lockStates: Map<string, {
        mode: LockMode;
        sharedHolders: Set<string>;
        exclusiveHolder: string | null;
        waitQueue: LockRequest[];
    }> = new Map();
    
    acquireExclusive(txId: string, dataItem: string): 'GRANTED' | 'BLOCKED' {
        const state = this.getOrCreateState(dataItem);
        
        // X-lock can only be granted if item is completely unlocked
        if (state.mode === LockMode.UNLOCKED) {
            state.mode = LockMode.EXCLUSIVE;
            state.exclusiveHolder = txId;
            return 'GRANTED';
        }
        
        // Any existing lock blocks X-lock
        // This includes: existing S-locks, existing X-lock
        state.waitQueue.push({
            txId,
            mode: LockMode.EXCLUSIVE,
            timestamp: Date.now()
        });
        return 'BLOCKED';
    }
    
    releaseExclusive(txId: string, dataItem: string): void {
        const state = this.lockStates.get(dataItem);
        
        if (!state || state.exclusiveHolder !== txId) {
            throw new Error(`Transaction ${txId} doesn't hold X-lock on ${dataItem}`);
        }
        
        state.mode = LockMode.UNLOCKED;
        state.exclusiveHolder = null;
        
        // Process wait queue - grant locks to waiting transactions
        this.grantWaitingLocks(dataItem);
    }
    
    private grantWaitingLocks(dataItem: string): void {
        const state = this.lockStates.get(dataItem)!;
        if (state.waitQueue.length === 0) return;
        
        // Priority: First waiting X-lock OR all waiting S-locks
        // (Policies vary - this is one common approach)
        const firstRequest = state.waitQueue[0];
        
        if (firstRequest.mode === LockMode.EXCLUSIVE) {
            // Grant X-lock to first waiting writer
            state.waitQueue.shift();
            state.mode = LockMode.EXCLUSIVE;
            state.exclusiveHolder = firstRequest.txId;
            this.wakeTransaction(firstRequest.txId);
        } else {
            // Grant S-locks to all waiting readers at front of queue
            while (state.waitQueue.length > 0 && 
                   state.waitQueue[0].mode === LockMode.SHARED) {
                const reader = state.waitQueue.shift()!;
                state.sharedHolders.add(reader.txId);
                this.wakeTransaction(reader.txId);
            }
            if (state.sharedHolders.size > 0) {
                state.mode = LockMode.SHARED;
            }
        }
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69

// Wait Queue Policies for Exclusive Locks
 
enum WaitPolicy {
    FIFO,           // First-come, first-served
    PRIORITY,       // Higher priority transactions go first
    WOUND_WAIT,     // Older transactions "wound" younger
    WAIT_DIE        // Older transactions wait, younger abort
}
 
class WaitQueueManager {
    private policy: WaitPolicy;
    
    constructor(policy: WaitPolicy = WaitPolicy.FIFO) {
        this.policy = policy;
    }
    
    // Determine if transaction should wait or be aborted
    shouldWait(
        requestingTx: Transaction,
        holdingTx: Transaction | null
    ): boolean {
        if (!holdingTx) return false; // No holder, no need to wait
        
        switch (this.policy) {
            case WaitPolicy.FIFO:
                return true; // Always wait
                
            case WaitPolicy.WAIT_DIE:
                // Older waits for younger; younger aborts
                if (requestingTx.startTime < holdingTx.startTime) {
                    return true; // Older tx waits for younger
                } else {
                    throw new AbortException("Younger tx dies");
                }
                
            case WaitPolicy.WOUND_WAIT:
                // Older "wounds" (aborts) younger; younger waits
                if (requestingTx.startTime < holdingTx.startTime) {
                    // Abort the holding transaction!
                    holdingTx.abort("Wounded by older transaction");
                    return false; // Will acquire lock
                } else {
                    return true; // Younger waits for older
                }
                
            case WaitPolicy.PRIORITY:
                // Based on transaction priority
                return requestingTx.priority <= holdingTx.priority;
        }
    }
    
    // Select next transaction from wait queue
    selectNext(waitQueue: Transaction[]): Transaction | null {
        if (waitQueue.length === 0) return null;
        
        switch (this.policy) {
            case WaitPolicy.FIFO:
                return waitQueue.shift()!;
                
            case WaitPolicy.PRIORITY:
                // Find highest priority
                waitQueue.sort((a, b) => b.priority - a.priority);
                return waitQueue.shift()!;
                
            default:
                return waitQueue.shift()!;
        }
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

-- Pattern 1: Bank Transfer (X-lock both accounts)
BEGIN TRANSACTION;
 
-- Lock accounts in consistent order (by ID) to prevent deadlock
SELECT * FROM accounts WHERE id IN (101, 102) 
  ORDER BY id FOR UPDATE;
 
-- Now safely modify both
UPDATE accounts SET balance = balance - 100 WHERE id = 101;
UPDATE accounts SET balance = balance + 100 WHERE id = 102;
 
COMMIT;
 
 
-- Pattern 2: Inventory Reservation
BEGIN TRANSACTION;
 
SELECT * FROM products WHERE sku = 'ABC123' FOR UPDATE;
 
-- Check availability (protected by X-lock)
DECLARE @available INT;
SELECT @available = quantity FROM products WHERE sku = 'ABC123';
 
IF @available >= @requested_quantity
BEGIN
    UPDATE products 
    SET quantity = quantity - @requested_quantity 
    WHERE sku = 'ABC123';
    
    INSERT INTO reservations (sku, quantity, customer_id) 
    VALUES ('ABC123', @requested_quantity, @customer_id);
END
 
COMMIT;
 
 
-- Pattern 3: Job Queue Processing (SKIP LOCKED)
BEGIN TRANSACTION;
 
-- Lock ONE unprocessed job, skip any already locked
SELECT * FROM job_queue 
WHERE status = 'pending' 
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
 
-- Process the job...
UPDATE job_queue SET status = 'processing' WHERE id = @job_id;
 
COMMIT;
 
-- SKIP LOCKED is powerful: multiple workers can process in parallel!