Pessimistic Locking

Using pessimistic locking, the engine locks the page that contains the record being edited as soon as the Edit method is issued, and doesn’t release the lock until the changes to the record are explicitly committed or canceled. Pessimistic locking is the default locking mode for recordsets.

Note Explicit transactions change the behavior regarding when a lock is released. For more information, see the Note at the end of the next section, “Optimistic Locking.”

The primary advantage of pessimistic locking is that after you have obtained a lock, you know that you will not encounter any locking conflicts as long as the record is locked. Additionally, pessimistic locking is the only way to guarantee that your application reads the most current data, because one user can’t change a record after another has begun editing it.

The disadvantage of pessimistic locking is that the entire page containing the record is locked for the duration of the procedure that has edited and locked the record. If you’re giving users access to data through a user interface, there is also the problem of a user starting an edit on a record, locking it, and leaving the computer for a period of time. This causes not only the record the user is editing to be locked, but possibly other records that reside within the locked page.

Û To use pessimistic locking in your code

1. Open a table- or dynaset-type Recordset on the data you want to edit.
   
   
2. Enable pessimistic locking by setting the LockEdits property of the Recordset to True.
   
   
3. Move to the record you’re interested in.
   
   
4. Try to use the Edit method to see if the record can be locked. If not, prompt the user to retry or cancel.
   
   
5. Once the record is locked, make changes to the record.
   
   
6. Save the changes to the record by using the Update method. After your changes are saved, the lock is released.

The following code gives an example of how to implement pessimistic locking. In this example, strDbPath is the path to the NorthwindTables database:

Dim dbs As Database
Dim rst As Recordset
Dim blnEdit As Boolean

' Open the database in shared mode.
Set dbs = OpenDatabase(strDbPath, False)
  
' Open the Orders table in shared mode for pessimistic locking.
Set rst = dbs.OpenRecordset("Orders", dbOpenDynaset, dbSeeChanges, dbPessimistic)
  
With rst
	' Setting LockEdits to True tells Jet to use
	' pessimistic locking.
	.LockEdits = True
	' Search for record.
	.FindFirst "[OrderID]=10565"
	
	' Try to edit the record. This causes a lock attempt.
	blnEdit = True
	Do While blnEdit = True
		On Error Resume Next
		.Edit
		' If lock fails because another user is editing
		' the record, allow the user to retry.
		If Err.Number <> 0 Then
			If MsgBox("Cannot edit: " & Err.Description & " Try again?", _
				vbYesNo + vbQuestion) <> vbYes Then
				blnEdit = False
			End If
		Else
			Exit Do
		End If
	Loop
		
	' If lock attempt was successful, make changes and commit.
	If blnEdit Then
		![ShipAddress] = "New Address 1"
		.Update
	End If
End With
	  
rst.Close
dbs.Close

See Also For more information about working with recordsets, see Chapter 5, “Working with Records and Fields.” For more information about setting pessimistic or optimistic locking, search the DAO Help index for “LockEdits property.”