lock_timeout error

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