Database Reference
In-Depth Information
■
not all
ROLLBACK
statements are created equal. it should be noted that a
ROLLBACK TO SAVEPOINT
command
will not end a transaction! only a full, proper
ROLLBACK
will.
Note
You should always explicitly terminate your transactions with a
COMMIT
or
ROLLBACK
; otherwise, the tool or
environment you're using will pick one or the other for you. If you exit your SQL*Plus session normally, without
committing or rolling back, SQL*Plus assumes you wish to commit your work and it does so. If you just exit from a
Pro*C program, on the other hand, an implicit rollback takes place. Never rely on implicit behavior, as it could change
in the future. Always explicitly
COMMIT
or
ROLLBACK
your transactions.
■
as an example of something changing in the future, sQL*plus in oracle 11
g
release 2 and above contains a
setting, “exitcommit.” this setting controls whether sQL*plus issues a
COMMIT
or
ROLLBACK
upon exit. so when you use
11
g
release 2, the default behavior that has been in place since sQL*plus was invented may well be different!
Note
Transactions are
atomic
in Oracle, meaning that either every statement that comprises the transaction is
committed (made permanent) or all of the statements are rolled back. This protection is extended to individual
statements as well. Either a statement entirely succeeds or the statement is entirely rolled back. Note that I said
the “statement” is rolled back. The failure of one statement does not cause previously executed statements to be
automatically rolled back. Their work is preserved and must either be committed or rolled back by you. Before we get
into the details of exactly what it means for a statement and transaction to be atomic, let's take a look at the various
transaction control statements available to us:
•
COMMIT
: To use this statement's simplest form, you just issue
COMMIT
. You could be more
verbose and say
COMMIT WORK
, but the two are equivalent. A
COMMIT
ends your transaction
and makes any changes permanent (durable). There are extensions to the
COMMIT
statement
used in distributed transactions that allow you to label a
COMMIT
(label a transaction) with
some meaningful comment and force the commit of an in-doubt distributed transaction.
There are also extensions that allow you to perform an asynchronous commit—a commit that
actually breaks the
durability
concept. We'll take a look at this in a bit and see when it might
be appropriate to use.
•
ROLLBACK
: To use this statement's simplest form, you just issue
ROLLBACK
. Again, you could
be more verbose and say
ROLLBACK WORK
, but the two are equivalent. A rollback ends your
transaction and undoes any uncommitted changes. It does this by reading information stored
in the rollback/undo segments (going forward I'll refer to these exclusively as
undo segments
,
the favored terminology for Oracle 10
g
and later) and restoring the database blocks to the state
they were before your transaction began.
•
SAVEPOINT
: A
SAVEPOINT
allows you to create a marked point within a transaction. You may
have multiple
SAVEPOINT
s within a single transaction.
•
ROLLBACK TO <SAVEPOINT>
: This statement is used with the
SAVEPOINT
command. You can
roll back your transaction to that marked point without rolling back any of the work that
preceded it. So, you could issue two
UPDATE
statements, followed by a
SAVEPOINT
and then two
DELETE
statements. If an error or some sort of exceptional condition occurs during execution
of the
DELETE
statements, and you catch that exception and issue the
ROLLBACK TO SAVEPOINT
command, the transaction will roll back to the named
SAVEPOINT
, undoing any work
performed by the
DELETE
s but leaving the work performed by the
UPDATE
statements intact.
