Implementing a Custom Locking Scheme

You might encounter situations in your multiuser application where the behavior of page locking is not appropriate, and where the use of optimistic locking is not a suitable workaround. In this case, you may want to consider your own custom locking scheme. Implemented through a set of routines that you write, custom locking completely bypasses Microsoft Jet locking. Your code controls locking by identifying when a record should be locked and unlocked.

The most common approach to this technique is to have a semaphore, or lock table, that identifies when a record is locked. The lock table typically identifies records by storing the key value of the record, the lock status (locked or unlocked), and the name of the user who has the record locked.

Implementing a custom locking scheme requires a great deal of design, implementation, and testing time. In many cases, a custom locking scheme cannot duplicate the functionality that is built into Microsoft Jet. For example, even if you implement single-record locking, it would be very difficult to handle data in a recordset that is based on more than one table, because you would have to determine all the tables that contain records that have to be locked. Custom locking schemes are most suitable for situations where the data to be locked is stored in a few tables and where the application is not based on a data model with complex joins and relationships.