Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Record Locks

Posted on 2014-03-09
Medium Priority
Last Modified: 2014-03-16
I have noticed a form property called Record Locks that can be set to "No Locks" In reality does this have any effect when in a multi user environment?

Question by:DatabaseDek
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +2
LVL 85
ID: 39915750
Yes, they can have an impact.

For example, if you select the "All Records" lock method, then Access will lock all the records in the form's recordsource (including any related tables). It's very restrictive, and is generally not used for day-to-day operations.

"Edited Records" locks the record the user is editing, as well as any other records on the same "page" as that record. A "page" is the location on disk where the data is stored, and depending on various settings, could encompass several records other than the one you're currently editing.

"No Locks" essentially means that any user is free to edit any record, but if two users edit the same record you'll get the "Another user has changed this record blah blah" message.

Generally speaking you'd use the Edited Records or No Locks setting, depending on how your users interact with the system.

Of course, if you're working with data stored in other databases (like a SQL Server database, for example) then the locking may be determined by that database.

Note too that there are record lock settings in Access Options - Client Settings - Advanced, as well as other settings that can impact multiuser databases.
LVL 58

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 total points
ID: 39915843

The record locks setting controls when a lock is placed.   With "Edited Records", it's placed as soon as the user makes the record dirty (when they make the first change) and is not released until the record is updated.

"No Locks" is a misnomer; it's means no edited record locks, not that there is no locking going on.   In this case, the lock is placed just before the record is updated, then released after that.

These equate to pessimistic and optimistic  locking respectively.

With the first, two users can't over-write each others changes or loose edits they have done, but it can lead to concurrency issues (who can get at what when).

Locks are placed either at the record level or page level depending on how you open the database.

 In JET, record level locking was an add-on, so it doesn't work all that well.

 But using edited records long with page level locking can tie up a lot of records.


Author Comment

ID: 39916326
That's very clear thank you both.

If you wanted the least hassle in a multi user environment would you suffer the "Another user has changed this record" or would you suffer the potential problem of another user starting to edit a record and before saving decides to go to lunch locking everybody out of that record?
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

LVL 85
ID: 39916526
"Least hassle" would be no locks - as JimD wrote, this is often known as optimistic locking, and is generally the one that causes the least interference with users.

Just be aware that "no locks" doesn't really mean "no lock" ...
LVL 58
ID: 39917299
um, I take the opposite view; I'd use Edited Record Locking (pessimistic) with Record Level locking.

However I would not rely on the record level locking on JET.  What I do typically is leave it set for Edited Record, and then if I have concurrency issues, I pad the records out past half a page.

 Since JET does not allow a record to span a page, this ensures one record per page and thus I have "record level locking" with the added benefit that I can enable/disable this on a table by table basis.

  Generally, I find that I need to do this on only a couple of the main tables in order to avoid problems.

  You can approach it from the other end as well though, which is to use no locks and see if you get a lot of over write conflict dialogs.  Certainly no locks is less overhead on the DB's part.

LVL 39

Assisted Solution

PatHartman earned 1000 total points
ID: 39917614
I use optimistic locking (no locks).  I seem to have users who start an edit and then go to lunch or take a phone call leaving the record (and others in the page) locked until they get back.

There is no good way to release locks once they are acquired.  Nor with ACE is there a good way to find out who is the culprit.  You have to get everyone to exit the application and then delete the .lccdb or. ldb file associated with the BE.  If you can't delete the lock file (which happens way too often), you will need to reboot the server to get rid of the DOS file locks and then you can delete it.

Of course, most of my applications don't have heavy data entry users and there is little chance of real contention for the same record.  To circumvent this problem in one app where there would have been contention, I implemented my own "lock" process.  The users were working off a to do list so it was possible that multiple users would select the "next" item.  So, I added a lockedBy and LockedDT field.  When a user selected a record, I updated the lock columns (which were displayed on the to do list) so that others could see that someone was already working on the record.  I also displayed them on the form and locked it to make it obvious that another update was in process.  When the user left the record, I removed the lock information to free up the record.  I also included update queries that would "free" all records to handle problems caused by lost network connections and power outages.
LVL 58
ID: 39917628
<<If you can't delete the lock file (which happens way too often), you will need to reboot the server to get rid of the DOS file locks and then you can delete it.>>

 Actually, you don't need to reboot.  You just need to close the open file handle.

<<I implemented my own "lock" process. >>

  That's another approach as well.  In fact I wrote up an article on it:

  That often is a better approach because beside the record updating itself, it is sometimes a process based one like the example Pat gave.

LVL 85
ID: 39917656
I guess it depends on what is meant by "less hassle" ...
LVL 58
ID: 39917732
<<I guess it depends on what is meant by "less hassle" ... >>

 Excellent point....any of the approaches carries baggage.   It's one of those things where there really is no right or wrong answer.

  It's good just to know the various techniques and then decide what's best for the situation you have.

LVL 75
ID: 39918253
Unless you implement the ADO technique described Here ... Jet 4.0 Row-Level Locking Is **Not** Available with DAO 3.60 ... you are not going to get true Row Level Locking in a multi-user database (with front end and back end).  

I've been using this at work in several multi-user app for a couple of years, and it seems to work quite well, especially getting around one user locking a 'page' of records - and user working in a 'nearby' record getting the 'Record locked by another user ...." error.


Author Comment

ID: 39931420
To eliviate the "I'm in the middle of editing a record but the dinner bell has gone so I'm off!" people" would it not be a good idea to simply have a refresh on a timer?

Excellent articles, by the way, Jim
LVL 58
ID: 39931552
<<To eliviate the "I'm in the middle of editing a record but the dinner bell has gone so I'm off!" people" would it not be a good idea to simply have a refresh on a timer?>>

  Many apps do have some type of in-activity timer and undo a record if something is in progress for just that reason (or log out the user).

<<Excellent articles, by the way, Jim >>

 Wish I could find the time to write more.

LVL 39

Expert Comment

ID: 39931571
If you elect to use the timer event you will need to be extremely careful during development.  I have all my timers set to turn off if I log in as myself.  So, when I am testing the timer functionality, I must log in under a different ID.

The reason I do this is because when I am in the process of making changes, I do not want to put any object into design view and modify it while there is a timer event active on another open form.  So, you either have to be completely disciplined and shut down all objects except the one you are editing or make sure there is no timer event running while you are editing.  Corruption is the almost certain result if you make design changes while a timer event is active.

Author Closing Comment

ID: 39932640
Great stuff as usual.

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question