SQL Server Error 1222 - Causes & Fixes

Error 1222 triggers when SQL Server's lock manager determines that a session has waited for a lock longer than the current LOCK_TIMEOUT setting (default is -1, meaning infinite wait). The lock manager maintains a lock compatibility matrix and tracks lock waits through the scheduler's wait list. When LOCK_TIMEOUT is set to a positive value, the scheduler will abort the waiting request after the specified milliseconds.

The lock manager uses a hash table to track locks on resources, with each resource identified by a lock resource identifier. When a session requests an incompatible lock, it enters a wait state and gets added to the lock wait list. The scheduler periodically checks wait times against LOCK_TIMEOUT values. Unlike deadlocks which involve circular blocking chains, Error 1222 represents a simple blocking scenario where the victim session gave up waiting.

SQL Server 2016 introduced the sys.dm_exec_session_wait_stats DMV which provides cumulative wait statistics per session. SQL Server 2017 enhanced lock monitoring with additional columns in sys.dm_tran_locks. SQL Server 2019 improved blocking detection through intelligent query processing features that can identify long-running blocking scenarios earlier.

The error surfaces differently in Always On availability groups where readable secondaries may experience lock timeouts due to snapshot isolation conflicts with the log apply process. Query Store captures these timeout events starting in SQL Server 2016, providing historical context for blocking patterns.

-- Identify current blocking chains and sessions
SELECT 
    r.session_id,
    r.blocking_session_id,
    DB_NAME(r.database_id) AS database_name,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    t.text AS sql_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

-- Find sessions with LOCK_TIMEOUT settings and their current locks
SELECT 
    s.session_id,
    s.lock_timeout,
    s.login_name,
    s.program_name,
    l.resource_type,
    l.resource_database_id,
    l.resource_associated_entity_id,
    l.request_mode,
    l.request_status
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_tran_locks l ON s.session_id = l.request_session_id
WHERE s.session_id > 50
ORDER BY s.session_id;

-- Examine lock waits and blocking duration
SELECT 
    wt.session_id,
    wt.wait_duration_ms,
    wt.wait_type,
    wt.resource_description,
    wt.blocking_session_id,
    er.command,
    er.percent_complete
FROM sys.dm_os_waiting_tasks wt
LEFT JOIN sys.dm_exec_requests er ON wt.session_id = er.session_id
WHERE wt.wait_type LIKE 'LCK_%'
ORDER BY wt.wait_duration_ms DESC;

-- Historical blocking events from Extended Events (requires session setup)
SELECT 
    event_data.value('(event/@timestamp)[1]', 'datetime2') AS event_time,
    event_data.value('(event/data[@name="database_id"]/value)[1]', 'int') AS database_id,
    event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') AS duration_ms,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS sql_text
FROM (
    SELECT CAST(target_data AS XML) AS target_data
    FROM sys.dm_xe_session_targets st
    JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
    WHERE s.name = 'blocking_monitoring'
) AS xml_data
CROSS APPLY target_data.nodes('//event') AS split_data(event_data)
WHERE event_data.exist('(event[@name="lock_timeout"])') = 1;

-- Check transaction log usage which can contribute to blocking
SELECT 
    DB_NAME(lsu.database_id) AS database_name,
    d.log_reuse_wait_desc,
    lsu.used_log_space_in_percent,
    lsu.used_log_space_in_bytes / 1024 / 1024 AS used_log_space_mb,
    lsu.total_log_size_in_bytes / 1024 / 1024 AS total_log_size_mb,
    lsu.log_space_in_bytes_since_last_backup / 1024 / 1024 AS log_space_since_backup_mb
FROM sys.dm_db_log_space_usage lsu
INNER JOIN sys.databases d ON lsu.database_id = d.database_id
WHERE lsu.used_log_space_in_percent > 70
ORDER BY lsu.used_log_space_in_percent DESC;

Terminate Blocking Session This script identifies and kills the root blocking session. Use only when the blocking session is confirmed to be hung or non-essential.

-- Find root blocker and terminate (USE WITH EXTREME CAUTION)
DECLARE @RootBlocker INT;

WITH BlockingChain AS (
    SELECT session_id, blocking_session_id, 0 AS level
    FROM sys.dm_exec_requests 
    WHERE blocking_session_id = 0 AND session_id IN 
        (SELECT DISTINCT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0)
    
    UNION ALL
    
    SELECT r.session_id, r.blocking_session_id, bc.level + 1
    FROM sys.dm_exec_requests r
    JOIN BlockingChain bc ON r.blocking_session_id = bc.session_id
)
SELECT @RootBlocker = session_id FROM BlockingChain WHERE level = 0;

-- Verify blocker details before killing
SELECT 
    session_id, login_name, program_name, host_name, 
    last_request_start_time, status
FROM sys.dm_exec_sessions 
WHERE session_id = @RootBlocker;

-- Uncomment to execute kill
-- EXEC('KILL ' + CAST(@RootBlocker AS VARCHAR(10)));

Reduce Application Lock Timeout Modify session-level LOCK_TIMEOUT to prevent long waits in the future.

-- Set lock timeout for current session (5 seconds)
SET LOCK_TIMEOUT 5000;

-- Verify setting
SELECT @@LOCK_TIMEOUT AS current_lock_timeout_ms;

-- For application-wide setting, modify connection string or stored procedures
-- Connection string: "...;Connection Timeout=30;Lock Timeout=5000;..."

Enable Snapshot Isolation Configure database for snapshot isolation to reduce blocking on read operations.

-- Enable snapshot isolation (requires exclusive access briefly)
-- TEST IN DEVELOPMENT FIRST
USE master;
ALTER DATABASE [YourDatabaseName] 
SET ALLOW_SNAPSHOT_ISOLATION ON;

ALTER DATABASE [YourDatabaseName] 
SET READ_COMMITTED_SNAPSHOT ON;

-- Verify settings
SELECT 
    name,
    snapshot_isolation_state_desc,
    is_read_committed_snapshot_on
FROM sys.databases 
WHERE name = 'YourDatabaseName';

Create Extended Events Session for Monitoring Establish ongoing monitoring for lock timeout events.

-- Create Extended Events session for lock timeout monitoring
CREATE EVENT SESSION [lock_timeout_monitoring] ON SERVER 
ADD EVENT sqlserver.lock_timeout(
    ACTION(sqlserver.client_app_name,sqlserver.database_name,
           sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
    WHERE duration > 1000000  -- 1 second in microseconds
)
ADD TARGET package0.event_file(
    SET filename=N'C:\temp\lock_timeouts.xel',
    max_file_size=100,
    max_rollover_files=5
)
WITH (STARTUP_STATE=ON);

-- Start the session
ALTER EVENT SESSION [lock_timeout_monitoring] ON SERVER STATE = START;

Configure LOCK_TIMEOUT at the application level rather than leaving it at the default infinite wait. Set values between 5-30 seconds based on application requirements. Implement retry logic in applications to handle lock timeout exceptions gracefully.

Enable READ_COMMITTED_SNAPSHOT isolation level to eliminate shared lock blocking for read operations. This reduces blocking scenarios by 60-80% in typical OLTP workloads. Monitor tempdb space usage after enabling as snapshot isolation increases tempdb consumption.

Optimize long-running queries that hold locks extensively. Use query execution plans to identify table scans and missing indexes causing extended lock duration. Implement proper indexing strategies to minimize lock escalation from row to table level.

Design transactions to be as short as possible. Move non-database operations outside transaction boundaries. Use explicit transactions with proper error handling rather than relying on implicit transactions which can hold locks longer than necessary.

Monitor blocking using Extended Events rather than SQL Server Profiler. Create automated alerts when blocking duration exceeds acceptable thresholds. Establish baseline metrics for lock wait statistics using sys.dm_os_wait_stats to identify trending issues.

Consider partitioning strategies for large tables experiencing frequent blocking. Partition elimination reduces lock contention by isolating operations to specific partitions. Implement proper maintenance windows for index rebuilds and statistics updates to avoid blocking during business hours.