Microsoft Jet uses seven types of locks:
Using the extended byte range locks discussed earlier, these seven types are organized into six virtual regions of the locking information file in Microsoft Jet version 2.x, and five virtual regions in Microsoft Jet versions 3.0 and 3.5. These regions are used by Microsoft Jet to place extended byte range locks that range from 10000001 to 6FF800FFh. These locks are present only when users have the database open in shared or read-only mode.
User locks are used for modifying the commit byte values in the database header page (DBH), for writing the computer and user-level security account names in the correct location in the locking information file, and for retrieving the computer names and user-level security account names of users with conflicting locks. A user lock is obtained when the user opens the database and is persistent for as long as a user has the database open. (Persistence in this context indicates the duration of the lock, or how long the lock is held.) Only one user lock exists for each connected user; however, multiple instances of Microsoft Jet on the same computer create an additional user lock per instance.
User locks are always in the range of 10000001h through 100000FFh and occupy only 1 byte.
Write locks prevent users from changing data while another user is modifying data. A write lock is typically placed on data pages, index pages, or long value pages. Long value pages are a type of data page that contains ANSI SQL data types of CHARACTER VARYING, BIT, or BIT VARYING. These data types are known as Memo or OLE Object fields in Microsoft Access. The persistence of write locks is directly related to the duration of a transaction.
In Microsoft Jet 2.x and 3.0, all SQL data manipulation language (DML) statements have implicit transactions (transactions automatically defined by Microsoft Jet) placed around them. Therefore, an UPDATE statement holds persistent write locks until the entire update is committed. The persistence of write locks is also determined by the type of locking chosen for recordset navigation and form editing (in Microsoft Access), and the presence of explicit transactions.
In Microsoft Jet 3.5, SQL DML statements do not have implicit transactions placed around them.
See Also For more information about how Microsoft Jet 3.5 handles SQL DML statements, see Chapter 13, “Optimizing Performance.”
Write locks are in the range of 20000000h through 2FF80FFh. They always span between 256 and 512 bytes, and therefore are always exclusive locks.
You use read locks, a type of shared lock, primarily for the immediate recycling of index pages (when the index page is no longer being used) and for ensuring that index pages in the engine’s cache are up-to-date. This type of lock is placed on long value pages and index pages. It’s used to prevent an index page from being recycled while that page is being referenced in the Microsoft Jet cache and to provide an integral view of the index.
Read locks are placed in the range of 30000000 to 3FF800FFh. They occupy only 1 byte.
Read locks in version 2.x are perhaps the most troublesome locks to deal with because they can cause locking conflicts that are not obvious to the developer or user. The persistence of read locks is determined by:
Read locks are typically placed when an index or long value page is read and placed in the Microsoft Jet cache. An example would be performing an SQL UPDATE statement on a table that has a primary key.
See Also For more information about Microsoft Jet configuration settings, see Appendix C, “Registry Settings.”
The primary purpose of read locks in Microsoft Jet 3.0 is to allow multiple users to read long value pages, simultaneously preventing others from writing to those pages. Microsoft Jet 3.0 and 3.5 have reduced the number of read locks placed on index pages, which directly results in greater concurrency and improved performance. Read locks placed on index pages are now placed only when referential integrity is being enforced. In Microsoft Jet 3.0, a read lock is persistent until the user moves to a new record. Thus, a read lock on a long value page remains until the user leaves that record. An exception to this is when a long value page contains data from more than one record of data. In this case, the engine releases the lock on that page. An example of this situation is when several records of Memo data are placed in one long value page.
Microsoft Jet 3.0 and 3.5 read locks are placed in the same range as write locks: 20000000h through 2FF80FFh. They are differentiated from write locks in that they are shared locks and occupy only 1 byte.
Important Read locks are persistent in Microsoft Jet 3.0 until the user moves to a new record. Microsoft Jet 2.x leaves read locks until they time out or until the user uses the Idle method of the DBEngine object with the dbFreeLocks constant or uses the FreeLocks method. These statements are not supported in versions 3.0 and 3.5 unless the user references the DAO 2.5/3.0 compatibility type library. For more information about the DAO 2.5/3.0 compatibility type library, see Chapter 2, “Introducing Data Access Objects.”
In Microsoft Jet 3.5, using the Idle method of the DBEngine object without arguments allows Microsoft Jet to perform background tasks that may not be up-to-date because of intense data processing, which releases unneeded read locks. Additionally, you can use the Idle method with the dbRefreshCache constant to force any pending writes to the .mdb file and refresh Microsoft Jet’s cache with the most current data from the .mdb file. For more information about the using the Idle method of the DBEngine object in Microsoft Jet 3.5, see Chapter 13, “Optimizing Performance.”
Commit locks are present only in Microsoft Jet 2.x. They are similar to read locks, except that they are always exclusive locks. These locks are also placed in the 30001A01 to 3FF800FFh range. They are placed when index pages or long value pages are being written to the database file, and they conflict only with read locks. Read locks are typically placed on index pages whenever an index page is placed in the engine’s cache. Because index pages reference many data pages, users can experience locking conflicts even when they know other users are not editing data on the same page.
One of the best ways to prevent these conflicts when using Microsoft Jet 2.x is to judiciously use the Idle method with the dbFreeLocks constant or the FreeLocks method.
Commit locks do not exist in Microsoft Jet 3.0 and 3.5 because the range for read locks has been moved into the same range as write locks, thus eliminating the need for this type of lock.
Table-read locks are used to control placing a table in deny-read mode, which prevents other users from reading data from the table. Unlike the previous types of locks, table-read locks and the rest of the table-type locks are placed only on a special type of page called a table header (TBH) page. There is one TBH page for each table in the database, and each TBH page contains statistics about the table, such as record count, next counter value, field data types, and index types.
Table-read locks are placed in the 40000000 to 4FF800FFh range and can be placed as shared locks or exclusive locks.
When a table is opened, a shared table-read lock is placed. Deny-read mode is set when a Recordset object is opened through DAO using the dbDenyRead constant for the options argument of the OpenRecordset method. Microsoft Access also places a shared table-read lock on a table when the table is opened in Microsoft Access and the user has clicked All Records under Default Record Locking on the Advanced tab of the Options dialog box (Tools menu). The exclusive table-read lock prevents other users from acquiring shared locks when they try to open a table. The table-read locks are persistent until the user closes the table.
Table-write locks are used in conjunction with table deny-write locks and are placed in the 50000000 to 5FF800FFh range. These shared locks are persistent whenever a table is opened in a state that allows writing.
These locks are used in conjunction with table-write locks and are explicitly set when opening a table in deny-write mode. These locks are placed in the 60000000 to 6FF800FFh range and have a persistent shared lock while a table is open in deny-write mode. An exclusive lock is placed, but not held, to determine which other users have the table opened in deny-write mode.