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.

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle Internal and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s