Lock management in SQL Server 2000

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,
z1hou1How 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
>|||Thanks Tibor,
The SET LOCK TIMEOUT does offer a solution. I will try it out and take
it from there.
z1hou1|||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|||> 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
>sql