Lock Request Time Out Period Exceeded (Error 1222)

This is not a deadlock. It is the single most useful thing to know about it, because the two get treated as the same problem and they are not.

• A deadlock is two transactions waiting on each other. It can never resolve, so SQL Server kills one and you get Msg 1205.
• A lock timeout is one transaction waiting on another that is perfectly healthy and still working. Nothing is broken. Your session simply gave up first.

So the question is never “what caused the timeout”. It is what is holding the lock, and why for so long.

Something Set a Timeout

By default SQL Server waits forever. A timeout only happens because something asked for one:

SELECT @@LOCK_TIMEOUT AS lock_timeout_ms;   -- -1 means wait indefinitely

If this returns anything other than -1, something in the session set it:

SET LOCK_TIMEOUT 5000;   -- give up after 5 seconds

SSMS sets this itself in some dialogs, which is why people meet 1222 while clicking around Object Explorer rather than while running queries. Expanding a table list behind a long transaction produces exactly this.

Application frameworks and ORMs also set it, often globally in a connection setup routine that nobody has read in years. It is a per-connection setting, so a value you see in one session says nothing about the application’s.

Microsoft’s reference covers SET LOCK_TIMEOUT and MSSQLSERVER_1222 in full.

Find What Is Actually Holding the Lock

This is the part that matters, and it has to be run while the blocking is happening:

SELECT  r.session_id,
        r.blocking_session_id,
        r.wait_type,
        r.wait_time / 1000.0        AS wait_seconds,
        r.wait_resource,
        DB_NAME(r.database_id)      AS database_name,
        s.login_name,
        s.host_name,
        s.program_name,
        SUBSTRING(t.text, (r.statement_start_offset/2) + 1, 4000) AS running_statement
FROM    sys.dm_exec_requests r
JOIN    sys.dm_exec_sessions s ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE   r.blocking_session_id <> 0;

blocking_session_id is the head of the chain. Then look at what that session is doing:

SELECT  s.session_id,
        s.status,
        s.login_name,
        s.host_name,
        s.program_name,
        s.open_transaction_count,
        s.last_request_start_time,
        s.last_request_end_time,
        DATEDIFF(SECOND, s.last_request_start_time, GETDATE()) AS seconds_running
FROM    sys.dm_exec_sessions s
WHERE   s.session_id = <blocking_session_id>;

A status of sleeping with open_transaction_count above 0 is the classic finding. The application began a transaction, did its work, hit an error path that never committed or rolled back, and the connection is now sitting idle holding locks. Nobody is running anything. The lock will be held until that connection is closed.

The Fixes, in the Order Worth Trying

Deal with the blocker, not the timeout. Raising or removing the timeout just converts a fast failure into a slow one, and a query that hangs forever is harder to diagnose than one that errors.

• An idle transaction is an application bug. Find the code path that opens a transaction and can exit without committing. Killing the session clears it today and it returns tomorrow.
• A genuinely long-running transaction is a design question. Batch large updates rather than doing 10 million rows in one statement.
• Reads blocking reads should not be happening at all. If they are, RCSI removes the whole class of problem, because readers stop taking shared locks.
• A missing index makes a small update lock far more rows than it needs to, because a scan takes locks on everything it touches.

The Emergency Version

If something is blocking production right now and you need it gone:

KILL <session_id>;

The rollback also takes time, sometimes longer than the original work, and the locks are held throughout it. Check what you are about to kill with the session query above, before you run this: open_transaction_count and the statement it last ran tell you how much work is about to be undone.

Once the kill is in and the rollback is under way, this reports its progress:

KILL <session_id> WITH STATUSONLY;   -- only valid while a rollback is actually running

It is not a preview. Run it against a session that is not rolling back and you get Msg 6120, Status report cannot be obtained. Rollback operation for Process ID <session_id> is not in progress. Measured on SQL Server 2025 CU8.

Stopping It Recurring

• Alert on transactions open longer than a couple of minutes, not on the timeout error. The timeout is the symptom arriving too late.
• Do not set a global lock timeout to make errors go away. It hides blocking rather than fixing it, and turns a diagnosable wait into a scattering of unrelated failures.
• Consider RCSI if reads are being blocked by writes. It is the single biggest reduction in blocking available on most systems.

Common Questions

Related Scripts

• Get Lock Contention and Blocking Plans, what is fighting what, with the plans behind it
• Get Blocking Chains, the live head-of-chain view
• Reading and Fixing a Deadlock, the other error people arrive here looking for
• Isolation Levels and RCSI, the setting that removes a whole class of this
• Get Open Transactions, the sleeping session holding a transaction open, which is the usual blocker behind a 1222