MSDN Documentation – SQL Server

Overview

SQL Server uses a lock manager to guarantee transaction isolation and consistency. A lock is an internal synchronization object placed on a resource (row, page, table, or database) to prevent other transactions from accessing the resource in a conflicting way.

Lock Types

Blocking Behavior

When a transaction requests a lock that conflicts with an existing lock, it is placed in a wait state. The duration of the wait is determined by lock timeout settings and the deadlock_priority of the sessions.

• Short‑lived blocking is normal for high‑concurrency workloads.
• Long‑running transactions can cause cascade blocking.
• Deadlocks are resolved by automatically terminating one of the sessions.

Diagnosing Blocking

SQL Server provides several DMVs and tools to investigate blocking chains:

-- Current blocking sessions
SELECT
    blocking_session_id,
    session_id,
    wait_type,
    wait_time / 1000.0 AS WaitSeconds,
    DB_NAME(database_id) AS DBName,
    request_status,
    TEXT AS SqlText
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
WHERE blocking_session_id <> 0;

-- Blocked process report (requires trace flag 1222)
SELECT
    CONVERT(xml, event_data).value('(event/@name)[1]', 'varchar(50)')        AS EventName,
    CONVERT(xml, event_data).value('(event/data[@name="duration"]/value)[1]', 'bigint')/1000 AS DurationMs,
    CONVERT(xml, event_data).value('(event/data[@name="database_name"]/value)[1]', 'nvarchar(128)') AS DatabaseName,
    CONVERT(xml, event_data).value('(event/data[@name="object_name"]/value)[1]', 'nvarchar(128)') AS ObjectName,
    CONVERT(xml, event_data).value('(event/data[@name="transactionname"]/value)[1]', 'nvarchar(128)') AS TransactionName,
    CONVERT(xml, event_data).value('(event/data[@name="deadlock_graph"]/value)[1]', 'xml') AS DeadlockGraph
FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
WHERE CONVERT(xml, event_data).value('(event/@name)[1]', 'varchar(50)') = 'xml_deadlock_report';

Best Practices

1. Keep transactions short. Reduce the time locks are held.
2. Use appropriate isolation levels. Consider READ COMMITTED SNAPSHOT to reduce shared locks.
3. Index wisely. Proper indexes avoid key‑range scans that lead to extensive locking.
4. Avoid user‑defined cursors. They hold locks row‑by‑row.
5. Monitor wait stats. High PAGEIOLATCH_ and LCK_M_ wait types indicate locking pressure.

Sample Scripts

Enable snapshot isolation for a database:

ALTER DATABASE AdventureWorks2019
SET ALLOW_SNAPSHOT_ISOLATION ON;

Set a lock timeout of 5 seconds for the current session:

SET LOCK_TIMEOUT 5000;

Detect blocking chains automatically (SQL Agent job example):

CREATE PROCEDURE dbo.usp_MonitorBlocking
AS
BEGIN
    SET NOCOUNT ON;
    IF EXISTS (SELECT 1 FROM sys.dm_exec_requests WHERE blocking_session_id <> 0)
    BEGIN
        INSERT INTO dbo.BlockingLog(LoggedAt, Details)
        SELECT GETDATE(),
               (SELECT r.session_id AS BlockedSession,
                       r.blocking_session_id AS BlockingSession,
                       DB_NAME(r.database_id) AS DBName,
                       r.wait_type,
                       r.wait_time/1000.0 AS WaitSec,
                       txt.TEXT AS SqlText
                FROM sys.dm_exec_requests r
                CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) txt
                WHERE r.blocking_session_id <> 0
                FOR XML AUTO, TYPE);
    END
END;