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
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.
Table 7-6. Timeline 2 in Non-Oracle Database Using READ REPEATABLE Isolation
We have just reached the classic deadlock condition. Our query holds resources the update needs and vice versa. Our query has just deadlocked with our update transaction. One of them will be chosen as the victim and will be killed. We just spent a long time and a lot of resources only to fail and get rolled back at the end. This is the second side effect of shared read locks: readers and writers of data can and frequently will deadlock each other.
In Oracle, we have statement-level read consistency without reads blocking writes or deadlocks. Oracle never uses shared read locks—ever. Oracle has chosen the harder-to- implement but infinitely more concurrent multiversioning scheme.
