Row Locking

By Bill Graziano on 13 August 2000 | 6 Comments | 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:


FROM authors
WHERE au_id = '274-80-9391'

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


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.

Discuss this article: 6 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

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

How to Download Ringtones (1 Reply)

Database Diagram not showing relationship of table (3 Replies)

SQL2008R2: Should there be a limit xmla qry size? (3 Replies)

Variables in Where Clause (20 Replies)

how to update 2 records on 1 field (20 Replies)

How to migrate Replication DB on sql server 2008 (7 Replies)

Reading XML in SQL (5 Replies)

Return a zero value in place of null (8 Replies)

Subscribe to

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers. Articles via RSS Weblog via RSS

- Advertisement -