About
All Oracle transactions comply with the basic properties of a database transaction, known as ACID properties.
Articles Related
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,
- slot,
- and sequence number.
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
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 VTRANSACTION.
Status
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.
Data changes made by a transaction are temporary until the transaction is committed or rolled back.
Before the transaction ends, the state of the data is as follows:
- Oracle Database has generated undo data information in the system global area (SGA).
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.
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.
Type
Example
The following example execute an UPDATE statement to begin a transaction and queries VTRANSACTION 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