SQL Server error 1222 lock request time out period exceeded

As the error says error 1222 lock request time out period exceeded, it occurs when a query waits longer than the lock timeout setting. The lock timeout setting is the time in millisecond a query waits on a blocked resource and it returns error when the wait time exceeds the lock time out setting. The default value of LOCK TIMEOUT is -1.

Let’s now replicate the issue. The below query begins a transaction and executes and update command on Person.Person table however, it doesn’t completes the transaction; the transaction is in open state.

BEGIN TRAN
GO
UPDATE Person.Person SET Suffix='Mr' 
WHERE BusinessEntityID between 10 and 100

Open a second query window and run below query. The query executes a select statement on Person.Person table with LOCK TIMEOUT setting of 10 millisecond.

SET lock_timeout 10
GO
select * from 
 Person.Person  
where BusinessEntityID  between 10 and 100

sql server error 1222 lock request time out

The query fails with Lock request time out period exceeded error. The select query waits on update query for 10 ms and then terminates as the lock on Person.Person table is not released.

The short term or quick resolution for this issue is to commit/rollback open transaction and then fix the issue with the blocking/long running query.

 

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *