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

1. 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;

2. 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.
3. 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.
4. 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.
5. 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.