Isolation Level

To understand isolation level, you need to understand three problems related to concurrency control, dirty reads, unrepeatable reads and phantoms.

Dirty Reads

A dirty read occurs when your application reads data from a database that has not been committed to permanent storage yet. Consider two instances of the same component performing the following:

1. You read account balance X from the database. The database now contains X = 0.
2. You add 10 to X and save it to the database. Now X = 10, but not committed, this is so-called dirty.
3. Another instance reads X from database, it will get X = 10, which is dirty.
4. You abort the transaction, which restores the X to 0.
5. The other instance adds 10 to X and saves. The database now contains X = 20.

The problem here is the second instance commit its transaction based on a dirty data. The problem of reading uncommitted data is a dirty read.

Unrepeatable Read

Unrepeatable read occur when a component reads data from database, but upon re-reading the same data, the data has been changed. This can arise when another concurrently executing transaction modifies the data. For example,

1. You read a data set X from database.
2. Another component overwrites all or part of the set X.
3. You re-read the set X and values have changed.

To prevent such changes, you need to lock out other components from modifying the data.

Phantom

Phantom is a new set of data that inserted in a database between two read operations. For example:

1. You read the database and find 5 computers ordered by A.
2. A order another computer.
3. You re-read the database and find 6 computers by A.
4. If one of your transaction depends on the number of computers ordered, you will have problem to believe when the number will not change during your transaction.

Now it becomes very easy to understand these isolation levels. READ UNCOMMITTED prevents none of these problems. READ COMMITTED prevents only the dirty read. REPEATABLE READ prevents dirty read and unrepeatable read. SERIALIZABLE prevents all the above mentioned problems.