Isolation (database)

from Wikipedia, the free encyclopedia

In computer science , the term isolation describes the separation of transactions in such a way that an ongoing transaction cannot be brought into an undefined state by a parallel transaction by changing the data used. Isolation is one of the four ACID properties.

example

The following is an example of a transaction related to a warehouse database. More precisely, it is a possible transaction that could occur when inserting a new article into an inventory management system.

Start of transaction
Select all data records that contain the text "biscuit pudding" in the Title column (Action 1)
If such a record exists
warn the user and display the existing entry
otherwise
Add entry "biscuit pudding" (action 2)
Transaction end

At least one or at most two individual work steps occur during this database transaction. If two users enter the same data record into the hypothetical inventory control system at the same time, it can happen that the corresponding transactions run in parallel in an unfavorable way:

time Transaction 1 Transaction 2 Result
1 Action 1 No entries found.
2 Action 1 No entries found.
3 Action 2 Entry is added.
4th Action 2 Entry is added again .

The formation of doubles can be avoided by isolating these two parallel transactions. In the case of isolation through serialization (strict separation of transactions and their execution in sequence), this process could look like this, for example:

time Transaction 1 Transaction 2 Result
1 Action 1 No entries found.
2 [Action 1] Table locked: transaction must wait.
3 Action 2 Entry is added.
4th Action 1 Transaction continues. New record is found.

Possible problems

In the case of databases, the following problems ( anomalies ) can essentially occur due to a lack of transaction isolation :

  1. Dirty Read : Data from a transaction that has not yet been completed is being read by another transaction.
  2. Lost Updates : Two transactions modify the same data record in parallel and after these two transactions have expired, only the change is adopted by one of them.
  3. Non-Repeatable Read : Repeated read processes produce different results.
  4. Phantom Read : Search criteria apply to different data records during a transaction because another transaction (in the course of this transaction) has added, removed or changed data records.

Transaction isolation in SQL

The ANSI / ISO SQL standard (SQL-92) of the database interface SQL provides for four transaction isolation levels, which, however, are not all supported by all database systems.
The following table provides an overview of the quality and the problems that arise when using the various insulation levels:

Isolation level Dirty Read Lost updates Non-repeatable read phantom
Read Uncommitted possible also possible with Db2 CS possible possible
Read committed impossible also possible with Db2 CS possible possible
Repeatable Read impossible impossible impossible possible
Serializable impossible impossible impossible impossible

Read Uncommitted

At this isolation level, read operations ignore any locks, so the anomalies Lost Update, Dirty Read, Non-Repeatable Read, and the Phantom problem can occur. The SQL-92 standard specifies that a transaction is either carried out in full or not at all at each isolation level and that no updates may be lost, but this does not always seem to be guaranteed with Read Uncommitted, depending on the implementation or locking procedure. So-called dirty writes are prevented in any case, but lost updates can still occur despite this restriction. For this reason, an isolation level is implemented in most DBMS , the locking behavior of which prevents all lost updates and thus offers more protection than the Read Uncommitted of the SQL-92 standard.

Read committed

This isolation level sets write locks on objects that are to be changed for the entire transaction, but only sets read locks for a short time when the data is actually read. Therefore, non-repeatable read and phantom read can occur if, during repeated read operations on the same data, between the first and the second read operation, a write operation of another transaction changes and commits the data. Under DB2 , for example, this isolation level is called a cursor stability (CS).

Repeatable Read

This isolation level ensures that repeated read operations with the same parameters also have the same results. Locks are set for the entire duration of the transaction for both read and write operations. This means that apart from phantom reads, no anomalies can occur.

Serializable

The highest level of isolation guarantees that the effect of transactions running in parallel is exactly the same as what the corresponding transactions would show if they took place one after the other. This ensures that no transaction is lost and that no two transactions affect each other. However, since most database systems only maintain an illusion of sequential execution without actually processing all transactions individually, it can happen that a transaction has to be aborted from the database side. An application that works with a database for which the isolation level Serializable has been selected must therefore be able to deal with serialization errors and, if necessary, restart the corresponding transaction.

MVCC

MVCC (Multi Version Concurrency Control) ensures the shortest possible waiting times by creating versions. "Lost updates" can occur due to version jumps, which can be prevented with "select ... for update" or by blocking the data. Read operations never cause latency because write operations and read operations do not access the same version. In order to be able to save several versions, much more memory is required. The "dirty read" cannot occur because write operations only generate a version that is accessible to read operations after a "commit".

Isolation level Dirty Read Non-repeatable read Phantom Read Lost Updates 1
Read committed impossible possible possible possible
Repeatable Read impossible impossible possible possible
Serializable impossible impossible impossible possible
1Can occur when a transaction begins with a read operation and later performs a write operation. If a transaction does a write between the two operations, it will access the same version as the first. Therefore one of the writing actions is lost.

Individual evidence

  1. ↑ http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt , p. 69
  2. ↑ http://research.microsoft.com/apps/pubs/default.aspx?id=69541 , p. 7
  3. ↑ http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt , p. 68
  4. ↑ http://research.microsoft.com/apps/pubs/default.aspx?id=69541 , p. 5
  5. ↑ http://research.microsoft.com/apps/pubs/default.aspx?id=69541 , p. 7
  6. ↑ http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzcursorstability.htm

Web links