1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

-- In SQL, shared locks are typically acquired implicitly during reads
-- The exact syntax varies by database system
 
-- PostgreSQL: Explicitly request a row-level shared lock
SELECT * FROM accounts WHERE id = 1001 FOR SHARE;
 
-- MySQL/InnoDB: Shared lock (read lock) on a row
SELECT * FROM accounts WHERE id = 1001 LOCK IN SHARE MODE;
-- or in newer MySQL versions:
SELECT * FROM accounts WHERE id = 1001 FOR SHARE;
 
-- SQL Server: Shared lock hints
SELECT * FROM accounts WITH (HOLDLOCK) WHERE id = 1001;
 
-- Oracle: Oracle uses MVCC primarily, but explicit locking:
SELECT * FROM accounts WHERE id = 1001 FOR UPDATE WAIT 5;
-- Note: Oracle's FOR UPDATE is actually exclusive, shared locking works differently

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

// Shared Lock State Machine Implementation
enum LockMode {
    UNLOCKED,
    SHARED,
    EXCLUSIVE
}
 
interface LockState {
    mode: LockMode;
    sharedHolders: Set<string>;  // Transaction IDs holding S-locks
    exclusiveHolder: string | null;
    waitQueue: LockRequest[];
}
 
class SharedLockManager {
    private lockState: Map<string, LockState> = new Map();
    
    acquireShared(txId: string, dataItem: string): boolean {
        const state = this.getOrCreateState(dataItem);
        
        switch (state.mode) {
            case LockMode.UNLOCKED:
                // First shared lock - grant immediately
                state.mode = LockMode.SHARED;
                state.sharedHolders.add(txId);
                return true;
                
            case LockMode.SHARED:
                // Already has shared locks - compatible, add this one
                state.sharedHolders.add(txId);
                return true;
                
            case LockMode.EXCLUSIVE:
                // Exclusive lock held - must wait
                state.waitQueue.push({
                    txId,
                    requestedMode: LockMode.SHARED
                });
                return false; // Transaction will be blocked
        }
    }
    
    releaseShared(txId: string, dataItem: string): void {
        const state = this.lockState.get(dataItem);
        if (!state || !state.sharedHolders.has(txId)) {
            throw new Error(`Transaction ${txId} doesn't hold S-lock on ${dataItem}`);
        }
        
        state.sharedHolders.delete(txId);
        
        // If no more shared holders, transition to unlocked
        if (state.sharedHolders.size === 0) {
            state.mode = LockMode.UNLOCKED;
            this.processWaitQueue(dataItem);
        }
    }
    
    private processWaitQueue(dataItem: string): void {
        const state = this.lockState.get(dataItem);
        if (!state || state.waitQueue.length === 0) return;
        
        // Grant waiting requests that are now compatible
        // (Details depend on fairness policy)
    }
}

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

// Efficient Shared Lock Implementation (Conceptual)
// Real databases use similar patterns
 
class ReadWriteLock {
private:
    std::atomic<int> readers{0};       // Count of shared lock holders
    std::atomic<bool> writer{false};   // Exclusive lock held?
    std::mutex mtx;                    // For complex state transitions
    std::condition_variable cv;        // For waiting
 
public:
    // Acquire shared lock (optimized for uncontended case)
    void lock_shared() {
        // Fast path: no writer, just increment reader count
        while (true) {
            // Optimistic increment
            readers.fetch_add(1, std::memory_order_acquire);
            
            // Check if writer snuck in
            if (!writer.load(std::memory_order_acquire)) {
                return; // Got the lock!
            }
            
            // Writer present - undo and wait
            readers.fetch_sub(1, std::memory_order_release);
            
            std::unique_lock<std::mutex> lock(mtx);
            cv.wait(lock, [this] { 
                return !writer.load(std::memory_order_acquire); 
            });
            // Loop and retry
        }
    }
    
    void unlock_shared() {
        if (readers.fetch_sub(1, std::memory_order_release) == 1) {
            // Was the last reader - wake up waiting writer
            cv.notify_one();
        }
    }
    
    // Shared lock count for debugging/monitoring
    int shared_count() const {
        return readers.load(std::memory_order_relaxed);
    }
};

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

-- Pattern 1: Informational Read (short S-lock is fine)
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1001;
-- S-lock acquired and released; balance shown to user
COMMIT;
 
-- Pattern 2: Consistent Report (needs long S-locks)
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT SUM(balance) as total FROM accounts;
SELECT COUNT(*) as num_accounts FROM accounts;
-- All S-locks held until commit for consistency
SELECT AVG(balance) as average FROM accounts;
COMMIT;
 
-- Pattern 3: Read-Modify-Write (don't use S-lock)
-- WRONG: Upgrade deadlock risk
BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE id = 1001 FOR SHARE; -- S-lock
-- If another transaction does the same, neither can upgrade!
UPDATE accounts SET balance = balance - 100 WHERE id = 1001;
COMMIT;
 
-- RIGHT: Use X-lock from the start
BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE id = 1001 FOR UPDATE; -- X-lock
UPDATE accounts SET balance = balance - 100 WHERE id = 1001;
COMMIT;