SQL SERVER – Alternate Fix : ERROR 1222 : Lock request time out period exceeded

ERROR 1222: Lock request time out period exceeded.

SQL SERVER - Alternate Fix : ERROR 1222 : Lock request time out period exceeded erroricon1-800x640 It says find the offending transaction and terminate it and run the query again. Though sometimes there is a requirement that we cannot terminate anything. If we know which transaction is locking up resources and database, we need to still run the same transaction.

  • Locate the transaction that is holding the lock on the required resource, if possible. Use sys.dm_os_waiting_tasks and sys.dm_tran_locksdynamic management views.
  • If the transaction is still holding the lock, terminate that transaction if appropriate.
  • Execute the query again.

Alternate Fix/WorkAround/Solution:
In this scenario following changes must be done in the offending transaction:
1) Modify the Transaction use query hints (use RECOMPILE,MAXDOPhints)
2) Run big Transaction in smaller transactions.
3) Upgrade Hardware if possible.
4) To prevent this, make sure every BEGIN TRANSACTION has COMMIT
5) If you are running this in SQL Server Management Studio, you can close the query windows and it will automatically close the transaction.

If this error occurs frequently change the lock time-out period or modify the offending transactions so that they hold the lock in less time.

Let me know if you have faced this problem in the past and how did you resolved it.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – ERROR Messages – sysmessages error severity level
Next Post
SQL SERVER – 2005 – DBCC ROWLOCK – Deprecated

Related Posts

20 Comments. Leave new

  • Hello

    Here is the script that can kill locks

    Create Table #Tmp
    (
    spid smallint,
    ecid smallint,
    status nchar(30),
    loginame nchar(128),
    hostname nchar(128),
    blk char(5),
    dbname nchar(128),
    cmd nchar(16)
    )

    Create Table #TmpLocks
    (
    spid smallint,
    dbid smallint,
    ObjId int,
    IndId smallint,
    Type nchar(4),
    Resource nchar(16),
    Mode nvarchar(8),
    Status nvarchar(28)
    )

    Insert Into #Tmp
    Exec sp_who

    Insert Into #TmpLocks
    Exec sp_lock

    If(Select Count(*) From #Tmp T
    Join #TmpLocks TL On T.spid = TL.spid
    Where /*This is for tempdb*/ dbid = 2 And objid In (1, 2, 3)) > 0

    Then you can kill the concerned spid with the command :

    Kill — The concerned spid

    Drop Table #Tmp
    Drop Table #TmpLocks

    Reply
    • abhIShek BandI
      August 9, 2012 8:03 pm

      @Spade

      What are those objid’s means
      1-
      2-
      3-

      select OBJECT_NAME(1),OBJECT_NAME(2),OBJECT_NAME(3)
      for objectid 3 am getting-sysrscols
      wat abt 1 and 2?

      Reply
  • Thank you very much.I have resolved the issue using ur script

    Reply
  • the command

    Kill — The concerned spid

    ?
    getting an error there… any idea plz?

    Reply
  • Thank you very much. Great Job!!

    Reply
  • Hi,

    I try to drop an index which is huge and I get the error message “Lock request time out period exceeded”

    I have no lock on the database returned by the script mentioned here.
    I have no transaction running on the database.

    Do you know what else could I check ?

    a+, =)
    -=Clement=-

    Configuration : SQL Server 2005

    Reply
  • Farhang Amary
    June 6, 2010 11:14 am

    thanks
    there wase usefull for me

    I am writing a programm, in that programm in somewhere i need to kill some process. i use that query

    Reply
  • Hi there,
    I faced similar problem today. Fortunately the session which caused table lock to occur was still open.
    I did ROLLBACK TRAN in the same session and issue got fixed.
    you can try above suggested troubleshooting steps if you unfortunately close the session.

    Cheers,
    Keep it simple :)

    Reply
  • thanks, helped me a lot!

    Reply
  • Hi

    I am getting same error when i opening the database properties.Can you please advise me.

    Reply
  • Thanks you saved my neck. Good job.

    Reply
  • This can also occur if you browse indexes in SSMS whilst indexes are being created (even if the index you are browsing is not currently being created). SQL attempts to acquire a KEY lock but is unable to do so as the system catalog/tables is/are locked by the script/job that is creating the indexes.

    Reply
  • kuldeep singh
    July 30, 2012 12:11 pm

    hi sir i like your Blog .it is very KnowledgeStormful.
    but sir iam geeting error on update record:

    Error 50013, severity 16, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.

    My procedure is:
    –payManualAttProc ‘13800’,’2012/07/28′,’PL’,’PL’,”,’INT01′
    CREATE proc [dbo].payManualAttProc
    @empcode varchar(5),
    @dt varchar(10),
    @statdis varchar(15),
    @stat1dis varchar(15),
    @user varchar(6),
    @comp varchar(5)

    As
    — exec payManualAttProc ‘10057’,’2010/02/02′,’PL’,’PL’,”,’INT01′
    declare @i int,@j int,@lcode varchar(15),@stat1 char(1),@stat2 char(2),@pin int,@maxno int,@lform varchar(15),@holi varchar(15)
    select @i = 0
    select @j = 0

    If @statdis = ‘Present’
    begin
    select @stat1 = ‘P’
    select @i = 0
    end
    Else If @statdis = ‘RL’
    begin
    select @stat1 = ‘P’
    select @lcode = ‘INT01RIL001’
    select @i = 1
    end
    Else If @statdis = ‘Absent’
    begin
    select @stat1 = ‘A’
    select @i = 0
    end
    Else If @statdis = ‘PL’
    begin
    select @stat1 = ‘E’
    select @lcode = ‘INT01EAR001’
    select @i= 1
    end
    Else If @statdis = ‘ML’
    begin
    select @stat1 = ‘F’
    select @lcode = ‘INT01MAT001’
    select @i= 1
    end
    Else If @statdis = ‘LWP’
    begin
    select @stat1 = ‘L’
    select @lcode = ‘INT01LWP001’
    select @i= 1
    end
    Else If @statdis = ‘CL’
    begin
    select @stat1 = ‘C’
    select @lcode = ‘INT01CAS001’
    select @i= 1
    end
    Else If @statdis = ‘SL’
    begin
    select @stat1 = ‘S’
    select @lcode = ‘INT01SHO001’
    select @i= 2
    end
    Else If @statdis = ‘Late’
    begin
    select @stat1 = ‘T’
    end
    Else If @statdis = ‘Out Duty’
    begin
    select @stat1 = ‘O’
    select @lcode = ‘INT01ODD001’
    select @i= 2
    end
    Else If @statdis = ‘Tour’
    begin
    select @stat1 = ‘R’
    select @lcode = ‘INT01TOU001’
    select @i= 1
    end
    Else If @statdis = ‘SICK’
    begin
    select @stat1 = ‘K’
    select @lcode = ‘INT01SIC001’
    select @i= 2
    end
    Else If @statdis = ‘1/2 CL’
    begin
    select @stat1 = ‘Y’
    select @lcode = ‘INT01SUN014’
    select @i= 2
    end
    Else If @statdis = ‘Compensation’
    begin
    select @stat1 = ‘M’
    select @lcode = ‘INT01COM001’
    select @i= 2
    end
    Else
    begin
    select @stat1 = ”
    select @lcode = ”
    select @i= 0
    End

    If @stat1dis = ‘Present’
    begin
    select @stat2 = ‘P’
    select @j = 0
    end
    Else If @stat1dis = ‘RL’
    begin
    select @stat2 = ‘P’
    select @lcode = ‘INT01RIL001’
    end
    Else If @stat1dis = ‘Absent’
    begin
    select @stat2 = ‘A’
    end
    Else If @stat1dis = ‘PL’
    begin
    select @stat2 = ‘E’
    select @lcode = ‘INT01EAR001’
    end
    Else If @stat1dis = ‘ML’
    begin
    select @stat2 = ‘F’
    select @lcode = ‘INT01MAT001’
    end
    Else If @stat1dis = ‘LWP’
    begin
    select @stat2 = ‘L’
    select @lcode = ‘INT01LWP001’
    end
    Else If @stat1dis = ‘CL’
    begin
    select @stat2 = ‘C’
    select @lcode = ‘INT01CAS001’
    end
    Else If @stat1dis = ‘SL’
    begin
    select @stat2 = ‘S’
    select @lcode = ‘INT01SHO001’
    select @j = 3
    end
    Else If @stat1dis = ‘Late’
    begin
    select @stat2 = ‘T’
    end
    Else If @stat1dis = ‘Out Duty’
    begin
    select @stat2 = ‘O’
    select @lcode = ‘INT01ODD001’
    select @j = 3
    end
    Else If @stat1dis = ‘Tour’
    begin
    select @stat2 = ‘R’
    select @lcode = ‘INT01TOU001’
    end
    Else If @stat1dis = ‘SICK’
    begin
    select @stat2 = ‘K’
    select @lcode = ‘INT01SIC001’
    select @j = 3
    end
    Else If @stat1dis = ‘1/2 CL’
    begin
    select @stat2 = ‘Y’
    select @lcode = ‘INT01SUN014’
    select @j = 3
    end
    Else If @stat1dis = ‘Compensation’
    begin
    select @stat2 = ‘M’
    select @lcode = ‘INT01COM001’
    select @j = 3
    end
    Else
    begin
    select @stat2 = ”
    select @lcode = ”
    End

    If datename(dw,@dt) = ‘Sunday’
    select @holi = ‘INT01SUN001’
    else
    select @holi = ”

    If @stat1 = ‘P’ And @stat2 = ‘P’ And @stat1dis = ‘Present’
    select @lcode = ”

    If @i = 1 Or @i + @j = 5
    select @pin = 1
    Else
    select @pin = @i + @j

    if exists (select * from pay_leave_form_tmp where emp_code=@empcode and convert(varchar,date_from,111)=@dt and l_city=’P’)
    BEGIN

    raiserror (‘Leave already pending for approval this date. Updates for this date aborted’,10,1)

    END

    If @pin > 0
    begin
    select @maxNo=max(convert(int , right(leave_form_code,6))) from pay_leave_form where left(leave_form_code,5) = @comp
    select @lform = @comp + replicate(‘0′,6-len(@maxno))+ ltrim(str(@maxno+1))

    –select * from master.dbo.sysmessages where error > 50000

    —select * from pay_leave_form where emp_code=’10057′
    insert into pay_leave_form(emp_code,leave_form_code,leave_code,date_from,date_to,no_of_days,l_address1,l_address2,l_address3,
    l_city,l_pin,created_by,created_on,last_touch_by,last_touch_on,holiday)
    values(@empcode,@lform,@lcode,@dt,@dt,1,”,”,’Entry by Personal’,”,@pin,@user,getdate(),@user,getdate(),0)

    End
    — select * from pay_attendence where emp_code = @empcode and month(date) =’ & Mid(mskdate.Text, 4, 2) & ‘ and year(date) = ‘ & Right(mskdate.Text, 4), con, adOpenForwardOnly, adLockReadOnly

    If @stat1 ” Or @stat2 ”
    begin
    if exists (select * from pay_attendence where emp_code=@empcode and convert(varchar,date,111)=@dt )
    begin
    update pay_attendence set status = @stat1, status1 =@stat2, leave_code =@lcode ,holiday_code =@holi,
    last_touch_by = @user, last_touch_on = getdate() where emp_code = @empcode and convert(varchar,date,111) =@dt
    end
    else
    begin
    insert into pay_attendence ( emp_code , date ,incomingtime, outgoingtime ,status , status1 ,leave_code,holiday_code,created_by,
    created_on ,last_touch_by, last_touch_on)
    values (@empcode,@dt,@dt,@dt,@stat1 ,@stat2,”,@holi,@user,getdate(),@user,getdate())
    End
    end

    Reply
  • Pinal Dave, I get this error when trying to expand the Temporary Tables in TempDB using SSMS. I was researching the reason why Who_is was showing I had a process blocked by TempDB (the only process I was running was Who_IS). We have been having timeout errors recently from applications which call stored procs, but this error number was not listed on those errors.
    Any ideas?

    Reply
  • This query helps u definetly.

    Just kill the process that locked the database object by using

    –Kill @SpId

    select
    distinct object_name(a.rsc_objid), a.req_spid, b.loginame
    from master.dbo.syslockinfo a (nolock) join
    master.dbo.sysprocesses b (nolock) on a.req_spid=b.spid
    where object_name(a.rsc_objid) is not null
    ORDER BY object_name(a.rsc_objid)

    EX:- Kill (req_spid)

    Reply
  • Mehboob Ali Khan
    March 28, 2013 4:21 pm

    Restart the service solve my problem.

    Reply
  • Rama Chandra.. Very good script.. it helped get the process holding locks.. THank you very muc..You are an expert !

    Reply
  • Pinal, We get this error every day like 3 o 4 times at day, I can kill the process locks by I have do it every tine and uses get fustraded

    Reply

Leave a Reply

Menu