Dear readers of our blog, we'd like to recommend you to visit the main page of our website, where you can learn about our product SQLS*Plus and its advantages.
 
SQLS*Plus - best SQL Server command line reporting and automation tool! SQLS*Plus is several orders of magnitude better than SQL Server sqlcmd and osql command line tools.
 

REQUEST COMPLIMENTARY SQLS*PLUS LICENCE

Enteros UpBeat offers a patented database performance management SaaS platform. It proactively identifies root causes of complex revenue-impacting database performance issues across a growing number of RDBMS, NoSQL, and deep/machine learning database platforms. We support Oracle, SQL Server, IBM DB2, MongoDB, Casandra, MySQL, Amazon Aurora, and other database systems.

Discrete and standalone transactions in Oracle

3 September 2020

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.

 
Tags: , , , ,

MORE NEWS

 

Preamble​​NoSql is not a replacement for SQL databases but is a valid alternative for many situations where standard SQL is not the best approach for...

Preamble​​MongoDB Conditional operators specify a condition to which the value of the document field shall correspond.Comparison Query Operators $eq...

5 Database management trends impacting database administrationIn the realm of database management systems, moreover half (52%) of your competitors feel...

The data type is defined as the type of data that any column or variable can store in MS SQL Server. What is the data type? When you create any table or...

Preamble​​MS SQL Server is a client-server architecture. MS SQL Server process starts with the client application sending a query.SQL Server accepts,...

First the basics: what is the master/slave?One database server (“master”) responds and can do anything. A lot of other database servers store copies of all...

Preamble​​Atom Hopper (based on Apache Abdera) for those who may not know is an open-source project sponsored by Rackspace. Today we will figure out how to...

Preamble​​MongoDB recently introduced its new aggregation structure. This structure provides a simpler solution for calculating aggregated values rather...

FlexibilityOne of the most advertised features of MongoDB is its flexibility.  Flexibility, however, is a double-edged sword. More flexibility means more...

Preamble​​SQLShell is a cross-platform command-line tool for SQL, similar to psql for PostgreSQL or MySQL command-line tool for MySQL.Why use it?If you...

Preamble​​Writing an application on top of the framework on top of the driver on top of the database is a bit like a game on the phone: you say “insert...

Preamble​​Oracle Coherence is a distributed cache that is functionally comparable with Memcached. In addition to the basic function of the API cache, it...

Preamble​​IBM pureXML, a proprietary XML database built on a relational mechanism (designed for puns) that offers both relational ( SQL / XML ) and...

  What is PostgreSQL array? In PostgreSQL we can define a column as an array of valid data types. The data type can be built-in, custom or enumerated....

Preamble​​If you are a Linux sysadmin or developer, there comes a time when you need to manage an Oracle database that can work in your environment.In this...

Preamble​​Starting with Microsoft SQL Server 2008, by default, the group of local administrators is no longer added to SQL Server administrators during the...