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
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.