ADO - ADO Recordset LockType and Record Locking Strategies

In ActiveX Data Objects (ADO), the LockType property of a Recordset determines how records are locked when an application retrieves and modifies data. Locking is important when multiple users or applications access the same database at the same time. It helps prevent two users from making conflicting changes to the same record. ADO provides different locking strategies so developers can choose an appropriate balance between data protection, concurrency, and application performance.

1. What Is the LockType Property?

The LockType property specifies the type of locking that ADO should use when updating records in a Recordset. It is associated with the Recordset object and is generally set before opening the Recordset.

The basic syntax is:

Recordset.LockType = LockTypeValue

For example:

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

rs.LockType = adLockOptimistic
rs.Open "SELECT * FROM Employees", ConnectionObject

Here, adLockOptimistic tells ADO to use optimistic locking when records are updated.

The locking method becomes particularly important when a Recordset is editable and several users may attempt to modify the same database records.

2. Types of Locking Supported by ADO

ADO provides several LockType options. The commonly used values are:

LockType	Description
adLockReadOnly	Records cannot be modified through the Recordset
adLockPessimistic	A record is locked while it is being edited
adLockOptimistic	A record is locked only when an update is submitted
adLockBatchOptimistic	Updates are accumulated and submitted as a batch

These options determine when ADO requests locks from the underlying data provider.

3. adLockReadOnly

adLockReadOnly is used when the application only needs to read information.

For example:

rs.LockType = adLockReadOnly
rs.Open "SELECT EmployeeID, Name FROM Employees", conn

With this setting, the application can navigate through the records and retrieve their values, but it cannot directly modify them through the Recordset.

This approach is useful for applications such as reporting systems, search screens, dashboards, and data-viewing applications where users do not need to change database records.

Because no editing is required, read-only access can reduce unnecessary locking and may provide better performance.

4. adLockPessimistic

adLockPessimistic assumes that conflicts are likely to occur. When an application begins editing a record, the provider can lock that record so that other users cannot modify it simultaneously.

For example:

rs.LockType = adLockPessimistic
rs.Open "SELECT * FROM Products", conn

Suppose User A opens a product record and starts editing its price. With pessimistic locking, the record may remain locked while User A is editing it. User B attempting to modify the same record may have to wait or may receive a locking-related error, depending on the database provider.

The main advantage is that it reduces the possibility of conflicting updates.

However, pessimistic locking can reduce concurrency. If a user opens a record and leaves it in edit mode for a long period, other users may be prevented from accessing that record for modification.

5. adLockOptimistic

adLockOptimistic is one of the most commonly used locking approaches. Instead of locking a record for the entire editing period, ADO generally waits until the application submits the change before requesting the necessary lock.

For example:

rs.LockType = adLockOptimistic
rs.Open "SELECT * FROM Products", conn

Consider a product record with a price of 500. User A retrieves the record and changes the price to 550. The record is not necessarily locked throughout the entire period during which User A is editing the information.

When User A executes:

rs.Update

ADO submits the modification to the data provider.

Optimistic locking is useful when conflicts are relatively uncommon. It allows multiple users to work with the database concurrently and generally provides better concurrency than pessimistic locking.

The disadvantage is that a conflict can occur if another user changes the same record before the first user's update is submitted. The application must then handle the resulting update conflict appropriately.

6. adLockBatchOptimistic

adLockBatchOptimistic is designed for scenarios where multiple changes are accumulated and submitted together.

For example:

rs.LockType = adLockBatchOptimistic
rs.Open "SELECT * FROM Employees", conn

The application can make several changes locally and later submit them as a batch.

A typical operation may involve:

rs.UpdateBatch

This approach can be useful when an application needs to process many changes rather than immediately sending every individual modification to the database.

Batch optimistic locking is particularly useful for disconnected or intermittently connected applications, although the exact behavior depends on the provider and cursor configuration.

7. Pessimistic vs Optimistic Locking

The most important distinction is when the record becomes locked.

With pessimistic locking, the application assumes that another user might change the same record, so it attempts to obtain a lock while the record is being edited.

With optimistic locking, the application assumes that conflicts are uncommon. It allows users to work without maintaining an editing lock for the entire editing period and checks for problems when the update is submitted.

The difference can be summarized as follows:

Feature	Pessimistic Locking	Optimistic Locking
Lock timing	During editing	At update time
Concurrency	Lower	Higher
Conflict prevention	Stronger	Conflicts may occur
Suitable for	Frequently conflicting data	Normally low-conflict data
User experience	May involve waiting	Usually more responsive
Resource usage	Potentially higher	Generally lower

8. Choosing the Appropriate Locking Strategy

The appropriate LockType depends on the nature of the application.

For a reporting application, adLockReadOnly is usually appropriate because the application does not need to modify records.

For a normal data-entry application where simultaneous editing conflicts are relatively uncommon, adLockOptimistic is often a practical choice.

For highly sensitive records where simultaneous modification could cause serious problems, adLockPessimistic may be considered.

For applications that make numerous changes and submit them together, adLockBatchOptimistic can be useful.

The database provider also matters. ADO does not independently control every aspect of locking. The underlying OLE DB provider and database management system determine how the requested locking behavior is actually implemented.

9. Example of Optimistic Locking

Consider an employee-management application:

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

rs.LockType = adLockOptimistic

rs.Open "SELECT EmployeeID, Name, Salary FROM Employees", conn

rs.Fields("Salary").Value = 60000
rs.Update

The application retrieves the employee record, changes the salary, and then calls Update.

The important point is that the application does not need to keep the database record locked throughout the entire period in which the user is viewing and editing the information.

This makes optimistic locking suitable for many interactive applications.

10. Example of Pessimistic Locking

A pessimistic approach can be configured as follows:

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

rs.LockType = adLockPessimistic

rs.Open "SELECT EmployeeID, Name, Salary FROM Employees", conn

rs.Fields("Salary").Value = 60000
rs.Update

When the record is being edited, ADO requests the appropriate lock from the provider. Other users attempting to modify the same record may be prevented from doing so until the lock is released.

This can help protect against simultaneous modifications but should be used carefully because prolonged locks can affect other users.

11. Factors That Affect Locking Behavior

Setting the LockType property does not guarantee identical behavior across all database systems. Several factors influence the actual locking behavior.

The first is the database management system. SQL Server, Microsoft Access, Oracle, and other systems can implement locking differently.

The second is the OLE DB provider. ADO communicates with the database through a provider, and the provider determines which requested features it supports.

The third is the cursor type. Certain cursor configurations support particular locking mechanisms better than others.

The fourth is the transaction environment. If the application uses transactions, the lifetime and scope of database locks can be affected by transaction boundaries.

The fifth is the database isolation and concurrency configuration. The underlying database may apply additional rules governing how simultaneous reads and writes are handled.

12. Advantages of Proper Record Locking

Correctly selecting a LockType can provide several benefits. It can help protect data from conflicting modifications, improve multi-user access, reduce unnecessary database locking, and provide better application responsiveness.

For example, using pessimistic locking everywhere could unnecessarily restrict concurrent users. Conversely, using optimistic locking without handling update conflicts could result in unexpected application errors.

Therefore, locking should be selected based on the application's actual data-access requirements rather than simply choosing the strongest available locking method.

13. Important Consideration

LockType should not be treated as a complete solution for all concurrency problems. It is one part of the ADO data-access model. The actual locking behavior depends on ADO, the OLE DB provider, the database engine, cursor configuration, and transaction settings.

Developers should therefore test the selected LockType with the specific database and provider being used.

Conclusion

The ADO LockType property controls how an editable Recordset manages database record locks. adLockReadOnly is appropriate for read-only operations, adLockPessimistic provides stronger protection by locking records during editing, adLockOptimistic favors concurrency by delaying locking until updates are submitted, and adLockBatchOptimistic supports accumulating multiple changes before submitting them.

Understanding these strategies allows developers to select a locking approach that balances data integrity, concurrency, performance, and user experience according to the requirements of the application.