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

So, two of those rows are locked. If anyone tried to update them, that user would be blocked. So far, the behavior
we are seeing is more or less consistent across all databases. The difference will be in what happens when the query
gets to the locked data.
When the query we are executing gets to the block containing the locked row (row 342,023) at the bottom of the
table, it will notice that the data in it has changed since the time at which it started execution. To provide a consistent
(correct) answer, Oracle will at this point create a copy of the block containing this row as it existed when the query
began . That is, it will read a value of $100.00, the value that existed at the time the query began. Effectively, Oracle
takes a detour around the modified data; it reads around it, reconstructing it from the undo segment (also known as a
rollback segment ). A consistent and correct answer comes back without waiting for the transaction to commit.
Now, a database that allowed a dirty read would simply return the value it saw in account 987 at the time it
read it, in this case $500.00. The query would count the transferred $400 twice. Therefore, not only does it return the
wrong answer, but also it returns a total that never existed in the table at any committed point in time. In a multiuser
database, a dirty read can be a dangerous feature and, personally, I have never seen the usefulness of it. Say that,
rather than transferring, the transaction was actually just depositing $400.00 in account 987. The dirty read would
count the $400.00 and get the “right” answer, wouldn't it? Well, suppose the uncommitted transaction was rolled back.
We have just counted $400.00 that was never actually in the database.
The point here is that dirty read is not a feature; rather, it is a liability. In Oracle, it is just not needed. You get all of
the advantages of a dirty read (no blocking) without any of the incorrect results.

READ COMMITTED
The READ COMMITTED isolation level states that a transaction may only read data that has been committed in the
database. There are no dirty reads. There may be nonrepeatable reads (i.e., rereads of the same row may return a
different answer in the same transaction) and phantom reads (i.e., newly inserted and committed rows become
visible to a query that were not visible earlier in the transaction). READ COMMITTED is perhaps the most commonly used
isolation level in database applications everywhere, and it is the default mode for Oracle databases, it is rare to see a
different isolation level used.
However, achieving READ COMMITTED isolation is not as cut-and-dried as it sounds. If you look at Table 4-1 , it
looks straightforward. Obviously, given the earlier rules, a query executed in any database using the READ COMMITTED
isolation will behave in the same way, will it not? It will not . If you query multiple rows in a single statement, in almost
every other database, READ COMMITTED isolation can be as bad as a dirty read, depending on the implementation.
In Oracle, using multiversioning and read-consistent queries, the answer we get from the ACCOUNTS query is the
same in READ COMMITTED as it was in the READ UNCOMMITTED example. Oracle will reconstruct the modified data as it
appeared when the query began, returning the answer that was in the database when the query started.
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 4-4 shows how another database, running in some default READ COMMITTED
mode, might arrive at the answer.