Fix SQL Server Error 1222 – Lock Request Timeout

Quick answer

Kill the blocking session. Run sp_who2 or query sys.dm_exec_requests to find the SPID holding the lock, then KILL <SPID>.

What's happening here

Error 1222 shows up when one query waits too long for a lock that another session holds. SQL Server's default lock timeout is infinite (-1), but if an application or session explicitly sets SET LOCK_TIMEOUT to a low value (like 5000ms), you'll get this error when that threshold is crossed. I see it most often in reporting apps that hit large tables while an overnight ETL job holds exclusive locks. The culprit is almost always a long-running transaction that doesn't commit fast enough.

This is not a deadlock. Deadlock error 1205 kills one session automatically. With 1222, the blocked query just times out and returns control to the app. The blocking transaction keeps running.

Step-by-step fix

Find the blocking chain. Run this in SSMS or your query tool:

SELECT session_id, blocking_session_id, wait_type, wait_time, command, text
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE blocking_session_id > 0;

Look for the root blocker. The blocking_session_id column shows who's blocking. Follow the chain until you hit a session where blocking_session_id = 0. That's your culprit.
Check what the blocker's doing. Run sp_who2 and look at the BlkBy column. Also check sys.dm_exec_requests for the blocker's command — if it says AWAITING_COMMAND, it's idle but holding locks. That usually means an uncommitted transaction.
Kill the blocker. Only do this if you're sure it's safe (or in dev/test). Command:
```
KILL <SPID>;
```
Replace <SPID> with the session ID. In production, contact the app owner first — killing can roll back data changes.
Verify the blocked query completes. Re-run your original query. If error 1222 is gone, the fix worked.

Alternative fixes if killing isn't an option

Increase lock timeout on the blocked session. If you can't kill the blocker (e.g., it's a critical transaction), set a higher timeout on the blocked query:
```
SET LOCK_TIMEOUT 30000; -- 30 seconds
-- your query here
```
This doesn't fix the root cause but buys time.
Use snapshot isolation. Enable READ_COMMITTED_SNAPSHOT at the database level. This lets read queries avoid writer locks. Run:
```
ALTER DATABASE YourDB SET READ_COMMITTED_SNAPSHOT ON;
```
Downside: tempdb load increases. Test first.
Optimize the blocker query. Index tuning or batch commit can shorten transaction duration. Check the blocker's query plan for table scans.

Prevention tip

Stop setting LOCK_TIMEOUT to low values in application code unless you really mean it. Design long-running ETL to use batch commits (e.g., commit every 1000 rows) so they don't hold locks for minutes. And always monitor blocking with a periodic query on sys.dm_exec_requests. Set up an alert if blocking lasts longer than 30 seconds — that catches 1222 before users complain.

One more thing: don't confuse this with a deadlock. Deadlock error 1205 kills one session automatically. With 1222, nothing gets killed — your query just times out. That makes it harder to detect. So watch those lock timeouts.