Oracle Architecture: Transaction

A transaction is implicitly started by any operation that obtains a TX lock:

• DML statements
• select for update statements
• Executing set transaction
• Using dbms_transaction

The transaction is ended

• explicitly by either executing a commit or a rollback or
• implicitly ended by executing a DDL statement

When a session wishes to commit the changes it made, the redo log entries that belong to that transactions and that are still stored in the redo log buffer need to be written to disk so that is possible to recovery the transaction.

This is necessary because the changes might not yet be written to the data files.

The redo records are written to disk by the Log Writer process (LGWR).

When LGWR writes the redo entries to the online redo log file, it also assigns an SCN to be able to identify them again.

When LGWR is finished with everything, it notifies the session that the transaction is now securly safed.

It should be noted that LGWR, in order to reduce I/O, also writes redo log entries of transactions that have not yet been committed. In case of a recovery, these can be rolled back.

A transaction can be marked as read only when starting it with set transaction read only.

In a read only transaction, only the following statements are allowed:

• select statements (without the for update clause)
• lock table
• set role
• alter session
• alter system

A read only transaction does not see data that was modified and committed after the transaction was started. This behavior is referred to as transaction-level read consistency.

User SYS cannot set its transaction to read only.

It is possible to prevent a PL/SQL procedure from executing a commit or rollback statement:

alter session disable commit in procedure;

The first DML statement in a transaction allocates a transaction ID which uniquely identifies the transaction.

The transaction id consists of

• the undo segment number
• a (transaction table?) slot
• a sequence number

These three numbers can be queried from v$transaction: attributes xidusn, xidslot and xidsqn. The value of xid combines these numbers.

The function dbms_transaction.local_transaction_id returns these three values separated by a dot.

In PL/SQL, a (non-nested) block can be given the pragma autonomous_transaction which causes this block to be executed in its own transaction.

A DDL statement commits the current transaction (but the creation of a Private Temporary Table does not).

The SQL*Plus configuration setting exitcommit.

The set constraints and set transaction statements.

TCL statements

Reservable columns allow multiple transactions to concurrently update their values.

The error message ORA-02091: transaction rolled back