Oracle Database - Transactions

Card Puncher Data Processing

About

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

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:

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.

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

Documentation / Reference





Discover More
Card Puncher Data Processing
Oracle Database - COMMIT (of a transaction)

in oracle A commit ends the current transaction and makes permanent all changes performed in the transaction. When a transaction commits, the following actions occur: A system change number...
Card Puncher Data Processing
Oracle Database - DDL Locks (data dictionary lock)

A data dictionary (DDL) lock protects the definition of a schema object while an ongoing DDL operation (CREATE, ALTER, DROP) acts on or refers to the object. Only individual schema objects that are modified...
Card Puncher Data Processing
Oracle Database - Distributed Transactions

A distributed database is a set of databases in a distributed system that can appear to applications as a single data source. A distributed transaction is a transaction that includes one or more statements...
Toad Lock
Oracle Database - Locks

Lock Mechanism in Oracle Oracle Database provides: data concurrency, consistency. The data a session is viewing or changing must not be changed by other sessions until the user is finished. and...
Card Puncher Data Processing
Oracle Database - ROLLBACK (undo of a transaction)

Use the ROLLBACK statement to undo work done in the current transaction or to manually undo the work done by an in-doubt distributed transaction. An implicit ROLLBACK occurs when the session (or...
Card Puncher Data Processing
Oracle Database - Read Consistency

in Oracle The database uses a transaction table, also called an interested transaction list (ITL), to determine if a transaction was uncommitted when the database began modifying the block. The block...
Oracle Database Row Lock Tx
Oracle Database - Row Locks (TX)

A row lock, also called a TX lock, is a lock on a single row of table. A transaction acquires a row lock for each row modified by an INSERT, UPDATE, DELETE, MERGE, or SELECT ... FOR UPDATE statement. The...
Card Puncher Data Processing
Oracle Database - SAVEPOINT (of a transaction)

A savepoint is a user-declared intermediate marker within the context of a transaction. Internally, this marker resolves to an SCN. Savepoints divide a long transaction into smaller parts. If you use...
Card Puncher Data Processing
Oracle Database - System Change Numbers (SCNs) - Point in time

A system change number (SCN) is a logical, internal time stamp used by Oracle Database. SCNs order events that occur within the database, which is necessary to satisfy the ACID properties of a transaction....
Card Puncher Data Processing
Oracle Database - Table Lock (TM)

A table lock, also called a TM lock, is acquired by a transaction when a table is modified by an: INSERT, UPDATE, DELETE, MERGE, SELECT with the FOR UPDATE clause, or LOCK TABLE statement....



Share this page:
Follow us:
Task Runner