Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

For what period does the LOCK_TIMEOUT LAST?

I do a "SET LOCK_TIMEOUT 10" and a "SELECT @@LOCK_TIMEOUT" in one command after a login and get 10 returned. Immediately after this I do a "SELECT @@LOCK_TIMEOUT" again and it returns -1. I would have thought it still be 10.

I did some looking on the MSDN site and could not find if the LOCK_TIMEOUT is session based or statement based.

share|improve this question
    
What I left out was doing a "SELECT @@spid" it returns 52 after both commands - indicating I am on the same connection - doesn't it? –  David Gray Wright Jan 25 '12 at 3:10

2 Answers 2

up vote 2 down vote accepted

Does your application submit its queries via sp_executesql? (You can check this in Profiler if not sure)

EXEC sp_executesql N'SET LOCK_TIMEOUT 10; 
                     SELECT @@LOCK_TIMEOUT';

SELECT @@LOCK_TIMEOUT;

Returns

-----------
10

-----------
-1

for me.

Edit And I also see the same kind of behaviour with exec sp_prepexec

declare @p1 int

exec sp_prepexec @p1 output,
                 NULL,
                 N'SET LOCK_TIMEOUT 10; SELECT @@LOCK_TIMEOUT AS LOCK_TIMEOUT_INSIDE' 

select @p1 AS handle, @@LOCK_TIMEOUT AS LOCK_TIMEOUT_OUTSIDE
share|improve this answer
    
From looking at the profiler - it does the following. declare 'at'p1 int set 'at'p1=2 exec sp_prepexec 'at'p1 output,NULL,N'SET LOCK_TIMEOUT 10; SELECT @@LOCK_TIMEOUT' select 'at'p1 –  David Gray Wright Jan 26 '12 at 20:16
    
@DavidGrayWright - Looks like the same issue. Guess you'll need to see if you can get the application to stop using that for the call that sets the LOCK_TIMEOUT or explain a bit more about your situation to see if anyone can come up with another way of achieving the same result. –  Martin Smith Jan 27 '12 at 11:30
    
What exactly is the issue? Is it that I can't call this stored procedure to make the change to LOCK_TIMEOUT? If so, why not? Why does the set NOT impact outside the stored proc? –  David Gray Wright Jan 29 '12 at 22:27
    
@DavidGrayWright - This runs as a child batch and when the child batch ends and the TSQL call stack unwinds the SET option gets restored to its previous value. See the value of the SET option is restored after control is returned from the batch specified in the dynamic SQL string. –  Martin Smith Jan 29 '12 at 23:26
    
I have the LOCK_TIMEOUT surviving past it's initial set point. I now use SQLExecDirect and bypass the stored procedure. Thanks. –  David Gray Wright Jan 30 '12 at 5:17

From the SET LOCK_TIMEOUT Books Online entry:

"At the beginning of a connection, this setting has a value of -1. After it is changed, the new setting stays in effect for the remainder of the connection."

All the tests I have performed have reflected this documented behaviour. Perhaps you are using different connections but getting the same session id? Use this query to check:

SELECT 
    dec.connection_id, 
    dec.connect_time 
FROM sys.dm_exec_connections AS dec 
WHERE 
    dec.session_id = @@SPID;
share|improve this answer
    
Yes, it all works fine in SSMS. I modified the application code to output the Connection Id as per your SQL statement above and I am getting two GUID's back... 7518881638be4b45a382cff71943798e and 16faaaa821497b42983e6eb08d4e74fe these are consistent. I have a method to reset the LOCK_TIMEOUT and to get the LOCK_TIMEOUT. The method that sets the LOCK_TIMEOUT also does a select so it returns 10. The method to get the LOCK_TIMEOUT is still returning -1. –  David Gray Wright Jan 25 '12 at 5:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.