Database Reference
In-Depth Information
We would like to run an end-of-day report that tells us how much money is in the bank. That's an extremely
simple query:
select sum(account_balance) from accounts;
And, of course, in this example the answer is obvious: $1250. However, what happens if we read row 1, and while
we're reading rows 2 and 3, an automated teller machine (ATM) generates transactions against this table and moves
$400 from account 123 to account 456? Our query counts $500 in row 4 and comes up with the answer of $1650,
doesn't it? Well, of course, this is to be avoided, as it would be an error—at no time did this sum of money exist in the
account balance column. Read consistency is the way Oracle avoids such occurrences. Oracle's methods differ from
those of most other databases, and you need to understand how.
In many other databases, if you wanted to get a “consistent” and “correct” answer to this query, you'd either have
to lock the whole table while the sum was calculated
or
you'd have to lock the rows as you read them. This prevents
people from changing the answer as you are getting it. If you lock the table up front, you get the answer that was in the
database at the time the query began. If you lock the data as you read it (commonly referred to as a
shared read lock
,
which prevents updates, but not other readers from accessing the data), you get the answer that was in the database
at the point the query finished. Both of these methods inhibit concurrency a great deal. The table lock prevents any
updates from taking place against the entire table for the duration of your query (for a table of four rows, this would
only be a very short period, but for tables with hundreds of thousands of rows, it could be several minutes). The “lock
as you go” method prevents updates on data you have read and already processed and could actually cause deadlocks
between your query and other updates.
Now, I said earlier that you wouldn't be able to take full advantage of Oracle if you didn't understand the concept
of multiversioning. Here is one reason why that is true. Oracle uses multiversioning to get the answer, as it existed
at the point in time the query began, and the query will take place
without locking a single thing
(while our account
transfer transaction updates rows 1 and 4, these rows will be locked to other writers, but not locked to other readers,
such as our
SELECT SUM...
query). In fact, Oracle doesn't have a “shared read” lock (a type of lock common in other
databases)—it doesn't need it. Everything inhibiting concurrency that can be removed has been removed.
I have seen actual cases where a report written by a developer who did not understand Oracle's multiversioning
capabilities would lock an entire system up as tight as could be. The reason: the developer wanted to have read-
consistent (i.e., correct) results from his queries. In every other database the developer had worked with, this required
locking the tables, or using a
SELECT ... WITH HOLDLOCK
(a SQL Server mechanism for locking rows in a shared
mode as you go along). So the developer would either lock the tables prior to running the report or use
SELECT ....
FOR UPDATE
(the closest he could find to
WITH HOLDLOCK
). This would cause the system to basically stop processing
transactions—needlessly.
So, how does Oracle get the correct, consistent answer ($1250) during a read without locking any data—in other
words, without decreasing concurrency? The secret lies in the transactional mechanisms that Oracle uses. Whenever
you modify data, Oracle creates entries in two different locations (most other databases would put both entries in
the same location; for them undo and redo are just “transaction data”). One entry goes to the redo logs where Oracle
stores enough information to
redo
or “roll forward” the transaction. For an insert, this would be the row inserted.
For a delete, it is conceptually a message to delete the row in file X, block Y, row slot Z. And so on. The other entry
is an
undo
entry, written to an undo segment. If your transaction fails and needs to be undone, Oracle will read the
“before” image from the undo segment and restore the data. In addition to using this undo segment data to undo
transactions, Oracle uses it to undo changes to blocks as it is reading them—to restore the block to the point in
time your query began. This gives you the ability to read right through a lock and to get consistent, correct answers
without locking any data yourself.
So, as far as our example is concerned, Oracle arrives at its answer as shown in Table
1-2
.
