background image
<< Using DELETE Statements | Rolling Back Transaction Changes >>
<< Using DELETE Statements | Rolling Back Transaction Changes >>

Controlling Transactions

Controlling Transactions
2-30 Oracle Database 2 Day Developer's Guide
Controlling Transactions
Many applications model business processes that require that several different
operations be performed together, or not at all. For example, if a manager left the
company, a row would be inserted into the
job_history
table to show when that
person left, and all the employees that report to that manager must be re-assigned
within the
employees
table. This sequence of operations must be treated as a single
unit, or a transaction.
The following transaction control statements manage the changes made by DML
statements and group them into transactions.
The COMMIT statement ends the current transaction and makes all changes
performed in the transaction permanent.
COMMIT
also erases all savepoints in the
transaction, and releases transaction locks.
The ROLLBACK statement reverses the work done in the current transaction; it
causes all data changes since the last
COMMIT
or
ROLLBACK
to be discarded. The
state of the data is then "rolled back" to the state it had prior to the requested
changes.
The SAVEPOINT statement identifies a point in a transaction to which you can
later roll back.
Oracle recommends that you explicitly end transactions using either a
COMMIT
or a
ROLLBACK
statement. If you do not explicitly commit the transaction and the program
terminates abnormally, then Oracle Database automatically rolls back the last
uncommitted transaction.
Committing Transaction Changes
An explicit
COMMIT
statement ends your transaction, and makes all the changes in the
database permanent. Until you commit a transaction, you can see all of the changes
made by you to the database, but these changes are not final or visible to other users of
the database instance. Once you commit a transaction, all changes become visible to
other users and their statements that execute after your transaction.
You can undo any changes made prior to an explicit
COMMIT
by a
ROLLBACK
statement.
Example 2­51
shows how to use the
COMMIT
statement after adding a new row to the
regions
table.
Example 2­51 Using the COMMIT Statement
INSERT INTO regions VALUES (5, 'Africa');
COMMIT;
The results of the query and
COMMIT
statement appear.
Commit complete.
If you manually check the contents of the
regions
table, you will see that it now has
the new row.