A transaction is a logical unit of work that contains one or more SQL statements. A transaction is an atomic unit. The effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database).
A transaction begins with the first executable SQL statement. A transaction ends when it is committed or rolled back, either explicitly with a COMMIT or ROLLBACK statement or implicitly when a DDL statement is issued.
Statement Execution and Transaction Control
A SQL statement that runs successfully is different from a committed transaction. Executing successfully means that a single statement was:
- Parsed
- Found to be a valid SQL construction
- Run 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, runs successfully.
You can name a transaction using the SET TRANSACTION... NAME statement before you start the transaction. This makes it easier to monitor long-running transactions and to resolve in-doubt distributed transactions.
Statement-Level Rollbakc
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 statemetn had never been run. This operation is a statement-level rollback.
Resumable Space Allocation
Oracle provides a means for suspending, and later resuming, the execution of large database operations in the event of space allocation failures. This enables an administrator to take corrective action, instead of the Oracle database server returning an error to the user. After the error condition is corrected, the suspended operation automatically resumes.
A statement runs in a resumable mode only when the client explicitly enables resumable semantics for the session using the ALTER SESSION statement.
Resumable space allocation is suspended when one of the following conditions occur:
- Out of space condition
- Maximum extents reached condition
- Space quota exceeded condition
For nonresumable space allocation, these conditions result in errors and the statement is rolled back.
Suspending a statement automatically results in suspending the transaction. Thus all transactional resources are held through a statement suspend and resume.
When the error condition disappears, the suspended statement automatically resumes execution.
Overivew of Transaction Management
A transaction in Oracle begings 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 undo tablespace to record the rollback entries for the new transaction.
A transaction ends when any of the following occurs:
- A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause.
- A user runs a DDL statement such as CREATE, DROP, RENAME, or ALTER. If the current transaction contains any DML statements, Oracle first commits the transaction, and then runs 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.
Commit Transactions
Committing a transaction means making permanent the changes performed by the SQL statements within the transaction.
Before a transaction that modifies data is committed, the following has occurred:
- Oracle has generated undo information. The undo information contains the old data values changed by the SQL statements of the transaction.
- Oralce has generated redo log entries in the redo log buffer of the SGA. The redo log record contains the change to the data block and the change to the rollback block. 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 is committed.
When a transaction is committed, the following occurs:
1. The internal transaction table for the associated undo tablespace records that the transaction has committed, and the corresponding unique system change number (SCN) of the transaction is assigned and recorded in the table.
2. The log writer process (LGWR) writes redo log entries in the SGA's redo log buffers to the redo log file. It also writes the transaction's SCN to the redo log file. This atomic event constitutes the commit of the transaction.
3. Oracle release locks held on rows and tables.
4. Oracle marks the transaction complete.
Rollback of Transactions
Rolling back means undoing any changes to data that have been performed by SQL statements within an uncommitted transaction. Oracle uses undo tablespaces (or rollback segments) to store old values. The redo log contains a record of changes.
Oracle lets you rollback an entire uncommitted transaction. Alternatively, you can roll back the trailing portion of an uncommitted transaction to a marker called a savepoing.
In rolling back an entire transaction, without referencing any savepoints, the following occurs:
1. Oracle undoes all changes made by all the SQL statements in the transaction by using the corresponding undo tablespace.
2. Oracle release all the transaction's locks of data.
3. The transaction ends.
Savepoints In Transactions
You can declare intermediate markers called savepoints within the context of a transaction. Savepoints divide a long transaction into smaller parts.
When a transaction is rolled back to a savepoint, the following occurs:
1. Oracle rolls back only the statements run after the savepoint.
2. Oracle preserves the specified savepoint, but all savepoints that were established after the specified one are lost.
3. Oracle releases all table and row locks acquired since that savepoint but retains all data locks acquired previous to the savepoint.
Whenever a session is waiting on transaction, a rollback to savepoint does not free row locks. To make sure a transaction does not hang if it cannot obtain a lock, use FOR UPDATE...NOWAIT before issuing UPDATE or DELETE statements.
Transaction Naming
You can name a transaction, using a simple and memorable text string. This name is a reminder of what the transaction is about. Transaction names replace commit comments for distributed transactions, with the following advantages:
- It is easier to monitor long-running transactions and to resolve in-doubt distributed transactions.
- You can view transaction names along with transaction IDs in applications. For example, a database administrator can view transaction names in Enterprise Manager when monitoring system activity.
- Transaction names are written to the transaction auditing redo record, if compatibility is set to Oracle9i or higher.
- LogMiner can use transaction names to search for a specific transaction from transaction auditing records in the redo log.
- You can use transaction names to find a specific transaction in data dictionary views, such as V$TRANSACTION.