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,
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