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
tkyte@TKYTE816> create table t
3 select * from all_users;
tkyte@TKYTE816> variable x refcursor
2 open :x for select * from t;
PL/SQL procedure successfully completed.
tkyte@TKYTE816> delete from t;
18 rows deleted.
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
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,
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):