Oracle Database - Transactions

> Database > Oracle Database

1 - About

All Oracle transactions comply with the basic properties of a database transaction, known as ACID properties.

Advertising

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,
  • slot,
  • 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

Advertising

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.

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:

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".

  • Oracle Database has generated redo in the online redo log buffer of the SGA.

The redo log record contains the change to the data block and the change to the undo block.

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.

Advertising

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 issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause.
  • A user runs a DDL command such as CREATE, DROP, RENAME, or ALTER. The database issues an implicit COMMIT statement before and after every DDL statement.
  • 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

9 - Documentation / Reference

db/oracle/transaction.txt · Last modified: 2018/10/18 11:25 by gerardnico