Let’s look at the implications of multiversioning: read-consistent queries and nonblocking reads. If you are not familiar with multiversioning, what you see in the following code might be surprising. For the sake of simplicity, assume the table we are reading stores one row per database block (the smallest unit of storage in the database) and that we are full scanning the table in this example.
The table we will query is a simple ACCOUNTS table. It holds balances in accounts for a bank. It has a very simple structure:
SQL> create table accounts
( account_number number primary key, account_balance number);
In reality, the ACCOUNTS table would have hundreds of thousands of rows in it, but for simplicity we’re just going to consider a table with four rows, as shown in Table 1-2. (We will visit this example in more detail in Chapter 7.)
Table 1-2. Accounts Table Contents
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:
SQL> 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-3.
Table 1-3. Multiversioning in Action
Table 1-3. (continued)
At time T6, Oracle is effectively “reading through” the lock that our transaction placed on row 4. This is how nonblocking reads are implemented: Oracle only looks to see if the data changed; it doesn’t care if the data is currently locked (which implies that the data may have changed). Oracle simply retrieves the old value from the undo segment and proceeds to the next block of data.
This is another clear demonstration of multiversioning. Multiple versions of the same piece of information, all at different points in time, are available in the database. Oracle is able to use these snapshots of data at different points in time to provide us with read-consistent queries and nonblocking reads.
This read-consistent view of data is always performed at the SQL statement level. The results of any single SQL statement are consistent with respect to the point in time they began. This quality is what makes a statement like the following insert a predictable set of data:
for x in (select * from t)
loop
insert into t values (x.username, x.user_id, x.created); end loop;
The result of the SELECT * FROM T is preordained when the query begins execution. The SELECT will not see any of the new data generated by the INSERT. Imagine if it did—this statement might be a never-ending loop. If, as the INSERT generated more rows in T, the SELECT could “see” those newly inserted rows, the preceding code would create some unknown number of rows. If the table T started out with 10 rows, we might end up with 20, 21, 23, or an infinite number of rows in T when we finished. It would be totally unpredictable. This consistent read is provided to all statements so that an INSERT such as the following is predictable as well:
insert into t select * from t;
The INSERT statement will be provided a read-consistent view of T. It will not see the rows that it just inserted; rather, it will only insert the rows that existed at the time the SELECT began. Some databases won’t even permit recursive statements such as the preceding because they can’t tell how many rows might actually be inserted.
So, if you are used to the way other databases work with respect to query consistency and concurrency, or you never had to grapple with such concepts (i.e., you have no real database experience), you can now see how understanding how this works will be important to you. In order to maximize Oracle’s potential, and to implement correct code, you need to understand these issues as they pertain to Oracle—not how they are implemented in other databases.
