Database Reference
In-Depth Information
two physical operations with the data: decreasing checking and increasing savings account
balances. But logically such operations should be done in one transaction. If another
session reads account balances in the dirty reads mode in the intermediate step (in
between two updates) the results would be incorrect.
From the locking prospective, as we remember, session acquires and holds exclusive (X)
lock on the rows it modified. The only case when we can read those (modified) rows is
when we do not use shared (S) locks in read uncommitted transaction isolation level. All
other pessimistic isolation levels are using shared (S) locks and would not allow dirty reads.
Non-Repeatable Reads
: This data inconsistency issue arises when data is modified, or
even deleted, between the reads within the same transaction. Let's think about the situation
when we are preparing the report in an order entry system that displays the list of the orders
for specific customer and runs another query to aggregate the data (perhaps total amount
spent by customer on the monthly basis). If another session modifies or, perhaps, deletes
one of the orders, after we read the list but before we do the aggregation, those two parts of
the report would not match each other.
From the locking standpoint such a phenomenon could occur when we don't protect/lock
our data in between reads. This could happen in read uncommitted transaction isolation
levels where we do not use shared (S) locks during the reads as well as in read committed
transaction isolation levels when we acquire and release shared (S) locks immediately.
Repeatable read and serializable transaction isolation levels keep the shared (S) locks until
the end of the transaction so data cannot be modified once read.
Phantom Reads
: This phenomenon occurs when subsequent reads within the same
transaction return the new rows (the ones we did not read before). Think about our
previous example with the report when another session inserts the new order rather than
modifying existing one. Even repeatable read transaction isolation level would not protect
us from such situation. We are holding the shared (S) locks on the rows we read, but key
range intervals are not protected. Only the serializable transaction isolation level with
range locks is free from such phenomenon.
There are two other interesting cases related to the data movement due to the change of the
index key value.
Duplicated Reads
: Let's think about the query that returns the list of the orders for the
specific time interval using the index on the order date column during the execution.
What if another session changes the order date in the way that moves this row from the
processed to non-processed (by the first query) part of the index? In this case we can have
the situation that the first query reads the same row twice. Obviously this could happen
only in the case when we don't protect the rows with shared (S) locks after we read them
(in read uncommitted and read committed transaction isolation levels). Repeatable read
and Serializable isolation levels hold shared (S) locks until the end of the transaction and
free from such phenomenon because other sessions would not be able to update rows that
have been read.
Skipped Rows
: This is the same as above but let's assume that the second session moves
the data from the non-processed to processed area of the index. In such a case our original
query would miss that row and would not include it in the result set. This could happen
even in a repeatable read transaction isolation level when we protect/held shared (S) locks
on the individual rows but allow inserting the new data in between. Only a serializable
transaction isolation level would be free of such anomaly.
Table
17-4
summarizes data inconsistency issues within different transaction isolation levels.
