Discrete and standalone transactions in Oracle.

Preamble

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 alter data blocks until the transaction record.

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

This method expedites the execution of brief transactions. Problems may occur if a discrete transaction occurs during the execution of long queries, requiring data modified by these transactions.

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. Data blocks are not modified until the discrete transaction is fixed. 

Standalone transactions

A transaction can be a part of another transaction. The parent transaction is referred to as the substantial transaction, and the independent subsidiary transaction is referred to as the standalone transaction.

According to the formal definition, a solo transaction is an independent transaction that can call from another transaction. Even if it hails from the parent transaction, the child transaction is separate from it.

Packages, procedures, functions, and triggers may include transactions marked as offline. You will need to include some directives in the master transaction. Oracle must know that you intend to use offline marketing 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. Regardless of the call made by the parent transaction, nested offline transactions can be locked down and rolled back.

Take a note! All modifications made during the session will immediately commit or canceled if you do not use offline transactions (by COMMIT or ROLLBACK command). Offline transactions allow you to commit or undo changes in subroutines, regardless of the main program. Also, Oracle will issue an error message if an offline transaction is not committed or rolled back.

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.

Because Oracle’s default isolation level is READ COMMITTED, which allows the transaction to see all committed data, the standalone transaction’s committed updates are visible to the primary transaction.

Off-line transactions have many applications. They can be used, for instance, to deliver log messages concerning failures. 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. If you encode a straightforward pair of triggers using the Offline Transaction Tool, the user’s name will be logged in the audit database even if he doesn’t try to alter anything.

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>

Should note that using two common triggers to audit activity in an Oracle database is not always practicable. If the operator initiated the motivation fire, the triggers wouldn’t record the audit data they gave.

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.