Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 17th, 2005, 07:39 AM
Authorized User
Join Date: Jan 2005
Location: , , Uruguay.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default ADO vs DAO pessimistic locking

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.

Reply With Quote
  #2 (permalink)  
Old February 17th, 2005, 08:56 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

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.

Reply With Quote
  #3 (permalink)  
Old February 18th, 2005, 12:07 AM
Friend of Wrox
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts


Couple of thoughts...

quote:Locking is commomly described as disadvantage of ADO
Don't listen to those lazy programmer's!

quote:What does the term pessimistic locking means and how it is implemented
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="

    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="

    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="

    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

    Exit Sub

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


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.



Reply With Quote
The Following User Says Thank You to Bob Bedell For This Useful Post:
  #4 (permalink)  
Old December 3rd, 2008, 12:17 PM
Registered User
Join Date: Dec 2008
Location: New Westminster, BC, Canada.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts

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.

Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
DAO vs. ADO SerranoG Access VBA 11 December 5th, 2006 01:19 PM
Pessimistic locking SQLScott SQL Server 2005 1 September 14th, 2006 12:51 PM
ADO Record Locking pjohanne VB Databases Basics 3 March 9th, 2006 05:42 AM
Pessimistic locking Skittle VB Databases Basics 1 November 17th, 2003 08:43 AM
ADO Locking Skittle Pro VB Databases 9 November 14th, 2003 11:54 AM

All times are GMT -4. The time now is 05:51 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.