3

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.

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

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
  • 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
4

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;
  • 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
1

Although the SET LOCK_TIMEOUT documentation currently says it will stay in effect for the remainder of the connection, that is not entirely correct. It follows the same rules as other SET statements.

If a SET statement is run in a stored procedure or trigger, the value of the SET option is restored after control is returned from the stored procedure or trigger. Also, if a SET statement is specified in a dynamic SQL string that is run by using either sp_executesql or EXECUTE, the value of the SET option is restored after control is returned from the batch specified in the dynamic SQL string.

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-statements-transact-sql

Also, depending on how you are running the query, you may be using connection pooling. This can give you the same @@spid but does a exec sp_reset_connection when it retrieves a connection from the pool.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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