Row Locking

By Bill Graziano on 13 August 2000 | Tags: Locking

"I wish to keep some rows locked on my sql7 table (so that nobody modifies them until i am finished with it) until my vb6 form in question is closed. I do not want to store any status code within the table (which calls for addl. maintenance, for eg if my w/s boots with the status code still set!) How do i achieve this rowlock for extended period of time? Any clues???" Yep, we can handle this one. We'll use some locking hints.

Change your SELECT statement to look something like this:


BEGIN TRAN

SELECT *
FROM authors
WITH (HOLDLOCK, ROWLOCK)
WHERE au_id = '274-80-9391'

/* Do all your stuff here while the record is locked */

COMMIT TRAN

The HOLDLOCK hint will instruct SQL Server to hold the lock until you commit the transaction. The ROWLOCK hint will lock only this record and not issue a page or table lock.

The lock will also be released if you close your connection or it times out. I'd be VERY careful doing this since it will stop any SELECT statements that hit this row dead in their tracks. SQL Server has numerous locking hints that you can use. You can see them in Books Online when you search on either HOLDLOCK or ROWLOCK.