An isolation levels mechanism is
used to isolate a resource each transaction in a multi-user
environment. Isolation
can be set by obtaining locks on objects.
The correct use of the isolation levels mechanism prevents applications
from introducing errors that can occur from the following situations.
1. Lost Updates: This situation occurs when two transactions attempt
to update the same data. Consider the following example:
- Transaction A reads row 1.
- Transaction B reads row 1.
- Transaction A updates row
1.
- Transaction B updates row
1, overlaying changes applied by Transaction A.
In the above situation, updates performed by Transaction A are lost.
This problem could be avoided if the second Transaction could not make
changes until the first Transaction had finished.
2. Dirty Reads: This situation occurs when transactions read data
that has not been committed. Consider the following example:
- Transaction A inserts row 1
without committing.
- Transaction B reads row 1.
- Transaction A rolls back
row 1.
- Transaction B now has a row
that physically does not exist.
This problem could be avoided if no one could read the
changes until the first Transaction determined that the changes were final.
3. Nonrepeatable Reads: This situation occurs when a transaction
reads the same query multiple times and results are not the same each time.
Consider the following example:
- Transaction A reads a row
of data.
- Transaction B modifies this
row and commits.
- Transaction A re-reads the
same row and sets back different data values. (When the record was read for the second
time by Transaction A, it has changed).
This problem could be avoided if the Transaction A could
read the row only after the Transaction B has finished writing it.
4. Phantoms: This situation occurs when a row of data matches the
first time but does not match subsequent times. Consider the following example:
- Transaction A reads two
rows based on a Query A where clause.
- Transaction B inserts a new
row that happens to fall under Transaction A Query A's where clause.
- Transaction A runs Query A
again and now gets back three rows.
Example2:
Phantom reads occur when an insert or
delete action is performed against a row that belongs to a range of rows being
read by a transaction. The transaction's first read of the range of rows shows
a row that no longer exists in the second or succeeding read, as a result of a
deletion by a different transaction. Similarly, as the result of an insert by a
different transaction, the transaction's second or succeeding read shows a row
that did not exist in the original read.
The above four phenomenon demonstrate that there is a need to utilize a
mechanism called ISOLATION LEVELS.
