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

ERROR 1222 : Lock request time out period exceeded.

MSDN Suggests solution here.

It says find offending transaction and terminate it and run the query again. Though sometime there is requirement that we can not terminate anything. If we know which transaction is locking up resources and database, we need to still run the same transaction.

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.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

17 thoughts on “SQL SERVER – Alternate Fix : ERROR 1222 : Lock request time out period exceeded

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

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

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

  4. 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 :)

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

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

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

  8. 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)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s