- Transaction Isolation (Consistency) Levels

This section discusses the advantages of making transactions fully isolated from each other in order that they may be executed concurrently, but produces the same results as when executed one at a time.

In addition to providing recovery capabilities, transaction-processing systems can help protect application data consistency. Multiple retrieval by a transaction is consistent when they reflect either all or none of the changes made by other individual transactions. A fully consistent transaction is atomic with respect to concurrency (fully isolated from other transactions) and a set of such transactions is serialized. This means that, although executed concurrently, the results of each transaction are the same as if the transactions had been executed one at a time in some order.

Unfortunately, providing increased consistency requires extra locking, although the additional locks are often shared rather than exclusive (allowing other transactions to read but not write). Because this reduces concurrency, a DBMS or file system, allowing developers to select a level appropriate to the application may provide a choice of isolation levels. The default isolation level varies depending on the DBMS or file system in use and may be configurable by a system administrator.

Isolation levels cannot be specified in PROIV. Where a DBMS or file system provides a choice of isolation levels, the required level must be configured externally. The table below shows the typical locking effect of some recognized isolation levels.

Isolation Level	Locking of Data Read Without Write-Intent	Consistency
Uncommitted Read	No locking	Minimum
Committed Read	Record lock released immediately after read	\|
Cursor Stability	Record lock released when next record read	\|
Repeatable Read	Record locks held until end-of-transaction	\|
Serializable	All referenced parts of files locked until E-O-T	Maximum

Table 27. Database Isolation Levels and their Effects

The Oracle RDBMS is an exception to this because it avoids locking records (rows) selected without update intent by using rollback information to reconstruct them when necessary. Using this technique, Oracle transactions have a default isolation level called Statement Consistency that lies between Cursor Stability and Repeatable Read.

Comment on this topic

Topic ID: 720256