Getting Started - Oracle Database Transactions and Locking Revealed - page 3

simultaneously. These kinds of bugs are terribly hard to track down and fix. If you only test your application in
isolation and then deploy it to dozens of concurrent users, you are likely to be (painfully) exposed to an undetected
concurrency issue.
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.
Multiversioning
Multiversioning is related to concurrency control, as it forms the foundation for Oracle's concurrency control
mechanism. Oracle operates a multiversion, read-consistent concurrency model. In Chapter 4, we'll cover the
technical aspects in more detail, but, essentially, it is the mechanism by which Oracle provides for the following:
• Read-consistent queries : Queries that produce consistent results with respect to a point in time.
• Nonblocking queries : Queries are never blocked by writers of data, as they are in other databases.
These are two very important concepts in the Oracle database. The term multiversioning basically describes
Oracle's ability to simultaneously maintain multiple versions of the data in the database. The term read consistency
reflects the fact that a query in Oracle will return results from a consistent point in time. Every block used by a query
will be “as of ” the same exact point in time—even if it was modified or locked while you performed your query. If you
understand how multiversioning and read consistency work together, 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 multiversioning in Oracle:
EODA@ORA12CR1> create table t as select username, created from all_users;
Table created.
EODA@ORA12CR1> set autoprint off
EODA@ORA12CR1> variable x refcursor;
EODA@ORA12CR1> begin
2 open :x for select * from t;
3 end;
4 /
PL/SQL procedure successfully completed.
EODA@ORA12CR1> declare
2 pragma autonomous_transaction;
3 -- you could do this in another
4 -- sqlplus session as well, the
5 -- effect would be identical
6 begin
7 delete from t;
8 commit;
9 end;
10 /
PL/SQL procedure successfully completed.