In this recipe, you learn how to lock records that you are updating in order to prevent data loss.
| Applies to: | SAS/SHARE Data Provider, SAS IOM Data Provider |
| Implement using: | ADO |
By default, ADO locks open Recordsets objects and sets the lock type to adLockReadOnly. All the SAS Data Providers support this lock type. When your application does not need update access, you can use the following code:
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Provider = "sas.IOMProvider.1"
cn.Properties("Data Source") = "_LOCAL_"
cn.Open
' Since the lock type is not explicitly set, it will be set to the default value of adLockReadonly.
rs.Open "mylib.standard", cn, , , adCmdTableDirect
Note: For more information about how to open a Connection object, see Opening an ADO Connection Object.
To make updates to an open Recordset object, you must use either adLockPessimistic, adLockOptimistic, or adLockBatchOptimistic, which are supported only by the SAS IOM and SAS/SHARE providers. You must also:
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.
See the Details section for information about:
Use the adLockPessimistic lock type if your application needs to support multiple users modifying the same record concurrently. adLockPessimistic—which is the most stringent lock type available—will lock the record from the time editing begins until an Update or MoveNext method is called. Make sure that your Recordset Open method specifies the adCmdTableDirect option and that the cursor type is set to adOpenStatic or adOpenDynamic.
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Provider = "sas.IOMProvider.1"
cn.Properties("Data Source") = "_LOCAL_"
cn.Open
rs.LockType = adLockPessimistic
rs.CursorType = adOpenDynamic
rs.Open "mylib.standard", cn, , , adCmdTableDirect
Dim lt As ADODB.LockTypeEnum
lt = rs.LockType
' These statements lock the first record in the underlying data source
' and modify the fields in the local record buffer
' First check the lock type to make sure the Recordset can be updated
If (lt <> adLockReadOnly) Then
rs!age=19
rs!sat=808
rs!degree="BS"
rs!homest="NC"
End If
' This method call transmits the changes to the underlying data source and unlocks the record.
rs.Update
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. Make sure that your Recordset Open method specifies the adCmdTableDirect option and that the cursor type is set to adOpenStatic or adOpenDynamic.
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Provider = "sas.IOMProvider.1"
cn.Properties("Data Source") = "_LOCAL_"
cn.Open
rs.Open "mylib.standard", cn, adOpenDynamic, adLockOptimistic, adCmdTableDirect
' These statements 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"
' Now the record is locked, updated, and then unlocked.
rs.Update
If you want to improve performance and you are able to grant exclusive member level access to one user, then you can use the adLockBatchOptimistic lock type. This lock type 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. Make sure your Recordset Open specifies the adCmdTableDirect option and CursorType is set to adOpenStatic or adOpenDynamic.
Note: SAS permits batch updates only when the data set has been opened with an exclusive member lock. If a data set has not been opened with an exclusive member lock, then the UpdateBatch method will ignore the batch request and update the records one at a time, and there will be no performance gain. To enable an exclusive member lock, set the customized Recordset property "SAS Optimistic Locking" to False.
CAUTION: While your application has a Recordset open using exclusive member access, all other applications will be prevented from updating the data set. Make sure that this behavior is acceptable.
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Provider = "sas.IOMProvider.1"
cn.Properties("Data Source") = "_LOCAL_"
cn.Open
' Set exclusive member access
rs.ActiveConnection = cn
rs.Properties("SAS Optimistic Locking") = False
'Set the maximum number of rows for batch update.
rs.Properties("Maximum Open Rows") = 1000
rs.LockType = adLockBatchOptimistic
rs.CursorType = adOpenStatic
rs.Open "mydata.customers", , , , adCmdTableDirect
'Change all the records.
While Not rs.EOF
rs!balance = 0
rs.MoveNext
Wend
' The records are locked. All the changes are transmitted to the data source.
' Then the records are unlocked.
rs.UpdateBatch
SAS imposes restrictions for updating data sets. 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 Data Provider and the Local Data Provider and supercedes any ADO Recordset properties. Therefore, if your application spans multiple SAS processes, you will not be able to open one data set concurrently even with an ADO lock type of adLockPessimistic, adLockOptimistic, or adLockBatchOptimistic. A SAS data set can be opened concurrently for update access only from within a single SAS process.
This restriction does not apply to the SAS/SHARE Data Provider because the SAS/SHARE server will permit your application to open one data set concurrently for update access.