How to Use the Three Levels of Database Locking in VB 3.0

- Microsoft Visual Basic programming system for Windows, version 3.0

SUMMARY

The Visual Basic 3.0 "Professional Features Book 2" manual provides information on the three different locking methods that you can use to control multi-user data access in your Visual Basic applications. This information applies primarily to the way the Access engine handles locking for Access and ISAM databases. Notes on ODBC databases are included. This article contains code samples that demonstrate each method. Notes on using the Data Control in a multi-user situation are also included.

MORE INFORMATION

Each of the samples below requires the following constant declarations in the General Declarations section of Form1:

Const MB_RETRYCANCEL = 5 Const MB_YESNO = 4 Const IDCANCEL = 2 Const IDNO = 7 Const DB_DENYWRITE = &H1

Const DB_DENYREAD   = &H2

The sample programs check for possible multi-user locking conflicts at the appropriate places in the code, and inform the user with a message box if a conflict is detected. In most cases the code allows the user to retry the operation or cancel. Note that the error handling in the samples is only for the purposes of trapping locking errors, and you will need much more extensive error handling code in an actual application. A short explanation follows each sample.

Step-by-Step Example for Level 1: Database Locking

This first example is the most restrictive. It opens the database object for exclusive use, which prevents all other users from accessing that database while it is open.

1. Start a new project in Visual Basic. Form1 is created by default.

2. Add a command button (Command1) and a list box (List1) to Form1.

3. Add the following code to the Command1 click event procedure:

   Sub Command1_Click ()
     Dim db As database
     Dim ds As dynaset
     Dim ret As Integer
     Dim fSuccess As Integer
   
     ' Attempt to open database exclusively, checking for locking conflicts.
     fSuccess = False
         ' Disable any previous error handler
         ' and instead, just resume next
     On Error Resume Next
     While Not fSuccess
       Err = 0
       Set db = OpenDatabase("BIBLIO.MDB", True)
       If Err Then
         If Err = ERR_CANT_OPEN_DB Then
           ret = MsgBox("Database in use by another user.", MB_RETRYCANCEL)
           If ret = IDCANCEL Then Exit Sub
         Else
           MsgBox "Unexpected error" & Str$(Err) & " opening database."
           Exit Sub
         End If
       Else
         fSuccess = True
       End If
     Wend
     On Error GoTo 0 ' disable error trapping OR place On Error statements
                     ' pointing to a new error handler here
     ' Once the database is open we know we are accessing the data exclusively
     Set ds = db.CreateDynaset("Authors")
     Do Until ds.EOF = True
       If ds("Author") > " " Then list1.AddItem ds("Author")
       ds.MoveNext
     Loop
   
     ds.Close
     db.Close
   End Sub
   
   

4. From the Run menu, choose Start or press the F5 key to run the program. Click the Command1 button. If another user tries to open the BIBLIO.MDB database at the same time you are running the code listed above, they would get an error message.

   If Err = ERR_CANT_OPEN_DB Then

   If Err = ERR_RESERVED And InStr(Error$, RERR_ExclusiveDBConflict) Then

Step-by-Step Example for Level 2: Table or Dynaset Locking

This second example, Dynaset or Table Locking, is the most often used. It is less restrictive then database locking, since it allows other users to access other tables in the database. This method sets the DB_DENYWRITE and DB_DENYREAD options when opening a Dynaset object or table object. By using this method, you have the flexibility of locking only the records or tables you are working with at the time, so other records or tables can be used by others who are using the same database.

There are a number of variations at this locking level that you can access by changing the option settings.

1. Start a new project in Visual Basic. Form1 is created by default.

2. Add a command button (Command1) and a list box (List1) to Form1.

3. Add the following code to the Command1 click event procedure:

   Sub Command1_Click ()
     Dim db As database
     Dim ds As dynaset
     Dim ret As Integer, fSuccess As Integer
   
     Set db = OpenDatabase("BIBLIO.MDB")
   
     ' Attempt to open the dynaset, checking for locking conflicts.
     fSuccess = False
         ' Disable any previous error handler
         ' and instead, just resume next
     On Error Resume Next
     While Not fSuccess
       Err = 0
       Set ds = db.CreateDynaset("Authors", DB_DENYWRITE Or DB_DENYREAD)
       If Err Then
         If Err = ERR_CANT_LOCK_TABLE Then
           ret = MsgBox("Table(s) in use by another user.", MB_RETRYCANCEL)
           If ret = IDCANCEL Then Exit Sub
         Else
           MsgBox "Unexpected error" & Str$(Err) & " opening table."
           Exit Sub
         End If
       Else
         fSuccess = True
       End If
     Wend
     On Error GoTo 0 ' disable error trapping OR place On Error statements
                     ' pointing to a new error handler here
     ' Once the dynaset is open we know we are the only one accessing the data
     Do Until ds.EOF = True
       list1.AddItem ds(1)
       ds.MoveNext
     Loop
   
     ds.Close
     db.Close
   End Sub
   
   

4. From the Run menu, choose Start or press the F5 key to run the program. Click the Command1 button. If another user tries to update the Authors table when you're running the AddItem loop, they would get an error message.

Step-by-Step Example for Level 3: Page Locking

This third example shows the Page Locking that is automatically built in to the database engine. That is, if you don't provide Database Locking or Dynaset or Table Locking, Page Locking is enforced automatically.

There are two different types of Page Locking when the Edit mode is invoked, Optimistic or Pessimistic. Pessimistic Locking is the default. In pessimistic locking, Visual Basic locks the page containing a record as soon as you use the Edit method, and it unlocks the page when you use Update or Rollback. Use Optimistic Locking when you want to lock a page only at the time you Update the data. You do this by setting the record's LockEdits property.

Page locking is enforced on a per page basis. A page is based on the number of records that can fit within one 2048 byte (or 2K) block of memory.

Since entire pages are locked, and a given page may contain more then one record, users need not be on the exact same record to cause a locking conflict. For example, if user A locks a record at the very beginning of a page, and user B attempts to do an Edit of another record that is also on that page, user B will receive a locking error.

The same locking scheme applies to the index pages. When the Seek method is used or indexes are being rebuilt, the index pages are locked on a 2K page basis. This can also cause locking errors, which the programmer should handle appropriately.

1. Start a new project in Visual Basic. Form1 is created by default.

2. Add a command button (Command1) and a list box (List1) to Form1.

3. Add the following code to the Command1 click event procedure:

   Sub Command1_Click ()
     Dim db As database
     Dim ds As dynaset
     Dim ret As Integer, fSuccess As Integer
   
     Set db = OpenDatabase("BIBLIO.MDB")
     Set ds = db.CreateDynaset("Authors")
   
     Do Until ds.EOF = True
   
     ' Attempt to access records, checking for possible page locking conflicts
       fSuccess = False
         ' Disable any previous error handler
         ' and instead, just resume next
       On Error Resume Next
       While Not fSuccess
         Err = 0
         ds.Edit
         If Err Then
           If Err = ERR_DATA_CHANGED Then
             ret = MsgBox("Record has been updated. Overwrite?",
   

          If ret = IDNO Then fSuccess = True
        ElseIf Err = ERR_RECORD_LOCKED Then
          ret = MsgBox("Record in use by another user.", MB_RETRYCANCEL)
          If ret = IDCANCEL Then Exit Sub
        Else
          MsgBox "Unexpected error" & Str$(Err) & " editing record."
          Exit Sub
        End If
      Else
        fSuccess = True
      End If
    Wend
  On Error GoTo 0    ' disable error trapping OR place On Error statements
                     ' pointing to a new error handler here

  ds("Author") = ds("Author")

   ' With Optimistic locking you would check locking on Update vs. Edit
  ds.Update
  ds.MoveNext

  ds.Close
  db.Close
End Sub