Multi-Versioning - Page 6

This is a topic very closely related to concurrency control, as it forms the foundation for Oracle's concurrency control mechanism — Oracle operates a multi-version read-consistent concurrency model. In Chapter 3, Locking and Concurrency, we'll cover the technical aspects of this in more detail but, essentially, it is the mechanism by which Oracle provides for:

• Read-consistent queries: Queries that produce consistent results with respect to a point in time.
• Non-blocking queries: Queries are never blocked by writers of data, as they would be in other databases.

These are two very important concepts in the Oracle database. The term multi-versioning basically comes from the fact that Oracle is able to simultaneously maintain multiple versions of the data in the database. If you understand how multi-versioning works, you will always understand the answers you get from the database. Before we explore in a little more detail how Oracle does this, here is the simplest way I know to demonstrate multi-versioning in Oracle:

tkyte@TKYTE816> create table t
  2  as

  3  select * from all_users;
Table created.

tkyte@TKYTE816> variable x refcursor

tkyte@TKYTE816> begin
2          open :x for select * from t;
3  end;
4  /

PL/SQL procedure successfully completed.

tkyte@TKYTE816> delete from t;

18 rows deleted.

tkyte@TKYTE816> commit;

Commit complete.

tkyte@TKYTE816> print x

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SYS                                     0 04-NOV-00
SYSTEM                                  5 04-NOV-00
DBSNMP                                 16 04-NOV-00
AURORA$ORB$UNAUTHENTICATED             24 04-NOV-00
ORDSYS                                 25 04-NOV-00
ORDPLUGINS                             26 04-NOV-00
MDSYS                                  27 04-NOV-00
CTXSYS                                 30 04-NOV-00
...
DEMO                                   57 07-FEB-01

18 rows selected.

In the above example, we created a test table, T, and loaded it with some data from the ALL_USERS table. We opened a cursor on that table. We fetched no data from that cursor: we just opened it.

Bear in mind that Oracle and does not 'answer' the query, does not copy the data anywhere when you open a cursor — imagine how long it would take to open a cursor on a one billion row table if it did. The cursor opens instantly and it answers the query as it goes along. In other words, it would just read data from the table as you fetched from it.

In the same session (or maybe another session would do this), we then proceeded to delete all data from that table. We even went as far as to COMMIT work on that delete. The rows are gone — but are they? In fact, they are retrievable via the cursor. The fact is that the resultset returned to us by the OPEN command was pre-ordained at the point in time we opened it. We had touched not a single block of data in that table during the open, but the answer was already fixed in stone. We have no way of knowing what the answer will be until we fetch the data — however the result is immutable from our cursor's perspective. It is not that Oracle copied all of the data above to some other location when we opened the cursor; it was actually the delete command that preserved our data for us by placing it into a data area called a rollback segment.

This is what read-consistency is all about and if you do not understand how Oracle's multi-versioning scheme works and what it implies, you will not be able to take full advantage of Oracle nor will you be able to write correct applications in Oracle (ones that will ensure data integrity).

Let's look at the implications of multi-versioning, read- consistent queries and non-blocking reads. If you are not familiar with multi-versioning, what you see below might be surprising. For the sake of simplicity, we will assume that the table we are reading stores one row per database block (the smallest unit of storage in the database), and that we are fullscanning 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:

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 (we will visit this example in more detail in Chapter 3, Locking and Concurrency):