Database Reference
In-Depth Information
Let's now take a look at how our previous example might work in
READ COMMITTED
mode in other databases—you
might find the answer surprising. We'll pick up our example at the point described in the previous table:
•
We are in the middle of the table. We have read and summed the first N rows.
•
The other transaction has moved $400.00 from account 123 to account 987.
•
The transaction has not yet committed, so rows containing the information for accounts 123
and 987 are locked.
We know what happens in Oracle when it gets to account 987—it will read around the modified data, find out it
should be $100.00, and complete. Table
7-4
shows how another database, running in some default
READ COMMITTED
mode, might arrive at the answer.
Table 7-4.
Timeline in a Non-Oracle Database Using READ COMMITTED Isolation
Time
Query
Account Transfer Transaction
T1
Reads row 1, account 123, value=$500.
Sum=$500.00 so far.
--
T2
Reads row 2, account 456, value=$240.25.
Sum=$740.25 so far.
--
T3
--
Updates row 1 (account 123) and puts an exclusive
lock on row 1, preventing other updates and reads.
Row 1 had $500.00, now it has $100.00.
T4
Reads row N. Sum = . . .
--
T5
--
Updates row 342,023 (account 987) and puts an
exclusive lock on this row. This row had $100, now
it has $500.00.
T6
Tries to read row 342,023, account 987. Discovers
that it is locked. This session will block and wait for
this row's block to become available.
All processing on
this query stops
.
--
T7
--
Commits transaction.
T8
Reads row 342,023, account 987, sees $500.00, and
presents a final answer that includes the $400.00
double-counted.
--
The first thing to notice is that this other database, upon getting to account 987, will block our query. This session
must wait on that row until the transaction holding the exclusive lock commits. This is one reason why many people
have a bad habit of committing every statement, instead of processing well-formed transactions consisting of all of
the statements needed to take the database from one consistent state to the next.
Updates interfere with reads in most
other databases
. The really bad news in this scenario is that we are making the end user wait for the
wrong
answer. We
still receive an answer that never existed in the committed database state at any point in time, as with the dirty read,
but this time we made the user wait for the wrong answer. In the next section, we'll look at what these other databases
need to do to achieve read-consistent, correct results.
The important lesson here is that various databases executing in the same, apparently safe isolation level can and
will return very different answers under the exact same circumstances. It is important to understand that, in Oracle,
nonblocking reads are not had at the expense of correct answers. You can have your cake and eat it too, sometimes.
