ADO - ADO Recordset Locking Strategies

ADO Recordset locking strategies determine how and when records are locked while an application reads, updates, inserts, or deletes database records. Locking is important when multiple users or applications access the same database at the same time. Without an appropriate locking strategy, two users could modify the same record simultaneously, potentially causing data conflicts or unexpected results.

In ADO, locking is primarily controlled through the LockType property of the Recordset object. The selected lock type determines whether records are read-only, locked when edited, locked immediately when editing begins, or modified using optimistic batch techniques. Choosing the correct locking strategy depends on the application's requirements, the number of concurrent users, and how frequently data is modified.

1. Why Recordset Locking Is Required

Consider an application where two users access the same customer record.

Suppose the customer's credit limit is initially:

Credit Limit = 50,000

User A reads the record and changes the credit limit to:

60,000

At nearly the same time, User B reads the original value and changes it to:

55,000

If both users update the record without an appropriate concurrency mechanism, one user's modification may overwrite the other's change.

Locking strategies help control situations like this by determining when a record can be modified and whether another user can modify it simultaneously.

The main objectives of locking are:

• Prevent conflicting updates.

• Control simultaneous access to records.

• Maintain data consistency.

• Reduce unnecessary database conflicts.

• Provide an appropriate balance between concurrency and data protection.

2. LockType Property

ADO provides the LockType property to specify the type of locking used by a Recordset.

The commonly used ADO lock types are:

adLockReadOnly
adLockPessimistic
adLockOptimistic
adLockBatchOptimistic

These values represent different approaches to handling modifications.

For example:

rs.LockType = adLockOptimistic

This specifies optimistic locking for the Recordset.

The lock type is normally established when the Recordset is opened.

rs.Open sql, connection, adOpenKeyset, adLockOptimistic

Here:

adOpenKeyset       = Cursor type
adLockOptimistic   = Locking strategy

The cursor type and lock type are different concepts. The cursor determines how records can be navigated and what changes can be observed, while the lock type determines how modifications are handled.

3. Read-Only Locking

The adLockReadOnly option means that the Recordset cannot be used to modify records.

Example:

rs.Open sql, connection, adOpenForwardOnly, adLockReadOnly

This strategy is suitable when the application only needs to retrieve information.

For example, a reporting application might retrieve:

Customer Name
Customer ID
Order Date
Order Amount

but does not need to change any of these values.

Since no modifications are allowed through the Recordset, there is no need to acquire update locks.

Advantages

Read-only access is generally simple and efficient. It can also reduce locking overhead because the application is not attempting to modify the retrieved records.

Suitable situations

It is appropriate for:

• Reports.

• Data viewing screens.

• Search results.

• Read-only dashboards.

• Reference-data retrieval.

4. Pessimistic Locking

Pessimistic locking assumes that a conflict is likely to occur and therefore locks the record while it is being edited.

The corresponding ADO constant is:

adLockPessimistic

Example:

rs.Open sql, connection, adOpenKeyset, adLockPessimistic

Suppose a user opens an employee record and starts editing it. With pessimistic locking, the database may lock the corresponding record during the editing operation.

Another user attempting to modify the same record may have to wait until the first user's operation is completed.

The basic idea is:

User A starts editing
        |
        v
Record becomes locked
        |
        v
User A modifies record
        |
        v
User A saves changes
        |
        v
Lock is released

Advantages

Pessimistic locking can provide strong protection against simultaneous modifications because another user cannot freely modify the locked record.

Disadvantages

The major disadvantage is reduced concurrency.

If a user opens a record and leaves the application idle for several minutes, the record may remain locked for that period, depending on the database and transaction behavior.

This can cause:

• Waiting by other users.

• Lock contention.

• Reduced application responsiveness.

• Increased resource usage.

Therefore, pessimistic locking should be used carefully.

5. Optimistic Locking

Optimistic locking takes the opposite approach.

It assumes that conflicts are relatively uncommon. Instead of locking the record throughout the editing process, the application allows users to work with the data and checks for conflicts when the update is submitted.

The ADO constant is:

adLockOptimistic

Example:

rs.Open sql, connection, adOpenKeyset, adLockOptimistic

A simplified workflow is:

User reads record
       |
       v
User edits record
       |
       v
No long-term lock is maintained
       |
       v
User submits update
       |
       v
Database performs update

This approach generally allows more users to work with the database simultaneously.

Advantages

Optimistic locking is useful when:

• Conflicts are uncommon.

• Users may take some time to edit data.

• High concurrency is important.

• Applications need to minimize locking duration.

Disadvantages

If another user changes the same record before the first user's update is completed, a conflict can occur.

The application therefore needs an appropriate mechanism for detecting and handling update conflicts.

6. Pessimistic vs Optimistic Locking

The fundamental difference can be summarized as follows:

Feature	Pessimistic Locking	Optimistic Locking
Basic assumption	Conflicts are likely	Conflicts are uncommon
Locking approach	Locks while editing	Usually delays locking until update
Concurrency	Lower	Higher
Waiting	More likely	Less likely
Resource usage	Can be higher	Generally lower
Suitable for	Highly contested records	Collaborative applications with fewer conflicts

For example, imagine an inventory application where thousands of users are trying to update the same small number of stock records. Pessimistic locking may prevent some conflicting updates but could also create considerable contention.

In another application where users occasionally edit customer information, optimistic locking may provide better concurrency.

7. Batch Optimistic Locking

ADO also provides:

adLockBatchOptimistic

This locking strategy is associated with batch updates.

Instead of immediately sending every modification to the database, changes can be accumulated locally and submitted together.

A simplified process is:

Retrieve records
       |
       v
Modify records locally
       |
       v
Accumulate changes
       |
       v
Submit changes as a batch
       |
       v
Database processes updates

This can be useful when applications need to make several changes before synchronizing them with the database.

For example:

rs.Open sql, connection, adOpenKeyset, adLockBatchOptimistic

After making changes, the application can use:

rs.UpdateBatch

to submit pending changes.

Batch optimistic locking can be particularly useful in disconnected or intermittently connected scenarios, although the exact behavior depends on the provider and data source.

8. Locking and the Update Method

When using an editable Recordset, an application may modify a field and then call Update.

For example:

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

The locking strategy influences how ADO and the underlying provider handle the modification.

With optimistic locking, the application generally works with the record without maintaining a long-lasting edit lock.

With pessimistic locking, a lock can be obtained earlier in the editing process.

The actual behavior can vary according to the database provider, cursor configuration, transaction settings, and underlying database system.

Therefore, developers should not assume that an ADO lock type operates identically across every database provider.

9. Locking and Concurrent Users

Concurrency refers to multiple users or processes working with the same data simultaneously.

For example:

             Database
                |
       -------------------
       |                 |
     User A            User B
       |                 |
    Edit data          Read data

The locking strategy determines how these simultaneous operations interact.

A good locking strategy should balance two competing requirements:

Data consistency
       +
High concurrency

Using excessive locking may protect data but reduce performance. Using insufficient concurrency control may increase the possibility of conflicting changes.

10. Choosing the Appropriate Locking Strategy

There is no single locking strategy that is ideal for every ADO application.

A general approach is:

Use adLockReadOnly

When the application only retrieves data and does not modify records.

Use adLockOptimistic

When users need to modify records and conflicts are relatively uncommon.

This is often a practical choice for ordinary data-entry applications.

Use adLockPessimistic

When preventing simultaneous modifications is particularly important and the application can tolerate reduced concurrency.

Use adLockBatchOptimistic

When modifications need to be accumulated and submitted as a batch, particularly in scenarios involving disconnected Recordsets and batch synchronization.

11. Important Consideration: Lock Type Is Not the Same as Transaction Isolation

A common source of confusion is treating Recordset locking and transaction isolation as the same thing.

They are related to concurrency but serve different purposes.

The ADO LockType property controls how a Recordset handles modifications and record locking.

Transaction isolation, on the other hand, controls how database transactions interact with other transactions and what data can be seen while transactions are executing.

For example, concepts such as:

Read Uncommitted
Read Committed
Repeatable Read
Serializable

belong to transaction isolation rather than simply being ADO Recordset lock types.

Understanding this distinction is important when designing database applications.

12. Provider Dependency

ADO acts as a data-access layer between an application and an underlying data provider.

Therefore, the exact behavior of locking can depend on:

• Database system.

• OLE DB provider.

• Cursor type.

• Cursor location.

• Transaction configuration.

• Database table structure.

• Database isolation settings.

• Provider capabilities.

For this reason, developers should test locking behavior against the actual database environment rather than relying only on theoretical ADO behavior.

13. Example Scenario

Consider an employee-management application.

An administrator opens an employee record:

Employee ID: 105
Name: Rahul
Salary: 45,000

The administrator changes the salary to:

50,000

With pessimistic locking, the employee record may be locked while the administrator edits it.

With optimistic locking, the administrator can edit the record without maintaining a long-term lock, and the system checks the update when it is submitted.

With read-only locking, the administrator would not be able to update the record through that Recordset.

With batch optimistic locking, the application could make multiple changes and submit them together.

14. Best Practices

When working with ADO Recordset locking strategies, developers should:

1. Use read-only locking whenever modification is unnecessary.

2. Prefer optimistic locking when high concurrency is more important than preventing every possible simultaneous edit.

3. Use pessimistic locking only when its stronger locking behavior is genuinely required.

4. Avoid holding records in an edit state longer than necessary.

5. Keep database operations short.

6. Test locking behavior with the actual database provider.

7. Handle update conflicts appropriately.

8. Consider transaction behavior separately from Recordset locking.

9. Use batch optimistic locking when batch synchronization is appropriate.

10. Monitor performance when many users access the same records.

Conclusion

ADO Recordset locking strategies provide mechanisms for controlling how applications modify database records when multiple users are working with the same data. The major strategies are adLockReadOnly, adLockPessimistic, adLockOptimistic, and adLockBatchOptimistic.

The key distinction is between pessimistic locking, which attempts to prevent conflicts by locking records during editing, and optimistic locking, which prioritizes concurrency and deals with conflicts when updates are submitted. Read-only locking is appropriate when no modification is required, while batch optimistic locking is useful when changes need to be accumulated and submitted together.

Selecting the correct strategy requires considering data consistency, concurrency, application behavior, database provider capabilities, and transaction requirements. A well-designed ADO application uses the least restrictive locking strategy that still provides the required level of data integrity.