Giải pháp kiểm tra và xử lý tiến trình khóa bảng trong SQL Server

Khi hệ thống cơ sở dữ liệu SQL Server gặp hiện tượng truy vấn chậm hoặc treo, một nguyên nhân phổ biến là do các giao dịch khóa bảng (table lock) gây ra. Dưới đây là các câu lệnh SQL hữu ích để xác định, theo dõi và giải quyết tình trạng này.

1. Kiểm tra các tiến trình đang khóa bảng

Sử dụng view động sys.dm_tran_locks để xem danh sách các khóa đang được giữ trên đối tượng bảng:

SELECT 
    SPID = request_session_id,
    TableName = OBJECT_NAME(resource_associated_entity_id),
    resource_type,
    resource_description,
    request_mode,
    request_status
FROM sys.dm_tran_locks
WHERE resource_type = 'OBJECT';

2. Xác định tiến trình bị chặn và nguồn gốc chờ đợi

Câu lệnh sau giúp phát hiện các phiên làm việc bị chặn, cùng với thông tin về tiến trình gây ra tắc nghẽn:

SELECT 
    r.session_id,
    s.login_name,
    r.status,
    blocking_session_id,
    block.login_name AS blocker_login,
    block.open_transaction_count AS blocker_trans_count,
    block.status AS blocker_status,
    r.start_time,
    s.client_interface_name,
    s.host_name,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    r.transaction_id,
    r.command,
    DB_NAME(r.database_id) AS database_name,
    CONVERT(DECIMAL(10, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS estimated_hours_remaining,
    SUBSTRING(t.text, (r.statement_start_offset / 2) + 1, 
        CASE 
            WHEN r.statement_end_offset = -1 THEN LEN(t.text)
            ELSE (r.statement_end_offset - r.statement_start_offset) / 2 
        END
    ) AS current_statement
FROM sys.dm_exec_requests r
LEFT JOIN sys.dm_exec_sessions block ON r.blocking_session_id = block.session_id
LEFT JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.status IN ('suspended', 'running') OR r.blocking_session_id <> 0;

3. Kết thúc tiến trình khóa (KILL)

Nếu cần hủy một tiến trình cụ thể (ví dụ: SPID = 58), sử dụng lệnh sau:

KILL 58;

Lưu ý: Việc kết thúc tiến trình có thể ảnh hưởng đến giao dịch đang chạy, nên chỉ thực hiện khi chắc chắn không ảnh hưởng đến tính toàn vẹn dữ liệu.

4. Sử dụng hint WITH (NOLOCK) để tránh khóa

Trong các truy vấn đọc dữ liệu, nếu không cần độ chính xác tuyệt đối, có thể dùng hint NOLOCK để giảm thiểu khả năng bị khóa:

SELECT * FROM dbo.YourTable WITH (NOLOCK);

Tuy nhiên, hãy cẩn trọng vì NOLOCK có thể dẫn đến đọc dữ liệu chưa commit (dirty read).