ERROR 1222: Lock request time out period exceeded.
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)
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
@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?
Thank you very much.I have resolved the issue using ur script
the command
Kill — The concerned spid
?
getting an error there… any idea plz?
Thank you very much. Great Job!!
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
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
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 :)
thanks, helped me a lot!
Hi , Rollback tran worked!!!
Hi
I am getting same error when i opening the database properties.Can you please advise me.
Thanks you saved my neck. Good job.
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.
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
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?
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)
Restart the service solve my problem.
Rama Chandra.. Very good script.. it helped get the process holding locks.. THank you very muc..You are an expert !
Killing repeatedly would not be a good idea.
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