Database Reference
In-Depth Information
Ninety-nine percent of the time, locking is totally transparent and you need not concern yourself with it. It's that
other one percent you must be trained to recognize. There is no simple checklist of “if you do this, you need to do
this” for this issue. Successful concurrency control is a matter of understanding how your application will behave in a
multiuser environment and how it will behave in your database.
When we get to the chapters on locking and concurrency control, we'll delve into this topic in much more
depth. There you'll learn that integrity constraint enforcement of the type presented in this section, where you must
enforce a rule that crosses multiple rows in a single table or is between two or more tables (like a referential integrity
constraint), are cases where you must always pay special attention and will most likely have to resort to manual
locking or some other technique to ensure integrity in a multiuser environment.
Multiversioning
This is a topic very closely 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 7, we'll cover the
technical aspects 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.
•
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 (since version 3.0 in 1983!).
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 (this has been true since version 4.0 of Oracle in 1984!). 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
2 as
3 select username, created
4 from all_users
5 /
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
