SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Row Locking
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 08/01/2000 :  17:43:19  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
"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.

sqlguru
SQLTeam CoFounder

USA
20 Posts

Posted - 08/14/2000 :  10:49:29  Show Profile  Visit sqlguru's Homepage  Reply with Quote
Pessimistic Locking from the client program

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

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 09/15/2003 :  12:00:15  Show Profile  Reply with Quote
quote:
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..
quote:
HOLDLOCK Hold a shared lock until completion of the transaction

and..
quote:
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.

I.
Go to Top of Page

JCDISIT
Starting Member

USA
2 Posts

Posted - 02/19/2004 :  17:06:53  Show Profile  Reply with Quote
quote:
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

JCDISIT
Starting Member

USA
2 Posts

Posted - 02/19/2004 :  17:11:22  Show Profile  Reply with Quote
Sorry, I replyed wrong.

sqlguru:

I have the following statements in VB code:

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

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?

JCDISIT
Go to Top of Page

t1g312
Posting Yak Master

United Arab Emirates
148 Posts

Posted - 07/05/2004 :  09:48:35  Show Profile  Reply with Quote
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?

Adi

-------------------------
/me sux @sql server
Go to Top of Page

jojoimp
Starting Member

1 Posts

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


Jojo
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000