Lock_Timeout - best practice?

Hi,
I'm occosianally having my main shop floor users 'hang'
for 30+ seconds and I can see that my app is timing out
somewhere SQL Server when it fires a new record insert
transaction that, in turn, fires a trigger which calls a
number of procs. This problem only happens two or three
times per day, for no particular reason and some days no
problems at all. All the the sql code in the chain of
events set Lock_Timeout to 200ms.
I would like to ask three questions:
1) Is it generally better to shorten a Lock_Timeout so as
to not hang around too long and restart the trans quickly
or extend it in the hope the trans will eventually fire
and therefore not suffer the workload caused by a Rollback.
This seems like a classic 'it depends' question - but are
there any general rules here?
2) I've always presumed that Lock_Timeouts are used each
time SQLServer wants to aquire a lock, ie if a proc needs
to aquire 10 locks then each attempt has its own
Lock_Timeout - is this correct?
3) Lock_Timeouts in no way set the overall time limit for
executing a complete transaction - is this correct?
TIA - PeterOn Wed, 23 Jul 2003 16:33:43 -0700, "Peter Jones"
<jonespm@.ozemail.com.au> wrote:
>I'm occosianally having my main shop floor users 'hang'
>for 30+ seconds and I can see that my app is timing out
>somewhere SQL Server when it fires a new record insert
>transaction that, in turn, fires a trigger which calls a
>number of procs. This problem only happens two or three
>times per day, for no particular reason and some days no
>problems at all. All the the sql code in the chain of
>events set Lock_Timeout to 200ms.
>I would like to ask three questions:
>1) Is it generally better to shorten a Lock_Timeout so as
>to not hang around too long and restart the trans quickly
>or extend it in the hope the trans will eventually fire
>and therefore not suffer the workload caused by a Rollback.
Shorten it if you want to pop up a message to the user, otherwise you
might even want to lengthen it, if you know that you get these
infrequent 30+ second hangs which are correct functioning.
>This seems like a classic 'it depends' question - but are
>there any general rules here?
>2) I've always presumed that Lock_Timeouts are used each
>time SQLServer wants to aquire a lock, ie if a proc needs
>to aquire 10 locks then each attempt has its own
>Lock_Timeout - is this correct?
AFAIK.
>3) Lock_Timeouts in no way set the overall time limit for
>executing a complete transaction - is this correct?
Yes that is correct. The only limit I know on transaction times is
connection timeout, and I'm not even certain how those interact.
Joshua Stern|||<snip>
> 2) I've always presumed that Lock_Timeouts are used each
> time SQLServer wants to aquire a lock, ie if a proc needs
> to aquire 10 locks then each attempt has its own
> Lock_Timeout - is this correct?
Yes, but the lock_timeout of the 10th lock is not relevant, because the
1st lock is always the first to time out.
> 3) Lock_Timeouts in no way set the overall time limit for
> executing a complete transaction - is this correct?
It depends on the lock type. In default isolation transaction level,
Shared locks can be released immediately after the Select statement is
finished. However, exclusive locks are essential for the transaction,
and will be held until the end of the transaction.
For example, if you have the following transaction:
BEGIN TRANSACTION
UPDATE MyTable1 SET Col1 = 1
UPDATE MyTable2 SET Col2 = 2
COMMIT TRANSACTION
Then the exclusive locks on MyTable1 will be held until the transaction
is committed. If the lock_timeout is set to 10 seconds, then the
transaction will fail if the total time of the two Updates exceeds these
10 seconds.
Hope this helps,
Gert-Jan|||Hi Gert-Jan,
Yes - this and Joshua's reply are very helpful. But they
raise a couple of issues I would like to clarrify:
2) Using your example in point 3 I would have expected the
Lock_Timeout value to be used when updating T1 then
another, independent Lock_Timeout, to be used it tries to
update T2. Your response to point 2 indicates that this is
not true - am I understanding you correctly?
3) Regarding point 3, this relates to point 2 I guess in
that it is completely contary to what I understood about
Lock_Timeouts. Your response seems to say that a
Lock_Timeout is the time SQL Server will 'hold' a lock
whereas it was my understanding it is how long it
will 'wait' for a blocked resourse to become unblocked.
Please clarrify that I have understood your response
correctly.
Cheers, Peter
>--Original Message--
><snip>
>> 2) I've always presumed that Lock_Timeouts are used each
>> time SQLServer wants to aquire a lock, ie if a proc
needs
>> to aquire 10 locks then each attempt has its own
>> Lock_Timeout - is this correct?
>Yes, but the lock_timeout of the 10th lock is not
relevant, because the
>1st lock is always the first to time out.
>> 3) Lock_Timeouts in no way set the overall time limit
for
>> executing a complete transaction - is this correct?
>It depends on the lock type. In default isolation
transaction level,
>Shared locks can be released immediately after the Select
statement is
>finished. However, exclusive locks are essential for the
transaction,
>and will be held until the end of the transaction.
>For example, if you have the following transaction:
>BEGIN TRANSACTION
>UPDATE MyTable1 SET Col1 = 1
>UPDATE MyTable2 SET Col2 = 2
>COMMIT TRANSACTION
>Then the exclusive locks on MyTable1 will be held until
the transaction
>is committed. If the lock_timeout is set to 10 seconds,
then the
>transaction will fail if the total time of the two
Updates exceeds these
>10 seconds.
>Hope this helps,
>Gert-Jan
>.
>|||Peter,
I have to appologize. It seems you are correct. The lock_timeout value
is only used when acquiring locks, and not for holding the locks. I
verified this with a simple test.
So if we go back to your original question 2:
>> 2) I've always presumed that Lock_Timeouts are used each
>> time SQLServer wants to aquire a lock, ie if a proc needs
>> to aquire 10 locks then each attempt has its own
>> Lock_Timeout - is this correct?
I tested this. I made a transaction that runs for approximately 25
seconds if there are no lock waits. Then - with another connection - I
locked a relevant row for 50 seconds. When I ran the transaction again
with a lock_timeout ot 51000 it completed successfully in 56 seconds.
IMO this proves that the lock_timeout is set for each individual lock
acquisition. (Otherwise, the transaction could not have finished in > 51
seconds).
Gert-Jan