The SQL standard, which has been adopted by both ANSI and ISO/IEC, defines four levels of transaction isolation (Isolation Levels)
A transaction may read any version of data, committed or not.
This is achieved in a locking implementation by read requests proceeding without acquiring any locks.
A transaction may read any committed version of data.
Repeated reads of an object may result in different (committed) versions. This is achieved by read requests acquiring a read lock before accessing an object, and unlocking it immediately after access.
A transaction will read only one version of committed data; once the transaction reads an object, it will always read the same version of that object.
This is achieved by read requests acquiring a read lock before accessing an object, and holding the lock until end-of-transaction.
A serializable transaction operates in an environment that makes it appear as if no other users were modifying data in the database.
Systems provide the four isolation levels via locking-based implementations of concurrency control.
Default isolation implementation is generally, where data can not be read while it is modified, forbidding the return of ghost data to end users.
The isolation level of the data server enforces default locking behaviour. Changing the isolation level will affect how shared or exclusive locks must be set on the data for the entire database system.
See Gray’s work on “Degrees of Consistency” - work attempted to provide both a declarative definition of consistency degrees, and implementations in terms of locking.
Oracle Database offers:
- the read committed (default)
- and serializable isolation levels.
Also, the database offers a read-only mode.
Library / Test
Documentation / Reference
- Architecture of a Database System Joseph M. Hellerstein, Michael Stonebraker and James Hamilton