Isolation

Isolation imposes rules, which ensure that transactions do not interfere with each other even if they are executed at the same time. If 2 (or more) transactions are executed at the same time, they must be executed in a way so that transaction N won't be impacted by the intermediate data of transaction M. Note, that isolation does not dictate the order of the transactions. Another important thing to understand about isolation is serializability of transactions. If the effect on the database is the same when transactions are executed concurrently or when their execution is interleaved, these transactions are called serializable.

There are several degrees of isolation to be distinguished:

• degree 0: a transaction does not overwrite data updated by another user or process ("dirty data") of other transactions;
• degree 1: degree 0 plus a transaction does not commit any writes until it completes all its writes (until the end of transaction);
• degree 2: degree 1 plus a transaction does not read dirty data from other transactions;
• degree 3: degree 2 plus other transactions do not dirty data read by a transaction before the transaction commits.

The more common classification is by isolation levels:

1. Serializable

In this case, transactions are executed serially so that there is no concurrent data access. Transactions can also be executed concurrently but only when the illusion of serial transactions is maintained (i.e. no concurrent access to data occurs).  If the system uses locks, a lock should be obtained over the whole range of selected data ("WHERE" clause in SQL). If the system does not use locks, no lock is acquired; however, if the system detects a concurrent transaction in progress, which would violate the serializability illusion, it must force that transaction to rollback, and the application will have to restart the transaction.

2. Repeatable Read

In this case, a lock is acquired over all the data retrieved from a database. Phantom reads can occur (i.e. new data from the other committed transactions included in the result)

3. Read Committed

In this case, read locks are acquired on the result set, but released immediately. Write locks are acquired and released only at the end of the transaction. Non-repeatable reads can occur, i.e. deletions or modifications from the other committed transactions will be visible by the current transaction. Phantom reads are also possible.

4. Read Uncommitted
With this isolation level dirty reads are allowed.  Uncommitted modifications from the other transactions are visible. Both phantom and nonrepeatable reads can occur.