Database locking issues (1 Viewer)

namron

New member
Local time
Today, 21:23
Joined
Mar 10, 2018
Messages
15
Hi

I have a database in which Users keep getting a runtime error stating that records can't be updated because they are locked by another user on the network. Their copy of the FE then freezes. The only way to resolve the issue is to ask the person using the reported PC to log out of their PC or by restarting the server

Locking Error message.jpg

Default record locking is set to Edited Record in the Database options and also on each form.

The error only seems to happen when somebody tries to create a new record in a particular table when somebody else is also creating a new record in the same table. The way the system was working was that a user would click an 'add record' button and the code behind the button included, amongst other things, DoCmd.GoToRecord , , acNewRec. I thought this was the reason for the conflict as it can take quite a while to add the required information for the new record and it appeared the system was locking everybody else out whilst that new record was in progress.

My 'solution' was to use a separate form with unbound controls for people to create a new record. When completed, the 'save' button on the form runs a procedure (created for me by somebody on another forum) using a DAO recordset which appends the data on the form to the relevant table. This worked for a short time but then the users started to get the same locking message.

.The database is properly split with the backend on a shared network drive and a copy of the same front end file on each User's Desktop. Each time they log in a fresh copy of the front end file is copied to their Desktop.

When the database opens the default form is a login form which checks their password and then opens a main menu form if correct. The login form is then hidden and remains open. All users have read/write/modify/delete permissions on the folder containing the back end data file

One thing I've noticed is that the locking laccdb file does not get deleted when the last person closes this database and wondered if that file or the database is corrupted in some way. I've done a compact/repair on both the FE and BE, and I've imported all of the objects into new FE and BE database files but neither has helped.

The users are getting increasingly frustrated as it's happening multiple times each day. The database has no errors in it as far as I can establish, it works perfectly locally. The code all compiles OK.

Can anybody suggest what might be causing these errors and/or how I can stop them?

Thanks
Norman
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:23
Joined
Sep 21, 2011
Messages
14,047
You say 'their copy' ?
Does each user have their 'own' copy installed on their computer?
 

namron

New member
Local time
Today, 21:23
Joined
Mar 10, 2018
Messages
15
You say 'their copy' ?
Does each user have their 'own' copy installed on their computer?
Hi

Each user has a copy of the FE on their Desktop which links to the BE in a shared network folder.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:23
Joined
Feb 28, 2001
Messages
27,001
Should be a quick test. Find a user who is having the locking problem. From that user's PC, open Windows Explorer and look at the folder where the back-end is located. If there is an .LDB file, RIGHT-click it >> properties >> security >> advanced >> effective permissions. The person who wants to use the database has to have the equivalent of MODIFY access on the folder and all files within that folder. Failure to delete the .LDB file sounds like someone's permissions are not correct, perhaps at the folder level.
 

namron

New member
Local time
Today, 21:23
Joined
Mar 10, 2018
Messages
15
Should be a quick test. Find a user who is having the locking problem. From that user's PC, open Windows Explorer and look at the folder where the back-end is located. If there is an .LDB file, RIGHT-click it >> properties >> security >> advanced >> effective permissions. The person who wants to use the database has to have the equivalent of MODIFY access on the folder and all files within that folder. Failure to delete the .LDB file sounds like someone's permissions are not correct, perhaps at the folder level.
Thanks for your input, I did check with the Network Administrator who confirms that all users DO have read/write/modify/delete permissions on that folder. This seems to be confirmed as people don't have issues generally in accessing the database and editing records. It just seems to be an issue with this particular table.
However I'll do a manual check myself as you suggest, it's an accdb file I assume LDB relates to an earlier version of Access?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:23
Joined
Feb 28, 2001
Messages
27,001
No. The .LDB file is the LOCK file that Access opens when the first user of the day launches the DB, and that gets deleted when the last user of the day exits the DB. While it exists, it is what Access uses to track database locks. Windows only provides file-level locks so Access has to "roll its own" block-level locking. Since you are dealing with problems in only one table, the question is "What's up with that table?" In theory, though, it should not matter that two users are updating the same table. Access is made for that. So let's ask a different question. What are the lock settings on your forms and queries? If they are set to Pessimistic locks, there is your problem. If they are set to Optimistic Locks or No Locks then we have to look elsewhere.
 

namron

New member
Local time
Today, 21:23
Joined
Mar 10, 2018
Messages
15
Hi,
I've never seen an .LDB file? The laccdb file seems to serve the same purpose.
The lock settings in the BE File>Options>Advanced are 'Edited Record' and 'Open databases bu using record level locking' is checked. each form also has 'Record Locks' set to 'Edited Record.'

The table in question was originally using a multi-value field. I stopped using this and created a junction table instead to replace that functionality. The original field remained in the table with the multiple entries, although it was no longer used in any queries or forms. Possibly that table is corrupted so I am now going to replace that table with a new one, importing the data from the old table apart from the MVF.
 

Cronk

Registered User.
Local time
Tomorrow, 08:23
Joined
Jul 4, 2013
Messages
2,770
MDB's have LDB's
ACCDB's have LACCDB's
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:23
Joined
Feb 28, 2001
Messages
27,001
You mentioned the LACCDB file, it was just my old habits to call it an LDB. Same file, different version.
 

Cronk

Registered User.
Local time
Tomorrow, 08:23
Joined
Jul 4, 2013
Messages
2,770
Me too, old habits. MDB/ACCDB just Access database files. I've still got some systems running where FE is ACCDB but BE is MDB
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:23
Joined
Sep 12, 2006
Messages
15,614
Personally, I would not lock records at all. It is generally not required, other than in special circumstances, and needs special treatment to deal with the sort of error you describe. eg, in this situation you may need the database to request that one user or the other should unwind their process to remove the lock, which may not be an easy thing to do,

Secondly, locking single records is hard to do. What may happen is that a "page" gets locked, which may well include multiple records, and you are seeing the effect of that. ie - the second user has locked a different record which resides on the same page as your record.

Also, sometimes the culprit is yourself. (ie - a programme error). If the database is written so that you have two forms (or a form and a subform) open to the same record, both of which can be dirty, then the user will unwittingly be the cause of this, although it doesn't sound like it on this occasion.

Are all your users using the same server copy of the front end database. If so, that may be the issue with the new record buffer you describe.
 
Last edited:

namron

New member
Local time
Today, 21:23
Joined
Mar 10, 2018
Messages
15
Hi Guys

I think I've resolved this now - at least there have been no further reports of the 'locked' error appearing since I made the following changes over a week ago:

  • I replaced the suspect table with a new one, importing all of the data from the old table, excluding anything in the previously used multi-value field.
  • I renamed the back end data file and refreshed all of the links from the front end.
  • On the form used to create a new record in the table, I changed the Record Locks property to No Locks.
Not sure which bit did the trick but my guess is the No Locks change on that form.

Thanks for all input.

Norman
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:23
Joined
Feb 19, 2002
Messages
42,976
Changing the record locks property to no locks solved the problem with the locks. But, if the lock file isn't getting deleted when the last person closes the database, then there is a permissions issue that needs fixing. All user s must have CRUD permissions on the folder

"No Locks" is what is called optimistic locking. Access doesn't attempt to lock the record until you save your changes. This keeps the record locked for the minimum amount of time and reduces the potential for contention.

Since most people use autonumbers as their PK's all new records will get added into the last physical record (block, sector, depending on whether this is a Mainframe or a PC) Since all new records are added to the same physical record, you run into contention when new records are being added.
 

namron

New member
Local time
Today, 21:23
Joined
Mar 10, 2018
Messages
15
Changing the record locks property to no locks solved the problem with the locks. But, if the lock file isn't getting deleted when the last person closes the database, then there is a permissions issue that needs fixing. All user s must have CRUD permissions on the folder

"No Locks" is what is called optimistic locking. Access doesn't attempt to lock the record until you save your changes. This keeps the record locked for the minimum amount of time and reduces the potential for contention.

Since most people use autonumbers as their PK's all new records will get added into the last physical record (block, sector, depending on whether this is a Mainframe or a PC) Since all new records are added to the same physical record, you run into contention when new records are being added.
Thanks Pat

The network Administrators inform me that all users have read/write/modify/delete permissions on the folder containing the back end data file. This seems to be borne out by the fact that generally everybody is able to read/create/modify or delete records, it's just been this one particular problem when creating new records in that particular table.

Not sure I understand your last paragraph, the server is a dedicated PC on the network. Are you saying that the problem is likely to arise again at some point? If so are you aware of a way of avoiding it permanently?

Thanks

Norman
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:23
Joined
Feb 19, 2002
Messages
42,976
In the world of comptuters, there is the concept of a logical record and a physical record. A logical record is defined by the columns and their widths in a table or a flat file. Computers do not Read/write from/to disk one record at a time. They work with "physical records" These "physical records used to be variable length when we were working with the main frame and were defined as some number of records. Say 20 logical records = 1 physical record and it was called a block. The internal I/O (input/output) routines worked with blocks. In the PC world, the "block" is usually called a "sector" and it is fixed in size depending on the actual disk drive. A common size used to be 4K so if your records were 1k, then 4 would fit in 1 sector and so four would be read or written at one time. That is why you end up with contention only during new record adds. All new records go into the first "empty" sector so if more than one person was working on an add, there would be a conflict.
 

namron

New member
Local time
Today, 21:23
Joined
Mar 10, 2018
Messages
15
That being the case you would think this issue would be more commonly reported, most people I’ve mentioned it to have never come across it.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:23
Joined
Sep 12, 2006
Messages
15,614
Well when you are talking about locks, in this case the locks are held for a very few clock ticks

User A saves a record
User B saves the same record

User A goes first. Optimistic locking. The database checks to see if the record had changed since User A started the edit and if not, then Access (I imagine) decides it's safe to write, so then locks it, writes it and releases the lock. This process takes the PC a 1/10000 of a second maybe. Then User B goes.

The chance of A and B going at the same time is vanishingly small. In fact both the Access processes will probably attempt the read multiple times before reporting failure, just in case, and User A has ample time to complete his process by the time User B has finished re-read attempts.

This is rather different to a programmer doing this by choosing to lock the table manually perhaps at the wrong point, and keeping the lock for too long, or even worse, keeping multiple locks, leading to a deadlock.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:23
Joined
Feb 19, 2002
Messages
42,976
That being the case you would think this issue would be more commonly reported, most people I’ve mentioned it to have never come across it.
That is because most people don't change the lock settings. They are set for optimistic locking. It is only if you change that setting that you end up with the problem you had.
 

namron

New member
Local time
Today, 21:23
Joined
Mar 10, 2018
Messages
15
Thank you so much for that Pat, that's clear enough for me!
In the process of creating this database I'd been looking for information on lock settings and read in a number of places that the recommended setting in a multi user environment was 'Edited Record' so that's what I went for.
I've had lots of input from people on what could be causing the locking errors when creating new records, including asking me to clarify what my lock settings were, but nobody pointed out that my settings may actually be causing the problem! I thought the cause of the error was much more complicated. I just wish I could have had this answer weeks ago.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:23
Joined
Feb 19, 2002
Messages
42,976
At least you now have a better understanding of what causes the problem:) And, people who read this thread will also so they can avoid the problems caused by modifying the lock property. One other point, if the BE is NOT Jet/ACE, then the setting has no impact. The settings are controlled by the database engine so if you wanted to twiddle with them, you would need to go to SQL Server or whatever RDBMS you are linked to.
 

Users who are viewing this thread

Top Bottom