Lock request time out period exceeded. (SQL Server error 1222)

What it means

Another session holds a lock your statement needs (a row it changed, a table it altered), and your statement waited as long as SET LOCK_TIMEOUT allows, then gave up with error 1222.

By default SQL Server has no lock timeout: @@LOCK_TIMEOUT is -1 and a blocked statement waits forever, or until the client’s own command timeout cancels it (30 seconds in many .NET apps, which shows up as a client timeout, not 1222). So if you see 1222, something set a limit: your code, a tool, or a library. SET LOCK_TIMEOUT 0 means don’t wait at all.

What 1222 does and doesn’t undo:

• Only the statement that waited is cancelled. The batch carries on with the next statement.
• Your transaction stays open, with everything it did before. If your code doesn’t notice the error, it can go on and commit a transaction that’s missing a step.
• With SET XACT_ABORT ON, the error rolls back the whole transaction instead.

Common causes

1. A transaction left open somewhere: a session that ran BEGIN TRANSACTION (or turned off autocommit) and never committed, often an idle query window or an app that forgot to commit.
2. A long-running write, such as a big UPDATE, DELETE or index rebuild, holding locks on rows or the whole table.
3. Schema changes. ALTER TABLE and some index operations need a schema-modification lock that waits for every reader, and blocks everyone queued behind it.
4. Readers blocked by writers under the default READ COMMITTED level, which takes shared locks and waits for uncommitted changes.
5. A short timeout set by a tool or framework, so ordinary waits fail.

How to fix it

Find the blocker

While it’s happening, from another session:

SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time, r.wait_resource, 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;

blocking_session_id is the session holding the lock. If that session isn’t running anything, it won’t be in sys.dm_exec_requests; look it up in sys.dm_exec_sessions instead:

SELECT s.session_id, s.login_name, s.host_name, s.program_name, s.status,
       s.open_transaction_count, s.last_request_end_time
FROM sys.dm_exec_sessions AS s
WHERE s.session_id = <blocking session id>;

sleeping with open_transaction_count above 0 is the classic forgotten transaction. EXEC sp_who2 gives a quicker overview: its BlkBy column shows the blocker’s session ID.

Without the VIEW SERVER STATE permission (or VIEW SERVER PERFORMANCE STATE on 2022 and later), both only show your own session, so ask someone who has it.

End the blocking transaction

Best is to have its owner commit or roll back. If you can’t, end the session; its open transaction is rolled back, which can take as long as the work it undoes:

KILL <session id>;

KILL needs ALTER ANY CONNECTION. Check what the session is first: killing a session in the middle of a big batch job throws away its work.

Handle 1222 in your code

If you set a lock timeout, treat 1222 as a failed step and roll back, or turn on XACT_ABORT so the server does it for you:

SET XACT_ABORT ON;
SET LOCK_TIMEOUT 5000;   -- milliseconds
BEGIN TRY
  BEGIN TRANSACTION;
  UPDATE accounts SET balance = balance + 1 WHERE id = 2;
  UPDATE accounts SET balance = balance + 1 WHERE id = 1;
  COMMIT;
END TRY
BEGIN CATCH
  IF XACT_STATE() <> 0 ROLLBACK;
  THROW;
END CATCH;

Then retry the whole transaction later, or report that the data is busy.

Don’t make readers wait

For reads that can use the last committed data, row versioning avoids the wait entirely. If the database allows snapshot isolation (ALLOW_SNAPSHOT_ISOLATION ON), a session can ask for it:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
SELECT id, balance FROM accounts WHERE id = 1;

Or turn on READ_COMMITTED_SNAPSHOT for the database, so every READ COMMITTED read does this without code changes (it needs a moment with no other sessions, and testing; see deadlock victim). The READPAST table hint skips locked rows instead, which suits queue tables. NOLOCK also stops the wait, but it reads uncommitted changes that may be rolled back, so avoid it for anything that matters.

Run schema changes and big writes carefully

Run ALTER TABLE and index rebuilds when traffic is low, with a lock timeout on that session so it gives up instead of queueing everyone behind it, and split large UPDATE and DELETE statements into batches of a few thousand rows, committing between them.

Reproduce it

On SQL Server 2022 (RTM-CU27, 16.0.4295.3), two sqlcmd sessions a second apart on a table seo_sqlerr_conn.accounts:

-- Session 1 (spid 58): holds a row lock for 10 seconds
BEGIN TRAN;
UPDATE seo_sqlerr_conn.accounts SET balance = balance - 10 WHERE id = 1;
WAITFOR DELAY '00:00:10';
ROLLBACK;

-- Session 2
SELECT @@LOCK_TIMEOUT AS default_lock_timeout;
SET LOCK_TIMEOUT 2000;
BEGIN TRAN;
UPDATE seo_sqlerr_conn.accounts SET balance = balance + 1 WHERE id = 2;
UPDATE seo_sqlerr_conn.accounts SET balance = balance + 1 WHERE id = 1;
SELECT @@TRANCOUNT AS trancount, XACT_STATE() AS xact_state;
SELECT id, balance FROM seo_sqlerr_conn.accounts WITH (READPAST);
ROLLBACK;

Session 2 printed:

default_lock_timeout
--------------------
-1
Msg 1222, Level 16, State 51, Server 7732422b7f56, Line 6
Lock request time out period exceeded.
The statement has been terminated.
trancount xact_state
--------- ----------
1 1
id balance
-- -------
2 101.00

The second update was cancelled after two seconds, but the transaction was still open and committable, with the first update in it (101.00): a COMMIT there would have saved half the transfer. READPAST skipped row 1, which session 1 had locked.

In an earlier run, while a SELECT of row 1 (session 62, with a 5-second timeout) was waiting on session 58, the query above from a third session showed the blocker:

session_id blocking_session_id wait_type wait_time wait_resource text
---------- ------------------- --------- --------- ------------- ----
62 58 LCK_M_S 2008 KEY: 5:72057594049331200 (8194443284a0) (@1 tinyint)SELECT [id],[balance] FROM [seo_sqlerr_conn].[accounts] WHERE [id]=@1

and sp_who2 listed session 62 as SUSPENDED with 58 in BlkBy. Run as a login without VIEW SERVER STATE, sys.dm_exec_requests and sp_who2 returned only that login’s own session. The same SELECT with SET TRANSACTION ISOLATION LEVEL SNAPSHOT (the test database allows snapshot isolation) returned at once, with the last committed balance, 100.00.

SQL Server 2019 (15.0.4490.9) and 2025 (17.0.5005.3) gave the same error, level and state.

In Inlet

Inlet’s Activity view finds the blocker while your statement waits: Sessions shows who blocks whom and can end a session (KILL, which needs ALTER ANY CONNECTION), and Locks lists each session’s locks per table and index, waits first, naming the session each wait is for. Activity reads the server’s DMVs, so it needs VIEW SERVER STATE (VIEW SERVER PERFORMANCE STATE from SQL Server 2022, VIEW DATABASE STATE on Azure SQL Database). On connections tagged production, Inlet opens read-only and refuses writes before they’re sent, so a browsing session there doesn’t leave changes, and their locks, open. When a statement fails with 1222, the error links to this page.