Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
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. |
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2003-09-15 : 12:00:15
|
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. |
 |
|
|
JCDISIT
Starting Member
2 Posts |
Posted - 2004-02-19 : 17:06:53
|
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 getsThe 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.
|
|
|
|
JCDISIT
Starting Member
2 Posts |
Posted - 2004-02-19 : 17:11:22
|
| 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 |
|
|
|
t1g312
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?Adi-------------------------/me sux @sql server |
|
|
|
jojoimp
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?Jojo |
|
|
|
|
|
|
|
|
Article: Row Locking