Waiting for Locks in SQL Server

by Granville Bonyata on July 26, 2012

Transact-SQL 2008 allows code to specify the number of milliseconds that a statement waits for a lock to be released. If the timeout is exceeded while a statement waits, the error message 1222 is raised. The default lock_timeout value is -1, which is equivalent to infinity in this context. The statement below sets that timeout limit to 1 second.

set lock_timeout 1000;

The way Transact-SQL handles lock_timeout value before and after procedure calls can be confusing. The lock_timeout value is not like a global variable. If a statement sets its value in one place, the value will not necessarily be the same from then on. Instead, the value is passed onto each called procedure. After returning from that called procedure, the value is reset to what it last was in the calling procedure. I have written a small script to demonstrate this. In the script, procedure prTestProcedure calls procedure prTestProcedure2. The results of the script are below.

Lock timeout at start of prTestProcedure is -1.
Lock timeout after prTestProcedure sets it is 1000.
Lock timeout at start of prTestProcedure2 is 1000.
Lock timeout after prTestProcedure2 sets it is 2000.
Lock timeout after returning to prTestProcedure from prTestProcedure2 is 1000.

And the code for this:

create procedure dbo.prTestProcedure2
as
begin
 select 'Lock timeout at start of prTestProcedure2 is ' + cast(@@lock_timeout as varchar(10)) + '.';
 set lock_timeout 2000;
 select 'Lock timeout after prTestProcedure2 sets it is ' + cast(@@lock_timeout as varchar(10)) + '.';
end;
go

create procedure dbo.prTestProcedure
as
begin
 select 'Lock timeout at start of prTestProcedure is ' + cast(@@lock_timeout as varchar(10)) + '.';
 set lock_timeout 1000;
 select 'Lock timeout after prTestProcedure sets it is ' + cast(@@lock_timeout as varchar(10)) + '.';
 exec dbo.prTestProcedure2;
 select 'Lock timeout after returning to prTestProcedure from prTestProcedure2 is ' + cast(@@lock_timeout as varchar(10)) + '.';
end;
go

exec dbo.prTestProcedure;
drop procedure dbo.prTestProcedure2;
drop procedure dbo.prTestProcedure;

Previous post:

Next post: