I have a question about transaction when lock_timeout error occurs
the procedure as follows,
CREATE PROCEDURE dbo.usp_glock
AS
BEGIN
SET NOCOUNT ON
SET LOCK_TIMEOUT 2000
UPDATE glocktbl SET name='john'
WHERE id=1
IF @.@.Error<>0
BEGIN
GOTO Err_Handle
END
Return 0
Err_Handle:
DECLARE @.intID INT
--/*
DECLARE cursor_Sql CURSOR
LOCAL
FORWARD_ONLY
STATIC
FOR
SELECT TOP 1 id FROM gcurtbl
OPEN cursor_Sql
FETCH NEXT FROM cursor_Sql
INTO @.intID
WHILE @.@.FETCH_STATUS = 0
BEGIN
print @.intID
End
Close cursor_Sql
--*/
insert into gerrtbl (errdesc)
values('Lock time out error.')
END
When lock happens, then error message 1222, "Lock request time-out period
exceeded" was catched by error handle. In normal case, the transaction will
not be rolled back, and this store procedure can continue to next statement
,until execute 'insert into gerrtbl (errdesc) values('Lock time out
error.')'.but actually,this procedure terminated when execute 'OPEN
cursor_Sql FETCH NEXT FROM cursor_Sql'.
who can help me explain such phenomenon?
thanks a lot.Try setting the lock-timeout to 0 (indefinite) or increase it as appropriate
.
SET LOCK_TIMEOUT 0;
http://msdn.microsoft.com/library/d... />
a_5n78.asp
http://msdn.microsoft.com/library/d... />
t_1yr8.asp
ML
http://milambda.blogspot.com/|||use 'Exec dbo.usp_glock'
the transaction will not be rolled back when lock happens.
thanks a lot.
--
wq352
"ML" wrote:
> Try setting the lock-timeout to 0 (indefinite) or increase it as appropria
te.
> SET LOCK_TIMEOUT 0;
> http://msdn.microsoft.com/library/d...>
_7a_5n78.asp
> http://msdn.microsoft.com/library/d...>
set_1yr8.asp
> ML
> --
> http://milambda.blogspot.com/|||So, what measures have you taken to solve the problem? Have you increased th
e
timeout or turned it off?
ML
http://milambda.blogspot.com/|||On Tue, 16 May 2006 09:04:02 -0700, wq352 wrote:
(snip)
>this procedure terminated when execute 'OPEN
>cursor_Sql FETCH NEXT FROM cursor_Sql'.
Hi wq352,
Since you didn't post an error message, I tried to reproduce it. I had
to change some table names to make it run. After that, I didn't get any
error message - instead, I got into an endless loop here:
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> print @.intID
> End
Generally, a loop that starts wiith WHILE @.@.FETCH_STATUS = 0 should
include at least one FETCH statement. This loop holds only a PRINT
statement, which will never change the value of @.@.FETCH_STATUS.
However, I also fail to see why you use a looop at all - considering
that you include a TOP 1 clause, you'll get just one row annyway and
there's no need to use a cursor at all.
Err_Handle:
DECLARE @.intID INT
--/*
SET @.intID = (SELECT TOP 1 id FROM gcurtbl)
PRINT @.intID
--*/
insert into gerrtbl (errdesc)
values('Lock time out error.')
Another important note - using TOP without ORDER BY means that you're
getting just one row, but it's unpredictable what row it will be. Are
you sure that that's what yoou want?
Hugo Kornelis, SQL Server MVP
Showing posts with label lock_timeout. Show all posts
Showing posts with label lock_timeout. Show all posts
Friday, March 30, 2012
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
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
Lock_timeout
Hello there,
Is there any way to set a default lock time out for the server with out using the sentence SET LOCK_TIMEOUT 20 ?
Could you explain me step by step ?
Thanks !Please Check sp_configure locks execute this first check the locks space allocated, Increase the locks by executing sp_configure locks 100000 then execute the Store Procedure you will not get the problem.|||Please try with this procedure
Execute sp_configure locks
check the locks allocated
now increase the locks by exec
Execute sp_configure locks 100000
then execute your request you will not get the error|||Thanks !|||I want to eliminate automatically every lock after 10 seconds and I did this:
USE master
go
EXEC sp_configure 'locks', 10000
go
RECONFIGURE WITH OVERRIDE
go
Is this correct ? Do I have to restart sql server ?
Thanks|||If you want to make the locks "go away", why not just ignore them and deal with the consequences later? You can set the transaction isolation level down, and the server will just ignore the locks held by other processes.
This is very dangerous, but it is less dangerous than simply trying to break the existing locks since it only puts your process at risk instead of the whole server.
-PatP|||I want to eliminate automatically every lock after 10 seconds and I did this:
USE master
go
EXEC sp_configure 'locks', 10000
go
RECONFIGURE WITH OVERRIDE
go
Is this correct ? Do I have to restart sql server ?
Thanks
Yes, Exactly
Is there any way to set a default lock time out for the server with out using the sentence SET LOCK_TIMEOUT 20 ?
Could you explain me step by step ?
Thanks !Please Check sp_configure locks execute this first check the locks space allocated, Increase the locks by executing sp_configure locks 100000 then execute the Store Procedure you will not get the problem.|||Please try with this procedure
Execute sp_configure locks
check the locks allocated
now increase the locks by exec
Execute sp_configure locks 100000
then execute your request you will not get the error|||Thanks !|||I want to eliminate automatically every lock after 10 seconds and I did this:
USE master
go
EXEC sp_configure 'locks', 10000
go
RECONFIGURE WITH OVERRIDE
go
Is this correct ? Do I have to restart sql server ?
Thanks|||If you want to make the locks "go away", why not just ignore them and deal with the consequences later? You can set the transaction isolation level down, and the server will just ignore the locks held by other processes.
This is very dangerous, but it is less dangerous than simply trying to break the existing locks since it only puts your process at risk instead of the whole server.
-PatP|||I want to eliminate automatically every lock after 10 seconds and I did this:
USE master
go
EXEC sp_configure 'locks', 10000
go
RECONFIGURE WITH OVERRIDE
go
Is this correct ? Do I have to restart sql server ?
Thanks
Yes, Exactly
Lock_timeout
Hello,
in EM (SQL7) i got the following errormessage, when i click on
current activity:
"Error: 1222 lock request time out period exceeded"
I run the sp_who /sp_who2, but nothing is blocked.
I set the lock_timeout th 5000, but it does not help.
Does anyone have an idea ?
best regards
Martinnetwork traffic is an idea
try to change for instance from tcp/ip to netbeui, or vice versa as the protocol you use to connect to the db server
see what happens.
also try to run a performance monitor to the db server.
check for cpu usage and latches.
if avg.latch wait time is high, ie. over 1000 and total latch wait time is also high i.e. over 100 for instance, then you probably experience sql server locks (watch for lock timeout /second)...
this can mean that the server is small, the app is poor, problem with your net h/w...
but this is some place to start with...
Cheers.|||Thanks for help Arounis,
network traffic is not possible, because i can log on to the server an got
the same error message.
For remember: I only get the error message, when i click on current activity in EM. No User told me that a problem still exits.
Checking avg.latch time and total latch time results:
520 constant for avg latch time
0 - 16 for total latch time.
You don't think that the problem occours about a "user - transaction" ?
Martin|||Try seeing if there is a locked resource which doesn't have a corresponding entry in sysprocesses
select *
from master..syslocks a
left join master..sysprocesses b on a.spid = b.spid
where b.spid is null
You shouldn't get any results.
Don't know what to tell you if you get any results.|||Thanks Wilso_s, but i get no results.
One more detail i detected, is that i can't access the
tempdb in EM. EM hangs up.
This short KB artikel Q308518 describes problems with
tempdb, but there is no workaround.
Maybe a restart will help ?
Thnx
in EM (SQL7) i got the following errormessage, when i click on
current activity:
"Error: 1222 lock request time out period exceeded"
I run the sp_who /sp_who2, but nothing is blocked.
I set the lock_timeout th 5000, but it does not help.
Does anyone have an idea ?
best regards
Martinnetwork traffic is an idea
try to change for instance from tcp/ip to netbeui, or vice versa as the protocol you use to connect to the db server
see what happens.
also try to run a performance monitor to the db server.
check for cpu usage and latches.
if avg.latch wait time is high, ie. over 1000 and total latch wait time is also high i.e. over 100 for instance, then you probably experience sql server locks (watch for lock timeout /second)...
this can mean that the server is small, the app is poor, problem with your net h/w...
but this is some place to start with...
Cheers.|||Thanks for help Arounis,
network traffic is not possible, because i can log on to the server an got
the same error message.
For remember: I only get the error message, when i click on current activity in EM. No User told me that a problem still exits.
Checking avg.latch time and total latch time results:
520 constant for avg latch time
0 - 16 for total latch time.
You don't think that the problem occours about a "user - transaction" ?
Martin|||Try seeing if there is a locked resource which doesn't have a corresponding entry in sysprocesses
select *
from master..syslocks a
left join master..sysprocesses b on a.spid = b.spid
where b.spid is null
You shouldn't get any results.
Don't know what to tell you if you get any results.|||Thanks Wilso_s, but i get no results.
One more detail i detected, is that i can't access the
tempdb in EM. EM hangs up.
This short KB artikel Q308518 describes problems with
tempdb, but there is no workaround.
Maybe a restart will help ?
Thnx
Lock: Timeout - @@LOCK_TIMEOUT
Hi,
Trace file which I have here, contains many Lock: Timeout
events. I don't understand how is it possible when
@.@.LOCK_TIMEOUT is set to -1 for all connections.
Thanks,
OJThis event is generated by a low level system component. Please post the
entire event data column for us to understand the situation.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:200b01c4dca6$c0f48a30$a301280a@.phx.gbl...
> Hi,
> Trace file which I have here, contains many Lock: Timeout
> events. I don't understand how is it possible when
> @.@.LOCK_TIMEOUT is set to -1 for all connections.
> Thanks,
> OJ|||I'm not sure how this can help, but here it is (one of
many examples):
row_number=188337
event_class=27
text_Data=NULL
Binary_data=0x000707000DD07C4F23006F01DE541AB4
DatabaseID=14
HostName=PROD1
SPID=73
ObjectID=1333579789
IndexID=30
Mode=3
Also, some of them are caused by SPID lower than 50, and
mode in that case is: 5.
Thanks,
OJ
>--Original Message--
>This event is generated by a low level system component.
Please post the
>entire event data column for us to understand the
situation.
>--
>Wei Xiao [MSFT]
>SQL Server Storage Engine Development
>http://weblogs.asp.net/weix
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"OJ" <anonymous@.discussions.microsoft.com> wrote in
message
>news:200b01c4dca6$c0f48a30$a301280a@.phx.gbl...
>> Hi,
>> Trace file which I have here, contains many Lock:
Timeout
>> events. I don't understand how is it possible when
>> @.@.LOCK_TIMEOUT is set to -1 for all connections.
>> Thanks,
>> OJ
>
>.
>|||As an optimization, SQL Server internally needs to check if a transaction
can acquire some locks without waiting. When this fails, the lock timeout
event is generated (with a duration of 0) but SQL Server will wait for the
lock instead.
The value of the "Duration" column indicates for how long SQL Server waits
before the timeout. If the value is 0, then this is the internal no-wait
timeout.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:193d01c4dd59$34acbc60$a501280a@.phx.gbl...
> I'm not sure how this can help, but here it is (one of
> many examples):
> row_number=188337
> event_class=27
> text_Data=NULL
> Binary_data=0x000707000DD07C4F23006F01DE541AB4
> DatabaseID=14
> HostName=PROD1
> SPID=73
> ObjectID=1333579789
> IndexID=30
> Mode=3
>
> Also, some of them are caused by SPID lower than 50, and
> mode in that case is: 5.
> Thanks,
> OJ
>>--Original Message--
>>This event is generated by a low level system component.
> Please post the
>>entire event data column for us to understand the
> situation.
>>--
>>Wei Xiao [MSFT]
>>SQL Server Storage Engine Development
>>http://weblogs.asp.net/weix
>>This posting is provided "AS IS" with no warranties, and
> confers no rights.
>>"OJ" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:200b01c4dca6$c0f48a30$a301280a@.phx.gbl...
>> Hi,
>> Trace file which I have here, contains many Lock:
> Timeout
>> events. I don't understand how is it possible when
>> @.@.LOCK_TIMEOUT is set to -1 for all connections.
>> Thanks,
>> OJ
>>
>>.|||Thanks
>--Original Message--
>As an optimization, SQL Server internally needs to check
if a transaction
>can acquire some locks without waiting. When this fails,
the lock timeout
>event is generated (with a duration of 0) but SQL Server
will wait for the
>lock instead.
>The value of the "Duration" column indicates for how long
SQL Server waits
>before the timeout. If the value is 0, then this is the
internal no-wait
>timeout.
>--
>Wei Xiao [MSFT]
>SQL Server Storage Engine Development
>http://weblogs.asp.net/weix
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"OJ" <anonymous@.discussions.microsoft.com> wrote in
message
>news:193d01c4dd59$34acbc60$a501280a@.phx.gbl...
>> I'm not sure how this can help, but here it is (one of
>> many examples):
>> row_number=188337
>> event_class=27
>> text_Data=NULL
>> Binary_data=0x000707000DD07C4F23006F01DE541AB4
>> DatabaseID=14
>> HostName=PROD1
>> SPID=73
>> ObjectID=1333579789
>> IndexID=30
>> Mode=3
>>
>> Also, some of them are caused by SPID lower than 50, and
>> mode in that case is: 5.
>> Thanks,
>> OJ
>>--Original Message--
>>This event is generated by a low level system component.
>> Please post the
>>entire event data column for us to understand the
>> situation.
>>--
>>Wei Xiao [MSFT]
>>SQL Server Storage Engine Development
>>http://weblogs.asp.net/weix
>>This posting is provided "AS IS" with no warranties, and
>> confers no rights.
>>"OJ" <anonymous@.discussions.microsoft.com> wrote in
>> message
>>news:200b01c4dca6$c0f48a30$a301280a@.phx.gbl...
>> Hi,
>> Trace file which I have here, contains many Lock:
>> Timeout
>> events. I don't understand how is it possible when
>> @.@.LOCK_TIMEOUT is set to -1 for all connections.
>> Thanks,
>> OJ
>>
>>.
>
>.
>sql
Trace file which I have here, contains many Lock: Timeout
events. I don't understand how is it possible when
@.@.LOCK_TIMEOUT is set to -1 for all connections.
Thanks,
OJThis event is generated by a low level system component. Please post the
entire event data column for us to understand the situation.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:200b01c4dca6$c0f48a30$a301280a@.phx.gbl...
> Hi,
> Trace file which I have here, contains many Lock: Timeout
> events. I don't understand how is it possible when
> @.@.LOCK_TIMEOUT is set to -1 for all connections.
> Thanks,
> OJ|||I'm not sure how this can help, but here it is (one of
many examples):
row_number=188337
event_class=27
text_Data=NULL
Binary_data=0x000707000DD07C4F23006F01DE541AB4
DatabaseID=14
HostName=PROD1
SPID=73
ObjectID=1333579789
IndexID=30
Mode=3
Also, some of them are caused by SPID lower than 50, and
mode in that case is: 5.
Thanks,
OJ
>--Original Message--
>This event is generated by a low level system component.
Please post the
>entire event data column for us to understand the
situation.
>--
>Wei Xiao [MSFT]
>SQL Server Storage Engine Development
>http://weblogs.asp.net/weix
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"OJ" <anonymous@.discussions.microsoft.com> wrote in
message
>news:200b01c4dca6$c0f48a30$a301280a@.phx.gbl...
>> Hi,
>> Trace file which I have here, contains many Lock:
Timeout
>> events. I don't understand how is it possible when
>> @.@.LOCK_TIMEOUT is set to -1 for all connections.
>> Thanks,
>> OJ
>
>.
>|||As an optimization, SQL Server internally needs to check if a transaction
can acquire some locks without waiting. When this fails, the lock timeout
event is generated (with a duration of 0) but SQL Server will wait for the
lock instead.
The value of the "Duration" column indicates for how long SQL Server waits
before the timeout. If the value is 0, then this is the internal no-wait
timeout.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:193d01c4dd59$34acbc60$a501280a@.phx.gbl...
> I'm not sure how this can help, but here it is (one of
> many examples):
> row_number=188337
> event_class=27
> text_Data=NULL
> Binary_data=0x000707000DD07C4F23006F01DE541AB4
> DatabaseID=14
> HostName=PROD1
> SPID=73
> ObjectID=1333579789
> IndexID=30
> Mode=3
>
> Also, some of them are caused by SPID lower than 50, and
> mode in that case is: 5.
> Thanks,
> OJ
>>--Original Message--
>>This event is generated by a low level system component.
> Please post the
>>entire event data column for us to understand the
> situation.
>>--
>>Wei Xiao [MSFT]
>>SQL Server Storage Engine Development
>>http://weblogs.asp.net/weix
>>This posting is provided "AS IS" with no warranties, and
> confers no rights.
>>"OJ" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:200b01c4dca6$c0f48a30$a301280a@.phx.gbl...
>> Hi,
>> Trace file which I have here, contains many Lock:
> Timeout
>> events. I don't understand how is it possible when
>> @.@.LOCK_TIMEOUT is set to -1 for all connections.
>> Thanks,
>> OJ
>>
>>.|||Thanks
>--Original Message--
>As an optimization, SQL Server internally needs to check
if a transaction
>can acquire some locks without waiting. When this fails,
the lock timeout
>event is generated (with a duration of 0) but SQL Server
will wait for the
>lock instead.
>The value of the "Duration" column indicates for how long
SQL Server waits
>before the timeout. If the value is 0, then this is the
internal no-wait
>timeout.
>--
>Wei Xiao [MSFT]
>SQL Server Storage Engine Development
>http://weblogs.asp.net/weix
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"OJ" <anonymous@.discussions.microsoft.com> wrote in
message
>news:193d01c4dd59$34acbc60$a501280a@.phx.gbl...
>> I'm not sure how this can help, but here it is (one of
>> many examples):
>> row_number=188337
>> event_class=27
>> text_Data=NULL
>> Binary_data=0x000707000DD07C4F23006F01DE541AB4
>> DatabaseID=14
>> HostName=PROD1
>> SPID=73
>> ObjectID=1333579789
>> IndexID=30
>> Mode=3
>>
>> Also, some of them are caused by SPID lower than 50, and
>> mode in that case is: 5.
>> Thanks,
>> OJ
>>--Original Message--
>>This event is generated by a low level system component.
>> Please post the
>>entire event data column for us to understand the
>> situation.
>>--
>>Wei Xiao [MSFT]
>>SQL Server Storage Engine Development
>>http://weblogs.asp.net/weix
>>This posting is provided "AS IS" with no warranties, and
>> confers no rights.
>>"OJ" <anonymous@.discussions.microsoft.com> wrote in
>> message
>>news:200b01c4dca6$c0f48a30$a301280a@.phx.gbl...
>> Hi,
>> Trace file which I have here, contains many Lock:
>> Timeout
>> events. I don't understand how is it possible when
>> @.@.LOCK_TIMEOUT is set to -1 for all connections.
>> Thanks,
>> OJ
>>
>>.
>
>.
>sql
Wednesday, March 28, 2012
lock timeout
my SQL studio view is running into timeout error block. how do i insert the
SET LOCK_TIMEOUT -1GO
in the SQL statement of the view to allow this to run to completion? an example of the SQL view is;
SELECT TOP (100) PERCENT dbo.Entry_Race.E_TDR, dbo.Entry_Race.E_Surface, dbo.Entry_Race.E_Race_Class_Codes,
FROM dbo.Entry_Race INNER JOIN
dbo.Entry_Horse ON dbo.Entry_Race.E_TDR = dbo.Entry_Horse.E_TDR
WHERE (CONVERT(varchar(07), dbo.Entry_Horse.E_Date) BETWEEN CONVERT(varchar(07), GETDATE(), 0) AND CONVERT(varchar(07), GETDATE() + 1, 0))
ORDER BY dbo.Entry_Race.E_TDR, dbo.Entry_Horse.E_Horse, dbo.Entry_Horse.E_Traininer
Do you really need to wait indefinitelly? It is not a very normal situation to have the client waiting tens of seconds for a response - why not using a more optimistic locking mechanism?
You shouldn't user the convert funcion to compare the dates, but using dateadd () over the getdate() functions and compare directly - as it is, any indexes over dbo.Entry_Horse.E_Date will not be used by SQL...
Lock timeout
Hi all,
I need to set a default value for lock timeout on all
connections. Is there any way to do this without running:
set @.@.lock_timeout = ?
at the beginning of every connection? I'd like a global
setting on this.
Best regardsHi,
I feel there is no setting to control Lock time out at server level.
Thanks
Hari
MCDBA
"Johnny" <anonymous@.discussions.microsoft.com> wrote in message
news:0a5c01c3c8f9$8a88d120$a001280a@.phx.gbl...
> Hi all,
> I need to set a default value for lock timeout on all
> connections. Is there any way to do this without running:
> set @.@.lock_timeout = ?
> at the beginning of every connection? I'd like a global
> setting on this.
> Best regardssql
I need to set a default value for lock timeout on all
connections. Is there any way to do this without running:
set @.@.lock_timeout = ?
at the beginning of every connection? I'd like a global
setting on this.
Best regardsHi,
I feel there is no setting to control Lock time out at server level.
Thanks
Hari
MCDBA
"Johnny" <anonymous@.discussions.microsoft.com> wrote in message
news:0a5c01c3c8f9$8a88d120$a001280a@.phx.gbl...
> Hi all,
> I need to set a default value for lock timeout on all
> connections. Is there any way to do this without running:
> set @.@.lock_timeout = ?
> at the beginning of every connection? I'd like a global
> setting on this.
> Best regardssql
Lock request time out period exceeded
Hi,
What is the default for @.@.LOCK_TIMEOUT?
When it is not set, how long it is?
Thanks,
JanosThe default is -1 which is it won't timeout, it will wait
forever. See the books online topic: SET LOCK_TIMEOUT
for more information.
-Sue
On Mon, 7 Nov 2005 12:12:04 -0700, "Janos Horanszky"
<jhoranszky@.chartelltechnology.com> wrote:
>Hi,
>What is the default for @.@.LOCK_TIMEOUT?
>When it is not set, how long it is?
>Thanks,
>Janos
>|||Thanks Sue.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:6gf0n1dh5lfvos0ua3ic5sdg08vnsmja27@.4ax.com...
> The default is -1 which is it won't timeout, it will wait
> forever. See the books online topic: SET LOCK_TIMEOUT
> for more information.
> -Sue
> On Mon, 7 Nov 2005 12:12:04 -0700, "Janos Horanszky"
> <jhoranszky@.chartelltechnology.com> wrote:
>>Hi,
>>What is the default for @.@.LOCK_TIMEOUT?
>>When it is not set, how long it is?
>>Thanks,
>>Janos
>sql
What is the default for @.@.LOCK_TIMEOUT?
When it is not set, how long it is?
Thanks,
JanosThe default is -1 which is it won't timeout, it will wait
forever. See the books online topic: SET LOCK_TIMEOUT
for more information.
-Sue
On Mon, 7 Nov 2005 12:12:04 -0700, "Janos Horanszky"
<jhoranszky@.chartelltechnology.com> wrote:
>Hi,
>What is the default for @.@.LOCK_TIMEOUT?
>When it is not set, how long it is?
>Thanks,
>Janos
>|||Thanks Sue.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:6gf0n1dh5lfvos0ua3ic5sdg08vnsmja27@.4ax.com...
> The default is -1 which is it won't timeout, it will wait
> forever. See the books online topic: SET LOCK_TIMEOUT
> for more information.
> -Sue
> On Mon, 7 Nov 2005 12:12:04 -0700, "Janos Horanszky"
> <jhoranszky@.chartelltechnology.com> wrote:
>>Hi,
>>What is the default for @.@.LOCK_TIMEOUT?
>>When it is not set, how long it is?
>>Thanks,
>>Janos
>sql
LOCK REQUEST TIME OUT PERIOD EXCEEDED
It seems the table is locked by one process for a longer
time, and this has forced other process to throw the
error 1222.
The LOCK_TIMEOUT setting allows an application to set a
maximum time that a statement waits on a blocked
resource. When a statement has waited longer than the
LOCK_TIMEOUT setting, the blocked statement is canceled
automatically, and error message 1222 "Lock request time-
out period exceeded" is returned to the application.
I think if after every 1000 inserts, if you commit the
transaction, then the resource will not be locked for a
longer duration.
regds,
Shrikant Patil,
MCDBA
>--Original Message--
>Got a VB6 program creating records in an SQL 2000 DB. I
have 5 client
>machines creating 100,000 records each in the same table
at the same time.
>On a couple of machine I get the error "LOCK REQUEST
TIME OUT PERIOD
>EXCEEDED". This happened maybe 4 times. Would this be
due to a network or
>hardware limitation or is it a SQL DB factor maybe ?
>The SQL server is only a P4 with 512 ram.
>Clients machines vary greatly and run 2000pro and XPpro.
>Thanks for any pointers.
>Scott.
>
>.
>
i see.
so the error was recived because one clinet/process was ready to commit 1000
records while another clinet/process was in the process of commiting - hence
the error.
the chances that a clinet machine would recive this with our software in
practice is very remotei guess as they would probablty never create that
many records at the same time to the same table. even if they did the RETRY
option seems to deal with it well anyway.
Is there other locking methods that can be employed rather than the 1000
batch one ?
Thanks for your time
Scott
time, and this has forced other process to throw the
error 1222.
The LOCK_TIMEOUT setting allows an application to set a
maximum time that a statement waits on a blocked
resource. When a statement has waited longer than the
LOCK_TIMEOUT setting, the blocked statement is canceled
automatically, and error message 1222 "Lock request time-
out period exceeded" is returned to the application.
I think if after every 1000 inserts, if you commit the
transaction, then the resource will not be locked for a
longer duration.
regds,
Shrikant Patil,
MCDBA
>--Original Message--
>Got a VB6 program creating records in an SQL 2000 DB. I
have 5 client
>machines creating 100,000 records each in the same table
at the same time.
>On a couple of machine I get the error "LOCK REQUEST
TIME OUT PERIOD
>EXCEEDED". This happened maybe 4 times. Would this be
due to a network or
>hardware limitation or is it a SQL DB factor maybe ?
>The SQL server is only a P4 with 512 ram.
>Clients machines vary greatly and run 2000pro and XPpro.
>Thanks for any pointers.
>Scott.
>
>.
>
i see.
so the error was recived because one clinet/process was ready to commit 1000
records while another clinet/process was in the process of commiting - hence
the error.
the chances that a clinet machine would recive this with our software in
practice is very remotei guess as they would probablty never create that
many records at the same time to the same table. even if they did the RETRY
option seems to deal with it well anyway.
Is there other locking methods that can be employed rather than the 1000
batch one ?
Thanks for your time
Scott
Subscribe to:
Posts (Atom)