patternsqlMinor
SET LOCK_TIMEOUT, is it session or statement based?
Viewed 0 times
lock_timeoutstatementbasedsessionset
Problem
For what period does the
I do a
I did some looking on the MSDN site and could not find if 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.Solution
From the
"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:
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;Code Snippets
SELECT
dec.connection_id,
dec.connect_time
FROM sys.dm_exec_connections AS dec
WHERE
dec.session_id = @@SPID;Context
StackExchange Database Administrators Q#11511, answer score: 4
Revisions (0)
No revisions yet.