In database systems, isolation is one of the ACID (*Atomicity, Consistency, Isolation, Durability*) transaction properties. It determines how transaction integrity is visible to other users and systems. A lower isolation level increases the ability of many users to access the same data at the same time, but also increases the number of concurrency effects (such as dirty reads or lost updates) users might encounter. Conversely, a higher isolation level reduces the types of concurrency effects that users may encounter, but requires more system resources and increases the chances that one transaction will block another.[1]
Concurrency control comprises the underlying mechanisms in a DBMS which handle isolation and guarantee related correctness. It is heavily used by the database and storage engines both to guarantee the correct execution of concurrent transactions, and (via different mechanisms) the correctness of other DBMS processes. The transaction-related mechanisms typically constrain the database data access operations' timing (transaction schedules) to certain orders characterized as the serializability and recoverability schedule properties. Constraining database access operation execution typically means reduced performance (measured by rates of execution), and thus concurrency control mechanisms are typically designed to provide the best performance possible under the constraints. Often, when possible without harming correctness, the serializability property is compromised for better performance. However, recoverability cannot be compromised, since such typically results in a database integrity violation.
The ANSI/ISO standard SQL 92 refers to three different read phenomena when a transaction retrieves data that another transaction might have updated.
In the following examples, two transactions take place. In transaction 1, a query is performed, then in transaction 2, an update is performed, and finally in transaction 1, the same query is performed again.
The examples use the following relation:
Users
| id | name | age |
|---|---|---|
| 1 | Alice | 20 |
| 2 | Bob | 25 |
Main article: Write-read conflict
A dirty read (aka uncommitted dependency) occurs when a transaction retrieves a row that has been updated by another transaction that is not yet committed.
In this example, transaction 1 retrieves the row with id 1, then transaction 2 updates the row with id 1, and finally transaction 1 retrieves the row with id 1 again. Now if transaction 2 rolls back its update (already retrieved by transaction 1) or performs other updates, then the view of the row may be wrong in transaction 1. At the READ UNCOMMITTED isolation level, the second SELECT in transaction 1 retrieves the updated row: this is a dirty read. At the READ COMMITTED, REPEATABLE READ, and SERIALIZABLE isolation levels, the second SELECT in transaction 1 retrieves the initial row.
| Transaction 1 | Transaction 2 |
|---|---|
| `BEGIN; | |
| SELECT age FROM users WHERE id = 1; | |
| -- retrieves 20` | |
| `BEGIN; | |
| UPDATE users SET age = 21 WHERE id = 1; | |
| -- no commit here` | |
| `SELECT age FROM users WHERE id = 1; | |
| *-- READ UNCOMMITTED retrieves 21 (dirty read) | |
| -- READ COMMITTED retrieves 20 (dirty read has been avoided) | |
| -- REPEATABLE READ retrieves 20 (dirty read has been avoided) | |
| -- SERIALIZABLE retrieves 20 (dirty read has been avoided)* | |
| COMMIT;` | |
**ROLLBACK**; |
A non-repeatable read occurs when a transaction retrieves a row twice and that row is updated by another transaction that is committed in between.
In this example, transaction 1 retrieves the row with id 1, then transaction 2 updates the row with id 1 and is committed, and finally transaction 1 retrieves the row with id 1 again. At the READ UNCOMMITTED and READ COMMITTED isolation levels, the second SELECT in transaction 1 retrieves the updated row: this is a non-repeatable read. At the REPEATABLE READ and SERIALIZABLE isolation levels, the second SELECT in transaction 1 retrieves the initial row.
| Transaction 1 | Transaction 2 |
|---|---|
| `BEGIN; | |
| SELECT age FROM users WHERE id = 1; | |
| -- retrieves 20` | |
| `BEGIN; | |
| UPDATE users SET age = 21 WHERE id = 1; | |
| COMMIT;` | |
| `SELECT age FROM users WHERE id = 1; | |
| *-- READ UNCOMMITTED retrieves 21 (non-repeatable read) | |
| -- READ COMMITTED retrieves 21 (non-repeatable read) | |
| -- REPEATABLE READ retrieves 20 (non-repeatable read has been avoided) | |
| -- SERIALIZABLE retrieves 20 (non-repeatable read has been avoided)* | |
| COMMIT;` |
A phantom read occurs when a transaction retrieves a set of rows twice and new rows are inserted into or removed from that set by another transaction that is committed in between.