Locking, Blocking & Deadlocks

SQL Server dùng locking mechanism để kiểm soát truy cập đồng thời vào dữ liệu, đảm bảo tính toàn vẹn của transactions. Hiểu rõ cơ chế locking là yêu cầu bắt buộc để diagnose và giải quyết các vấn đề hiệu suất trong production.

1. Các loại Lock (Lock Types)

Shared Lock (S) — Đọc

• Lấy khi thực hiện SELECT
• Nhiều Shared lock có thể tồn tại đồng thời trên cùng resource
• Không compatible với Exclusive lock
• Trong READ COMMITTED: giải phóng sau khi đọc xong statement
• Trong REPEATABLE READ/SERIALIZABLE: giữ đến khi transaction kết thúc

Exclusive Lock (X) — Ghi

• Lấy khi thực hiện INSERT, UPDATE, DELETE
• Không compatible với bất kỳ lock nào khác
• Chỉ một X lock tại một thời điểm
• Giữ đến khi transaction kết thúc (COMMIT/ROLLBACK)

Update Lock (U) — Chuẩn bị Update

• Bước trung gian khi SQL Server scan để tìm rows cần UPDATE
• Compatible với S lock (nhiều readers có thể đọc trong khi U scan)
• Không compatible với U lock khác (ngăn chặn deadlock U→X)
• Sẽ convert sang X lock khi thực sự modify row

Intent Locks — Cấp thấp hơn thông báo ý định

Intent locks được đặt ở cấp Page/Table để thông báo lock intention ở cấp thấp hơn (Row), giúp SQL Server kiểm tra nhanh compatibility mà không cần scan từng row:

Schema Locks

Bulk Update Lock (BU)

Dùng trong BULK INSERT với TABLOCK hint — cho phép nhiều bulk insert đồng thời trên cùng table.

Key-Range Lock (SERIALIZABLE only)

Khóa khoảng giá trị trong index để ngăn Phantom Read:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT * FROM Orders WHERE Amount BETWEEN 1000 AND 5000;
-- Locks key range [1000, 5000] — ngăn INSERT vào range này

2. Lock Compatibility Matrix

Bảng tương thích giữa các loại lock (✅ = Compatible, ❌ = Conflict):

3. Lock Granularity & Lock Escalation

Hierarchy của lock granularity (từ nhỏ đến lớn)

Database → File → Filegroup → Table → Extent → Page → Row (RID/KEY)

SQL Server cố gắng lock ở level nhỏ nhất (row-level) để maximize concurrency.

Lock Escalation

Khi số lượng row/page locks trong một statement vượt ngưỡng (~5,000 locks), SQL Server escalate (nâng cấp) lên table lock để tiết kiệm memory lock manager.

5,000 row locks × (64 bytes/lock) = 320KB bộ nhớ → Escalate!

Vấn đề: Table lock block toàn bộ table → giảm concurrency.

-- Xem lock escalation đang xảy ra
SELECT 
    object_name(object_id) AS TableName,
    index_id,
    last_escalation_time,
    escalation_attempt_count,
    escalation_success_count
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL)
WHERE escalation_success_count > 0
ORDER BY escalation_success_count DESC;

-- Tắt lock escalation cho table cụ thể
ALTER TABLE dbo.HighTrafficTable SET (LOCK_ESCALATION = DISABLE);

-- Hoặc chỉ cho phép escalate lên partition level (partitioned tables)
ALTER TABLE dbo.PartitionedTable SET (LOCK_ESCALATION = AUTO);

-- Force row-level locks trong query
SELECT * FROM BigTable WITH (ROWLOCK) WHERE Id > 1000;

Monitor Locks hiện tại

-- Xem tất cả locks đang active
SELECT 
    resource_type,
    resource_subtype,
    resource_database_id,
    resource_description,
    resource_associated_entity_id,
    request_mode,
    request_type,
    request_status,
    request_session_id as session_id,
    resource_lock_partition
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID()
ORDER BY request_session_id, resource_type;

-- Locks của một session cụ thể
SELECT 
    tl.request_session_id,
    tl.resource_type,
    tl.resource_description,
    tl.request_mode,
    tl.request_status,
    OBJECT_NAME(p.object_id) AS table_name
FROM sys.dm_tran_locks tl
LEFT JOIN sys.partitions p ON tl.resource_associated_entity_id = p.hobt_id
WHERE tl.request_session_id = 55 -- thay session_id
AND tl.resource_database_id = DB_ID();

4. Blocking

Blocking xảy ra như thế nào

Session A (spid 52):
  BEGIN TRAN;
  UPDATE Orders SET Status = 'Processing' WHERE OrderId = 1;
  -- Giữ X lock trên OrderId = 1
  -- Chưa COMMIT...

Session B (spid 67):
  SELECT * FROM Orders WHERE OrderId = 1;
  -- Muốn S lock → CONFLICT với X lock của spid 52
  -- → BLOCKED! Phải chờ spid 52 COMMIT/ROLLBACK

Phát hiện Blocking

-- Cách 1: sp_who2 (đơn giản, nhanh)
EXEC sp_who2;
-- Cột BlkBy: session_id của blocker (0 = không bị block)

-- Cách 2: sys.dm_exec_requests (chi tiết hơn)
SELECT 
    r.session_id,
    r.blocking_session_id,
    r.status,
    r.wait_type,
    r.wait_time / 1000.0 AS wait_seconds,
    r.total_elapsed_time / 1000.0 AS elapsed_seconds,
    DB_NAME(r.database_id) AS database_name,
    SUBSTRING(st.text, (r.statement_start_offset/2)+1,
        ((CASE r.statement_end_offset 
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE r.statement_end_offset 
          END - r.statement_start_offset)/2)+1) AS current_statement,
    qp.query_plan
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) qp
WHERE r.blocking_session_id > 0;

-- Cách 3: Cây blocking (blocking chain)
WITH BlockingChain AS (
    SELECT 
        session_id,
        blocking_session_id,
        CAST(session_id AS VARCHAR(100)) AS chain,
        0 AS level
    FROM sys.dm_exec_requests
    WHERE blocking_session_id = 0 AND session_id IN (
        SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0
    )
    UNION ALL
    SELECT 
        r.session_id,
        r.blocking_session_id,
        bc.chain + ' → ' + CAST(r.session_id AS VARCHAR(10)),
        bc.level + 1
    FROM sys.dm_exec_requests r
    INNER JOIN BlockingChain bc ON r.blocking_session_id = bc.session_id
)
SELECT * FROM BlockingChain ORDER BY chain;

-- Cách 4: sys.dm_os_waiting_tasks
SELECT 
    wt.session_id,
    wt.blocking_session_id,
    wt.wait_type,
    wt.wait_duration_ms / 1000.0 AS wait_seconds,
    wt.resource_description
FROM sys.dm_os_waiting_tasks wt
WHERE wt.blocking_session_id IS NOT NULL;

LOCK_TIMEOUT — Hết kiên nhẫn chờ

-- Set timeout (milliseconds): fail sau 5 giây chờ
SET LOCK_TIMEOUT 5000;

BEGIN TRY
    SELECT * FROM Orders WHERE OrderId = 1; -- Blocked...
    -- Nếu chờ > 5000ms → Error 1222: Lock request time out period exceeded
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() = 1222
        PRINT 'Lock timeout - resource is blocked';
END CATCH;

-- Reset về vô hạn
SET LOCK_TIMEOUT -1;

-- Kiểm tra setting hiện tại
SELECT lock_timeout FROM sys.dm_exec_sessions WHERE session_id = @@SPID;

5. Deadlocks

Deadlock xảy ra như thế nào

Deadlock là tình trạng circular dependency — không ai chịu nhường ai:

Session A (spid 52):              Session B (spid 67):
BEGIN TRAN;                        BEGIN TRAN;
UPDATE TableA SET ...              UPDATE TableB SET ...
  → Giữ X lock trên TableA          → Giữ X lock trên TableB

UPDATE TableB SET ...              UPDATE TableA SET ...
  → Muốn X lock trên TableB          → Muốn X lock trên TableA
  → BLOCKED bởi spid 67             → BLOCKED bởi spid 52

                  ↕ DEADLOCK! ↕
SQL Server chọn một trong hai làm "deadlock victim" và ROLLBACK nó.

Minh hoạ vòng tròn:

spid 52 → chờ spid 67 (lock TableB)
spid 67 → chờ spid 52 (lock TableA)
→ Circular dependency → DEADLOCK

Deadlock Victim Selection

SQL Server chọn victim để ROLLBACK dựa trên:

1. DEADLOCK_PRIORITY: Session có priority thấp hơn bị chọn
2. Transaction cost: Session có ít log records hơn (rẻ hơn để rollback) bị chọn

-- Tự nguyện làm victim
SET DEADLOCK_PRIORITY LOW;    -- Range: -10 (LOWEST) đến 10 (HIGHEST), default NORMAL=0

-- Victim nhận lỗi:
-- Msg 1205, Level 13, State 51
-- Transaction (Process ID XX) was deadlocked on lock resources with another process
-- and has been chosen as the deadlock victim. Rerun the transaction.

Phát hiện Deadlock

Cách 1: Trace Flags (Legacy)

-- Bật globally (ảnh hưởng tất cả sessions)
DBCC TRACEON(1222, -1); -- Detailed deadlock info vào Error Log
DBCC TRACEON(1204, -1); -- Basic deadlock info

-- Xem Error Log để thấy deadlock
EXEC sp_readerrorlog 0, 1, 'deadlock';

-- Tắt
DBCC TRACEOFF(1222, -1);

Cách 2: Extended Events (Recommended)

-- Tạo XEvent session để capture deadlock graphs
CREATE EVENT SESSION [DeadlockMonitor] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report(
    ACTION(sqlserver.client_app_name, sqlserver.client_hostname,
           sqlserver.database_name, sqlserver.username)
)
ADD TARGET package0.ring_buffer(SET max_memory = 51200)
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS);

-- Bật session
ALTER EVENT SESSION [DeadlockMonitor] ON SERVER STATE = START;

-- Xem deadlock graphs (XML)
SELECT 
    xdr.value('@timestamp', 'datetime2') AS DeadlockTime,
    xdr.query('.') AS DeadlockGraph
FROM (
    SELECT CAST(target_data AS XML) AS target_data
    FROM sys.dm_xe_session_targets t
    INNER JOIN sys.dm_xe_sessions s ON t.event_session_address = s.address
    WHERE s.name = 'DeadlockMonitor'
    AND t.target_name = 'ring_buffer'
) AS data
CROSS APPLY target_data.nodes('//RingBufferTarget/event') AS XEventData(xdr)
ORDER BY DeadlockTime DESC;

-- Tắt và xóa session khi không cần
ALTER EVENT SESSION [DeadlockMonitor] ON SERVER STATE = STOP;
DROP EVENT SESSION [DeadlockMonitor] ON SERVER;

Cách 3: System Health Session (luôn bật sẵn)

-- SQL Server tự có session 'system_health' luôn capture deadlocks
SELECT 
    xdr.value('@timestamp', 'datetime2') AS DeadlockTime,
    xdr.query('.') AS DeadlockGraph
FROM (
    SELECT CAST(target_data AS XML) AS target_data
    FROM sys.dm_xe_session_targets t
    INNER JOIN sys.dm_xe_sessions s ON t.event_session_address = s.address
    WHERE s.name = 'system_health'
    AND t.target_name = 'ring_buffer'
) AS data
CROSS APPLY target_data.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xdr)
ORDER BY DeadlockTime DESC;

6. Chiến lược Ngăn chặn Deadlock

Chiến lược 1: Access objects theo cùng thứ tự

-- BAD: T1 lock A rồi B, T2 lock B rồi A → Deadlock tiềm năng
-- Transaction 1:
BEGIN TRAN; UPDATE Accounts ...; UPDATE Inventory ...;

-- Transaction 2:
BEGIN TRAN; UPDATE Inventory ...; UPDATE Accounts ...; -- ← Đảo ngược!

-- GOOD: Cả hai đều lock theo thứ tự A → B
-- Transaction 1:
BEGIN TRAN; UPDATE Accounts ...; UPDATE Inventory ...; COMMIT;

-- Transaction 2:
BEGIN TRAN; UPDATE Accounts ...; UPDATE Inventory ...; COMMIT;

Chiến lược 2: Giữ transaction ngắn

-- BAD: Xử lý nặng trong transaction
BEGIN TRANSACTION;
    SELECT @Data = -- complex calculation
    EXECUTE dbo.SlowProcedure @Data; -- 30 giây
    UPDATE Table1 SET ...;
COMMIT; -- Lock giữ 30+ giây

-- GOOD: Tính toán ngoài transaction
EXECUTE dbo.SlowProcedure @Data; -- Tính ngoài (30 giây, không lock)
BEGIN TRANSACTION;
    UPDATE Table1 SET ...;
COMMIT; -- Lock chỉ giữ mili giây

Chiến lược 3: Dùng UPDLOCK thay vì S → X conversion

-- BAD: Deadlock pattern phổ biến (Select then Update)
BEGIN TRAN;
    SELECT @Stock = Stock FROM Products WHERE ProductId = 1; -- S lock
    -- Thời điểm này: Session khác cũng lấy S lock!
    UPDATE Products SET Stock = @Stock - 1 WHERE ProductId = 1;
    -- Cả hai cùng muốn upgrade S → X → DEADLOCK!

-- GOOD: Lấy U lock từ đầu
BEGIN TRAN;
    SELECT @Stock = Stock 
    FROM Products WITH (UPDLOCK) -- U lock ngay từ đầu
    WHERE ProductId = 1;
    -- Session khác muốn UPDLOCK sẽ bị block (không deadlock!)
    UPDATE Products SET Stock = @Stock - 1 WHERE ProductId = 1;
COMMIT;

Chiến lược 4: Thêm index phù hợp

-- Thiếu index → Table/Index scan → Lock nhiều rows hơn cần thiết → Tăng deadlock risk
-- Với index tốt → Seek → Chỉ lock rows cần thiết

-- Kiểm tra missing indexes
SELECT 
    mid.statement AS missing_index_table,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.avg_user_impact AS estimated_improvement_pct,
    migs.user_seeks * migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) AS score
FROM sys.dm_db_missing_index_details mid
INNER JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
ORDER BY score DESC;

Chiến lược 5: Dùng RCSI/Snapshot để loại bỏ Read-Write Deadlocks

-- Bật RCSI: Readers không lấy S locks → Read-Write deadlock không thể xảy ra
ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON;
-- 70-80% deadlocks là Read-Write → RCSI loại bỏ phần lớn deadlocks

Chiến lược 6: Retry Logic trong Application

// C# với Polly
var policy = Policy
    .Handle<SqlException>(ex => ex.Number == 1205) // Deadlock victim
    .WaitAndRetry(
        retryCount: 3,
        sleepDurationProvider: attempt => 
            TimeSpan.FromMilliseconds(100 * Math.Pow(2, attempt)) // Exponential backoff
            + TimeSpan.FromMilliseconds(Random.Shared.Next(0, 100)) // Jitter
    );

await policy.ExecuteAsync(async () => {
    await ExecuteTransactionAsync();
});

7. Optimistic vs Pessimistic Concurrency

Pessimistic Concurrency (Locking-based)

Giả định: Conflicts xảy ra thường xuyên → Phải lock trước
Cơ chế: Lock tài nguyên trước khi đọc/ghi, giữ lock cho đến khi xong
Trade-off: Consistency cao, Concurrency thấp
Phù hợp: Môi trường high-contention, tài chính

-- Pessimistic: Lock ngay khi đọc
BEGIN TRAN;
    SELECT * FROM Products WITH (UPDLOCK, ROWLOCK) WHERE ProductId = 1;
    -- Bây giờ không ai có thể update ProductId = 1
    UPDATE Products SET Stock = Stock - 1 WHERE ProductId = 1;
COMMIT;

Optimistic Concurrency (Versioning-based)

Giả định: Conflicts hiếm khi xảy ra → Không cần lock
Cơ chế: Đọc không lock, khi ghi thì kiểm tra xem data có thay đổi không
Trade-off: Concurrency cao, phải retry khi có conflict
Phù hợp: Môi trường low-contention, web APIs

-- Optimistic với rowversion/timestamp column:
-- 1. Đọc với rowversion
SELECT ProductId, Stock, rowver FROM Products WHERE ProductId = 1;
-- → Nhận được: ProductId=1, Stock=100, rowver=0x0000000000000123

-- 2. Ghi với điều kiện rowver không đổi
UPDATE Products 
SET Stock = Stock - 1
WHERE ProductId = 1 AND rowver = 0x0000000000000123; -- Optimistic check!

IF @@ROWCOUNT = 0
    THROW 50001, 'Concurrent update detected - please retry', 1;
    -- Conflict! Row đã bị modify bởi người khác

8. Hints: NOLOCK, UPDLOCK, HOLDLOCK, ROWLOCK

NOLOCK (READUNCOMMITTED)

-- ⚠️ NGUY HIỂM: Dirty Read, skip rows, duplicate rows
SELECT COUNT(*) FROM Orders WITH (NOLOCK); -- Approximate count

-- KHÔNG BAO GIỜ dùng NOLOCK khi:
-- ✗ Tính toán tài chính (SUM, COUNT cho báo cáo chính xác)
-- ✗ Business logic dựa trên kết quả
-- ✗ Foreign key lookups
-- ✗ Kiểm tra tồn kho trước khi bán

UPDLOCK

-- Lấy U lock thay vì S lock — ngăn deadlock khi scan rồi update
SELECT ProductId, Stock 
FROM Products WITH (UPDLOCK)
WHERE Category = 'Electronics' AND Stock < 10;
-- Các rows này không thể bị concurrent update vì U lock

HOLDLOCK (= SERIALIZABLE)

-- Giữ S lock cho đến khi transaction kết thúc (như SERIALIZABLE)
SELECT * FROM Orders WITH (HOLDLOCK) WHERE CustomerId = 1;
-- Không ai có thể INSERT rows mới thỏa WHERE condition trong khi transaction đang chạy

-- Kết hợp
SELECT * FROM Orders WITH (UPDLOCK, HOLDLOCK) WHERE CustomerId = 1;
-- Vừa U lock, vừa giữ range lock

ROWLOCK

-- Force SQL Server dùng row-level lock (chống lock escalation)
UPDATE Orders WITH (ROWLOCK) 
SET Status = 'Processed' 
WHERE OrderDate < DATEADD(DAY, -1, GETDATE());

PAGLOCK, TABLOCK, TABLOCKX

-- PAGLOCK: Dùng page-level lock
-- TABLOCK: Lock toàn bộ table với S lock
-- TABLOCKX: Lock toàn bộ table với X lock (exclusive)

-- TABLOCK thường dùng với bulk operations
BULK INSERT dbo.StagingTable 
FROM 'C:\data\import.csv'
WITH (TABLOCK); -- Cho phép parallel bulk insert, minimal logging

9. sys.dm_tran_locks — Xem Locks hiện tại

-- Tất cả locks trong database hiện tại
SELECT
    tl.request_session_id AS SPID,
    tl.resource_type,
    tl.resource_subtype,
    CASE tl.resource_type
        WHEN 'OBJECT' THEN OBJECT_NAME(tl.resource_associated_entity_id)
        WHEN 'DATABASE' THEN DB_NAME(tl.resource_database_id)
        ELSE tl.resource_description
    END AS resource_name,
    tl.request_mode AS lock_mode,
    tl.request_status AS lock_status,
    tl.request_type,
    es.login_name,
    es.host_name,
    es.program_name
FROM sys.dm_tran_locks tl
JOIN sys.dm_exec_sessions es ON tl.request_session_id = es.session_id
WHERE tl.resource_database_id = DB_ID()
AND tl.resource_type <> 'DATABASE'
ORDER BY tl.request_session_id, tl.resource_type;

-- Tìm locks đang conflict (WAIT status)
SELECT
    blocked.request_session_id AS blocked_spid,
    blocker.request_session_id AS blocking_spid,
    blocked.resource_type,
    OBJECT_NAME(blocked.resource_associated_entity_id) AS object_name,
    blocked.request_mode AS blocked_lock_mode,
    blocker.request_mode AS blocking_lock_mode
FROM sys.dm_tran_locks blocked
JOIN sys.dm_tran_locks blocker 
    ON blocked.resource_associated_entity_id = blocker.resource_associated_entity_id
    AND blocked.resource_type = blocker.resource_type
WHERE blocked.request_status = 'WAIT'
AND blocker.request_status = 'GRANT';

10. Script tổng hợp: Blocking & Deadlock Diagnostic

-- =====================================================
-- Complete Blocking & Deadlock Diagnostic Script
-- =====================================================

-- 1. Tổng quan blocking ngay lập tức
PRINT '=== BLOCKING OVERVIEW ===';
SELECT
    r.session_id AS blocked_spid,
    r.blocking_session_id AS blocker_spid,
    r.wait_type,
    r.wait_time / 1000.0 AS wait_seconds,
    s.login_name AS blocked_login,
    s.host_name AS blocked_host,
    SUBSTRING(st.text, (r.statement_start_offset/2)+1, 200) AS blocked_query,
    s2.login_name AS blocker_login,
    s2.host_name AS blocker_host
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
JOIN sys.dm_exec_sessions s2 ON r.blocking_session_id = s2.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.blocking_session_id > 0;

-- 2. Open transactions (tất cả)
PRINT '=== OPEN TRANSACTIONS ===';
SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.open_transaction_count,
    DATEDIFF(SECOND, at.transaction_begin_time, GETDATE()) AS tran_seconds_open,
    at.name AS transaction_name,
    SUBSTRING(st.text, 1, 200) AS last_sql
FROM sys.dm_exec_sessions s
JOIN sys.dm_tran_session_transactions tst ON s.session_id = tst.session_id
JOIN sys.dm_tran_active_transactions at ON tst.transaction_id = at.transaction_id
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE s.open_transaction_count > 0
ORDER BY tran_seconds_open DESC;

-- 3. Lock summary
PRINT '=== LOCK SUMMARY BY TABLE ===';
SELECT
    OBJECT_NAME(p.object_id) AS table_name,
    tl.resource_type,
    tl.request_mode,
    COUNT(*) AS lock_count
FROM sys.dm_tran_locks tl
JOIN sys.partitions p ON tl.resource_associated_entity_id = p.hobt_id
WHERE tl.resource_database_id = DB_ID()
AND tl.resource_type IN ('KEY', 'PAGE', 'RID')
GROUP BY OBJECT_NAME(p.object_id), tl.resource_type, tl.request_mode
ORDER BY lock_count DESC;

Best Practices Tóm tắt