UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Locking Behavior    
Locking Behavior

Access supports different level and granularity of locking on various objects, which can lead to complex and nuanced effect. Understanding the locking behavior will help us design applications that meets the requirement with minimum of lock contention, deadlocks or conflicts.


Contents

File Level Locking

File level locking refers to how people can open the Access file itself and is quite straightforward. We can either open an Access file as Shared or Exclusive. Typically, in a split database, this is an non-issue because everyone has their own copy of front-end and are sharing back-end. Thus, exclusive locking usually makes sense in context of administrating a front-end rather than a typical use scenario


Object Level Locking

Queries, forms, and certain controls such as ComboBox and ListBox can specify a certain locking behavior. By default, it inherits from the Access' options but can be changed anytime by the user. Generally, the default Access installation will have objects default to a setting of "No Locks" with "Record-level locking" enabled. Because a form or control can use a query, we also need to consider the combined behavior if the locking specified are different between the objects. Typically, the most restrictive behavior will win out.

Locking Options

There are two different locking methods; Optimistic and Pessimistic. Note that the wording as shown on the Access Options dialog does not correspond to the wording used in VBA. "No Locks" corresponds to Optimistic Locking and "Edited Records" to Pessimistic Locking. There is also "All Records" which does not have a direct analogy but can be approximated in VBA by using dbDenyWrite option for the DAO Recordset.

With an optimistic locking, there is no lock requested until a save upon the dirty record is attempted. On the other hand, pessimistic locking locks the record as soon as the record becomes dirty. Pessimistic locking may be desirable for scenarios where there is high probability of multiple users writing to the same records, however it comes with a significant caveat: If a user dirtied a record and stepped out for the coffee break, the other users would be at mercy of the coffee slinging user because only that user can release the lock on the dirty record. Furthermore, there is a cost to the application performance where pessimistic locking is used. Because it is not typical that users would be writing to the same records, optimistic locking is probably the most widely used setting.

Locking Granularity

Since Jet 4.0, the engine has supported record-level locking. Prior to this version, there was only one granularity: page level locking. As noted, record level locking is on default. However, it should be cautioned that it is not available in all settings. For example, running an Action Query will place a page level lock. This is one of reason why some novice developers run into Write Conflict dialog when they run an action query against same table that's bound by a form they're editing even though they may be editing a different record that happens to be on the same page.

Transactions

The database engine also supports ACID-compliant transactions which can have additional effects on the locking behaviors.

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 6,533 times.  This page was last modified 11:40, 21 July 2012 by Walter Niesz. Contributions by Jack Leach, pacala_ba, rslheath and BananaRepublic  Disclaimers