Solved

Record Locks

Posted on 2014-03-09
14
2,149 Views
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?

Derek
0
Comment
Question by:DatabaseDek
  • 5
  • 3
  • 3
  • +2
14 Comments
 
LVL 84
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.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 39915843
Absolutely

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.

Jim.
0
 

Author Comment

by:DatabaseDek
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?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 84
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" ...
0
 
LVL 57
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.

Jim.
0
 
LVL 35

Assisted Solution

by:PatHartman
PatHartman earned 250 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.
0
 
LVL 57
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:

http://www.experts-exchange.com/Database/MS_Access/A_5328-Resource-locking-in-your-applications.html

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

Jim.
0
 
LVL 84
ID: 39917656
I guess it depends on what is meant by "less hassle" ...
0
 
LVL 57
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.

Jim.
0
 
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.

mx
0
 

Author Comment

by:DatabaseDek
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
0
 
LVL 57
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.

Jim.
0
 
LVL 35

Expert Comment

by:PatHartman
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.
0
 

Author Closing Comment

by:DatabaseDek
ID: 39932640
Great stuff as usual.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

773 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