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. |
Effectively Handling Locking Conflicts
If a user has a page or record locked and another user tries to view data in the record, or on that page, no conflict occurs. On the other hand, if other users try to edit data on that same page, they get an error.
You won’t always want Access’s own error handling to take over when a locking conflict occurs. For example, rather than having Access display its generic error message indicating that a record is locked, you might want to display your own message and then try to lock the record a couple of additional times. To do something like this, you must learn to interpret each locking error generated by VBA, so you can make a decision about how to respond.
Locking conflicts happen in the following situations:
- A user tries to edit a record that’s already locked.
- A record has changed or been deleted since the user first started to edit it.
These errors can occur whether you’re editing bound data through a form or accessing the records through VBA code.
Errors with Pessimistic Locking
To begin the discussion of locking conflicts, take a look at the types of errors that occur when pessimistic locking is in place. With pessimistic locking, you generally need to code for the following errors:
- The current record is locked by another user. Usually, you can just wait a short period of time and then try the lock again.
- The record has been deleted since the recordset was retrieved. In this case, it’s best to refresh the data.
NOTE |
The error numbers that occur differ based on the provider that is being used. The examples in this chapter therefore use sample error numbers. Refer to online help for trappable Microsoft Jet errors, ADO error codes, and ADO provider errors. As an alternative to the VBA error code, you can use the Errors collection of the Connection object to view properties of the error that occurred. |
Coding Around Pessimistic Locking Conflicts
It’s fairly simple to write code to handle pessimistic locking conflicts. Your code should look like Listing 5.
Listing 5 – Handling Pessimistic Locking Errors
Sub PessimisticRS(strAuthorID As String) On Error GoTo PessimisticRS_Err Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim strCriteria As String Dim intChoice As Integer 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 Exit Sub PessimisticRS_Err: Select Case Err.Number Case 3197 rst.Move 0 Resume Case -2147217887 intChoice = MsgBox(Err.Description, vbRetryCancel + vbCritical) Select Case intChoice Case vbRetry Resume Case Else MsgBox "Couldn't Lock" End Select Case 3021 MsgBox "Record Has Been Deleted" Case Else MsgBox Err.Number & ": " & Err.Decription End Select End Sub
The error-handling code for this routine handles the errors that can happen with pessimistic locking. If a -2147217887 Record Is Locked error occurs, the user is asked whether she wants to try again. If she responds affirmatively, the edit process resumes; otherwise, the user is informed that the lock failed. If the record being edited has been deleted, an error 3021 occurs, and the user is informed that the record has been deleted. The situation looks like Listing 6 when transaction processing is involved.
Listing 6 – Handling Pessimistic Locking Errors in Transactions
Sub PessimisticRSTrans() On Error GoTo PessimisticRSTrans_Err Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim strCriteria As String Dim intCounter As Integer Dim intTry As Integer Dim intChoice As Integer Set cnn = CurrentProject.Connection Set rst = New ADODB.Recordset rst.ActiveConnection = cnn rst.CursorType = adOpenKeyset rst.CursorLocation = adUseServer rst.LockType = adLockPessimistic 'Invoke Pessimistic Locking rst.Open "tblCustomers", _ Options:=adCmdTable cnn.BeginTrans Do Until rst.EOF rst!CompanyName = rst!CompanyName & 1 'Lock occurs here rst.Update rst.MoveNext Loop cnn.CommitTrans 'Lock released here PessimisticRSTrans_Exit: Exit Sub PessimisticRSTrans_Err: Select Case Err.Number Case 3197 rst.Move 0 Resume Case -2147217887 intCounter = intCounter + 1 If intCounter > 2 Then intChoice = MsgBox(Err.Description, _ vbRetryCancel + vbCritical) Select Case intChoice Case vbRetry intCounter = 1 Case vbCancel Resume CantLock End Select End If DoEvents For intTry = 1 To 100: Next intTry Resume Case Else MsgBox "Error: " & Err.Number & ": " & Err.Description End Select CantLock: cnn.RollbackTrans Exit Sub End Sub
This code tries to lock the record. If it’s unsuccessful (that is, an error -2147217887 is generated), it tries three times; then prompts the user for a response. If the user selects Retry, the process repeats. Otherwise, a rollback occurs and the subroutine is exited. If any other error occurs, the rollback is issued and none of the updates are accepted.
NOTE |
Listing 6 opens the tblCustomers table directly. This is for the example only. You should never open a base table directly because this downloads all the rows and their column properties from the server. Instead, you should limit the rows returned to only those rows you need to work with. Finally, when using a database server such as Microsoft SQL Server, the task of updating records is accomplished much more effectively using a stored procedure. |
Errors with Optimistic Locking
Now that you have seen what happens when a conflict occurs with pessimistic locking, see what happens when optimistic locking is in place. These are the two most common error codes generated by locking conflicts when optimistic locking is in place:
- An error occurs when the Update method is used to save a locked record or a record on a locked page. This error can occur when optimistic locking is used and a user tries to update a record on the same page as a record that’s locked by another machine. You can usually just wait a short period of time and then try the lock again.
- An error occurs with optimistic locking when someone else has updated a record in the time since you first started viewing it.
Coding Around Optimistic Locking Conflicts
Remember that with optimistic locking, VBA tries to lock the page when the Update method is issued. There’s a chance that a Data Has Changed error could occur. This needs to be handled in your code, so modify the preceding subroutine for optimistic locking with the code in Listing 7.
Listing 7 – Handling Optimistic Locking Errors
Sub OptimisticRS(strAuthorID) On Error GoTo OptimisticRS_Err Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim strCriteria As String Dim intChoice As Integer 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 OptimisticRS_Exit: Exit Sub OptimisticRS_Err: Select Case Err.Number Case -2147217885 If rst.EditMode = adEditInProgress Then 'Data has Changed MsgBox "Another User has Edited Record Since You Began " & _ "Modifying It" End If Case -2147217871 'Locked or ODBC Timeout intChoice = MsgBox(Err.Description, vbRetryCancel + vbCritical) Select Case intChoice Case vbRetry Resume Case vbCancel MsgBox "Update Cancelled" End Select Case Else MsgBox "Error: " & Err.Number & ": " & Err.Description End Select Resume OptimisticRS_Exit End Sub
As with pessimistic error handling, this routine traps for all potential errors that can occur with optimistic locking. In the case of a Data Has Changed conflict, the user is warned of the problem. In the case of a locking conflict, the user is asked whether she wants to try again. Listing 8 shows what it looks like with transaction processing involved.
Listing 8 – Handling Optimistic Locking Errors in Transactions
Sub OptimisticRSTrans() On Error GoTo OptimisticRSTrans_Err Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim strCriteria As String Dim intChoice As Integer Dim boolInTrans As Boolean 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 "tblCustomers", _ Options:=adCmdTable cnn.BeginTrans boolInTrans = True Do Until rst.EOF rst!CompanyName = rst!CompanyName & 1 'Lock occurs here rst.Update rst.MoveNext Loop cnn.CommitTrans 'Lock released here Exit Sub OptimisticRSTrans_Err: Select Case Err.Number Case -2147217885 'Data has Changed If rst.EditMode = adEditInProgress Then MsgBox "Another User has Edited Record" & _ " Since You Began " & _ "Modifying It" End If Case -2147217871 'Locked or ODBC Timeout intChoice = MsgBox(Err.Description, _ vbRetryCancel + vbCritical) Select Case intChoice Case vbRetry Resume Case vbCancel MsgBox "Update Cancelled" End Select Case Else MsgBox "Error: " & Err.Number & ": " & Err.Description End Select If boolInTrans Then cnn.RollbackTrans End If Exit Sub End Sub
If a Data Has Changed conflict occurs, the entire processing loop is canceled (a rollback occurs). If a locking error occurs, the lock is retried several times. If it’s still unsuccessful, the entire transaction is rolled back.
Testing a Record for Locking Status
Often, you want to determine the locking status of a record before you attempt an operation with it. By utilizing pessimistic locking and trying to modify the record, you can determine whether the current row is locked. The code looks like Listing 9.
Listing 9 – Determining Whether a Record Is Locked Before Editing It
Sub TestLocking() Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim boolLocked As Boolean 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 "Authors", Options:=adCmdTable boolLocked = IsItLocked(rst) MsgBox boolLocked End Sub Function IsItLocked(rstAny As ADODB.Recordset) As Boolean On Error GoTo IsItLocked_Err IsItLocked = False With rstAny .Update End With Exit Function IsItLocked_Err: If Err = -2147467259 Then IsItLocked = True Exit Function End If End Function
The TestLocking routine sends its recordset to the IsItLocked() function, which receives the recordset as a parameter. It then issues an Update method on the recordset. If an error occurs, the record is locked. The error handler sets the return value for the function to True.
In conclusion
VBA offers several alternative locking strategies for the developer, ranging from locking the entire database to locking one record at a time. In addition, VBA lets you control how long data will be locked. You use this feature through the techniques of optimistic and pessimistic locking, as well as deciding on page locking versus record locking. The developer must select which combination of strategies should be used in each particular application. The decision about which method to use is influenced by many factors, including the volume of traffic on the network and the importance of making sure collisions never happen.
About the Author
Alison Balter is the author of Mastering Microsoft Access 2000 Development(Click to buy) a book published by Sams Publishing. This article is based on information from her book.
Alison Balter is the president of InfoTechnology Partners, Inc., a Microsoft Solutions Partner. Her training videos for Keystone Learning Systems are well-known in the Access community. Alison is a regular speaker at conferences, is an author and speaker for Advisor Media, and has also written a number of Microsoft Access books.
© copyright 2001 by Sams Publishing. All Rights Reserved.
Alison Balter’s Mastering Access 2000, 0-672-31484-3, 17 17-15