Lock Concepts

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

// Decision Support: Choosing Lock Granularity
 
interface WorkloadCharacteristics {
    transactionsPerSecond: number;
    avgRowsPerTransaction: number;
    concurrentTransactions: number;
    readToWriteRatio: number;  // e.g., 0.9 means 90% reads
    hotSpotFrequency: number;  // How often transactions touch same rows
}
 
function recommendGranularity(workload: WorkloadCharacteristics): string {
    // Calculate expected lock count
    const rowLocksPerSecond = 
        workload.transactionsPerSecond * 
        workload.avgRowsPerTransaction;
    
    // Calculate contention factor
    const contentionFactor = 
        workload.concurrentTransactions * 
        workload.hotSpotFrequency;
    
    // Decision logic
    if (workload.avgRowsPerTransaction > 10000) {
        return "TABLE_LEVEL - Bulk operation pattern detected";
    }
    
    if (rowLocksPerSecond > 100000 && workload.concurrentTransactions < 10) {
        return "TABLE_LEVEL - High volume, low concurrency; reduce overhead";
    }
    
    if (contentionFactor > 0.5 && workload.concurrentTransactions > 50) {
        return "ROW_LEVEL - High concurrency with contention; maximize parallelism";
    }
    
    if (workload.readToWriteRatio > 0.95) {
        return "ROW_LEVEL with MVCC - Read-heavy; leverage snapshots";
    }
    
    return "ROW_LEVEL - Default recommendation for balanced OLTP";
}
 
// Example usage:
const eCommerceWorkload: WorkloadCharacteristics = {
    transactionsPerSecond: 1000,
    avgRowsPerTransaction: 5,
    concurrentTransactions: 200,
    readToWriteRatio: 0.8,
    hotSpotFrequency: 0.1  // 10% of transactions touch same popular products
};
 
console.log(recommendGranularity(eCommerceWorkload));
// Output: "ROW_LEVEL - Default recommendation for balanced OLTP"

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

-- Multi-Granularity Locking Example
 
-- Transaction T1: Update single row in employees
BEGIN;
-- System automatically acquires:
-- 1. IX on database (if tracked)
-- 2. IX on employees table
-- 3. IX on page containing row
-- 4. X on the specific row
UPDATE employees SET salary = 75000 WHERE employee_id = 12345;
COMMIT;
-- All locks released
 
-- Transaction T2 (concurrent): Read different row
BEGIN;
-- System acquires:
-- 1. IS on employees table (compatible with T1's IX)
-- 2. IS on different page (or same page - IS compatible with IX)
-- 3. S on row 67890
SELECT * FROM employees WHERE employee_id = 67890;
-- SUCCEEDS - IS and IX are compatible at table level
COMMIT;
 
-- Transaction T3 (concurrent): Full table lock for maintenance
BEGIN;
-- System requests S on employees table
-- BLOCKED if T1 is still running (S conflicts with IX)
LOCK TABLE employees IN SHARE MODE;
-- Will proceed after T1 commits
COMMIT;

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

-- SQL Server: Lock Escalation Examples
 
-- View lock escalation events
SELECT 
    object_name(p.object_id) as TableName,
    escalation_cause,
    COUNT(*) as EscalationCount
FROM sys.dm_tran_locks l
JOIN sys.partitions p ON l.resource_associated_entity_id = p.hobt_id
GROUP BY object_name(p.object_id), escalation_cause;
 
 
-- Disable lock escalation on a table
ALTER TABLE orders SET (LOCK_ESCALATION = DISABLE);
-- Warning: May cause out-of-memory if many rows locked
 
-- Enable escalation only to table level (not partition)
ALTER TABLE orders SET (LOCK_ESCALATION = TABLE);
 
-- Enable escalation to partition level first (for partitioned tables)
ALTER TABLE orders SET (LOCK_ESCALATION = AUTO);
 
 
-- Trigger escalation intentionally for testing
UPDATE large_table SET status = 'processed';
-- If touching > 5000 rows, likely to escalate

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

-- Pattern 1: Batch processing to avoid escalation
DECLARE @batch_size INT = 5000;
DECLARE @rows_affected INT = 1;
 
WHILE @rows_affected > 0
BEGIN
    BEGIN TRANSACTION;
    
    UPDATE TOP (@batch_size) orders
    SET processed = 1
    WHERE processed = 0;
    
    SET @rows_affected = @@ROWCOUNT;
    
    COMMIT;
    -- Each batch: row-level locks, released at commit
    -- Never exceeds escalation threshold
END
 
 
-- Pattern 2: Explicit table lock for maintenance
BEGIN TRANSACTION;
 
-- Lock table - signals intent, prevents row escalation surprises
LOCK TABLE audit_log IN EXCLUSIVE MODE;
 
-- Perform bulk deletion
DELETE FROM audit_log WHERE log_date < DATEADD(year, -1, GETDATE());
 
-- Single table lock throughout; clear blocking behavior
COMMIT;
 
 
-- Pattern 3: SKIP LOCKED for parallel queue processing
-- Each worker gets different rows; no blocking
BEGIN TRANSACTION;
 
SELECT TOP 1 *
FROM work_queue WITH (ROWLOCK, XLOCK, READPAST)
WHERE status = 'pending'
ORDER BY created_at;
 
-- Process the item...
UPDATE work_queue SET status = 'complete' WHERE id = @selected_id;
 
COMMIT;