Multi User Database with Record Locking

[gemma-the-husky]

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.

He says he has his own record locking implementation. I presume that's causing the problem.

 

[Pat Hartman]

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!

You have 100% control over that. Why is everyone selecting entire tables and locking them with bulk updates?

Are you using bound forms? If you are using bound forms and the forms are bound to queries that select a SINGLE record for update, it would be extremely rare that users would be conflicting with each other.

Please describe how the forms/updates are designed.

Access has no control over the locking of a RDBMS. The lock options you see in Access apply ONLY to Jet and ACE databases, NEVER to SQL Server or any other RDBMS.

You need to turn off all your locking schemes and figure out what you are doing to cause the conflicts? Are you using queries that update multiple tables, this can lead to a deadly embrace where user a locks tblA and user b locks tblB and that prevents user a from also locking tblB and so his multi-table update can't happen.

 

[ben.johnston]

You have 100% control over that. Why is everyone selecting entire tables and locking them with bulk updates?

Are you using bound forms? If you are using bound forms and the forms are bound to queries that select a SINGLE record for update, it would be extremely rare that users would be conflicting with each other.

Please describe how the forms/updates are designed.

Access has no control over the locking of a RDBMS. The lock options you see in Access apply ONLY to Jet and ACE databases, NEVER to SQL Server or any other RDBMS.

You need to turn off all your locking schemes and figure out what you are doing to cause the conflicts? Are you using queries that update multiple tables, this can lead to a deadly embrace where user a locks tblA and user b locks tblB and that prevents user a from also locking tblB and so his multi-table update can't happen.

The database only operates on a single record at a time, all queries operate on a single record at a time. The issue isn't with row-level locking on the table itself since that only applies for the duration that you are reading/writing data.

The form uses a query that is effectively `select * from tbldata where lock = username` which only returns one record as the user can only have one lock at a time.

The issue with the custom locking behaviour is that when you update a record on Access it doesn't immediately replicate to all other copies of the frontend DB which allows the database to lock a record someone else already owns.

There is only one table being used and updated, no multi-table locking involved.

The full flow whena user opens a record is:
- Clear any existing locks for this user
- Select one record that doesnt already have a lock and matches a list of rules
- Update lock field to current user
- Open form, form query selects a single record where lock = username

There are quite a few steps involved and some of the queries while they aren't massive, do add a none 0 duration to them which is where the issue lies.

 

[ebs17]

If several users rush to work on the same record and then work on it practically at the same time, then you should first check your work organization.
If there is a pile of tasks, not every user has to reach for the same task, but as a first step the developer can ensure that the closest record is a different one for each user. In the following article, this is attempted by making a random selection.

hide a record while it's being edited in a form

Hello everyone! I have this database in order to assign a task to each logged on user. The db is split between be and fe already and everything is on a network drive. Admins import tasks on the main table where there is a 'inUse' boolean column defaulting as false. A query reads a few fields...

www.access-programmers.co.uk

 

[ben.johnston]

If several users rush to work on the same record and then work on it practically at the same time, then you should first check your work organization.

Most of the time this isn't an issue, but as the day goes on the records available get smaller and smaller, but the team size doesn't change. This isn't something that can be changed, definitely not something I have any control over.

If there is a pile of tasks, not every user has to reach for the same task, but as a first step the developer can ensure that the closest record is a different one for each user. In the following article, this is attempted by making a random selection.

Might look into this, the rules around record selection are pretty strict, could potentially randomise the top 10 most applicable record order.

 

[Gasman]

When I worked at a bank, we were allocated a set of records to work on for the day.

 

[ben.johnston]

When I worked at a bank, we were allocated a set of records to work on for the day.

We do that for our other databases, where we have all the records ahead of time. This database receives records in real time and need to be operated on in a specific order.

 

[gemma-the-husky]

We do that for our other databases, where we have all the records ahead of time. This database receives records in real time and need to be operated on in a specific order.

I've already said that it's still not necessarily a record locking issue. Optimistic (no locking) would still work, but a user would just get warned that the record had changed so that he can't save his own edit. The number of real world situations where you need to introduce record locking is vanishingly small.

It would be useful if you could explain the industry, or maybe the task you are trying to manage.

Without actually locking the record, you could simply store a "user id" against a record being worked on, so a second user can't obtain that record. The issue with that is you also need a safe "release lock" process as at some point that will prove necessary.

 

[Pat Hartman]

The issue with the custom locking behaviour is that when you update a record on Access it doesn't immediately replicate to all other copies of the frontend DB which allows the database to lock a record someone else already owns.

But that doesn't matter. In the current event of the form, your code needs to check the lock. Or you can check it in the form's on dirty event. That event runs as soon as the user types a character into any control. So, you check the lock and if it isn't assigned to this user, you undo the update, cancel it and give the user an error message.

You cannot rely on the list being accurate which is what it sounds like you are doing.

I have a similar process in several applications. The users see a task list that shows incomplete items. If the task is already assigned, it shows the person working on it but you can't rely on the users to refresh the list regularly so you can do it for them. Anyone can select a record that someone else is working on if they need to view it but they cannot update it. To update the list, open your edit form in dialog mode. That stops the code in the list form from running further. The user does his thing with the form he just opened and closes it. Control then returns to the line of code that followed the openForm command. just use Me.Requery and that refreshes the list every time the user goes back to it.

Access does refresh the lists automatically but not quickly. You can control the setting to get it to refresh more quickly. The requery that follows the OpenForm is probably good enough assuming the user will immediately select a new record to work on. But if not, then your code in the on dirty event will solve the problem.

 

[The_Doc_Man]

The issue with the custom locking behaviour is that when you update a record on Access it doesn't immediately replicate to all other copies of the frontend DB which allows the database to lock a record someone else already owns.

But this is self-defeating behavior! Your statement implies that locks are private to each user - but locks must be public for testing or they are trash. (Pardon me if that sounds a bit harsh.) The locks HAVE to be shared so that user A can easily see to keep his mitts off the record that user B is currently working. If you are keeping that in front-end files, OF COURSE you have problems. WINDOWS will not let you directly do things to other users on other machines. The ONLY way you can coordinate this is with a common structure that everyone can see.

Your locking information needs to IMMEDIATELY be removed from the FE files and put in a back-end file. IMMEDIATELY. It is perfectly permissible to have more than one back-end file, so you can keep your main data file as-is. Access allows at least 16 files in older versions and I don't know (or care) about files in newer versions. But all you need for this is three files - the FE file, the locking BE file, and the data BE file.

Create a back end file that you will share with others and keep your locking tables there. They will update within milliseconds if they are on a LAN. I don't want this to come across as brutal, but you will NEVER make that front-end locking scheme work right. Make it a shared back-end scheme and once you iron out the linking for the second BE file, that thing should purr like a contented kitten getting a belly rub.