Basic Understanding of Autonomous Transaction in Oracle

Most of us are aware of Use of Autonomous transactions in oracle and what benefit it exhibit.

just to elaborate on same,
It help to Perform SQL operations(Especially committed DML) independent of primary transaction.
In precise, Its shares no lock , resources or commit dependency with main or primary transactions.

In current Blog, will try to explain in brief how internally Autonomous transaction is implemented.

Internally whenever we mark a block as “PRAGMA AUTONOMOUS_TRANSACTION”, Oracle will create new database session for executing operations defined within autonomous transaction.

By creating New Session, Oracle is able to perform operations within “Pragma Autonomous” as independent entities.

lets walk through an examples, experience by me on one of performance analysis.

create table tab_auto_trans
(
col1 number primary key
);

CREATE OR REPLACE FUNCTION Test_func_Auto_Trans RETURN NUMBER IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO tab_auto_trans Values(1);
COMMIT;
RETURN 1;
END Test_func_Auto_Trans;
/


Function is defined as Pragma Block, with insert operations within a primary key column with hard coded value as “1”.

Session 1 :

Insert into tab_auto_trans values (1);

/*With in same session calling pragma autonomous functions and invoking same statement.*/

declare
i number;
begin
i := Test_func_Auto_Trans();
end;


Invoking Pragma block wihin same statement will cause deadlock.


Error report:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "DMAHTO.TEST_FUNC_AUTO_TRANS", line 4
ORA-06512: at line 4
00060. 00000 - "deadlock detected while waiting for resource"
*Cause: Transactions deadlocked one another while waiting for resources.
*Action: Look at the trace file to see the transactions and resources
involved. Retry if necessary.

At first glance, it was confusing to understand reason for same, as on actual cases bind values was used instead of hard codes as in our case.

It is causing deadlock, as from same session we are inserting a primary constraint data and later invoking autonomous function call causing new session to perform same insert operations.

Parent Session :
Insert –> primary key data insert but no commit.

Pragma autonomous functions call –> internally invoking new session and running same insert again.

As parent session is waiting for new invoke session to complete operations and new invoke session would be locked as same value is inserted due to primary constraint defined in parent session.

Hence deadlock was detected by oracle and thrown.

Point is to take is, what operation we are performing within Pragma autonomous block, whether it is related to parent session operation as both would be perform independently.To avoid any locked or deadlock issue.