>Hi
>
>I'd like to be able to perform record locking, row update, within SQL2000. Basically, I'm trying to do the following:
>
>1. Select the correct record
>2. Attempt to lock record
>3. If able to lock, update a field within that record, release lock
>4. If not able to lock, attempt to lock for a specified amount of time.
>
>Here are some questions:
>
>1. If I attempt to lock the record with the select, what happens if the record is already locked or I'm unable to complete the lock? i.e. I'd want to be able to return a code to the calling program why the SQL procedure couldn't lock the record.
>
>Basically, it's as simple as that but it I've found it hard work reading the on-line SQL help files.
>
>Regards

Derek,

In SQL Server, you don't have to worry about locking - it's all done internally. What you want to do in your situation is write a single UPDATE statement. This automatically performs the following actions:

1. Acquires a shared intent lock on the row and any indexes that are affected by the update.
2. Converts the shared locks into exclusive locks.
3. Updates the row & indexes and releases the locks.

If another user has the row locked, the UPDATE statement will wait until the row is unlocked. There is no SQL Server timeout for the UDPATE statement, but there is a timeout on the client - usually referred to as 'querytimeout' in a connection string. If there is a deadlock, SQL Server will automatically kill one of the offending processes so that the other goes through.