Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 Article Discussion
 Article: Row Locking

Author  Topic 

Ask SQLTeam Question

0 Posts

Posted - 2000-08-01 : 17:43:19
"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.

Article Link.

Constraint Violating Yak Guru

392 Posts

Posted - 2003-09-15 : 12:00:15
I'd be VERY careful doing this since it will stop any SELECT statements that hit this row dead in their tracks.

I'm confused by this statement.

It says in the manual..
HOLDLOCK Hold a shared lock until completion of the transaction

Shared (S) locks allow concurrent transactions to read (SELECT) a resource.

I paused a transaction before it had finished and after it had read a record using HOLDLOCK, ROWLOCK and was still able to read the same record using SQL Query Analyzer.

Go to Top of Page

Starting Member

2 Posts

Posted - 2004-02-19 : 17:06:53
Originally posted by sqlguru

Pessimistic Locking from the client program <P>The locking scheme you're referring to is called "pessimistic locking". This means that you're holding the lock for the entire duration of the view/edit/update of the record. It gets

The opposite of this is called "optimistic locking", in which you hold a lock on the record for only as long as it takes for the database engine to physically update the row. However, while you're editing the record, another user could go in and edit/update the record, causing a conflict while you're trying to update.

Which is better? Depends on your situation. Pessimistic locking definitely prevents others from modifying "your" record, but it can be expensive in terms of concurrency... depending on their transaction isolation level, they may not even be able to read the record you have locked for update. Optimistic locking is good for concurrency, but you have to add extra logic in your program to handle update conflicts.

I prefer optimistic locking, personally, but if you want to use pessimistic, I'd recommend implementing some sort of "edit timeout" feature in your client program that releases the lock after a period of no activity. Or, if you have other processes that need to read data from that table while updates are occuring, you can set their transaction isolation level to "read uncommitted".

Oh, and to get to the point that I was orignally going to make... You can most likely control the locking scheme from VB, depending on what data access method you're using. For instance, DAO supports both locking methods. Pessimistic will lock on the recordset .Edit method, and hold the lock until the .Update method. Optimistic will just perform the lock during .Update. You can do a search on pessimistic locking in the VB books online and that should steer ya in the right direction.

Go to Top of Page

Starting Member

2 Posts

Posted - 2004-02-19 : 17:11:22
Sorry, I replyed wrong.


I have the following statements in VB code:

Set rsRef = dbJobTrace.OpenRecordset("Select * from tblRef", dbOpenDynaset, dbSeeChanges, dbPessimistic)

However, when I run it on two computers (using Pessimistic record locking when EDIT is invoked, both computesr are still able to open this record (I step through the code pausing on one computer while edit is invoked). Can you inform me how to properly lock out the second computer so he/she gets an error when the rsRef.Edit line is executed?

Go to Top of Page

Posting Yak Master

148 Posts

Posted - 2004-07-05 : 09:48:35
The lock set through ADO stays only as long as the recordset is open right? But once a record is retrieved from the database, the recordset is closed. What do I do when I want other people to be able to read the same record but not make changes to it?


/me sux @sql server
Go to Top of Page

Starting Member

1 Post

Posted - 2009-03-30 : 00:37:56
If the ROW LOCK has been set. How will I know that ROW is locked?

Go to Top of Page

- Advertisement -