Multi User Database with Record Locking

[ben.johnston]

Hello,

I have an Access Database that multiple people use at any given time, the database is split between a frontend and backend, the backend being an ODBC MySQL linked table (Not the traditional Access FE and Access BE).

The issue I am facing is that since there are 3 or 4 people using it at any given time there are record collisions whenever someone opens up a record at the same time as someone else, we do have our own record locking implementation but that only works when there is a delay between people attempting to open a record.

My understanding as to why this happens is Access loads the remote linked table into memory and does whatever it needs to do with it locally and at some point updates the remote table with any changes that have been made which is where the issue occurs. Everyone loves race conditions!

By the time the record collision has happened it is already open and being worked on by two different people who don't know it has occurred until they attempt to save or move on which is when Access realises what has happened

I am running out of ideas on how to combat this, my next solution is to move the record locking logic out to a web API and have the VBA code query the response from the web API, which would always use the most recent data instead of a cached local copy.

Any solutions to combat this would be greatly appreciated

Many thanks,
Ben

 

[jdraw]

You may get some insight from this Implementing Queue-based applications article by Philipp Stiefel.

 

[ben.johnston]

We have already implemented the first part of that article, not quite the same but functionally similar

'Need to filter to the top record first and lock it
DoCmd.OpenQuery "V3_Day1_OOH_Call1_select" 'log your name against the best unlocked record
DoCmd.SetWarnings True

'Open script and now when we do its just for the locked record
DoCmd.OpenForm "Script", , "qryScript_select_locked"

UPDATE tbldata SET locked = [forms]![frmMain]![cboEmpName]
WHERE id = (SELECT V3_Day1_OO_Call1.ID
FROM V3_Day1_OO_Call1) and (locked is null or locked = "");

"V3_Day1_OOH_Call1_select" is a bit of a misleading name its an update query that sets the lock name and time on a record that doesn't already have one, then qryScript_select_locked is just select whatever record where lock name = whatever the user has selected.

This is what confuses me the article you mentioned says that Access serialises the data on write/update but that doesn't seem to be whats happening in this case, multiple people will acquire the lock for the same record despite it being physically impossible for a record to have more than one user locking it at any given time.

 

[jdraw]

Ben,
I am not a developer and do not have any sort of multi-user testing.
Have you determined that the Lock is acquired before attempting changes to a record?
Can you/have you checked/verified that the same record is locked by more than 1 user at 1 time?

Perhaps one of the active developers will offer some advice.
I know Philipp (sonic8) is online from time to time.

 

[sonic8]

This is what confuses me the article you mentioned says that Access serialises the data on write/update but that doesn't seem to be whats happening in this case, multiple people will acquire the lock for the same record despite it being physically impossible for a record to have more than one user locking it at any given time.

Any database engine must serialize the query execution to lock the record.
However, with ODBC Linked tables, the ACE-Engine, which is not responsible to execute the actual query to the stored data in this case, may split a query that it deems too complex into several queries and pass them individually to the ODBC backend.
Although not explicitly for update queries, I describe this mechanism in: ODBC Linked Tables – Mechanisms and Performance
This can be confirmed or disproved for each case by an ODBC trace or server side query log.

Splitting the query would be fatal to the logic of the record locking as the sub query may return a record id that is beeing locked by another user at the same time. A immediate, but subsequent, update would then just overwrite the lock info.
Options to prevent this:
1.) Use a Pass-Through-Query to lock the record.
2.) Explicitly include the check for Locked in the main (update) query.

Example for 2.):

UPDATE tbldata
SET locked = [forms]![frmMain]![cboEmpName]
WHERE (locked is null or locked = "")
AND id = (SELECT V3_Day1_OO_Call1.ID
            FROM V3_Day1_OO_Call1) and (locked is null or locked = "");

(Disclosure: I'm the author of the Queue Based Applications article)

 

[LarryE]

Hello,

I have an Access Database that multiple people use at any given time, the database is split between a frontend and backend, the backend being an ODBC MySQL linked table (Not the traditional Access FE and Access BE).

The issue I am facing is that since there are 3 or 4 people using it at any given time there are record collisions whenever someone opens up a record at the same time as someone else, we do have our own record locking implementation but that only works when there is a delay between people attempting to open a record.

My understanding as to why this happens is Access loads the remote linked table into memory and does whatever it needs to do with it locally and at some point updates the remote table with any changes that have been made which is where the issue occurs. Everyone loves race conditions!

By the time the record collision has happened it is already open and being worked on by two different people who don't know it has occurred until they attempt to save or move on which is when Access realises what has happened

I am running out of ideas on how to combat this, my next solution is to move the record locking logic out to a web API and have the VBA code query the response from the web API, which would always use the most recent data instead of a cached local copy.

Any solutions to combat this would be greatly appreciated

Many thanks,
Ben

It's my understanding that every user should have their own copy of the FE on their own computer. Is that the case here? They are not sharing a single FE file are they?

 

[ben.johnston]

1.) Use a Pass-Through-Query to lock the record.
2.) Explicitly include the check for Locked in the main (update) query.

I've implemented #2, will see how it goes. If it doesn't work for whatever reason I will try #1 and report back.
Thanks @sonic8 and @jdraw for your help

It's my understanding that every user should have their own copy of the FE on their own computer. Is that the case here? They are not sharing a single FE file are they?

Each user has a copy of the frontend DB on their local machine, they never use the "main" accde file.

 

[theDBguy]

@ben.johnston

Hi. Welcome to AWF!

 

[sonic8]

It's my understanding that every user should have their own copy of the FE on their own computer. Is that the case here? They are not sharing a single FE file are they?

The general recommendation is correct.
Nonetheless, basic transaction isolation *must* work even when a file is shared between multiple users.

 

[The_Doc_Man]

This is a "divide and conquer" situation where you divide up the steps in a way that does not assume availability status.

Premise: Person X wants to update the designated record.
1. Query the record for being marked as "in use" BEFORE attempting to lock.
1.1 If it is in use by anyone else except X, exit with "NO ACCESS" status.
1.2 If it is not in use by anyone, proceed.
1.3 If it is already in use by X (abnormal result), proceed and skip to step 4.
2. Update the record's "in use" marker with X. DO NOTHING ELSE in this step.
3. Query the record for now being marked as "in use" SPECIFICALLY TO X.
3.1 If it is now in use by someone else, you had a lock collision; X cannot touch it.
3.2 If it is still not in use (abnormal result), return to step 1.
3.3 If it is marked as "in use" by X, proceed.
4. The lock is set.
4.1 X works with the record.
4.2 AS A SEPARATE STEP: Mark this record as not in use.

Abnormal result #1.3 occurs if X attempted to get the record but somehow didn't originally think that succeeded. If X owns the record, X can work with the record - but it indicates some sloppiness in the locking.

Abnormal result #3.2 occurs if the locking process is somehow sloppy also.

The idea of dividing this up is that there is a "hole" in your lock sequence. It's called a "race" condition and occurs when X and Y both execute that update at the same time. One of them will come in 2nd and the last one to write to the lock wins. (The first one gets overwritten.) But your lock luck runs out if your next step doesn't verify access was granted before attempting to do the work as a separate determination. The secret of winning a "race" is to NEVER assume you won. Whether you would win or not depends on the specific implementation of SQL and the mode of locking chosen for the UPDATE query.

 

[cheekybuddha]

I have an Access Database that multiple people use at any given time, the database is split between a frontend and backend

Please confirm whether or not your users each have their own copy of the frontend, and are not all just opening the same copy (eg from a network share)

 

[sonic8]

Please confirm whether or not your users each have their own copy of the frontend, [...]

@cheekybuddha, Ben confirmed this already:

Each user has a copy of the frontend DB on their local machine, they never use the "main" accde file.

And also, it is not really relevant for the question asked.

 

[cheekybuddha]

Thanks, Philipp, I missed that

 

[gemma-the-husky]

Hello,

I have an Access Database that multiple people use at any given time, the database is split between a frontend and backend, the backend being an ODBC MySQL linked table (Not the traditional Access FE and Access BE).

The issue I am facing is that since there are 3 or 4 people using it at any given time there are record collisions whenever someone opens up a record at the same time as someone else, we do have our own record locking implementation but that only works when there is a delay between people attempting to open a record.

My understanding as to why this happens is Access loads the remote linked table into memory and does whatever it needs to do with it locally and at some point updates the remote table with any changes that have been made which is where the issue occurs. Everyone loves race conditions!

By the time the record collision has happened it is already open and being worked on by two different people who don't know it has occurred until they attempt to save or move on which is when Access realises what has happened

I am running out of ideas on how to combat this, my next solution is to move the record locking logic out to a web API and have the VBA code query the response from the web API, which would always use the most recent data instead of a cached local copy.

Any solutions to combat this would be greatly appreciated

Many thanks,
Ben

You can't possibly have multiple users opening the same record at the same time.

You shouldn't really need any record locking. Access uses a pseudo record locking strategy of "optimistic" locking, and that has been adequate for everything I do.

Why do you actually have multiple users editing the same record? I can understand it may happen but it ought to be a pretty rare event anyway.

Maybe your users are working with a record for an extended period of time without saving the changes, which permits multiple users to edit the same record. If so you might want to look at your processes.

Adding record locking is often problematic, as you most likely need to include a strategy to release the lock in certain cases, and undo any putative changes.

 

[The_Doc_Man]

Dave, the problem isn't that Access has "optimistic" locking. It is that this is an ODBC case where we don't know the lock settings on the true back end, but it isn't an Access back end. ben.johnson reports it as a MySQL database. I would agree with you in the "pure Access FE/BE" case, but this is not that case. Which is also why I hedged a bit in my earlier comments.

It also doesn't matter that he has multiple people editing the same record because he is actually trying to prevent that. That's the point of his post. His complaint is that improper or inadequate locking is going on.

 

[ben.johnston]

You can't possibly have multiple users opening the same record at the same time.

The issue is that no built in locking is occurring which isn't really the fault of ODBC or Access. The data isn't stored in an Access DB, the Access DB we have is purely a front end to work on the data. MySQL will only lock the record when it is being actively queried, not when someone has it open in Access.

Why do you actually have multiple users editing the same record? I can understand it may happen but it ought to be a pretty rare event anyway.

Its a queue based system, records go in automatically, users click a button which runs some queries to select the most appropriate record based on a set of rules we have and then opens a form to allow the user to work on the record. The reason multiple users are working on the same record at the same time is just a common race condition, since Access has its own local representation of the data and has its own query splitting behaviour each copy of the Frontend DB is never going to be fully in synch with the upstream table.

The issue as far as I understand it is down to Access/ODBC driver specific implementation details about how it operates over the data

 

[ebs17]

and then opens a form to allow the user to work on the record.

Does it help if you check whether the record can be written without errors by locking it before the actual editing = start of the Recordset.Edit?
Approximately

' focus on the desired record
With Me.Recordset
   .Edit
   .Fields("xy") = .Fields("xy")
   .Update

   ' if no error
   .Edit
   ' actual input

End With

The idea is that editing is tested by overwriting a field xy, which always has content, with itself. This should fail if a current lock on this record is detected.

 

[The_Doc_Man]

Its a queue based system, records go in automatically, users click a button which runs some queries to select the most appropriate record based on a set of rules we have and then opens a form to allow the user to work on the record.

My closest personal experience with this kind of system is a "help desk" where a Tier I operator takes a call, takes down the data, and if that operator cannot immediately handle it, it gets put in the queue for the Tier II or higher level support. I was Tier III in this situation, handling those calls involving the customer's O/S account or file permissions. The rule in the Help Desk software was that until some "master" record for that call was updated to show that YOU owned the call (for the moment), you could not even touch the record other than to determine assignment. But in this case, "record locking" becomes ambiguous. I'm not saying this is a Help Desk problem, but that's the closest experience I have with what I am seeing from ben.johnson's description.

In a normal "free-for-all" record protection scheme, the O/S and the DB app cooperate to be sure that two processes don't update the same record at the same time. O/S Locking is the usual scheme for this, with the DB app using the O/S's Lock API to take out "soft" O/S locks... soft because they are not related to or directly used by the file system. While the DB can certainly take out locks, it normally only deals with transitory situations, the moment when a record is being updated and it is a transaction-level operation - or something similar.

However, in a "strict audit-level accountability" system such as we used, the O/S and DB aren't enough. In this case, we had to add on to our locking so that the app ALSO had a level of record-level locking that was honored within the app itself. The lock you wish to take out in this case is more of a long-term lock that puts orange cones around the work area so nobody else strays into it. This lock needs to last through several query steps, probably mixed SELECT-class operations and action-class operations.

IF the DB includes the option to take out explicit longer-term locks on a record, then whatever mechanism it uses would be OK. However, if there is no such mechanism or if something about that mechanism is unacceptable, then you have to "roll your own" locking system. Which was the point of my earlier long post that detailed the steps of taking out a help-desk type of locking and queuing scheme.

 

[gemma-the-husky]

The issue is that no built in locking is occurring which isn't really the fault of ODBC or Access. The data isn't stored in an Access DB, the Access DB we have is purely a front end to work on the data. MySQL will only lock the record when it is being actively queried, not when someone has it open in Access.


Its a queue based system, records go in automatically, users click a button which runs some queries to select the most appropriate record based on a set of rules we have and then opens a form to allow the user to work on the record. The reason multiple users are working on the same record at the same time is just a common race condition, since Access has its own local representation of the data and has its own query splitting behaviour each copy of the Frontend DB is never going to be fully in synch with the upstream table.

The issue as far as I understand it is down to Access/ODBC driver specific implementation details about how it operates over the data

But if you actively lock records when a user reads a record you might prevent other users reading the record at all. If the first user doesn't want to edit the record at all, you can end up getting a worse performance (often much worse) especially if the first user decides to go to lunch or go home, and the record is permanently locked. That's why the access default of optimistic locking (ie no locking) works so well. What it does is re-reads the record before writing the change, and only reports an issue if the record has been changed by another user. Then the user can reload the record and try again. It's far easier than programming an active pessimistic locking strategy.

I'm not 100% sure if it works the same with a non Access back end, but I don't see why it wouldn't.

I still don't really understand why multiple users need to edit the same record simultaneously. That's pretty unusual in most systems.

In an accounting system for instance a user posting invoices might need to add a new invoice to the invoice table (which shouldn't cause any locking issues), and then maybe update the customer table to show the new outstanding balance. That write should be virtually instantaneous and not given rise to a locking collision either. Access will.actially attempt that write multiple times before reporting a failure. So it does come back to understanding why you need an existing record to be permanently locked for an extended period, and maybe changing your system so that lock isn't needed.

 

[The_Doc_Man]

Dave, I agree with you but ben.johnson says something here gives him trouble and he thinks it is a "preventing multiple access" problem - which is a locking problem of one kind or another. Until we get it understood or fully resolved, we have to consider everything is on the table.