record locking.

[ultimatewilliam]

using ado, how should i explicitly lock a certain record before editing it so other users will not be able to edit the same record. is there a function that would place lock a record on a record and will return a value if the lock is successful?

as an example, i'm using msaccess database and i have a table named "Parameters" with a field Counter (numeric). This counter will increment and the value will be assigned to a certain record on two tables "Transactions1" and "Transactions2". To ensure that there would be noduplicate assignments, a record on "Transactions1" who needs a number on the "Parameters" table will first check if Counter is locked by a record on "Transaction2", if not, it will lock it, increment the Counter, take the value, then unlock it.

I don't know if this technique is wise or if you have something else in mind i would be willing to apply it.

thank you all!

 

[vbkris]

the technique i use is create a column called 'use', whenever someone is using a record update it to some value, now u will know whehter the record is free or not by reading the 'use' column value.

Known is handfull, Unknown is worldfull

 

[ultimatewilliam]

i also thought of that but the problem will come up when the program crashes or terminated abnormally like power failure, then you won't have the chance tochange the value and free up the record. If you have any work around on this, thanks in advance.

 

[vbkris]

interesting, now u have me thinking real hard, i will try to come up with the solution...

Known is handfull, Unknown is worldfull

 

[DavidJWhite]

I had to deal with this problem, and I used the same method as vbkris.

I identified that the system could crash and leave the records in a "locked" state, so ...

I allowed the DBA to run a hidden function to clear all the locking fields - note that no one else can be using the Db at this time.

Hope this helps

 

[DavidJWhite]

Forgot to say above . . .

I held the locking field in a separate table, 1-1 relationship with same primary key, to avoid propagating any additional locking problems.

 

[zemp]

You can also control record locking through the .locktype property of the ADO recordset object.

Thanks and Good Luck!

zemp

 

[CajunCenturion]

The approach proposed by vbkris and supported by DavidJWhite requires that a test and set operation can be done without interruption.

Suppose that you have this "use" column on a shared database and both vbkris and DavidJWhite want to secure a database lock. What happens in the following scenario?

At time t=0 - vbkris gets "use" status
At time t=1 - vbkris determines lock is free
              David gets "use" status
At time t=2 - vbkris set "use" to locked.
              But since David got the status before the lock was set, David all determines that the lock is free and proceeds.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[ultimatewilliam]

the .locktype property of the ADO recordset doesn't seem to work on me.

 

[CCLINT]

>the .locktype property of the ADO recordset doesn't seem to work on me.

You need to use a server side cursor and set the lock type to pessimistic.

Once done, when you open the recordset and move to a record, then the lock will get placed as soon as you start editing the record.

Therefore, you can do this:

rsADO.Fields("SomeField&quot.Value = rsADO.Fields("SomeField&quot.Value

And the record will be locked (actually the page, unless you specify otherwise) until you use rsADO.Update or rsADO.CancelUpdate.

 

[CCLINT]

I must make the comment that I do not consider this type of locking good practice though.

I would rather let two concurrent users edit the same record as desired, and when the second who opened the record after the first user, and the first user has since edited and saved the record, and the second user then edits and tries to save, that either the record changes either just gets ironed over, or better, the second user is informed that the record data has changed since the last query, and go from there, either letting them see the changes and decide whether to still save changes, or whatever.

 

[ultimatewilliam]

ok, i will try both ways but with regards to letting two or more users edit the same record concurrently, it would be very frustrating if he finally realize that he could not save the record especially on huge data entry. Anyway i guess i should choose whatever technique should be applied on different situation. thanks!

 

[CCLINT]

>it would be very frustrating if he finally realize that he could not save the record

You can allow them to still save the record...just post a warning and let them decide if they want to review the changes made since, or go ahead and save anyways.

 

[ultimatewilliam]

ok.