Solved

Record Locks

Posted on 2014-03-09
14
2,043 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
 
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 34

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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 34

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now