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