Databases Reference
In-Depth Information
Now, consider interchanging steps 3 and 4 and a situation where Transaction 2 commits the update.
The new sequence of steps is as follows:
1.
Transaction 1 reads all the three data points in the set.
2.
Transaction 2 then reads the data point with id 2 and updates the Location (City) property
of that data item from “San Francisco” to “San Jose.” However, it does not commit the
change yet.
3.
Transaction 2 commits the update carried out in step 2.
4.
Transaction 1 re-reads all the three data points in the set.
The Read Uncommitted isolation level isn't affected by the change in steps. It's the level that allows
dirty reads, so obviously committed updates are read without any trouble. Read Committed behaves
differently though. Now, because changes have been committed in step 3, Transaction 1 reads
the updated data in step 4. The reads from step 1 and step 4 are not the same so it's a case of no-
repeatable reads.
As the isolation level is upped to Repeatable Read, the reads in step 1 and step 4 are identical.
That is, Transaction 1 is isolated from the committed updates from Transaction 2 while they are
both concurrently in process. Although a repeatable read is guaranteed at this level, insertion and
deletion of pertinent records could occur. This could lead to inclusion and exclusion of data items
in subsequent reads and is often referred to as a
phantom read
. To walk through a case of phantom
read consider a new sequence of steps as follows:
1.
Transaction 1 runs a range query asking for all data items with id between 1 and 5 (both
inclusive). Because there are three data points originally in the collection and all meet the
criteria, all three are returned.
2.
Transaction 2 then inserts a new data item with the following values:
{Id = 4, Name =
'Jane Watson', Occupation = 'Chef', Location (City) = 'Atlanta'}
.
3.
Transaction 2 commits the data inserted in step 2.
4.
Transaction 1 re-runs the range query as in step 1.
Now, with isolation set to the Repeatable Read level, the data set returned to Transaction 1 in
step 1 and step 4 are not same. Step 4 sees the data item with id 4 in addition to the original three
data points. To avoid phantom reads you need to involve range locks for reads and resort to using
the highest level of isolation, Serializable. The term
serializable
connotes a sequential processing
or serial ordering of transactions but that is not always the case. It does block other concurrent
transactions when one of them is working on the data range, though. In some databases, snapshot
isolation is used to achieve serializable isolation. Such databases provide a transaction with a
snapshot when they start and allow commits only if nothing has changed since the snapshot.
Use of higher isolation levels enhances the possibility of starvation and deadlocks. Starvation
occurs when one transaction locks out resources from others to use and deadlock occurs when two
concurrent transactions wait on each other to fi nish and free up a resource.
With the concepts of ACID transactions and isolation levels reviewed, you are ready to start
exploring how these ideas play out in highly distributed systems.
