dcsimg
January 19, 2017
Hot Topics:

Locking Recordsets in Microsoft Access 2000

  • March 23, 2001
  • By Alison Balter
  • Send Email »
  • More Articles »

Recordset Locking

Recordset locking locks pages of data found in a recordset. By using recordset locking, you can control when and for how long the data is locked. This is different from locking data through bound forms, which gives you little control over the specifics of the locking process.

When you're traversing through a recordset, editing and updating data, locking occurs regardless of whether you intervene, so you must understand when the locking occurs and whether you need to step in to intercept the default behavior. If you do nothing, a record, or possibly an entire page of records, will be locked each time you begin editing data from your VBA code. This record page is 4096 bytes (4K) and surrounds the record being edited. If an OLE object is found in the record being edited, it isn't locked with the record because it occupies its own space.

Pessimistic Locking

VBA lets you determine when and for how long a page is locked. The default behavior is called pessimistic locking, which means that the record or page is locked when the first field is updated. Listing 1 illustrates this process.

Listing 1 - Utilizing Pessimistic Locking

Sub PessimisticLock(strAuthorID As String)
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strCriteria As String

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

    Set rst = New ADODB.Recordset
    rst.ActiveConnection = cnn
    rst.CursorType = adOpenKeyset
    rst.LockType = adLockPessimistic 'Invoke Pessimistic Locking
    rst.CursorLocation = adUseServer
    rst.Open "Select * from Authors Where Au_ID ='" _
        & strAuthorID & "'", _
        Options:=adCmdText

    rst!City = "Thousand Oaks" 'Lock occurs here
    rst.Update 'Lock Released Here
End Sub
NOTE
Not all database providers support all lock types. To determine the functionality available for a particular Recordset object, use the Supports method with adUpdate and adUpdateBatch. Furthermore, the adLockPessimistic setting is not supported when the CursorLocation property is set to adUseClient. Whether the provider does not support the locking type, or the cursor location does not support the locking type, no error results. Instead, the closest available locking type is used.

In this scenario, although the lock occurs for a very short period of time, it's actually being issued when the data is first edited; then released at the update.

The advantage of this method of locking is that you can make sure no changes are made to the data between the time the edit process begins and the time the Update method is issued. Furthermore, when the edit process begins successfully, you are ensured write access to the record. The disadvantage is that the time between the edit and the update might force the lock to persist for a significant period of time, locking other users out of not only that record, but if page locking is used, the entire page of records the edited record is in.

This phenomenon is exacerbated when transaction processing is invoked. Basically, transaction processing ensures that when you make multiple changes to data, all changes are made successfully or no changes occur. Listing 2 illustrates how pessimistic record locking affects transaction processing:

Listing 2 - Pessimistic Record Locking and Its Effect on Transaction Processing

Sub PessimisticTrans(strOldCity As String, strNewCity As String)
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strCriteria As String

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

    Set rst = New ADODB.Recordset
    rst.ActiveConnection = cnn
    rst.CursorType = adOpenKeyset
    rst.LockType = adLockPessimistic 'Invoke Pessimistic Locking
    rst.CursorLocation = adUseServer
    rst.Open "SELECT * FROM Authors WHERE City = '" & 
                     strOldCity & "'", _
        Options:=adCmdText

    cnn.BeginTrans
    Do Until rst.EOF
        rst!City = strNewCity 'Lock occurs here
        rst.Update
        rst.MoveNext
    Loop
    cnn.CommitTrans  'Lock released here
End Sub

Here you can see that the lock is in place from when the city of the very first record is edited until the CommitTrans is issued. This means that no one can update any records, or possibly pages of data, involving the edited records until the CommitTrans is issued. This can take a prohibitive amount of time during a long process.

Optimistic Locking

Optimistic locking delays the time at which the record is locked. The lock is issued upon update rather than when the first field is edited. The code is shown in Listing 3.

Listing 3 - Utilizing Optimistic Locking

Sub OptimisticLock(strAuthorID As String)
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strCriteria As String

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

    Set rst = New ADODB.Recordset
    rst.ActiveConnection = cnn
    rst.CursorType = adOpenKeyset
    rst.LockType = adLockOptimistic 'Invoke Optimistic Locking
    rst.CursorLocation = adUseServer
    rst.Open "Select * from Authors Where Au_ID = '" & _
        strAuthorID & "'", _
        Options:=adCmdText

    rst!City = "Thousand Oaks"
    rst.Update 'Lock occurs and is Released Here
End Sub

As you can see, the lock doesn't happen until the Update method is issued. The advantage of this method is that the page, or record, is locked very briefly. However, the disadvantage occurs when two users grab the record for editing at the same time. When one user tries to update, no error occurs. When the other user tries to update, she gets an error indicating that the data has changed since her edit was first issued.

Optimistic locking with transaction handling isn't much different from pessimistic locking. As the code reaches the Update method for each record, the page containing that record is locked, and it remains locked until the transaction is committed. The code appears in Listing 4.

Listing 4 - Optimistic Record Locking and Its Effect on Transaction Processing

Sub OptimisticTrans(strOldCity As String, strNewCity As String)
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strCriteria As String

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

    Set rst = New ADODB.Recordset
    rst.ActiveConnection = cnn
    rst.CursorType = adOpenKeyset
    rst.LockType = adLockOptimistic 'Invoke Optimistic Batch Locking
    rst.CursorLocation = adUseServer
    rst.Open "SELECT * FROM Authors WHERE City = '" & 
                                   strOldCity & "'", _
        Options:=adCmdText

    cnn.BeginTrans
    Do Until rst.EOF
        rst!City = strNewCity
        rst.Update 'Lock occurs here
        rst.MoveNext
    Loop
    cnn.CommitTrans  'Lock released here
End Sub
NOTE
The constant adLockBatchOptimistic is used when batch updates, rather than immediate updates, are desired. The updates do not occur until the UpdateBatch method of the Recordset object is used. In the case of batch optimistic locking, the records are not locked until the UpdateBatch method is issued.




Page 1 of 3



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Sitemap | Contact Us

Thanks for your registration, follow us on our social networks to keep up-to-date
Rocket Fuel