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 five 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.

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.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 an open transaction 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 ten 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 killing before you do:

KILL <session_id> WITH STATUSONLY;   -- rollback progress, once it is running

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
• Isolation Levels and RCSI, the setting that removes a whole class of this