MS SQL Server - Lock management in SQL Server 2000

Author

Lock management in SQL Server 2000

z1hou1@gmail.com

2006-11-04, 5:12 am

Hi,

I am a newbie to SQL Server 2000 and have issues with lock management.

How would I know that a lock is already in place in SQL Server 2000
while I am attempting to update a record, or insert a record in the
same page that already has a lock on it?

I am looking for the equivalent of the Oracle statement:

SELECT * FROM TABLE FOR UPDATE NOWAIT
This statament will immediately return to me a sqlca.sqlcode of -54 (or
resource busy) if it cannot acquire the lock.

I could then loop through the statement for about 30 seconds, waiting
for about 5 seconds depending on the application. If the loop exits at
the end of 30 seconds, I know there is a serious error and can look at
why the record is not available for locking. If I do acquire the lock,
I will break from the loop, complete my DML, rollback/commit.

Does the SQL 2000 LOCK hint behave the same way?

Will the following snippet of code here work in a similar manner?

declare retry_sw int
begin
-- while loop begins
while retry_sw < 10
begin
select @lock_sw=1 from table where column_1 = '10' a with
lock
if @@error<>0 and @@rowcount = 0
begin
waitfor delay '000.00.05'
continue
end
if @@error = 0 and @@rowcount > 0 break
retry_sw = retry_sw + 1
end
-- while loop ends
if retry_sw = 10
begin
print "Unable to acquire lock"
return
end
-- Continue code from this point since we now have a lock.

Thank you for any help on what I am attempting here.

Regards,
z1hou1

Tibor Karaszi

2006-11-04, 7:12 pm

How about just using SET LOCK_TIMEOUT? Specify the number of seconds you want to wait, then you get
an error that you can trap.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

<z1hou1@gmail.com> wrote in message news:1162636791.380539.277830@b28g2000cwb.googlegroups.com...
> Hi,
>
> I am a newbie to SQL Server 2000 and have issues with lock management.
>
> How would I know that a lock is already in place in SQL Server 2000
> while I am attempting to update a record, or insert a record in the
> same page that already has a lock on it?
>
> I am looking for the equivalent of the Oracle statement:
>
> SELECT * FROM TABLE FOR UPDATE NOWAIT
> This statament will immediately return to me a sqlca.sqlcode of -54 (or
> resource busy) if it cannot acquire the lock.
>
> I could then loop through the statement for about 30 seconds, waiting
> for about 5 seconds depending on the application. If the loop exits at
> the end of 30 seconds, I know there is a serious error and can look at
> why the record is not available for locking. If I do acquire the lock,
> I will break from the loop, complete my DML, rollback/commit.
>
> Does the SQL 2000 LOCK hint behave the same way?
>
> Will the following snippet of code here work in a similar manner?
>
> declare retry_sw int
> begin
> -- while loop begins
> while retry_sw < 10
> begin
> select @lock_sw=1 from table where column_1 = '10' a with
> lock
> if @@error<>0 and @@rowcount = 0
> begin
> waitfor delay '000.00.05'
> continue
> end
> if @@error = 0 and @@rowcount > 0 break
> retry_sw = retry_sw + 1
> end
> -- while loop ends
> if retry_sw = 10
> begin
> print "Unable to acquire lock"
> return
> end
> -- Continue code from this point since we now have a lock.
>
>
> Thank you for any help on what I am attempting here.
>
> Regards,
> z1hou1
>

z1hou1@gmail.com

2006-11-05, 7:13 pm

Thanks Tibor,
The SET LOCK TIMEOUT does offer a solution. I will try it out and take
it from there.

z1hou1

z1hou1@gmail.com

2006-11-14, 12:12 am

While the LOCK_TIMEOUT works admirably, I noticed that error 1222 (lock
timed out) and error 1205 (deadlock...) cannot be trapped directly in
T-SQL. They seem to return control to the client - whatever the client
is and in my particular case, a Java JDBC program using Microsoft's
JDBC drivers.

Is there a way of trapping these errors in T-SQL?

Regards,
z1hou1

Tibor Karaszi

2006-11-15, 7:14 pm

> While the LOCK_TIMEOUT works admirably, I noticed that error 1222 (lock

> timed out) and error 1205 (deadlock...) cannot be trapped directly in
> T-SQL.

This is not what I am seeing. Running below script, I do indeed get the "err" string in the result:

SET LOCK_TIMEOUT 300

SELECT * FROM test
WHERE c1 between 1 and 5
IF @@ERROR <> 0 PRINT 'Err'

(In 2005, you have even more options using TRY/CATCH.)

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

<z1hou1@gmail.com> wrote in message news:1163475594.291513.50560@k70g2000cwa.googlegroups.com...
> While the LOCK_TIMEOUT works admirably, I noticed that error 1222 (lock
> timed out) and error 1205 (deadlock...) cannot be trapped directly in
> T-SQL. They seem to return control to the client - whatever the client
> is and in my particular case, a Java JDBC program using Microsoft's
> JDBC drivers.
>
> Is there a way of trapping these errors in T-SQL?
>
> Regards,
> z1hou1
>

Sponsored Links