Locking is commomly described as disadvantage of ADO. Although an ADO Recordset has a LockType property. In this way locking is implemented in ADO. In DAO you have the LockEdit property to dbPessimistic. What does the term pessimistic locking means and how it is implemented.
If someone could explain me in detail or give me some weblink where i can read more about locking in ADO or DAO.

Lock types interact with cursor types. In ADO you have 4 cursor types:

-Dynamic (view changes made by other users, add, change, delete records)
-KeySet (same as dynamic but not immediate updates when changes made by other users)
-Static (just a snapshot of the recordset)
-Forward-only (moves in one direction and is fast = default ADO cursor type)

  The LockType property interacts with the cursor type because it controls how users can manipulate a recordset.

-adLockReadOnly (1)is the default lock and used for recordset clones.
-adLockUnspecified (-1) for use with recordset clones.
-adLockPessimistic (2) locks a record as soon as a user starts to edit it.
-adLockOptimistic (3) locks a record only when the user chooses to commit edits back to the database (optimisitic because it is optimistic to assume that no one else has started to edit the same record you are on when you start)
-adLockBatchOptimistic (4) allows edits to a batch of records before an attempt to update a remote database from the local batch of records (this is VERY optimistic) (Use with objRecordset.UpdateBatch instead of objRecordset.Update method as in the previous examples.

  The best source on this is "Programming Microsoft Office Access 2003", Microsoft Press, Rick Dobson, ISBN: 0-7356-1942-5

   The Wrox Access 2003 VBA is also good, but doesn't describe and the little niceties of working with recordsets the way Dobson's book does. Plus he answers emails.


mmcdonal

Hello,

Couple of thoughts...

Don't listen to those lazy programmer's!

Pessimistic locking is for lazy programmers!! The vast majority of folks who design multi-user applications, and understand ADO record locking, will always opt for an optimistic locking scheme.

Here's the difference.

Say the folks in accounts recievable retrieve a customer's balance due to subtract a payment recieved. Meanwhile, a sales rep enters a new order update for the same customer (adding to the balance due)before the AR folks can enter their update. The AR update fails, because the sales rep has updated the record while the AR folks were editing it.

This is called a write-after-write conflict and there are, generally speaking, two ways to handle it.

a) prevent the sales rep from editing the record while AR is editing it (pessimistic locking scheme)

b) notify the AR folks that the balance due has been changed and why, and ask them if they want to retrieve the new balance due, which includes the additional order amount (optimistic locking scheme).

A pessimistic locking scheme assumes that the write-after-write scenario will occur, and flat out ensures that it can't happen.

An optimistic locking scheme assumes that the occurence of the write-after-write scenario is unlikely, but resolves it (via error handling routines or other means) should it occur.

Pessimistic locks prevent write-after-write conflicts by locking the record when AR starts editing it (the sales rep can't edit it at the same time):

Sub PessimisticLocking()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset

    Set cnn = New ADODB.Connection
    cnn.ConnectionString = " Provider=sqloledb;" & _
        "Data Source=(local);Initial Catalog=yourDatabase;uid=sa;pwd="
    cnn.Open

    Set rst = New ADODB.Recordset
    rst.ActiveConnection = cnn
    rst.CursorType = adOpenKeyset
    rst.LockType = adLockPessimistic 'Implement pessimistic locking scheme
    rst.CursorLocation = adUseServer
    rst.Open "Select * Table1"

    rst!SomeField = "NewValue" 'Lock placed on record
    rst.Update 'Lock released
End Sub

Optimistic locking schemes resolve write-after-write conflicts by allowing AR and the sales rep to edit the record simultaneously, not locking the record until one or the other attempts to update it.

Sub OptimisticLocking()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset

    Set cnn = New ADODB.Connection
    cnn.ConnectionString = " Provider=sqloledb;" & _
        "Data Source=(local);Catalog=yourDatabase;uid=sa;pwd="
    cnn.Open

    Set rst = New ADODB.Recordset
    rst.ActiveConnection = cnn
    rst.CursorType = adOpenKeyset
    rst.LockType = adLockOptimistic 'Implement optimistic locking scheme
    rst.CursorLocation = adUseServer
    rst.Open "Select * from Table1"

    rst!SomeField = "NewValue"
    rst.Update 'Lock placed on record and released
End Sub

When the write-after-write conflict occurs (the second update is issued) additional code is required to resolve the conflict (see below).

Pessimistic locking is problematic, because the time between AR's edit and update might force the lock to persist for a significant period of time, locking the sales rep out of not only that record, but if page locking is used, the entire page of records the edited record is in.

Optimistic locking greatly decreases the likelihood that the data the sales rep needs is locked, and the extra code needed to resolve write-after-write conflicts is well worth it (gotta' get them orders placed).

Here's an example of a minimal write-after-write conflict resolution routine that uses simple error handling:

Sub ResolveFailedOptimisticUpdate()

    On Error GoTo ResolveFailedOptimisticUpdate_Err:

    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim intResponse As Integer

    Set cnn = New ADODB.Connection
    cnn.ConnectionString = " Provider=sqloledb;" & _
        "Data Source=(local);Initial Catalog=yourDatabase;uid=sa;pwd="
    cnn.Open

    Set rst = New ADODB.Recordset
    rst.ActiveConnection = cnn
    rst.CursorType = adOpenKeyset
    rst.LockType = adLockOptimistic 'Implement optimistic locking scheme
    rst.CursorLocation = adUseServer
    rst.Open "Select * From Table1"

    rst!SomeField = "NewValue"
    rst.Update 'Lock placed on record and released

ResolveFailedOptimisticUpdate_Exit:
    Exit Sub

ResolveFailedOptimisticUpdate_Err:
    Select Case Err.Number ' write-after-write conflict, data was edited by another user.
        Case -2147217885
            If rst.EditMode = adEditInProgress Then
                MsgBox "Record was changed by another user while you were editing it." & _
                    vbCrLf & "Your changes will overwrite the " & _
                    "other changes if you click Save again."
            End If
        Case -2147217871 'locked or ODBC Timeout
            intResponse = MsgBox(Err.Description, vbRetryCancel + vbCritical)
            Select Case intResponse
                Case vbRetry
                    Resume
                Case vbCancel
                    MsgBox "Update Cancelled"
            End Select
        Case Else
            MsgBox "Error: " & Err.Number & ": " & Err.Description
    End Select
    Resume ResolveFailedOptimisticUpdate_Exit
End Sub

Without the additional code, the sales rep will just see the OLEDB provider error message:

“The specified row could not be located for updating: Some values may have been changed sunce it was last read.”

The ADO object model provides numerous properties for the Recordset object that help you to resolve write-after-write conflicts in an optimistic locking scheme. You can:

a) Use the Filter property to view only those records whose updates failed (adFilterConflictingRecords)
b) Use the Status property to determine whether an attempt to submit a record's pending changes failed because of a write-after-write conflict (adRecConcurrencyViolation)
c) Use the Value and OriginalValue properties to determine what the pending changes were supposed to do, so you can report them to the user
d) Use the Resynch method to retrieve the current contents of a record
e) Use the UnderlyingValue property to determine which changes were made to the record since you accessed it.

In short, ADO is designed to support a robust optimistic locking scheme.

A few final thoughts...

If you are using Access as a back-end, connecting via the Jet OLEDB provider, you can't even use pessimistic locking through ADO. ADO was designed for universal data access, so while it provides four different lock types, not all OLEDB providers support them. You can use adLockPessimistic as the LockType of a Jet recordset, but the retrieved Jet recordset wil always use BatchOptimistic locking. You can see this by opening a recordset in code using adLockPessimistic, then typing

?rst.LockType

in the Immediate Window. You'll see the number 4 displayed, which is the LockTypeEnum value for adLockBatchOptimistic (no matter what cursor type or cusor location you use). The same holds true for dynamic cursors. Jet doesn't support them. So:

rst.Open "Table1", cnn, adOpenDynamic, adLockPessimistic

actually gives you a Keyset cursor using BatchOptimistic locking for a server-side cursor, and a Static cursor using BatchOptimistic locking for a client-side cursor.

Anyway, optimistic locking is the way to go, and the scheme to learn to implement.

HTH,

Bob

Thanks, Bob
I have been looking for this information for a while. It's easy to find definitions of all the lock types and cursors but the important details are hard to find.
Like when you should use them.