Database Reference
In-Depth Information
REPEATABLE READ
The goal of
REPEATABLE
READ
is to provide an isolation level that gives consistent, correct answers and prevents lost
updates. We'll take a look at examples of both, see what we have to do in Oracle to achieve these goals, and examine
what happens in other systems.
Getting a Consistent Answer
If we have a
REPEATABLE READ
isolation, the results from a given query must be consistent with respect to some point
in time. Most databases (not Oracle) achieve repeatable reads via the use of row-level shared read locks. A shared read
lock prevents other sessions from modifying data that we have read. This, of course, decreases concurrency. Oracle
opted for the more concurrent, multiversioning model to provide read-consistent answers.
In Oracle, using multiversioning, we get an answer that is consistent with respect to the point in time the query
began execution. In other databases, using shared read locks, we get an answer that is consistent with respect to the
point in time the query completes—that is, when we can get the answer at all (more on this in a moment).
In a system that employs a shared read lock to provide repeatable reads, we would observe rows in a table getting
locked as the query processed them. So, using the earlier example, as our query reads the
ACCOUNTS
table, it would
leave shared read locks on each row, as shown in Table
7-5
.
Table 7-5.
Timeline 1 in Non-Oracle Database Using READ REPEATABLE Isolation
Time
Query
Account Transfer Transaction
T1
Reads row 1. Sum=$500.00 so far. Row 1
has a shared read lock on it.
--
T2
Reads row 2. Sum=$740.25 so far. Row 2
has a shared read lock on it.
--
T3
--
Attempts to update row 1 but is blocked. Transaction is
suspended until it can obtain an exclusive lock.
T4
Reads row N. Sum = . . .
--
T5
Reads row 342,023, sees $100.00, and
presents final answer.
--
T6
Commits transaction.
--
T7
--
Updates row 1 and puts an exclusive lock on this row.
Row now has $100.00.
T8
--
Updates row 342,023 and puts an exclusive lock on this
row. Row now has $500.00. Commits transaction.
Table
7-5
shows that we now get the correct answer, but at the cost of physically blocking one transaction and
executing the two transactions sequentially. This is one of the side effects of shared read locks for consistent answers:
readers of data will block writers of data
. This is in addition to the fact that, in these systems, writers of data will block
readers of data. Imagine if automatic teller machines (ATMs) worked this way in real life.
So, you can see how shared read locks would inhibit concurrency, but they can also cause spurious errors to
occur. In Table
7-6
, we start with our original table, but this time with the goal of transferring $50.00 from account 987
to account 123.
