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

Table 4-6. Timeline 2 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 -- Updates row 342,023 and puts an exclusive lock on row

342,023, preventing other updates and shared read
locks. This row now has $50.00.

T4 Reads row N. Sum = . . . --

T5 -- Attempts to update row 1 but is blocked. Transaction is

suspended until it can obtain an exclusive lock.

T6 Attempts to read row 342,023

but can't as an exclusive lock is already in place.

--

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.

Lost Updates: Another Portability Issue
A common use of REPEATABLE READ in databases that employ the shared read locks could be for lost update prevention.

■ Note lost update detection and solutions to the lost update problem are discussed in Chapter 2.

If we have REPEATABLE READ enabled in a database that employs shared read locks (and not multiversioning),
lost update errors can't happen. The reason lost updates will not happen in those databases is because the simple
act of selecting the data leaves a lock on it, and once read by our transaction, that data cannot be modified by any
other transaction. Now, if your application assumes that REPEATABLE READ implies “lost updates can't happen,”
you are in for a painful surprise when you move your application to a database that does not use shared read locks
as an underlying concurrency control mechanism.

■ In a stateless environment, such as a web-based application, lost updates would likely be a cause for
concern—even in REPEATABLE READ isolation. This is because a single database session is used by many clients via a
connection pool and locks are not held across calls. REPEATABLE READ isolation only prevents lost updates in a stateful
environment, such as that observed with a client-server application.

Note