Optimistic Locking

With optimistic locking, the engine locks the page only when you try to commit the record changes using the Update method. Because the lock is applied only when your application attempts to commit changes, the time the lock is in place is minimized; this is optimistic locking’s main advantage.

The disadvantage of optimistic locking is that when a user begins to edit a record, you can’t be sure that the update will succeed. An update that relies on optimistic locking will fail if another user has updated a record while the first user is still editing it.

Û To use optimistic locking in your code

1. Open a table- or dynaset-type Recordset object on the data you want to edit.
   
   
2. Enable optimistic locking by setting the LockEdits property of the Recordset object to False.
   
   
3. Move to the record you’re interested in.
   
   
4. Use the Edit method to allow edits to the record (the record is not yet locked).
   
   
5. Make changes to the record.
   
   
6. Commit the record changes using the Update method (this attempts to lock the record).
   
   
7. Check to see if the Update method succeeded. If it didn’t, try again.

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

Sub LockOptimistic()
	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.
	Set rst = dbs.OpenRecordset("Orders", dbOpenDynaset, _
		dbSeeChanges, dbOptimistic)
	  
	' Set the multiuser options for the recordset.
	With rst
		' Setting LockEdits to False tells Jet to use
		' optimistic locking.
		.LockEdits = False
		' Find record to edit.
		.FindFirst "[OrderID]=10565"
		.Edit
		![ShipAddress] = "New Address 2"
		
		' Check for errors when trying to commit the record.
		Do While True
			On Error Resume Next
			' Attempt update.
			.Update
			' If error occurs, prompt user to try again.
			If Err.Number <> 0 Then
				If MsgBox("Cannot commit: " & Err.Description & " Try again?", _
						vbYesNo + vbQuestion) <> vbYes Then
					' Cancel the edit if user does not wish to try again.
					.CancelUpdate
					' Set flag to False to prevent another iteration.
					blnEdit = False
				End If
			Else
				' If no error, exit loop.
				Exit Do
			End If
		Loop
	End With
		  
	rst.Close
	dbs.Close
End Sub

Important Optimistic locking turns into pessimistic locking when transactions are used. Because a transaction holds a write lock until the transaction is committed, a pessimistic locking mode occurs even though the LockEdits property may have been set to False. Also, it’s possible for the Update method to fail in optimistic locking. Just because a write lock has not been placed by an Edit method, it does not mean that a write lock has not been placed by another user using the Update method. In other words, one user could have a recordset open with pessimistic locking and cause another user who has the recordset open with optimistic locking and who is trying to update the same data to fail.