Executing successfully means that a single statement was parsed and found to be a valid SQL construction, and that the entire statement executed without
error as an atomic unit (for example, all rows of a multirow update are
changed). However, until the transaction that contains the statement is
committed, the transaction can be rolled back, and all of the changes of the
statement can be undone. A statement, rather than a transaction, executes
successfully.
Committing means that a user has said either explicitly or implicitly “make the changes in this transaction permanent”. The changes made by the SQL
Transaction Management 14-3
statement(s) of your transaction become permanent and visible to other users only after your transaction has been committed. Only other users’ transactions that started after yours will see the committed changes.
Statement-Level Rollback
If at any time during execution a SQL statement causes an error, all effects of the statement are rolled back. The effect of the rollback is as if that statement were never executed. This is a statement-level rollback.
Errors discovered during SQL statement execution cause statement-level rollbacks. (An example of such an error is attempting to insert a duplicate
value in a primary key.) Errors discovered during SQL statement parsing (such as a syntax error) have not yet been executed, so do not cause a statement-level rollback. Single SQL statements involved in a deadlock (competition for the
same data) may also cause a statement-level rollback. See “Deadlocks” on
page 22-17.
A SQL statement that fails causes the loss only of any work it would have
performed itself; it does not cause the loss of any work that preceded it in the current transaction. If the statement is a DDL statement, the implicit commit that immediately preceded it is not undone.
Note: Users cannot directly refer to implicit savepoints in rollback statements.
Oracle and Transaction Management
A transaction in Oracle begins when the first executable SQL statement is
encountered. An executable SQL statement is a SQL statement that generates calls to an instance, including DML and DDL statements.
When a transaction begins, Oracle assigns the transaction to an available
rollback segment to record the rollback entries for the new transaction. See
“Transactions and Rollback Segments” on page 2-18 for more information
about this topic.
A transaction ends when any of the following occurs:
•
You issue a COMMIT or ROLLBACK (without a SAVEPOINT clause)
statement.
•
You execute a DDL statement (such as CREATE, DROP, RENAME,
ALTER). If the current transaction contains any DML statements, Oracle
14-4 Oracle8 Server Concepts
first commits the transaction, and then executes and commits the DDL
statement as a new, single statement transaction.
•
A user disconnects from Oracle. (The current transaction is committed.)
•
A user process terminates abnormally. (The current transaction is rolled
back.)
After one transaction ends, the next executable SQL statement automatically
starts the following transaction.
Note: Applications should always explicitly commit or roll back transactions before program termination.
Committing Transactions
Committing a transaction means making permanent the changes performed by the SQL statements within the transaction.
Before a transaction that has modified data is committed, the following will
have occurred:
•
Oracle has generated rollback segment records in rollback segment
buffers of the system global area (SGA). The rollback information
contains the old data values changed by the SQL statements of the
transaction.
•
Oracle has generated redo log entries in the redo log buffers of the SGA.
These changes may go to disk before a transaction is committed.
•
The changes have been made to the database buffers of the SGA. These
changes may go to disk before a transaction actually is committed.
When a transaction is committed, the following occurs:
•
The internal transaction table for the associated rollback segment
records that the transaction has committed, and the corresponding
unique system change number (SCN) of the transaction is assigned and
recorded in the table.
•
The log writer process (LGWR) writes the redo log entries in the redo log
buffers of the SGA to the online redo log file. LGWR also writes the
transaction’s SCN to the online redo log file. This atomic event
constitutes the commit of the transaction.
•
Oracle releases locks held on rows and tables (see “Locking
Mechanisms” on page 22-3 for a discussion of locks).
•
Oracle marks the transaction “complete”.
Note: The data changes for a committed transaction, stored in the
Transaction Management 14-5
database buffers of the SGA, are not necessarily written immediately to the datafiles by the database writer (DBWR) background process. This
writing takes place when it is most efficient to do so. As mentioned
above, this may happen before the transaction commits or, alternatively,
it may happen some time after the transaction commits. See “Oracle