Understanding Microsoft Jet Locking

Kevin Collins
Microsoft Jet Program Management

November 21, 1995

Introduction

The purpose of this paper is to introduce you to Microsoft® Jet 2.x and Microsoft Jet 3.0 locking techniques so that you can apply the information to coding and debugging when developing multi-user applications. This paper will address the following areas:

• Layout of the LDB file
• Database Header Page
• Types of Microsoft Jet Locks
• Deciphering Microsoft Jet Locks

Layout of the .LDB File

The .LDB file plays an important role in the Microsoft Jet multiuser scheme. This file stores the computer and security names and has extended byte range locks placed on it by Jet.

Microsoft Jet retrieves the computer name by making a request to the operating system. To modify the computer name in Windows for Workgroups, Windows® 95 or Windows NT®, simply go to the Control Panel and choose the Network icon. From there a user can change the computer name. Windows For Workgroups stores the computer name in the SYSTEM.INI file, whereas Windows 95 and Windows NT store the computer name in the Registry. The security name is determined by passing a value to the Workspace object in DAO. The default security name is Admin.

Extended byte range locks are locks placed outside of the physical boundaries of a file—no data is ever physically locked. An example of this is placing a lock at 10 million hex for a file that has a physical size of only 64 bytes. In other words, a lock is virtually placed at a location that does not exist on the hard disk. This type of locking is used because extended byte range locks are not limited by the size of the physical file, allowing for locking algorithms that would not otherwise be possible. Also, placing locks inside a data file would prevent other users from reading that data. In the early dBASE days, a user could place a lock on a row located in the data file that prevented everyone from reading that data: when printing a report, for example.

One .LDB file is always created for every Microsoft Jet database file that is connected in a shared mode. The .LDB file always retains the same name as the database that was opened (for example, NWIND.MDB always has a NWIND.LDB), and the .LDB file will always be in the same directory as the database. If an .LDB file does not exist and the database is connected in a shared mode, an .LDB file is created. The physical structure of the .LDB file is best thought of as a one-dimensional array. Each element in the array, up to 255, consists of 64 bytes. The first 32 bytes contain the computer name (such as KevinCol), and the second 32 bytes contain the security name (such as Admin). This data is then used to provide information regarding which other users are holding locks. You can view this information by simply looking at the contents of the .LDB file.

The physical size of the .LDB file never exceeds 16,320 bytes, because the maximum number of concurrent users in a Microsoft Jet database is 255 (255 * 64 = 16320).

The high-order ASCII characters seen in a Microsoft Jet 2.x .LDB files are meaningless to both the viewer and to Jet. Microsoft Jet 3.0 eliminates the high order ASCII characters from the .LDB file so that just the computer and security names are shown.

Microsoft Jet 3.0 automatically deletes the .LDB file when the last user closes the database. This is done to aid in issues with replicated databases and to allow for performance improvements when determining which other users have locks. The exception to this is when a user does not have NOS/OS delete rights to the .LDB file or if the database is in a suspect state. There is no performance or concurrency benefit gained from deleting your .LDB file in a Jet 2.x environment.

A 32-bit utility (LDBView) is included to enable you to view the status of the commit byte(s) (commit bytes are described in the next section) and what users are currently logged onto the database. This utility helps you determine which user(s) left the database in a suspect state.

Another utility (MSLDBUSR.DLL) now enables you to programmatically retrieve the following information if you're running 32-bit VBA applications:

• List of all users in an .LDB file
• List of all connected users in an .LDB file
• List of users that left database in a suspected state

You can find out how to use the .DLL at the end of this paper.

Database Header Page

The database header page (DBH) is the first page in a database. It is partially used to store the commit byte(s) for each of the 256 possible users connected to a database. A commit byte (or pair of bytes in version 3.0) is a value that is used by Microsoft Jet to determine the state of the database.

Microsoft Jet 2.x utilizes 256 bytes that store the commit bytes for each possible user starting at 700 hex and continuing to the end of the page (800 hex). Microsoft Jet 3.0 utilizes 512 bytes that use two bytes per user starting at 600 hex. The first byte (Microsoft Jet 2.x) or the first two bytes (Microsoft Jet 3.0) are used only when a database is connected in an exclusive mode and the remaining 255 bytes (Microsoft Jet 2.x) or 510 bytes (Microsoft Jet 3.0) are used when the database is connected in a shared mode.

Commit bytes in Microsoft Jet 2.0 have only two valid values, FF and 00. A value of 00 represents a neutral state and a value of FF means that Microsoft Jet is in the process of physically writing data to disk. If there is a value of FF and no corresponding user lock, then a user has had an abnormal shutdown of the database file. New users attempting to connect to the database will receive the message "Database is corrupted or is not a database file" and they will be forced to run repair on the database to open it. An anomaly in Microsoft Jet 2.0 allowed instances where the database could be closed with a value of FF, thus preventing other users from connecting the database until repair could be run. This was corrected in Microsoft Jet 2.5.

Commit bytes in Microsoft Jet 2.5 have five valid values ranging from 00 to 04. This range of values provides more information on what users were doing when the database is left in a suspect state. Similar to Microsoft Jet 2.0, the database is in a suspect state if there is a nonzero value without a corresponding user lock. A value of 00 represents a neutral state. A value of 01 indicates that a user accessed a corrupted page in the database. A value of 02 indicates the database is being created. A value of 03 indicates that the database is being repaired, and a value of 04 indicates that the user is in the process of physically writing data to disk.

Commit bytes in Microsoft Jet 3.0 can have many different values, so they were increased to 2 bytes. A value of 00 00 indicates that the user is in the process of physically writing to disk, and a value of 01 00 indicates that a user has accessed a corrupted page. Therefore, if a value of 00 00 is present without a corresponding user lock, or a value of 01 00 is present, users will not be allowed to connect to the database without first executing the repair utility. If a user is looking at these commit byte values with the LDBView utility, they will notice that there may be many other values present in the 600~800 hex range. These values are used internally by Microsoft Jet for performance reasons (mainly to determine if other users have written data to the database file). If Microsoft Jet determines that other users have not written information to disk, it will delay refreshing its internal cache, resulting in fewer reads and increasing performance. Therefore, it is possible that a database opened in shared mode may approach the performance levels of a database opened in exclusive mode.

Again, which commit bytes are actually used for a user is determined by their corresponding user lock in the .LDB file. A detailed discussion of how these locations are obtained appears later in this paper. Also, a user can monitor these values for both Microsoft Jet 2.x and 3.0 databases by using the 32-bit LDBView utility included on the CD-ROM.

Types of Microsoft Jet Locks

Shared vs. Exclusive Locks

These two types of locks will be referred to frequently later in the paper and it is important to understand the concepts behind them. Regardless of the type of lock, each extended byte range lock placed on the .LDB file always represents one page in the .MDB file (except for user locks). No locks are ever placed on the .MDB file.

A shared lock occupies only 1 byte and never conflicts with another shared lock. These types of locks are typically utilized to allow many people to read information at the same time. An example of when shared locks are used is when many users have the same table open at the same time without conflicting with each other. Another example is when users are reading an index that participates in referential integrity: Many users would be able to read the index at the same time.

An exclusive lock spans between 256 and 512 bytes and always conflicts with other shared locks and other exclusive locks. The exclusive lock always locks the first 256 bytes of the range to prevent any shared locks from being set and to determine if there are any existing shared locks set. In addition, it locks enough additional bytes beyond 256 to determine which user is holding the lock An example of an exclusive lock is when a user opens a table in deny-read mode. This exclusive lock would be prevented if another user had a shared lock on the table; if the exclusive lock were acquired, it would prevent other users from obtaining a shared lock. Another example is when a user is writing information back to an index page that participates in referential integrity. This would produce a write lock, which is always exclusive, and would prevent anyone from reading the index page. The index page requires a read lock and is always shared.

There are seven types of Microsoft Jet locks:

• User locks
• Write locks
• Read locks
• Commit locks (Microsoft Jet 2.x only)
• Table-read locks
• Table-write locks
• Table deny-write locks

These extended byte range locks are broken out into six virtual regions off of the .LDB file for Microsoft Jet 2.x and five virtual regions for Microsoft Jet 3.0. These areas are where Microsoft Jet places extended byte range locks that range from 10000001 to 6FF800FF hex. These locks are only present when users have the database connected in a shared or read-only mode. Note that the names of these locks were assigned by the Microsoft Jet development team and do not necessarily have the same meaning that other database vendors use. See Table 1 after the descriptions below for a summary of these locking ranges.

User locks

User locks determine ownership of a commit byte(s) in the DBH, write the computer and security names in the correct location in the LDB file, and retrieve the computer and security names of another user that has conflicting locks. A user lock is obtained and is persistent for as long as a user is connected to the database (persistence in this context indicates the duration of a lock or how long a lock is held). Only one user lock exists for each connected user; however, multiple instances of Microsoft Jet on the same computer create an individual user lock for each instance. User locks are always in the range of 10000001 hex through 100000FF hex. They occupy only 1 byte (for example, 10000002 ~ 10000002).

Write locks

Write locks prevent other users from changing data while a user is modifying data. A write lock is typically placed on data, index, 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 in Microsoft Access or Visual Basic.) Write locks are in the range of 20000000 hex through 2FF800FF hex and always span between 256 and 512 bytes (for example, 2000E600 ~ 2000E700 hex), therefore they are always exclusive locks. The persistence of write locks is directly related to the duration of a transaction. All SQL DML statements have implicit transactions placed around them. Thus, an UPDATE statement will have persistent write locks until the entire update is committed. The persistence of write locks is also determined by the type of locking that is chosen for recordset navigation and form editing (for example, pessimistic/optimistic) and if explicit transactions are used. For more information on locking semantics, see Chapter 12 in Microsoft Access 2.0 Building Applications.

Read locks (Microsoft Jet 2.x)

Read locks, which are a type of shared lock, are primarily used for the immediate recycling of index pages and for ensuring that index pages in Jet's cache are up-to-date. This type of lock is placed on long value pages and index pages. It is used to prevent an index page from being recycled while that page is being referenced in Jet's cache, and to provide an integral view of the index. Read locks are placed in the range of 30000000 ~ 3FF800FF hex. They occupy only 1 byte (for example, 30001A01 ~ 30001A01).

Read locks in version 2.x are probably the most troublesome types of locks to deal with as they can cause locking conflicts that are not obvious to the developer or user. The persistence of read locks is determined by:

The LockedPageTimeout setting, specified in tenths of a second, which determines the amount of time the Microsoft Jet database engine retains locks before releasing them. By increasing the LockedPageTimeout setting, read locks are retained for a longer period of time, which reduces the need for Microsoft Jet to reread data pages into cache. The LockedPageTimeout setting can be changed by modifying the ISAM section of the corresponding .INI file (MSACC20.INI for Microsoft Access users) or by modifying the value in the Registry for Windows 95 or Windows NT in the Microsoft Jet 2.x\ISAM key. The side effects could be reduced concurrency due to commit lock conflicts, which will be talked about next.

Calling DBEngine.Idle DBFreeLocks or FreeLocks. However, these commands do not always free all read locks when inside a transaction (see lock log examples later in this paper). Note that the DBFreeLocks constant is used in DAO 3.0 and would be used when Access 7.0 or VB 4.0 has opened a Jet 2.x database. If Access 2.0 or Visual Basic 3.0 is used, then the constant would be DB_FreeLocks.

Read-locks are typically placed when an index or long value page is read and placed in Jet's cache. An example would be performing an SQL DML UPDATE command on a table that has a primary key.

Read locks (Microsoft Jet 3.0)

The main purpose of read locks in Microsoft Jet 3.0 is to allow multiple users to read long value data, but prevent users from writing to it while others are reading.

Microsoft Jet 3.0 has reduced the amount of read locks that are placed on index pages, which directly results in greater concurrency and performance. Read locks on index pages are now only placed when referential integrity is being enforced. Unlike Microsoft Jet 2.x, the persistence of read locks is determined by the currency of a row. Thus a read lock on a long value page will remain until the user leaves that row. An exception to this is when a long value page contains data from more than one row, at which point Microsoft Jet will release the read lock on that page. An example of this is when several rows of Memo data are placed on one long value page.

Because read locks are persistent until the user loses currency on the row, DBEngine.Idle DBFreeLocks and FreeLocks no longer provide any functionality (FreeLocks is no longer supported in Visual Basic 4.0 or Microsoft Access 7.0, unless the DAO 2.5/3.0 compatibility layer is enabled). Microsoft Jet 3.0 read locks are also placed in the same range as write locks; the only difference is that read locks are shared locks and they occupy only 1 byte (for example 20001A01 ~ 20001A01).

Commit locks

Commit locks are only present in Microsoft Jet 2.x and are very similar to read locks, except that they are always exclusive locks. These locks are also placed in the 30001A01 ~ 3FF800FF hex range. They are placed when either index pages or long value pages are being written back to the database, and they only conflict with read locks. Read locks are typically placed on index pages whenever an index page is placed in the Microsoft Jet cache. An index page references many data pages, therefore it becomes apparent why many users can experience locking conflicts when they know other users are not editing data on the same data page.

One of the best mechanisms to prevent these conflicts is to judiciously use DBEngine.Idle DB_FreeLocks or FreeLocks in the application code. The use of these two commands is the most effective way to free up read locks so that commit locks will not conflict with them.

Commit locks do not exist in Microsoft Jet 3.0, 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

Table-read locks are used to control placing a table in a deny-read mode, which uses an exclusive lock and 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 page (TBH). There is one TBH page for each table, and every TBH contains statistics about the table (such as row count, next counter value, column data types and index types).

Table-read locks are placed in the 40000000 ~ 4FF800FF hex 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 the default locking on a database is set to All Records (through the Microsoft Access user interface) or dbDenyRead is issued through the DAO OpenRecordSet method. If the exclusive lock can be obtained, then there are no other users that have the table opened. The exclusive lock prevents other users from acquiring shared locks when they try to open a table. These locks are persistent until the user closes the table.

Table-write locks

Table-write locks are used in conjunction with table deny-write locks and are placed in the 50000000 ~ 5FF800FF hex range. These shared locks are persistent whenever a table is opened in a state that allows writing.

Table deny-write locks

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 ~ 6FF800FF hex range and have a persistent shared lock while the table is opened in deny-write mode. An exclusive lock is placed, but not held, to determine which other users have the table open in deny-write mode.

Deciphering Lock Ranges

To utilize the information previously discussed in a way that can help you resolve locking issues with your application, you need to be able to view, understand, and decipher the locks that Microsoft Jet places.

To view the locks being placed, you need to have either a NetWare© environment, Microsoft SMS Network Monitor, or some other "network sniffing" tool. By having one of these utilities, particularly the real-time NetWare Monitor program, an administrator can view the locks being placed by Jet on the LDB file. Developers can also use this information to see how their code places locks on the .LDB file and what effects it might have in a multi-user environment.

User-Lock Algorithm

All Microsoft Jet multi-user locking schemes revolve around the placement of a user lock. As stated previously, user locks are placed in the 10 million hex range and occupy only 1 byte. When Microsoft Jet connects to a database in a shared or read-only mode, the following activities occur before a user lock is actually placed.

The first activity is to determine if the Microsoft Jet database engine is in a suspect state. This is done by examining the DBH page and seeing what bytes have a nonzero value. If the first byte (Microsoft Jet 2.x) or the first 2 bytes (Microsoft Jet 3.0) contain a commit in progress value, or the remaining 255 bytes (Microsoft Jet 2.x) or 510 bytes (Microsoft Jet 3.0) have a commit in progress value and do not have a corresponding user lock, then Microsoft Jet will force the user to repair the database. When opening a database in shared mode there is never a need to check for a user lock on the first byte (Microsoft Jet 2.x) or the first two bytes (Microsoft Jet 3.0) because it is only used when a database is connected exclusively. A corresponding user lock would be a lock that shares the same offset in the ten million hex range as the offset from the first byte in the DBH page. Thus, a non-zero commit byte at 701 hex (Microsoft Jet 2.x) or 602~603 hex (Microsoft Jet 3.0) would need to have a user lock at 10000001 in order for the database to be opened without a corrupt database message.

The Microsoft Jet database engine then opens the .LDB file (or creates one if one does not exist) and tries to place a lock at 10000001 hex. If Microsoft Jet is successful in obtaining this lock, it will write the computer and security name to the first 64 bytes of the file. If Microsoft Jet cannot acquire this lock, it will continue moving one byte further until a lock can be successfully obtained. After the user lock is acquired, the Microsoft Jet database engine will then write the computer and security name at the corresponding location in the .LDB file. For example, a user lock at 10000040 hex would write an entry starting at 4096 bytes in the physical part of the .LDB file.

Some users have experienced delays when trying to connect to a database that already has many users connected. This is mainly due to non-optimized network drivers. An example of this is using a Windows NT 3.5 client accessing a NetWare server. If a user were trying to connect to the database and 30 other users were currently connected, it could take upwards of 30 seconds to perform the user lock algorithm. Windows NT 3.51 has a modified NetWare requester driver that brings the above scenario down to approximately two seconds.

Deciphering Write-locks

As stated previously, write locks will always occur in the 20 million hex range and will always have a starting and ending lock range between 256 and 512 bytes. The code below places write locks on the .LDB file that relates to pages in the Customers table. All future references to locks on pages are really referring to locks placed on the .LDB file that relates to pages in the database file.

Note: The following code examples assume that the user is using Visual Basic 4.0 or Microsoft Access 7.0 with either an original Microsoft Jet 2.x version of the NWIND database that shipped with Access 2.0, or a NWIND database that was compacted to Microsoft Jet 3.x from the original Access 2.0 NWIND database (using the DAO CompactDatabase method in Visual Basic 4.0 32-bit, or Microsoft Access 7.0). If Microsoft Access 7.0 is used to convert the Microsoft Access 2.0 NWIND database via the Convert option from the Tools/Database Utilities menu, then the logs will differ from the Microsoft Jet 3.0 logs below. The logs also assume that the user is the first user logged onto the database, thus implying a user lock of 10000001 hex.

Data Page Write-locks

Here is sample code and a lock log of write locks placed on data pages:

Sub WriteLocksDataPages ()
    ' The example is intended to be run from Access 7.0 or VB 4.0.
    Dim db As Database
    Dim rs As Recordset
    Dim ws As Workspace
    Dim ContactName As String
    Set db = OpenDatabase("NWind.mdb", False, False)
    Set rs = db.OpenRecordset("SELECT * FROM Customers")
    rs.lockedits = false
    Set ws = Workspaces(0)
    ws.BeginTrans
    While Not rs.eof
         rs.Edit
         ContactName = rs![Contact Name]
         rs![Contact Name] = ContactName
         rs.Update
         rs.MoveNext
    Wend
    ws.CommitTrans
End Sub  

When you set a breakpoint on the recordset MoveNext method, the Microsoft Jet database engine will leave a write lock on the first data page of the Customers table. Assuming that only one user is connected to the database, the write lock will have a starting and ending lock address of 2000AC00 ~ 2000AD00 (Microsoft Jet 2.x). To determine what page is being locked, the following steps need to occur:

Remove the first digit. (2000AC00 à AC00)
1. Convert to decimal (AC00 à 44032)

Determine the page number by dividing by 512 (44032 / 512 = 86). Thus, page 86 is being locked via the .LDB file by rs.Edit. Page 86 would represent the page in the database starting at 176,128 bytes and extending to 178,176 bytes.

To determine the user number that is locking the page, simply take the last two digits of the ending address, convert it to decimal and add one digit if the database is Microsoft Jet 2.x (2000AD00 à 00 + 1 = 1). If the database is a Microsoft Jet 3.0 database simply converting the last two digits to decimal will give the user number (2000A701 à 01 = 1). Thus, in the Microsoft Jet 2.x example above, user one would be holding the lock (last two digits are zero, plus one).

To find the computer name of the user, simply open the corresponding .LDB file and move to the offset of the user number * 64. Using the previous example, the computer name would be in the first 32 bytes of the .LDB file (1 * 64). Alternatively, you can use the 32-bit LDBView utility included on the CD-ROM to quickly find the computer name of the user who is placing the lock.

In addition to knowing which user has a page locked, it is usually more useful to know which table is associated with the write lock. You can find this information by using the 32-bit Visual Basic 4.0 utility that is included on the CD-ROM (DBLock).

Following is a log of the majority of locks placed from the previouscode example with descriptions for each lock. By using DBLock, you can enter the beginning lock range shown and get a description of the type of lock and which table it is locking. DBLock cannot get table names for long value pages and other internal types of pages used by Jet.

There is some interesting information in this trace log. Notice that the Microsoft Jet 3.0 log has no read locks placed and that the last two digits of the write locks represent the true user number, whereas the last two digits for the Microsoft Jet 2.x write locks require the user to add one to determine the true user number. Also, when optimistic locking is used, the explicit transaction will hold write locks for the duration of the transaction, thus emulating pessimistic locking. Many users mistakenly believe that optimistic locking will always be in effect regardless of the transaction mechanism. Another example of this would be to issue the following code to mimic the DAO code example above.

Internal transactions on SQL DML commands

Sub WriteLocksDML ()
    ' This code example is intended to be run from Access 7.0 or VB 4.0.
    Dim db As Database
    Set db = OpenDatabase("NWind.mdb", False, False)
    DBEngine.Execute _
        "UPDATE Customers SET ContactName = ContactName", _
        dbFailOnError
End Sub  

Because all DML commands are implicitly wrapped in a transaction, the write locks in the example above will be held until the UPDATE statement successfully completes.

Internal Transactions on DAO code with Microsoft Jet 3.0

By removing the explicit calls to WS.BeginTrans and WS.CommitTrans from the code above (WriteLocksDatapage), Microsoft Jet 3.0 will utilize internal transactions. Based on settings (SharedAsyncDelay and MaxBufferSize) in the system Registry, Microsoft Jet places a series of commands into an internal transaction (more detail on these settings can be found in the "Microsoft Jet Engine Version 3.0 Performance Overview" white paper). While this does speed up performance, it may also create concurrency issues where they did not exist in Microsoft Jet 2.x.

Nested transactions

The following code example and lock log demonstrate that locks are held in nested transactions until the outermost transaction is committed:

Sub NestedTransactions()
    'This code example is intended to be run from Access 7.0 or VB 4.0.
    Dim ws As Workspace
    Set ws = Workspaces(0)
    Dim ws1 As Workspace
    Set ws1 = Workspaces(0)
    ws.BeginTrans
    db.Execute _
        "UPDATE Suppliers SET [Contact Name] = [Contact Name]", _
        dbFailOnError
    ws1.BeginTrans
    db.Execute _
         "UPDATE Employees SET [Last Name] = [Last Name]", _
        dbFailOnError
    ws1.CommitTrans
    ws.CommitTrans
End Sub

The previous example illustrates a couple key points. If a CommitTrans or Rollback is not issued or is not balanced with corresponding begintrans commands, write locks will be held until the database is closed. This can cause serious concurrency issues. If a user does not explicitly check the error status of a Rollback or CommitTrans statement, then the command could fail, leaving locks on the .LDB file. Also, simply executing a nested CommitTrans statement does not remove write locks nor is the modified data acutally flushed to disk until the outermost transaction is committed.

Index page write locks

Below is sample code and a lock log of the majority of write locks placed on both data and index pages.

Sub WriteLocksIndexPages ()
    'This code example is intended to be run from Access 7.0 or VB 4.0.
    Dim db As Database
    Set db = OpenDatabase("NWind.mdb", False, False)
    Dim CompanyName As String
    Set rs = db.OpenRecordset _
         ("SELECT * FROM Customers", dbopendynaset)
    'The constant dbFreeLocks is DAO v3 specific and is used 
    'when Jet 3.0 (Access 7.0/ VB 4.0) is opening a Jet 2.x database.
    'If Access 2.0 or VB 3.0 is used, the constant would be db_FreeLocks.
    DBEngine.Idle dbFreeLocks 'Works only in Jet 2.x.
    Set ws = Workspaces(0)
    ws.BeginTrans
    While Not rs.EOF
         rs.Edit
         CompanyName = rs![Company Name]
         rs![Company Name] = CompanyName
         DBEngine.Idle dbFreeLocks 'Works only in V2
         rs.Update
         rs.MoveNext
    Wend
    ws.CommitTrans
    rs.Close
End Sub

The above log illustrates the reduction in read locks from Microsoft Jet 2.x to Microsoft Jet 3.0. Most importantly, the lock log demonstrates how indexes can greatly affect concurrency. In both versions of Jet, there were three index pages that were locked in the Customers table that referenced eight data pages. Thus, by updating one index column, a user could be locking out many other users trying to update values on different data pages. Remember, while indexes can increase retrieval performance, it is very important to weigh their costs when using a multiuser system. By following the motto "If in doubt, index," you may experience substantial performance degradation and concurrency conflicts.

Coming up with the best balance of indexed and non-indexed columns is an art and is different for every application and database. One rule of thumb: Don't index columns that have a high duplication factor. An example of this is indexing a Customer Type column on the Customers table where there were only four unique Customer Types in the Customers table that had 100,000 rows. Indexing this column would typically not increase performance on SQL SELECT statements and would cause performance (maintaining the index) and concurrency issues (each highly duplicated index page would reference many data pages) on SQL DML statements. The developer and system administrator need to weigh the importance of retrieval time (SQL SELECT statements) versus OLTP time (SQL DML statements) and come up with a proper balance of indexes that provides the fastest retrieval times with the fastest OLTP times.

Inserting rows

The following code example and lock log illustrate the drastic improvement in the locking algorithms used in Microsoft Jet 3.0. While Microsoft Jet 2.x would require a lock on the last data page and the TBH during a row insert, Microsoft Jet 3.0 only requires a lock on the last data page. Also, if Microsoft Jet 3.0 can't acquire a lock on the last data page, it will continue seeking available data pages allocated to that table until it can successfully acquire a lock. This eliminates locking contentions when multiple users insert rows into the same table at the same time. The exception to this is when indexes are present on the table, because the user might experience some conflicts while modifying the index page.

Also note the keyword dbFailOnError after the SQL string. This is absolutely necessary if a user wants to have an error returned. This was not supported in Visual Basic 3.0, and it prevented many users from using SQL in multiuser environments because locking conflicts could not be trapped.

Sub InsertRows ()
    'This code example is intended to be run from Access 7.0 or VB 4.0.
    db.Execute _
       "INSERT INTO Shippers ([Company Name]) VALUES ('Test')", _
       dbFailOnError
End Sub

Inserting rows with a counter

The following code example and lock log illustrate some of the types of locks placed when inserting rows with a Counter data type.

Sub InsertCounter()
    'This code example is intended to be run from Access 7.0 or VB 4.0.
    Set ws = Workspaces(0)
    Dim SQLStr AS String
    SQLStr = "INSERT INTO Categories ([Category Name], Description, "
    SQLStr = SQLStr & "Picture) VALUES ('CounterLock', 'CounterLock', NULL)"
    ws.BeginTrans
    db.Execute SQLStr, dbFailOnError
    ws.CommitTrans
End Sub

Deleting rows

The following code example and lock log demonstrate the types of locks placed when doing a delete.

Sub DeleteRow()
    'This code example is intended to be run from Access 7.0 or VB 4.0.
    Set ws = Workspaces(0)
    ws.BeginTrans
    db.Execute _
    "DELETE ROWS FROM Customers WHERE [Customer Id] = 'Paris'", _
        dbFailOnError
    DBEngine.Idle dbFreeLocks
    ws.CommitTrans
End Sub

Updating rows in a multi-table join

The following code example and lock log illustrate that write locks are placed on all tables involved in the join, even when only one column from one table is being modified:

Sub UpdateJoin ()
    Dim SQLStr As String
    Dim ContactName As String
    SQLStr = "SELECT * FROM Customers AS C INNER JOIN "
    SQLStr = SQLStr & "(Orders AS O INNER JOIN "
    SQLStr = SQLStr & "[Order Details] AS OD ON "
    SQLStr = SQLStr & "OD.[Order Id] = O.[Order Id]) ON "
    SQLStr = SQLStr & "C.[Customer Id] = O.[Customer Id] "
    Set rs = db.OpenRecordset(SQLStr, dbOpenDynaset)
    rs.Edit
    ContactName = rs![Contact Name]
    rs![Contact Name] = ContactName
    rs.Update
    rs.Close
End Sub

Deciphering Read and Commit Locks (Microsoft Jet 2.x)

As stated previously, read and commit locks only occur in the 30 million hex range . Read-locks always start and end on the same byte and commit locks start and end between 256 and 512 bytes. To determine what page is being locked, use the 32bit VB 4.0 DBLock program included on the CD-ROM. To determine the user number, simply take the last two digits of the ending lock range and convert them to decimal.

Deciphering Read and Commit Locks (Microsoft Jet 3.0)

To determine which page is being locked, use the DBLock program. To determine the user number, simply take the last two digits of the ending lock range and convert them to decimal.

Following is a code sample and lock log file of read locks that were placed while reading a picture from the NWIND database:

Sub ReadLocks ()
    'This example is intended to be run from Access 7.0 or VB 4.0.
    Dim Photo As Variant
    Dim db As Database
    Dim rs As RecordSet
    Set Db = OpenDatabase("NWIND.MDB", False, True)
    Set rs = db.OpenRecordset _
        ("SELECT * FROM Employees", dbOpenDynaset)
    While Not rs.EOF
        Photo = rs!Photo
        DBEngine.Idle DBFreeLocks
        rs.MoveNext
    Wend
    rs.Close
End Sub

Deciphering All Types of Table Locks

As stated previously, table locks occur in three ranges: 40, 50, and 60 million hex. Using the Customers table as an example, and assuming that the user below is the first user to connect to the database in shared mode, the following steps determine which table is being locked:

Open the Customers table with the OpenRecordSet method, using the shared table-read lock at 4000A601 as a reference.
1. Drop the first digit (4000A601 à A601).
2. Drop the last two digits (A601 à A6).
3. Divide by two (A6 hex / 2 hex = 53 hex) and convert to decimal (83) to get the tableid.
4. Browse MSysObjects.Id for value 83 and then look at then MSysObjects.Name in the rows identified to determine the table name.

The same information can be obtained by using the 32-bit Visual Basic 4.0 utility included on the CD-ROM.

Below are the lock logs for the following commands:

Sub TableDenyRead ()
    'This example is intended to be run from Access 7.0 or VB 4.0.
    Dim db As Database
    Dim rs As Recordset
    Set db = OpenDatabase("NWind.mdb", false, false)
    Set rs = db.OpenRecordset _
        ("Customers", dbOpenTable, dbDenyRead)
End Sub

Sub TableDenyWrite ()
    'This example is intended to be run from Access 7.0 or VB 4.0.
    Dim db As Database
    Dim rs As Recordset
    Set db = OpenDatabase("NWind.mdb", false, false)
    Set rs = db.OpenRecordset _
        ("Customers", dbOpenTable, dbDenyWrite)
End Sub

Using the Utilities

All the utilities referenced in this paper are available on the companion CD-ROM in the Utilities folder.

LDBView

The utility LDBView (Figure 1) is used to accomplish the following tasks:

• Discover which users have been connected to the database and what user are currently connected to the database
• Discover the values of the commit bytes in the DBH (Database Header Page)
• Determine which user(s) have left the database in a suspect state

Figure 1

LDBView is a 32-bit single .EXE utility that can run under either Windows 95 or Windows NT, and can view both Microsoft Jet 2.x and Microsoft Jet 3.0 databases. In order for the utility to run, a corresponding .LDB file must be present.

To use the utility, simply use the File menu to select the database that you want to view. After selecting a database, a screen similar to Figure 1 is displayed. At this point, the refresh interval can be modified by using the View menu. Changing these values will determine how often LDBView looks at reads and displays information from the selected database and corresponding LDB file.

DBLock

The utility DBLock (Figure 2) determines which types of locks Microsoft Jet is placing, which page types are being locked and which tables are associated with those pages. By using a utility, such as NetWare's Monitor utility, you can immediately enter a beginning lock range and determine what effect a particular command would have on other users. This utility can also be used to help determine what bootlenecks exist when users experience locking conflicts. This information could then be used to modify the application or database design (by removing unnecessary indexes, for example).

Figure 2

DBLock is a Visual Basic 4.0 32-bit utility that runs under either Windows 95 or Windows NT and can work against both Microsoft Jet 2.x and Microsoft Jet 3.0 databases. The Setup program installs the Microsoft Jet DLLs necessary to run this program. By default, DBLock installs a default SYSTEM.MDA (security file for Microsoft Jet 2.x) and a default SYSTEM.MDW (security file for Microsoft Jet 3.0). If you have an unsecured database, the utility will grant rights to MSysObjects for user Admin. If you have a secured database, you will need to use the Tools menu to point to the path of your security database.

After a database has been selected, simply enter in the hexadecimal values that are displayed (on the NetWare Monitor utility, for example) and the information for the table name, page type, available page space, and type of lock will be populated.

MSLDBUSR.DLL

This 32-bit DLL enables you to retrieve one of the most sought-after pieces of information: a list of users connected to the database. This was previously impossible to do programmatically unless a programmer understood the user lock algorithm and understood how to place extended byte range locks. Since this is a 32-bit DLL, it will only work with 32-bit products (Microsoft Access 7.0, Visual Basic 4.0 32-bit, Microsoft Excel 7.0, Visual C ++ 32-bit) running on WINDOWS 95 or Windows NT. However, it will retrieve information for both Microsoft Jet 2.x and Microsoft Jet 3.0 databases. Now your application can display a list of users who are preventing you from opening the database exclusively or running a repair or compact.

Because the DLL places real time extended byte range locks, the information that it retrieves can be out of date as soon as another user logs out, but recalling the DLL will always provide current information.

Figure 3 is a screen shot of a sample Visual Basic 4.0 utility (LDBUsrDLL.VBP) that includes source code on how to use the DLL. Figure 4 shows a screen shot of a sample Microsoft Access 7.0 database (provided by Michael Kaplan).

Figure 3

To use the sample utility above, simply select a database file, choose an option under LDB User Options and click Execute or Refresh, which calls the function LDBUser_GetUsers. To view error codes and descriptions, press the Show Viewer button, which calls the LDBUser_GetError function. This utility will not work against library or wizard databases because those are opened exclusively and the Show Users That Left Database in a Suspect State option needs to open the database in a shared mode. For the utility to work properly, it is necessary to have the MSLDBUsr.DLL in the path or the SYSTEM folder.

Figure 4

The above screen shot uses the LDB700.MDB database from Microsoft Access 7.0. For the utility to work properly, it is necessary to have the MSLDBUsr.DLL in the path or the SYSTEM folder.

To use the DLL you must make the following two function declarations. These must go into your module (NOTE: This cannot be a form module) and the DLL must be located in the SYSTEM directory.

Declare Function LDBUser_GetUsers Lib "MSLDBUSR.DLL"
    (lpszUserBuffer() As String, ByVal lpszFilename As String,
     ByVal nOptions As Long) As Integer
Declare Function LDBUser_GetError Lib "MSLDBUSR.DLL" 
    (ByVal nErrorNo As Long) As String

The following options can be called from the function LDBUser_GetUsers:

Here is some sample VBA code that will get the first user in the LDB file:

'    The DLL must be in the SYSTEM directory or have a path referencing it.
Declare Function LDBUser_GetUsers Lib "MSLDBUSR.DLL" _
     (lpszUserBuffer() As String, ByVal lpszFilename As String, _
     ByVal nOptions As Long) As Integer
Declare Function LDBUser_GetError Lib "MSLDBUSR.DLL" _
    (ByVal nErrorNo As Long) As String
Public Const OptAllLDBUsers = &H1
Public Const OptLDBLoggedUsers = &H2
Public Const OptLDBCorruptUsers = &H4
Public Const OptLDBUserCount = &H8
Public Const OptLDBUserAuthor = &HB0B
Sub Main()
'   It is important that ReDim is used to define the array as the DLL, 
'   because the DLL depends on being able to redimension the array.
    ReDim msString(1) As String
'   The array is 1 based rather then 0 based, regardless if Option Base 1
'   is specified in the declarations section.
    Dim miLoop As Integer
    Dim LDBReturn As String
    Dim LDBName As String
    LDBName = InputBox _
        ("Enter a LDB name", " ", "c:nwind.mdb")
    miLoop = LDBUser_GetUsers(msString, LDBName, OptAllLDBUsers)
'   The function calls cannot be combined and must be used individually.
'   Get the first user in the selected .LDB file.
    LDBReturn = msString(1)
    MsgBox ("Total number of users in .LDB file is: " & miLoop & _
        ". The first computer name in .LDB file is " _
        & LDBReturn & ".")
End Sub