Access: Locking and Transactions

Access: Locking and Transactions

(Detail can be found in Access 97 Secrets by Prague, Amo and Foxall, IDG Books)

General Issues.

All Access locks are recorded in a file, related to the main <database.mdb file, called <database.ldb.

Access uses page locks (1 page = 2k bytes). Records can span more than one page if they are large enough or can be packed 1 or more to a page if small enough. Be careful with small records in highly concurrently accessed tables.

Large things, such as memo fields and OLE objects, are not stored directly in the data pages, rather the page contains a pointer to the object. Beware - you can pack a lot of pointers onto a 2k page - if you lock one record you lock all the records on the page…. think about it!

If user A takes out a lock on a record then only A can write to the record while that lock is held. However, any other users can read the locked record.

Access Locking Strategies.

There are two locking strategies available Optimistic and Pessimistic (I'll leave you to work out where the names come from…). The Access default is to use Pessimistic locking.

Pessimistic Locking (see course notes - this is common locking behaviour)

Locks are held from the moment you show interest in a record (i.e. retrieve it for potential editing)

Locks are released after the update is performed or abandoned (or if the lock is held inside a transaction then it is released when the Commit or Rollback occurs)

Note: long transactions, or opening an editable form then going to lunch without closing it, will lock the appropriate records until the end of the transaction or until you come back from lunch.

Optimistic Locking

Locks are held ONLY during the time the actual update is taking place.

This prevents users locking records for long periods and reduces lock conflicts.

HOWEVER

race conditions can occur

A	B
a=10	a=10
a=a+10	a=a+20

WHO UPDATES FIRST?

ONLY use Optimistic locking where the users are Very Very unlikely to get simultaneous access to the same records.

Setting Multi-User Locking Strategies

Several locking parameters can be set as defaults at the Database Level.

In the Tools = Options = Advanced form we can set defaults for the entire database.

Default Record Locking

No Locks	== Optimistic Locking
All Records	if you open a form or datasheet and start to edit the records shown then lock everything on the form and any underlying tables (Eager Pessimistic?)
Edited Record	== Pessimistic Locking

Default Open Mode

Shared	can be used simultaneously by more than one user
Exclusive	once opened another user cannot open it simultaneously

(NB the users ability to change this Open Mode can be controlled by the Users Permissions in a multi-user environment)

Default Retry Parameters

Number of Update Retries, Refresh Interval and Update Retry Interval

controls the frequency and persistence of a process which encounters a lock in retrying the locked page before returning with an error message.

Overriding the Default Record Locking for a specific form.

On an individual form you can override the default database record locking strategy. Change the Record Locks property for the Form to do this.

Locking Limits.

There is a registry entry for the Access Database - MaxLocksPerFile which specifies the number of locks which can be taken out on a specific file. The default is set to 9,500.

BE AWARE if the database engine has a queued transaction which requires more locks than currently available it will COMMIT all pending transactions in order to release the locks they hold - I do hope that there is a way of overriding this somewhere, it seems…. dangerous!

You can amend the registry entry to increase (or decrease) the default value - which you may do for efficiency purposes depending on the amount of concurrent interaction you expect in your application.

Locking and Visual Basic for Applications (VBA).

As you would expect, VBA allows lots of low level control over the choice of locking strategies and the opportunity of overriding the default settings. When opening a record set you can specify the

LockEdits option setting it to TRUE for pessimistic locking, FALSE for optimistic locking.

If you are using optimistic locking and you issue the Edit method and, before you perform the update method, some other user updates the record then you can get a trappable error to inform you that the update you performed was/is unsafe - I'm not sure what it does with the attempt to update at that point.

Transactions and VBA.

There are methods for WorkSpace objects which allow for Transaction management at the VBA level. A workspace is the "local copy of the things being manipulated by you".

WorkSpace.BeginTrans, WorkSpace.CommitTrans and WorkSpace.Rollback all have their expected behaviour.

Within a transaction the Pessimistic and Optimistic locking strategies have the usual meanings when an object is opened for editing and/or updating. However, locks taken inside the transaction are held until the commit or rollback is performed. So in an optimistic strategy transaction the lock is taken just as the update is started then released at the end of the transaction. With the pessimistic strategy the lock is taken when the intention to edit the record is noted.

(K Waugh, Feb 98)

Comments to k.g.waugh@open.ac.uk

Back to my home page......