Discrete and standalone transactions in Oracle

To help in managing transactions, Oracle DBMS allows you to use two special types of transactions – discrete transactions and standalone transactions. In this blog article, we will describe both concepts.

Discrete transactions

To increase the speed of transaction execution, Oracle Database allows you to explicitly use discrete transactions. When a transaction is specified as discrete, Oracle skips certain routine operations with overheads such as saving undo records, thus speeding up the transaction as a whole. Oracle does not modify data blocks until the transaction is recorded.

The BEGIN_DESCRETE_TRANSACTION procedure, included in the DBMS_TRANSACTION package, serves to implement a discrete transaction strategy.

Short transactions are executed faster using this procedure, but if a discrete transaction occurs during the execution of long queries, and these queries require data modified by these transactions, problems may occur.

Since discrete transactions skip the undo data recording process, lengthy queries cannot obtain a consistent data presentation. Oracle does not generate undo records for discrete transactions, because data blocks are not modified until the discrete transaction is fixed.

Standalone transactions

A transaction can be a part of another transaction. In such cases, the parent transaction is referred to as the principal transaction and the independent subsidiary transaction is referred to as the standalone transaction.

A standalone transaction is formally defined as an independent transaction that can be called from another transaction. Note that although the child transaction is called from the parent transaction, it is independent of the parent transaction.

Packages, procedures, functions, and triggers may include transactions marked as offline. You will need to include some directive in the master transaction so that Oracle knows that you intend to use an offline transaction within the master transaction.

Like any normal transaction, a standalone transaction can have its own ROLLBACK and COMMIT operators.

The master transaction, using a standalone transaction, can pause and execute the standalone transaction and then continue from the stopping point. In other words, you save the context of the calling transaction, execute SQL statements as part of the offline transaction, commit or rollback the transaction, and then continue executing the parent transaction until you return to the context of the calling transaction. Note that an offline transaction does not share resources like locks with a parent transaction.

Offline transactions provide developers with the ability to create finer-grained transactions when a transaction ceases to be subject to the “all or nothing” rule. Nested offline transactions can be locked down and rolled back regardless of the parent transaction call.

Take a note! If you do not use offline transactions, all changes in the session will be committed or canceled immediately (by COMMIT or ROLLBACK command). Offline transactions allow you to commit or undo changes in subroutines, regardless of the main program. It should also be noted that if an offline transaction is not committed or rolled back, Oracle will issue an error message.

The listing below shows a simple example of a stand-alone transaction. Note that the operator (compiler directive) PRAGMA_AUTONOMOUS_TRANSACTION forces Oracle to mark the attached part of the code – the function loans – as offline.

SQL> CREATE OR REPLACE package lending AS function loans
(user_id integer) return real;
-- add additional features and/or packages
END lending;

CREATE OR REPLACE PACKAGE BODY lending AS
function loans (user_id integer) return REAL IS
PRAGMA AUTONOMOUS_TRANSACTION;
loan_bal REAL;
BEGIN
-- Here's the code
END;

-- this is where any additional features and/or packages are placed.
END lending;
SQL>

Offline transactions provide high flexibility. You can pause the master transaction, start the offline transaction, and then resume processing the master transaction.

The committed changes of the standalone transaction are visible to the main transaction because the default isolation level in Oracle is READ COMMITTED, which means that the transaction can see all committed data.

Off-line transactions have many applications. For example, they can be used to send log messages about errors. You can have a single procedure that writes error messages to the log table and call this procedure as an offline transaction from a regular transaction. The listing below shows the error message entry in the table.

SQL> CREATE OR REPLACE PROCEDURE error_log(error_msg in varchar2,
procedure_name IN VARCHAR2 IS
PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN
INSERT INTO log_table (error_msg, procedure_name)
VALUES (error_msg,procedure_name));
COMMIT;
EXCEPTION
WHEN OTHERS THEN ROLLBACK;
END;
SQL>

Offline transactions may serve other purposes in the Oracle database. For example, they may allow processing non-standard PL/SQL code problems, such as using DDL operators in triggers. Offline transactions are also convenient for auditing database queries and unsuccessful (unauthorized) actions in the database.

The listing below shows an example of using the standalone transaction tool to audit (presumably) unauthorized update activity. Even if the user fails to attempt an update, his name shall be logged in the audit table if you encode a simple pair of triggers using the Offline Transaction Tool.

SQL> CREATE OR REPLACE TRIGGER aud_bef_trig
BEFORE INSERT ON emp FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION
BEGIN
INSERT INTO audit_employee VALUES (
:new.username, 'before inserting', sysdate);
COMMIT;
END;

SQL> CREATE OR REPLACE TRIGGER aud_aft_trig
AFTER INSERT ON emp FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS TRANSACTION
BEGIN
INSERT INTO audit_emp VALUES (
:new.username, 'after inserting', sysdate);
COMMIT;
END;
SQL>

Note that it is not always possible to use a pair of common triggers to audit activity in the Oracle database, as the audit data provided by the triggers will not be recorded if the operator that triggered the trigger is canceled.