ADO - Transaction Isolation Levels in ADO

Introduction

In multi-user database applications, multiple users often access and modify the same data simultaneously. For example, in a banking system, several employees may update customer accounts at the same time. If these operations are not properly controlled, data inconsistencies can occur, leading to incorrect results.

ADO (ActiveX Data Objects) supports database transactions that ensure data integrity during multiple database operations. A transaction is a sequence of one or more SQL statements treated as a single unit of work. Either all operations within the transaction are successfully completed, or none of them are applied.

Transaction Isolation Levels define how one transaction interacts with other concurrent transactions. They determine how much one transaction can see the changes made by another transaction before those changes are permanently committed to the database.

Choosing the correct isolation level is important because it balances data consistency with application performance.

What is a Transaction?

A transaction is a logical group of database operations that must either succeed together or fail together.

For example, consider transferring money between two bank accounts:

Deduct money from Account A.
Add the same amount to Account B.

If the deduction succeeds but the addition fails, the database becomes inconsistent. A transaction ensures that either both operations succeed or both are rolled back.

Example:

con.BeginTrans

con.Execute "UPDATE Accounts SET Balance = Balance - 1000 WHERE AccountNo = 101"

con.Execute "UPDATE Accounts SET Balance = Balance + 1000 WHERE AccountNo = 102"

con.CommitTrans

If an error occurs:

con.RollbackTrans

What is Transaction Isolation?

Transaction isolation determines how transactions are separated from one another while they are executing.

Suppose two users access the same employee salary record:

User A updates the salary.
User B reads the salary at the same time.

The isolation level determines whether User B sees:

The old salary
The new salary
An uncommitted salary
Or waits until User A finishes

Different isolation levels provide different behaviors.

Why Isolation Levels Are Important

Without proper isolation:

Incorrect data may be read.
Records may change unexpectedly.
Duplicate records may appear.
Reports may become inaccurate.
Financial calculations may produce incorrect results.

Isolation levels help prevent these problems.

Common Data Problems

Dirty Read

A dirty read occurs when one transaction reads data that has been modified by another transaction but not yet committed.

Example

Transaction A

Salary = 50000

Update Salary = 60000

Transaction B immediately reads:

Salary = 60000

Before Transaction A commits, it rolls back.

The actual salary remains:

Transaction B has read incorrect data.

Non-Repeatable Read

A non-repeatable read occurs when the same record is read twice within a transaction, but another transaction modifies it in between.

Example

Transaction A

Read Salary = 50000

Transaction B

Update Salary = 55000
Commit

Transaction A

Read Salary again = 55000

The same query returns different values.

Phantom Read

A phantom read occurs when rows are added or removed by another transaction between two executions of the same query.

Example

Transaction A

SELECT * FROM Employees

Returns:

100 Employees

Transaction B

Insert New Employee
Commit

Transaction A executes the same query again.

Now it returns:

101 Employees

The extra row is called a phantom row.

Isolation Levels in ADO

ADO works with the isolation levels supported by the underlying database.

The common isolation levels are:

Read Uncommitted
Read Committed
Repeatable Read
Serializable

1. Read Uncommitted

This is the lowest isolation level.

Transactions can read data even if another transaction has not committed it.

Characteristics

Fastest performance
Lowest locking
Allows dirty reads
Allows non-repeatable reads
Allows phantom reads

Example

Transaction A updates salary.

Transaction B immediately reads:

Even if Transaction A later rolls back.

Advantages

High performance
Minimal locking
Suitable for temporary reporting

Disadvantages

Data may be incorrect
Unsafe for financial applications

Suitable For

Read-only reports
Monitoring systems
Non-critical analytical queries

2. Read Committed

This is the default isolation level in many database systems.

A transaction cannot read data until another transaction commits it.

Characteristics

Prevents dirty reads
Allows non-repeatable reads
Allows phantom reads

Example

Transaction A

Update Salary = 60000

Transaction B attempts to read.

It waits until Transaction A commits.

After commit:

Salary = 60000

Advantages

Better consistency
Good performance
Widely used

Disadvantages

Same record may change between reads
Phantom rows may still occur

Suitable For

Business applications
Inventory systems
Customer management systems
Most enterprise software

3. Repeatable Read

This isolation level guarantees that if a transaction reads a row, the row cannot be modified by another transaction until the first transaction completes.

Characteristics

Prevents dirty reads
Prevents non-repeatable reads
Allows phantom reads

Example

Transaction A

Read Salary = 50000

Transaction B attempts:

Update Salary

The update waits until Transaction A completes.

Advantages

Stable data during the transaction
Better consistency

Disadvantages

More locking
Reduced concurrency
Phantom rows may still appear

Suitable For

Payroll systems
Banking applications
Order processing

4. Serializable

Serializable is the highest isolation level.

Transactions execute as if they were running one after another rather than concurrently.

Characteristics

Prevents dirty reads
Prevents non-repeatable reads
Prevents phantom reads

Example

Transaction A

SELECT * FROM Orders

Transaction B cannot:

Insert new orders
Delete orders
Update matching records

Until Transaction A finishes.

Advantages

Highest data consistency
Safest for critical applications

Disadvantages

Maximum locking
Lower performance
Increased waiting time
Higher chance of blocking

Suitable For

Banking systems
Financial software
Government databases
Online payment systems

Setting Isolation Level in ADO

ADO allows the isolation level to be specified using the IsolationLevel property of the Connection object before starting a transaction.

Example

Dim con As New ADODB.Connection

con.Open ConnectionString

con.IsolationLevel = adXactReadCommitted

con.BeginTrans

Other commonly used constants include:

adXactReadUncommitted
adXactReadCommitted
adXactRepeatableRead
adXactSerializable

Comparison of Isolation Levels

Isolation Level	Dirty Reads	Non-Repeatable Reads	Phantom Reads	Performance
Read Uncommitted	Yes	Yes	Yes	Very High
Read Committed	No	Yes	Yes	High
Repeatable Read	No	No	Yes	Medium
Serializable	No	No	No	Lower

Choosing the Right Isolation Level

The appropriate isolation level depends on the application's requirements.

Read Uncommitted

Choose when:

Speed is more important than absolute accuracy.
The data is used only for reporting or monitoring.

Read Committed

Choose when:

Building general business applications.
A balance between performance and consistency is required.

Repeatable Read

Choose when:

A transaction must repeatedly read the same data without changes.
Accurate updates are important.

Serializable

Choose when:

Every transaction must produce completely consistent results.
The application handles financial or highly sensitive information.

Real-World Applications

Banking System

Money transfers, account balance updates, and loan processing require the Serializable isolation level to ensure complete accuracy and prevent conflicting transactions.

Online Shopping

Read Committed is commonly used to process orders, update customer details, and maintain inventory while providing good performance.

Payroll Management

Repeatable Read helps ensure that employee salary records remain unchanged while payroll calculations are in progress.

Reporting Dashboard

Read Uncommitted may be used to generate quick reports where slight inconsistencies are acceptable and real-time performance is more important.

Airline Reservation System

When multiple users attempt to book the same seat, Serializable isolation helps prevent double booking by ensuring only one transaction can complete at a time.

Best Practices

Use the lowest isolation level that still satisfies the application's consistency requirements.
Keep transactions as short as possible to reduce locking and improve concurrency.
Begin a transaction only when necessary and commit or roll it back promptly.
Avoid lengthy user interactions while a transaction is active.
Test transaction behavior under concurrent access to identify blocking or deadlock issues.
Monitor database performance and adjust isolation levels if excessive locking affects throughput.
Combine proper isolation levels with error handling and rollback logic to maintain data integrity.

Advantages of Using Appropriate Isolation Levels

Maintains database consistency.
Prevents invalid or incomplete data from being read.
Reduces the risk of data corruption in concurrent environments.
Supports reliable transaction processing.
Helps balance performance with data accuracy.
Improves the reliability of enterprise applications.

Limitations

Higher isolation levels increase locking and reduce concurrency.
Excessive locking can slow down applications with many simultaneous users.
Improper isolation level selection may either compromise data accuracy or unnecessarily impact performance.
Some database systems may implement isolation levels differently, so application behavior should always be tested with the target database.

Conclusion

Transaction Isolation Levels are a fundamental aspect of ADO-based database programming. They control how concurrent transactions interact and determine the visibility of data changes between users. By understanding the characteristics of Read Uncommitted, Read Committed, Repeatable Read, and Serializable, developers can design applications that maintain data integrity while delivering acceptable performance. Selecting the appropriate isolation level for each scenario ensures reliable, consistent, and efficient database operations in multi-user environments.