SQL Server error 1222: Lock request time out period exceeded
A statement waited longer than the session's LOCK_TIMEOUT for a lock another session holds, so SQL Server cancelled the statement.
- Error number
1222- Severity
- 16
- Category
- Transactions and locking
What it means
By default SQL Server waits for a lock indefinitely (LOCK_TIMEOUT is -1). When a session sets SET LOCK_TIMEOUT to a number of milliseconds, any lock wait longer than that fails with 1222. Some tools, including SSMS's Object Explorer, set a lock timeout for their own queries, which is why 1222 often appears when browsing a database while a long transaction holds locks.
Only the statement is cancelled. The transaction stays open unless XACT_ABORT is on, so code that catches 1222 must decide whether to roll back.
Common causes
- Another session holds locks for a long time, typically an open transaction someone forgot to commit, or a long-running update or index rebuild.
- Schema modification locks held by
ALTER TABLEor other DDL in progress. - Object Explorer or a monitoring query with a short timeout running during heavy write activity.
How to fix it
-
Find who is blocking.
SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time, t.text FROM sys.dm_exec_requests AS r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t WHERE r.blocking_session_id <> 0;
-
Look for sessions with open transactions that are idle.
SELECT s.session_id, s.login_name, s.host_name, s.program_name, s.last_request_end_time FROM sys.dm_exec_sessions AS s WHERE s.open_transaction_count > 0 AND s.status = 'sleeping';
-
Commit or roll back the blocking transaction, or as a last resort end that session.
KILL 57; -- the blocking session_id
-
If your code sets
LOCK_TIMEOUT, handle 1222 by rolling back and retrying, and keep write transactions short.
Reproduce it safely
Uses the scratch table from the 1205 example; roll back session 1 afterwards.
-- Session 1: BEGIN TRAN; UPDATE dbo.DL SET v = v WHERE id = 1; -- Session 2: SET LOCK_TIMEOUT 1000; SELECT * FROM dbo.DL WHERE id = 1; -- Msg 1222 after 1 second
Related errors
1205Deadlocked and chosen as the victim5030Database couldn't be exclusively locked55P03PostgreSQL: lock not available1205MySQL: Lock wait timeout exceededORA-00054Oracle: resource busy and acquire with NOWAIT specifiedSQLITE_BUSYSQLite: database is locked
More transactions and locking errors
266Transaction count mismatch after EXECUTE3930Transaction cannot be committed3902COMMIT has no corresponding BEGIN3903ROLLBACK has no corresponding BEGIN3609Transaction ended in the trigger3960Snapshot isolation update conflict3616Error raised during trigger execution6401No transaction or savepoint found
The code and message text are from the Microsoft Database Engine events and errors reference. Browse the full SQL Server error list or search every database error.
Work on SQL Server in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQL Server and the other databases you use.