Transaction - Isolation (Level|Degree) - (Locking Level ?)

> (Data|State) Management and Processing > Data Properties and Transactions

1 - About

serializability defines a complete isolation between process.

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.

The SQL standard, which has been adopted by both ANSI and ISO/IEC, defines four levels of transaction isolation (Isolation Levels)

Advertising

3 - Definition

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:

  • CURSOR STABILITY
  • SNAPSHOT ISOLATION
  • READ CONSISTENCY

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.

3.1 - Read uncommitted

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.

Advertising

3.2 - Read committed

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.

3.3 - Repeatable Read

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.

3.4 - Serializable

A serializable transaction operates in an environment that makes it appear as if no other users were modifying data in the database.

See Serializable Isolation level

4 - Lock

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.

Advertising

5 - Oracle

Oracle Database offers:

  • the read committed (default)
  • and serializable isolation levels.

Also, the database offers a read-only mode.

6 - Documentation / Reference

  • Architecture of a Database System Joseph M. Hellerstein, Michael Stonebraker and James Hamilton
data/property/isolation_level.txt · Last modified: 2018/10/19 10:48 by gerardnico