Oracle Database - Transactions
Table of Contents
1 - About
2 - Articles Related
3 - Beginning of a Transaction
A transaction begins when the first executable SQL statement is encountered. An executable SQL statement is a SQL statement that generates calls to a database instance, including DML and DDL statements and the SET TRANSACTION statement.
When a transaction begins, Oracle Database assigns the transaction to an available undo data segment to record the undo entries for the new transaction. A transaction ID is not allocated until an undo segment and transaction table slot are allocated, which occurs during the first DML statement.
A transaction ID is unique to a transaction and represents the:
- undo segment number,
- and sequence number.
4 - Control
Transaction control (TCL) is the management of changes made by DML statements and the grouping of DML statements into transactions. In general, application designers are concerned with transaction control so that work is accomplished in logical units and data is kept consistent.
Transaction control involves using the following statements:
- 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 ROLLBACK TO SAVEPOINT statement undoes the changes since the last savepoint but does not end the entire transaction.
- The SAVEPOINT statement identifies a point in a transaction to which you can later roll back.
Example of Transaction Control
5 - Transaction Names
A transaction name is an optional, user-specified tag that serves as a reminder of the work that the transaction is performing. You name a transaction with the SET TRANSACTION … NAME statement, which if used must be first statement of the transaction.
Transaction names provide 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 Oracle Enterprise Manager (Enterprise Manager) when monitoring system activity.
- The database writes transaction names to the transaction auditing redo record, so you can use LogMiner to search for a specific transaction in the redo log.
- You can use transaction names to find a specific transaction in data dictionary views such as V$TRANSACTION.
6 - Status
6.1 - Active
An active transaction has started but not yet committed or rolled back. From the successful execution of this update until the ROLLBACK statement ends the transaction, the sal_update transaction is active.
Before the transaction ends, the state of the data is as follows:
The undo data contains the old data values changed by the SQL statements of the transaction. See "Read Consistency in the Read Committed Isolation Level".
The redo log record contains the change to the data block and the change to the undo block.
- Changes have been made to the database buffers of the SGA.
The data changes for a committed transaction, stored in the database buffers of the SGA, are not necessarily written immediately to the data files by the database writer (DBWn). The disk write can happen before or after the commit.
- The rows affected by the data change are locked.
Other users cannot change the data in the affected rows, nor can they see the uncommitted changes.
6.2 - Enqueued
Depending on the scenario, transactions waiting for previously locked resources may still be blocked after a rollback to savepoint. When a transaction is blocked by another transaction it enqueues on the blocking transaction itself, so that the entire blocking transaction must commit or roll back for the blocked transaction to continue.
In the scenario shown in this Table (10-2), session 1 rolls back to a savepoint created before it executed a DML statement. However, session 2 is still blocked because it is waiting for the session 1 transaction to complete.
7 - Type
8 - Example
The following example execute an UPDATE statement to begin a transaction and queries V$TRANSACTION for details about the transaction:
SQL> UPDATE hr.employees SET salary=salary; 107 ROWS updated. SQL> SELECT XID AS "txn id", XIDUSN AS "undo seg", XIDSLOT AS "slot", 2 XIDSQN AS "seq", STATUS AS "txn status" 3 FROM V$TRANSACTION; txn id undo seg slot seq txn STATUS ---------------- ---------- ---------- ---------- ---------------- 0600060037000000 6 6 55 ACTIVE
A transaction ends when any of the following actions occurs:
- A user exits normally from most Oracle Database utilities and tools, causing the current transaction to be implicitly committed. The commit behavior when a user disconnects is application-dependent and configurable.
- A client process terminates abnormally, causing the transaction to be implicitly rolled back using metadata stored in the transaction table and the undo segment.
After one transaction ends, the next executable SQL statement automatically starts the following transaction. The following example executes an UPDATE to start a transaction, ends the transaction with a ROLLBACK statement, and then executes an UPDATE to start a new transaction (note that the transaction IDs are different):
SQL> UPDATE hr.employees SET salary=salary; 107 ROWS updated. SQL> SELECT XID, STATUS FROM V$TRANSACTION; XID STATUS ---------------- ---------------- 0800090033000000 ACTIVE SQL> ROLLBACK; ROLLBACK complete. SQL> SELECT XID FROM V$TRANSACTION; no ROWS selected SQL> UPDATE hr.employees SET last_name=last_name; 107 ROWS updated. SQL> SELECT XID, STATUS FROM V$TRANSACTION; XID STATUS ---------------- ---------------- 0900050033000000 ACTIVE