Transaction isolation level

Transaction isolation level is applied to transactions in general and is directly related with the ACID transaction properties. Isolation level defines how the changes made to some data repository by one transaction affect other simultaneous concurrent transactions, and also how and when that changed data becomes available to other transactions.
The list of transaction isolation levels are given below.
1. READ_UNCOMMITTED
2. READ_COMMITTED
3. REPEATABLE_READ
4. SERIALIZABLE
5. DEFAULT

Dirty read: A transaction reads data written by a concurrent uncommitted transaction.
Nonrepeatable read: A transaction re-reads data it has previously read and finds that data has been modified by another transaction (that committed since the initial read).
Phantom read: A transaction re-executes a query returning a set of rows that satisfy a search condition and finds that the set of rows satisfying the condition has changed due to another recently-committed transaction.
For most databases, the default transaction isolation level is “read committed” (your transaction operates on the data that it sees at the beginning of the transaction). You can, for example, configure transactions to be serializable which increases lock contention, but is more suited for critical operations. Here we could get into database locking, predicate locking, etc.

READ_UNCOMMITTED

READ_UNCOMMITTED isolation level states that a transaction may read data that is still uncommitted by other transactions. This constraint is very relaxed in what matters to transactional concurrency but it may lead to some issues like dirty reads.

READ_UNCOMMITTED

 

In this example Transaction A writes a record. Meanwhile Transaction B reads that same record before Transaction A commits. Later Transaction A decides to rollback and now we have changes in Transaction B that are inconsistent. This is a dirty read. Transaction B was running in READ_UNCOMMITTED isolation level so it was able to read Transaction A changes before a commit occurred.

Note: READ_UNCOMMITTED is also vulnerable to non-repeatable reads and phantom reads. We will also see these cases in detail in the next sections.

READ_COMMITTED

READ_COMMITTED isolation level states that a transaction can’t read data that is not yet committed by other transactions. This means that the dirty read is no longer an issue, but even this way other issues may occur.

READ_COMMITTED

 
In this example Transaction A reads some record. Then Transaction B writes that same record and commits. Later Transaction A reads that same record again and may get different values because Transaction B made changes to that record and committed. This is a non-repeatable read.

Note: READ_COMMITTED is also vulnerable to phantom reads. We will also see this case in detail in the next section.

REPEATABLE_READ

REPEATABLE_READ isolation level states that if a transaction reads one record from the database multiple times the result of all those reading operations must always be the same. This eliminates both the dirty read and the non-repeatable read issues, but even this way other issues may occur.

REPEATABLE_READ

 
In this example Transaction A reads a range of records. Meanwhile Transaction B inserts a new record in the same range that Transaction A initially fetched and commits. Later Transaction A reads the same range again and will also get the record that Transaction B just inserted. This is a phantom read: a transaction fetched a range of records multiple times from the database and obtained different result sets (containing phantom records).

SERIALIZABLE

SERIALIZABLE isolation level is the most restrictive of all isolation levels. Transactions are executed with locking at all levels (read, range and write locking) so they appear as if they were executed in a serialized way. This leads to a scenario where none of the issues mentioned above may occur, but in the other way we don’t allow transaction concurrency and consequently introduce a performance penalty.

DEFAULT

DEFAULT isolation level, as the name states, uses the default isolation level of the datastore we are actually connecting from our application.