Concurrency and Multiversioning - Oracle Database Transactions and Locking Revealed - page 59

Table 4-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.

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.