Locking Modes

With Microsoft Jet, you can lock data at three different levels:

• Exclusive mode prevents all other users from using the entire database. This is the most restrictive mode.
  
  
• Recordset locking locks a Recordset object’s underlying tables with either table-read locks, table-write locks, or both.
  
  
• Page locking locks the 2,048 byte (2K) page where the data that is being edited resides. This is the least restrictive mode.

To determine the level at which to lock objects in your application, you must decide the level of concurrency needed. For example, if you want the objects to be available as often as possible to as many users as possible, a high-concurrency strategy would dictate that you use page locking, which is the least restrictive level. However, if your application requires guaranteed access to most or all of the data in the database, you may opt for exclusive mode. This ensures that your application has the database open exclusively so that it can’t be shared by other users.

The three levels of locking are not mutually exclusive. Many multiuser applications use all three levels at various times. For example, in an order entry system in which you want many order takers to have access to data simultaneously, you can use page locking to lock data in the Orders table. You can use recordset locking at the end of the day to lock a summary table to update it with summary data. Finally, you can use exclusive mode nightly to compact the database.