ADO - ADO Recordset LockType and Optimistic/Pessimistic Locking

In ActiveX Data Objects (ADO), multiple users or applications may access and modify the same database records at the same time. When two users attempt to modify the same record simultaneously, the application needs a mechanism to control how those changes are handled. Locking is used for this purpose. ADO provides different locking strategies through the LockType property of the Recordset object. Locking determines when a record should be locked, what type of lock should be applied, and whether other users can read or modify that record while it is being edited.

What is LockType in ADO?

LockType specifies the type of locking that ADO should use when working with records in a Recordset. It becomes particularly important when the application performs operations such as editing, updating, or deleting records.

The general syntax is:

Recordset.LockType = lockTypeValue

ADO provides the following commonly used LockType values:

adLockReadOnly
adLockPessimistic
adLockOptimistic
adLockBatchOptimistic

Each option provides a different approach to controlling concurrent access to data.

1. adLockReadOnly

adLockReadOnly indicates that the Recordset is intended only for reading. The application cannot modify the records through that Recordset.

Example:

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

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

This approach is useful when the application only needs to display or analyze information.

For example, suppose a company has a customer-reporting application. Employees can view customer information but are not allowed to change it. A read-only Recordset is appropriate in this situation.

The major advantage is that it avoids unnecessary locking for modifications because no updates are being performed.

2. adLockPessimistic

adLockPessimistic uses a more restrictive locking strategy. When an application begins editing a record, the provider attempts to lock that record immediately.

The basic idea is:

User starts editing
        |
        v
Record is locked
        |
        v
Other users may be prevented from modifying it
        |
        v
User completes the update
        |
        v
Lock is released

For example, suppose two employees are editing the same customer record.

Employee A opens the record and begins editing it. With pessimistic locking, the record can be locked while Employee A is editing. Employee B may then be unable to modify that same record until Employee A finishes.

This strategy is useful when it is important to prevent other users from changing a record while the current user is actively editing it.

However, pessimistic locking can reduce concurrency. If a user opens a record and spends several minutes editing it without saving, other users may have to wait.

3. adLockOptimistic

adLockOptimistic uses a less restrictive approach. Instead of locking the record for the entire editing period, ADO generally waits until the update is actually submitted before applying the necessary lock.

The process can be represented as:

User reads record
        |
        v
User modifies record locally
        |
        v
User calls Update
        |
        v
Record is locked briefly
        |
        v
Changes are written
        |
        v
Lock is released

For example:

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

rs.Find "EmployeeID = 101"

rs!Salary = 50000
rs.Update

Here, the record does not generally remain locked throughout the user's editing session. The lock is primarily required when the update is sent to the database.

Optimistic locking is particularly useful in applications where many users need simultaneous access to the same data.

Optimistic Locking vs Pessimistic Locking

The main difference is when the record is locked.

Feature	Optimistic Locking	Pessimistic Locking
Locking approach	Lock mainly when updating	Lock while editing
Concurrency	Higher	Lower
Waiting by other users	Less likely	More likely
Resource usage	Generally lower	Generally higher
Suitable for	Multi-user applications	Critical editing operations
Conflict possibility	Higher	Lower
Lock duration	Shorter	Longer

Optimistic locking assumes that conflicts are relatively uncommon. Pessimistic locking assumes that conflicts are more likely and attempts to prevent them before they happen.

4. adLockBatchOptimistic

adLockBatchOptimistic is designed for batch updating. Changes can be made locally and submitted to the database later as a group.

For example:

rs.LockType = adLockBatchOptimistic

The application can make multiple changes and later submit them using an appropriate batch-update operation.

This can be useful when an application needs to process several modifications together rather than immediately sending every change to the database.

Batch optimistic locking is particularly useful in disconnected data-access scenarios.

Why Locking Is Important

Consider an employee database containing this record:

EmployeeID: 101
Name: Ravi
Salary: 40000

Two users open the same record.

User A changes the salary to:

45000

User B changes the salary to:

48000

If both users update the record, a concurrency conflict can occur. The final value may depend on which update reaches the database last.

Locking strategies help applications manage this type of situation.

With pessimistic locking, User B may be prevented from modifying the record while User A is editing it.

With optimistic locking, both users may be allowed to work with the record, but the application or database must handle the possibility that the underlying data has changed before the update occurs.

Choosing the Appropriate LockType

The appropriate LockType depends on the application's requirements.

For applications that only display information, use:

adLockReadOnly

For applications where preventing simultaneous modification is more important than concurrency, consider:

adLockPessimistic

For applications with many concurrent users where conflicts are relatively uncommon, use:

adLockOptimistic

For applications that need to make multiple changes and submit them together, consider:

adLockBatchOptimistic

Example of Optimistic Locking

A simple example using ADO in Visual Basic is:

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

conn.Open "Provider=SQLOLEDB;Data Source=SERVER;Initial Catalog=CompanyDB;Integrated Security=SSPI"

rs.LockType = adLockOptimistic
rs.CursorType = adOpenKeyset

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

rs.MoveFirst

rs!Salary = 55000
rs.Update

rs.Close
conn.Close

Set rs = Nothing
Set conn = Nothing

In this example, the Recordset uses optimistic locking. The application retrieves the employee data, changes the salary, and calls Update to send the modification to the database.

Example of Pessimistic Locking

A pessimistic-locking Recordset can be configured as follows:

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

rs.LockType = adLockPessimistic
rs.CursorType = adOpenKeyset

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

rs.MoveFirst

rs!Salary = 60000
rs.Update

The provider attempts to maintain an appropriate lock while the record is being edited.

The exact locking behavior can depend on the database provider, cursor type, transaction settings, and database engine. Therefore, LockType should not be viewed as an independent guarantee of identical locking behavior across every database system.

Locking and Concurrency

Concurrency means that multiple users or processes can work with database information at the same time.

For example:

User A --------------------+
                            |
                            v
                       Database
                            ^
                            |
User B --------------------+

Without an appropriate concurrency strategy, simultaneous updates can result in lost changes or conflicts.

Pessimistic locking tries to prevent conflicts by restricting access during editing.

Optimistic locking allows greater concurrency and deals with conflicts when changes are submitted.

Therefore, the choice between optimistic and pessimistic locking is essentially a balance between data-conflict prevention and system concurrency.

Important Difference Between LockType and CursorType

LockType and CursorType are related to Recordsets but serve different purposes.

CursorType determines how the Recordset cursor behaves and what type of navigation or visibility of changes it provides.

LockType determines how records are locked when modifications are made.

For example:

rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic

Here, CursorType controls cursor behavior, while LockType controls the record-locking strategy.

Understanding this distinction is important when designing ADO applications.

Advantages of Optimistic Locking

Optimistic locking provides several benefits:

1. It allows more users to work with the database simultaneously.

2. Records are generally locked for a shorter period.

3. It reduces the possibility of users blocking one another during long editing sessions.

4. It is suitable for applications where simultaneous modifications are relatively uncommon.

5. It can improve concurrency in multi-user database applications.

Advantages of Pessimistic Locking

Pessimistic locking is useful when preventing simultaneous modification is critical.

Its advantages include:

1. It reduces the possibility of two users editing the same record simultaneously.

2. It can provide stronger control over concurrent editing.

3. It is useful when conflicts would be particularly costly.

4. It can be appropriate for workflows where a record should effectively belong to one editor at a time.

The disadvantage is that prolonged locks can reduce concurrency and potentially cause other users to wait.

Conclusion

The LockType property in ADO determines how a Recordset handles record locking during data operations. The main choices are adLockReadOnly, adLockPessimistic, adLockOptimistic, and adLockBatchOptimistic. Read-only locking is suitable for applications that only retrieve data, pessimistic locking prioritizes conflict prevention, optimistic locking prioritizes concurrency, and batch optimistic locking is useful when several changes need to be submitted together.

The most important concept to remember is that pessimistic locking locks earlier and more aggressively, while optimistic locking delays locking until the update is performed. Selecting the appropriate strategy helps an ADO application balance data consistency, concurrency, performance, and user experience.