A transaction is a logical, atomic unit of work that contains one or more SQL statements.
A transaction groups SQL statements so that they are either all committed, which means they are applied to the database, or all rolled back, which means they are undone from the database. Oracle Database assigns every transaction a unique identifier called a transaction ID.
All Oracle transactions obey the basic properties of a database transaction, known as ACID properties.
ACID is an acronym for the following:
• Atomicity
All tasks of a transaction are performed or none of them are. There are no partial transactions.
For example, if a transaction starts updating 100 rows, but the system fails after 20 updates, then the database rolls back the changes to these 20 rows.
• Consistency
The transaction takes the database from one consistent state to another consistent state.
For example, in a banking transaction that debits a savings account and credits a checking account, a failure must not cause the database to credit only one account, which would lead to inconsistent data.
• Isolation
The effect of a transaction is not visible to other transactions until the transaction is committed.
For example, one user updating the hr.employees table does not see the uncommitted changes to employees made concurrently by another user. Thus, it appears to users as if transactions are executing serially.
• Durability
Changes made by committed transactions are permanent. After a transaction completes, the database ensures through its recovery mechanisms that changes from the transaction are not lost.
The use of transactions is one of the most important ways that a database management system differs from a file system.
Structure of a Transaction
A database transaction consists of one or more statements.
Specifically, a transaction consists of one of the following:
• One or more data manipulation language (DML) statements that together constitute an atomic change to the database
• One data definition language (DDL) statement
A transaction has a beginning and an end.
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.
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
End of a Transaction
A transaction can end under different circumstances.
A transaction ends when any of the following actions occurs:
• A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause.
In a commit, a user explicitly or implicitly requested that the changes in the transaction be made permanent. Changes made by the transaction are permanent and visible to other users only after a transaction commits.
• 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. If the current transaction contains DML statements, then Oracle Database first commits the transaction and then runs and commits the DDL statement as a new, single-statement transaction.
• 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.
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
