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.


Related Articles

Application Locks (or Mutexes) in SQL Server 2005 (7 January 2008)

Introduction to Locking in SQL Server (12 December 2007)

SQL Server Lock Contention Tamed: The Joys Of NOLOCK and ROWLOCK (11 October 2000)

Avoid Locking Conflicts (18 September 2000)

Other Recent Forum Posts

Good Book for Configuring Availability Groups (39m)

Mbm Bet game cusTomer care helPliNe number ☎️❾❶+{{❾❸❹❶❼❼❺❻❸❾}} uvk (55m)

Mbm Bet game cusTomer care helPliNe number ☎️❾❶+{{❾❸❹❶❼❼❺❻❸❾}} yh (59m)

Mbm Bet game cusTomer care helPliNe number ☎️❾❶+{{❾❸❹❶❼❼❺❻❸❾}} h (62m)

How do I raise a complaint against Meesho?.. call me ☎️ (69m)

How do I raise a complaint against Meesho? (71m)

How can I complain to Meesho? (78m)

How to get money back from Paytm if wrong transaction? aa (91m)

- Advertisement -