|
There are three common types of inconsistency that can occur during the execution of concurrent transactions.
These three types are mentioned in the ISO SQL standard and are defined in terms of the behaviors that can occur at the lower isolation levels. This list is not exhaustive as other types of inconsistencies can also occur.
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.
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 is changed or deleted.
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 Transaction A's condition. If Transaction A then repeats the read, it obtains the updated set of rows.
The database server allows dirty reads, non-repeatable reads, and phantom rows, depending on the isolation level that is used. An X in the following table indicates that the behavior is allowed for that isolation level.
| Isolation level | Dirty reads | Non-repeatable reads | Phantom rows |
|---|---|---|---|
| 0-read uncommitted | X | X | X |
| readonly-statement-snapshot | X1 | X2 | X3 |
| 1-read committed | X | X | |
| statement-snapshot | X2 | X3 | |
| 2-repeatable read | X | ||
| 3-serializable | |||
| snapshot |
1 Dirty reads can occur for updatable statements within a transaction if the isolation level specified by the updatable_statement_isolation option does not prevent them from occurring.
2 Non-repeatable reads can occur for statements within a transaction if the isolation level specified by the updatable_statement_isolation option does not prevent them from occurring. Non-repeatable reads can occur because each statement starts a new snapshot, so one statement may see changes that another statement does not see.
3 Phantom rows can occur for statements within a transaction if the isolation level specified by the updatable_statement_isolation option does not prevent them from occurring. Phantom rows can occur because each statement starts a new snapshot, so one statement may see changes that another statement does not see.
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.
For statement snapshot isolation levels, non-repeatable reads and phantom rows can occur within a transaction, but not within a single statement in a transaction.
The isolation levels have different names under ODBC. These names are based on the names of the inconsistencies that they prevent.