ACC2000: Record-Level Locking Does Not Appear to Work

In a multiuser environment, two or more users are allowed to edit the same record at the same time, even though record-level locking has been turned on.

This behavior is by design.

By default, Access 2000 uses optimistic locking (that is, Access sets the Default record locking option to No locks). This allows two or more users to edit the same record at the same time. You must change this default to pessimistic locking (that is, set the Default record locking option to Edited record). Optimistic locking only enforces a lock when one user tries to save a change after another user has already changed the same record. Optimistic locking overrides record-level locking, even if the Open databases using record-level locking checkbox has been selected. Record-level locking is invoked when pessimistic (that is, the Default record locking option is set to Edited record) is selected.

When you use page-level locking (rather than record-level locking), multiple records can become locked while a user is editing a single record. In other words, all records that fit within the 4 kilobyte (4096 byte) page will be locked, even though only one of the records within that page is being edited.

With record-level locking, however, if a user edits any one record in a table, other users are able to edit any other record, except that one. But this is only true when both pessimistic and record-level locking has been set within the Microsoft Access Options dialog box.

The following steps show how record-level locking appears to not be working.

Steps to Reproduce Behavior

1. One the same computer, start Microsoft Access 2000 twice, and open the sample database Northwind.mdb in each instance of Access.
2. In the first instance of Access 2000, on to the Tools menu, click Options, and then click the Advanced tab.
3. Make sure the Open databases using record-level locking check box is selected, make sure Default record locking is set to No locks (optimistic locking), and then click OK.
4. Repeat steps 2 and 3 for the second instance of Access.
   
   NOTE: If you had to click to select the Open databases using record-level locking check box in either instance, close that instance, and then reopen the sample database Northwind.mdb.
5. Open the Categories table in both instances.
6. In the first instance, for CategoryID 4, change the Description box from Cheeses to Cheeses and Eggs. Do not save the change.
7. Edit the same record in the second instance, but change the value to Cheeses and Milk. Note that although record-level locking is invoked, you are allowed to edit the value in both instances. If you now save the change in the second instance, the first instance will generate the following Write Conflict error if you try to save its change:
   This record has been changed by another user since you started
   editing it. If you save the record, you will overwrite the changes
   the other user made.
   
   Copying the changes to the clipboard will let you look at the values
   the other user entered, and then paste your changes back in if you
   decide to make changes.

For more information about record locking, click Microsoft Access Help on the Help menu, type about choosing a record-locking strategy in an access database in a multiuser environment in the Office Assistant or the Answer Wizard, and then click Search to view the topic.