Database Reference
In-Depth Information
12.4 Commit and Rollback Operations
The
Commit
statement and the
Rollback
statement are both DCL statements that have
relevance to data manipulation. They are useful in transaction management: the
Commit
forces a write to the database table while the
Rollback
causes recovery to a safe point.
This is particularly important when multiple related tuples are written to different tables
(for instance, an invoice may consist of a summary tuple, written to one table, and several
detail tuples, written to another table). The syntax for both statements are shown:
Two related statements are the
Set-Transaction
statement and the
Save-Point
statement. The former is used to define the beginning of a transaction block; the latter
is used to create a recovery point in case of catastrophes such as power loss or system
failure. The syntax of each is in Figure
12-4
:
Note:
1.
These statements are typically used in a programming block,
not at the SQL prompt.
2.
The default transaction mode for Oracle is READ WRITE.
However, for application programs that are required to read
data only, this should be changed to READ ONLY to prevent
record locking. This is particularly important in a multi-user
environment.
3.
These transaction management statements work the same
way for insertions, updates, and deletions.
Figure 12-4.
Syntax For Set-Transaction and Save-Point Statements
