ASA SQL User's Guide
Using Transactions and Isolation Levels
Isolation levels and consistency
There are three typical types of inconsistency that can occur during the execution of concurrent transactions. This list is not exhaustive as other types of inconsistencies can also occur. These three types are mentioned in the ISO SQL/92 standard and are important because behavior at lower isolation levels is defined in terms of them.
Dirty read Transaction A modifies a row, but does not commit or roll back the change. Transaction B reads the modified row. Transaction A then either further changes the row before performing a COMMIT, or rolls back its modification. In either case, transaction B has seen the row in a state which was never committed.
For more information about how isolation levels create dirty reads, see Dirty read tutorial.
Non-repeatable read Transaction A reads a row. Transaction B then modifies or deletes the row and performs a COMMIT. If transaction A then attempts to read the same row again, the row will have been changed or deleted.
For more information about non-repeatable reads, see Non-repeatable read tutorial.
Phantom row Transaction A reads a set of rows that satisfy some condition. Transaction B then executes an INSERT, or an UPDATE on a row which did not previously meet A's condition. Transaction B commits these changes. These newly committed rows now satisfy the condition. Transaction A then repeats the initial read and obtains a different set of rows.
For more information about phantom rows, see Phantom row tutorial.
Other types of inconsistencies can also exist. These three were chosen for the ISO SQL/92 standard because they are typical problems and because it was convenient to describe amounts of locking between transactions in terms of them.
The isolation levels are different with respect to the type of inconsistent behavior that Adaptive Server Anywhere allows. An x means that the behavior is prevented, and a 
means that the behavior may occur.
| Isolation level | Dirty reads | Non-repeatable reads | Phantom rows |
|---|---|---|---|
| 0 |
|
|
|
| 1 | x |
|
|
| 2 | x | x |
|
| 3 | x | x | x |
This table demonstrates two points:
Each isolation level eliminates one of the three typical types of inconsistencies.
Each level eliminates the types of inconsistencies eliminated at all lower levels.
The four isolation levels have different names under ODBC. These names are based on the names of the inconsistencies that they prevent, and are described in The ValuePtr parameter.