Database Reference
In-Depth Information
DBMS_JOB
or something similar to send the e-mail after my transaction commits. This makes the sending of the e-mail
transactional
; that is, if the statement that caused the trigger to fire and send the e-mail is restarted, the rollback it
performs will roll back the
DBMS_JOB
request. Most everything nontransactional that I did in triggers was modified to
be done in a job after the fact, making it all transactionally consistent.
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
7-9
sums up what you might expect in a database that employs read locking versus Oracle's multiversioning
approach.
Table 7-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. Consider the resource scheduler example from Chapter 1. In a database without multiversioning
and its associated nonblocking reads, the original logic employed by the program may very well have worked.
However, this logic would fall apart when implemented in Oracle. It would allow data integrity to be compromised.
Unless you know how multiversioning works, you will write programs that corrupt data. It is that simple.
