Database Reference
In-Depth Information
Summary
In this chapter, we covered a lot of material that, at times, might not have been obvious. However, it is vital that you
understand these issues. For example, if you were not aware of the statement-level restart, you might not be able to
figure out how a certain set of circumstances could have taken place. That is, you would not be able to explain some of
the daily empirical observations you make. In fact, if you were not aware of the restarts, you might wrongly suspect the
actual fault to be due to the circumstances or end user error. It would be one of those unreproducible issues, as it takes
many things happening in a specific order to observe.
We took a look at the meaning of the isolation levels set out in the SQL standard and at how Oracle implements
them; at times, we contrasted Oracle's implementation with that of other databases. We saw that in other
implementations (i.e., ones that employ read locks to provide consistent data), there is a huge trade-off between
concurrency and consistency. To get highly concurrent access to data, you would have to decrease your need for
consistent answers. To get consistent, correct answers, you would need to live with decreased concurrency. In Oracle
that is not the case, due to its multiversioning feature.
Table 4-9 sums up what you might expect in a database that employs read locking versus Oracle's
multiversioning approach.
Table 4-9. A Comparison of Transaction Isolation Levels and Locking Behavior in Oracle vs. Databases That Employ
Read Locking
Isolation
Level
Implementation
Writes
Block Reads
Reads
Block
Writes
Deadlock-
Sensitive Reads
Incorrect
Query
Results
Lost
Updates
Lock
Escalation
or Limits
READ UNCOMMITTED
Not Oracle
No
No
No
Yes
Yes
Yes
READ COMMITTED
Not Oracle
Yes
No
No
Yes
Yes
Yes
READ COMMITTED
Oracle
No
No
No
No
No*
No
REPEATABLE READ
Not Oracle
Yes
Yes
Yes
No
No
Yes
SERIALIZABLE
Not Oracle
Yes
Yes
Yes
No
No
Yes
SERIALIZABLE
Oracle
No
No
No
No
No
No
* With SELECT FOR UPDATE NOWAIT .
Concurrency controls and how the database implements them are definitely things you want to understand.
I've been singing the praises of multiversioning and read consistency, but like everything else in the world, they are
double-edged swords. If you don't understand that multiversioning is there and how it works, you will make errors in
application design. Unless you know how multiversioning works, you will write programs that corrupt data. It is
that simple.
 
 
Search WWH ::




Custom Search