Implementing a Locking Strategy

Goal

You want your application to lock records, especially during update access. (Locking records during update access can prevent data loss.)

Note: When a record is being updated, ADO copies the record to a local record buffer where the changes are made. The changes are not transmitted to the data source until you call a Recordset Update or UpdateBatch method.

Although all of the providers support locking with read-only access, locking with update access is available only for the SAS/SHARE, IOM, and Base SAS providers. This recipe applies only to ADO. Sample code is included.

Implementation

Locking Records with Read-Only Access

By default, ADO locks open Recordset objects and sets the lock type to adLockReadOnly. When your application does not need update access, you can use the following code, which does not explicitly set the lock type:

Note: The local and OLAP providers support only read access.

' obConnection is an open Connection object.
Dim obRecordset As New ADODB.Recordset

' Do not set the lock type parameter. Let it default to adLockReadonly. 
obRecordset.Open "mylib.standard", obConnection, , , adCmdTableDirect

Locking Records During Concurrent Updating

If your application needs to support multiple users who can modify the same record concurrently, then use the adLockPessimistic lock type. This lock type is the most stringent lock type available. It locks the record from the time editing begins until an Update or MoveNext method is called. The Recordset Open method must specify the adCmdTableDirect option, and the CursorType property must be set to adOpenStatic or adOpenDynamic. The following Visual Basic code shows how this task is done:

' obConnection is an open Connection object.
Dim obRecordset As New ADODB.Recordset

obRecordset.LockType = adLockPessimistic
obRecordset.CursorType = adOpenDynamic
obRecordset.Open "mylib.standard", obConnection, , , adCmdTableDirect

Dim lt As ADODB.LockTypeEnum
lt = obRecordset.LockType

' Check the lock type to make sure the Recordset can be updated.
' Then, lock the first record in the underlying data source 
' and modify the fields in the local record buffer.

If (lt <> adLockReadOnly) Then
 rs!age=19 
 rs!sat=808
 rs!degree="BS" 
 rs!homest="NC"
End If

' The record is locked, updated, and then unlocked.
obRecordset.Update

Locking Records for a Minimal Amount of Time

When you want to minimize the amount of time a record is locked or if updates will be made infrequently, you can use the adLockOptimistic lock type. This lock type does not lock the open Recordset until the Update method is called. When Update is called, the provider compares the record in the local record buffer against the underlying data source to ensure that it has not been changed by another process. If a record has been changed, the update will fail. The Recordset Open method must specify the adCmdTableDirect option and that CursorType property must be set to adOpenStatic or adOpenDynamic. The following Visual Basic code shows how this task is done:

Dim obConnection As ADODB.Connection
Dim obRecordset As New ADODB.Recordset

obRecordset.Open "mylib.standard", obConnection, adOpenDynamic, adLockOptimistic, adCmdTableDirect

' Modify the fields in the local buffer for the first record. 
' The record is NOT locked in the underlying data set. 

rs!age=19 'record is now locked
rs!sat=808
rs!degree="BS" 
rs!homest="NC"

' The record is locked, updated, and then unlocked.
obRecordset.Update

Locking Records in Order to Improve Performance

Batch updating can improve the performance of your application. SAS permits batch updates when the data set has been opened with an exclusive member lock. To enable an exclusive member lock, you set the customized Recordset property SAS Optimistic Locking to False, as shown in the sample code.

After granting exclusive member-level access to one user, you can use the adLockBatchOptimistic lock type, which does not lock the open Recordset until the UpdateBatch method is called. When UpdateBatch is called, the provider compares each record in the local buffer against the underlying data source to ensure that it has not been changed by another process. If a record has been changed, the batch update will fail. The Recordset Open must specify the adCmdTableDirect option, and the CursorType property must be set to adOpenStatic or adOpenDynamic. The following Visual Basic code shows how this task is done:

' obConnection is an open Connection object.
Dim obRecordset As New ADODB.Recordset

' Set exclusive member access.
obRecordset.ActiveConnection = obConnection
obRecordset.Properties("SAS Optimistic Locking") = False

'Set the maximum number of rows for batch update.
obRecordset.Properties("Maximum Open Rows") = 1000 
obRecordset.LockType = adLockBatchOptimistic
obRecordset.CursorType = adOpenStatic
obRecordset.Open "mydata.customers", , , , adCmdTableDirect

'Change all of the records.
While Not obRecordset.EOF
 rs!balance = 0
 obRecordset.MoveNext
Wend

' The record is locked, updated, and then unlocked.
obRecordset.UpdateBatch

Note: If a data set has not been opened with an exclusive member lock, then the UpdateBatch method ignores the batch request and updates the records one at a time, and there will be no performance gain.

Update Restrictions

A SAS data set can be opened concurrently for update access only from within a single SAS process. If one SAS process opens a data set for an update, all other processes are restricted to read-only access of that data set. This restriction applies to the IOM provider and supersedes any explicitly set Recordset properties. Therefore, if your application spans multiple SAS processes, you will not be able to open one data set concurrently even if you specify an ADO lock type of adLockPessimistic, adLockOptimistic, or adLockBatchOptimistic.

This restriction does not apply to the SAS/SHARE and Base SAS providers because multiple requests to open the same data set are serviced by the same SAS process.