Very early in the development of the transaction concept (ie lock concept), attempts were made to increase concurrency by providing weaker isolation level than the serialiable one.
serializability defines a complete isolation between process.
The SQL standard, which has been adopted by both ANSI and ISO/IEC, defines four levels of transaction isolation (Isolation Levels)
The transaction isolation levels is defined in terms of phenomena (data problem) that are either permitted or prevented for each isolation level.
Preventable Read Phenomena by Isolation Level
| Number | Isolation Level | Dirty Read | Nonrepeatable Read | Phantom Read |
|---|---|---|---|---|
| 1 | Read uncommitted | Possible | Possible | Possible |
| 2 | Read committed | Not possible | Possible | Possible |
| 3 | Repeatable read | Not possible | Not possible | Possible |
| 4 | Serializable | Not possible | Not possible | Not possible |
In addition to the standard ANSI SQL isolation levels, various vendors provide additional levels such as:
These levels have differing degrees of impact on transaction processing throughput. Complete isolation of concurrently running transactions could mean that one transaction cannot perform an insertion into a table being queried by another transaction. (Not true with a write-ahead log) In short, real-world considerations usually require a compromise between perfect transaction isolation and performance.
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:
Also, the database offers a read-only mode.
See https://github.com/ept/hermitage (Blog testing the I in acid)