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 |
|
Utpal
Posting Yak Master
179 Posts |
 Posted - 2002-05-15 : 02:26:50
|
| Hi everybody, My front end is VB 6.0 and back end is SQL Server 7.0. In my Data entry module, to update/modify a record, I do the following :1) Open a ADO recordset on the record to be modified in pessimistic mode, which locks the record.2) Refresh the data entry controls in the form.3) Allow user A to edit data in the data entry controls.4) When user A clicks on save, I do the following :(i) I generate an "UPDATE TableName ..." SQL command string (strSave) (ii) Close the recordset based on the record (iii) Execute the SQL string on the connection object ( Conn.Execute strSave ). The record is locked during the time user A is editing the controls. But in the Save process, the record is unlocked for a very brief period, when the recordset based on the record is closed, before the SQL command for saving the record is executed. My concern is that, during the brief period when the record is unlocked, if user B starts editing the record, it would get locked by him and user A's save SQL command would fail. Is it possible to keep the record locked throughout the save process ? Please bear in mind that I don't want to use the Update method of the recordset object. |
|
|
rihardh
Constraint Violating Yak Guru
307 Posts |
Posted - 2002-05-15 : 07:31:08
|
| Why are you using pessimistic mode recordsets?The usual approach is to use optimistic record locking so two users can share the same data at the same time. The only difference is, that when user A updates his RS first the records get updated in the database, when user B tries to update his RS, you have to notify him, that the RS has been changed by another user and has to confirm (or not) his update. Bear in mind that server side cursors (like in your case) demand a lot of server resources and produce unnecessary network traffic.I have completed a lot of client server projects and I never had to take your approach.Take a closer look at ADO in MSDN or read "Programming ADO" by MS PRESS. |
 |
|
|
Utpal
Posting Yak Master
179 Posts |
Posted - 2002-05-16 : 02:03:18
|
| If user B is notified that the record has been changed only at the time of updating the record, he would have already wasted time in editing the record. Secondly during the time user B edits the record, the record data in the form controls would get outdated after user A saved the record. This could result in lot of confusion. Regarding pessimistic locks consuming lot of server resources, updates are not done very frequently and it is only during update I am using pessimistic locking. |
|
|
|
|
|
|
|
|
How to keep record locked during update ?