Database Reference
In-Depth Information
Chapter 8
Transactions
Transactions are one of the features that set databases apart from file systems. In a file system, if you are in the middle
of writing a file and the operating system crashes, that file will probably be corrupted, though there are “journaled” file
systems and the like that may be able to recover your file to some point in time. However, if you need to keep two files
synchronized, such a system won't help—if you update one file and the system fails before you finish updating the
second, your files won't be synchronized.
This is the main purpose of
transactions
—they take the database from one consistent state to the next. That is
their function. When you commit work in the database, you are assured that either all of your changes, or none of
them, have been saved. Furthermore, you are assured that the various rules and checks that protect data integrity
were implemented.
In the previous chapter, we discussed transactions in terms of concurrency control and how, as a result of
Oracle's multiversioning, read-consistent model, Oracle transactions can provide consistent data every time, under
highly concurrent data access conditions. Transactions in Oracle exhibit all of the required ACID characteristics:
•
Atomicity
: Either all of a transaction happens or none of it happens.
•
Consistency
: A transaction takes the database from one consistent state to the next.
•
Isolation
: The effects of a transaction may not be visible to other transactions until the
transaction has committed.
•
Durability
: Once the transaction is committed, it is permanent.
In particular, we discussed how Oracle obtains c
onsistency
and
isolation
in the previous chapter. Here we'll focus
most of our attention on the concept of
atomicity
and how that is applied in Oracle.
In this chapter, we'll discuss the implications of atomicity and how it affects statements in Oracle. We'll cover
transaction control statements such as
COMMIT
,
SAVEPOINT
, and
ROLLBACK
, and we'll discuss how integrity constraints
are enforced in a transaction. We'll also look at why you may have some bad transaction habits if you've been
developing in other databases. We'll look at distributed transactions and the two-phase commit (2PC). Lastly, we'll
examine autonomous transactions, what they are, and the role they play.
Transaction Control Statements
You don't need a “begin transaction” statement in Oracle. A transaction implicitly begins with the first statement that
modifies data (the first statement that gets a
TX
lock). You can explicitly begin a transaction using
SET TRANSACTION
or
the
DBMS_TRANSACTION
package, but it is not a necessary step, unlike in some other databases. Issuing either a
COMMIT
or
ROLLBACK
statement explicitly ends a transaction.
