Unanswered: Considerations for record locking in multiuser MDB backends?
I have little experience with multiuser MDB backends,and have some questions regarding ensuring that a particular holdings update process runs safely.
A transaction is saved through a form, a record is updated or added to the purchase table.
Then the holdings table will be updated.
I have not used transactions/rollback in this case, is that adviseable for this scenario? Would it solve the whole situation? (don't think so..)
The holdings update process use a dynamic recordset.
This update process fetches the transaction data just saved from the purchase table, does some calculations including new average.
What if another user/controller CORRECTS the PREVIOUS purchase right after the update process have fetched the data for the new average calculation, and before that process have saved the calculations back to the holdings table? This will firstly trigger an update of the previous record in the purchase table (ok), then actually perform a recalculation of concurrently calculated averages, namely the record just saved by the first user. I am not sure, but lets say that two computers with significantly differing speed does this, the latter the most speedy. Maybe that process will start later and finish earlier than the former, and then the former will overwrite the changed date with new values that did not take the short-lifed but correct change into account...
Two tables are affected for each update, the purchase table and the holdings table, and only one record in each at any given moment.
Should I (is it possible?) lock the records while the
holdings update process is running, and release it when the data is saved, AND include a check in the purchase save routine that checks for relevant changes before trying to re-edit a record that was just reported to be locked?
Is there a common way to handle or prevent this?
If at all - this all happens in milliseconds I guess, it could happen, even if not very likely. Could be that I am a bit paranoid here, but still, don't like if this is a possible scenario that is not properly handled.
I could make a test of the values of the holdings recordset at the beginning of the routine, and then check for changes just before updating, perhaps.
I am also wondering if these considerations should affect the choice between using a SQL UPDATE command or an RST.update (just now it is set to a SQL update).
One way that does not seem very tempting initially, is to create a Yes/No "tmpLock" field in the underlying tables, setting it to Yes from the ondirty event of the form, and to No on the afterupdate event.
But what if there is a crash on one of the computers, leaving that flag "on"? How to check which records that are locked are set locked by users that does not have the record in question open in a form at the moment..? (in order to quickly/automatically reset only relevant records to No?) Must perhaps also set a username and a precise timestamp to check for this, then it seems to become a bit of an application itself)
A cumbersome workaround, maybe a tad "too creative"? ;-)
Appreciate any suggestions/advice on this.
I am a bit pressed on time, would very much like a hint to if I am totally astray here by tomorrow/Sunday, as I must establish "some" control for this particular db before next week. I have a few weeks time more until this solution will be used by "many" concurrent users, though, but 2-3 people will start using it the coming week.