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:


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.

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

return exception depending on the condition (0 Replies)

overload in sql a server Processs is taking much t (4 Replies)

simple query run very slow (3 Replies)

Create Alias Table and inner join it to itself (6 Replies)

SQL Code Help (3 Replies)

production issue need to replicate and resolve (2 Replies)

An item with the same key has already been added. (1 Reply)

how to make a script sql with results like this (X (1 Reply)

Subscribe to SQLTeam.com

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

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -