Skip to main content

PRAGMA AUTONOMOUS_TRANSACTION with Transaction Isolation

What is PRAGMA ?

PRAGMA is a language construct that specifies how a compiler (or assembler or interpreter) should process its input. PRAGMA keyword in PLSQL is used to direct compiler to work differently when compared to the normal code. Pragmas are processed at compile time, not at run time.

What is AUTONOMOUS_TRANSACTION ?

The AUTONOMOUS_TRANSACTION pragma changes the way a subprogram works within a transaction. A subprogram marked with this pragma can do SQL operations and commit or roll back those operations, without committing or rolling back the data in the main transaction. The statement clearly states that autonomous transaction executes independent of main transaction. The concept was crystal clear to me and I had used it extensively in designing some of the solution. Very recently a question raised in my mind.

Will the changes made by autonomous transaction be visible in the main transaction ? I didn't have an answer and hence tried to do a research. Let try to get an answer using the below example.

Environment Setup


DROP TABLE emp_data; 

/ 
CREATE TABLE emp_data 
  ( 
     empno    NUMBER, 
     ename    VARCHAR2(10), 
     location VARCHAR2(10) 
  ); 

/ 
INSERT INTO emp_data 
            (empno, 
             ename, 
             location) 
VALUES      (1, 
             'Scott', 
             'US'); 

/ 
CREATE OR replace PROCEDURE Insert_emp(p_name IN VARCHAR2, 
                                       p_loc  IN VARCHAR2) 
IS 
  PRAGMA autonomous_transaction; 
BEGIN 
    INSERT INTO emp_data 
    VALUES      ( (SELECT Max(empno) 
                  FROM   emp_data), 
                 p_name, 
                 p_loc); 

    COMMIT; 
END; 

/ 
show errors 
/ 
CREATE OR replace PROCEDURE Add_emp(p_name IN VARCHAR2, 
                                    p_loc  IN VARCHAR2) 
IS 
  PROCEDURE Show_emp 
  IS 
  BEGIN 
      FOR data IN (SELECT * 
                   FROM   emp_data) LOOP 
          dbms_output.Put_line('EmpID : ' 
                               || data.empno 
                               || ' Ename : ' 
                               || data.ename 
                               ||' Location : ' 
                               || data.location); 
      END LOOP; 
  END; 
BEGIN 
    dbms_output.Put_line('****Before Insert****'); 

    show_emp; 

    Insert_emp(p_name, p_loc); 

    dbms_output.Put_line('****After Insert****'); 

    show_emp; 
END; 

/ 
show errors 
/


Lets try executing the code and see the output.

set serveroutput ON 

BEGIN 
    Add_emp('Tiger', 'UK'); 
END;



Output

****Before Insert****
EmpID : 1 Ename : Scott Location : US
****After Insert****
EmpID : 1 Ename : Scott Location : US
EmpID : 1 Ename : Tiger Location : UK

This answered by question . The example proves that changes made in autonomous transactions are visible in main transaction.

My next question was , Can we stop the changes made by autonomous transaction being show in the main transaction ? It is possible by controlling transaction visibility i.e. using transaction isolation level.

Lets see how this can be achieved.

ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE;

execute add_emp('Steve','Aus');

Output

****Before Insert****
EmpID : 1 Ename : Scott Location : US
EmpID : 1 Ename : Tiger Location : UK
****After Insert****
EmpID : 1 Ename : Scott Location : US
EmpID : 1 Ename : Tiger Location : UK


With this example i could conclude that changes made by an autonomous transaction become visible to other transactions when the autonomous transaction commits. The changes also become visible to the main transaction when it resumes, but only if its isolation level is set to READ COMMITTED (the default). If you set the isolation level of the main transaction to SERIALIZABLE, changes made by its autonomous transactions are not visible to the main transaction when it resumes.


Comments

Post a Comment

Popular posts from this blog

Check Whether File Exists on the Server Using PLSQL

There are different packages using which you can check whether a file exists on the database / application server. The different ways are 1. Using UTL_HTTP from Web Server 2. Using UTL_FILE from Database Server 3. Using DBMS_LOB from Database Server 1. You can also use UTL_HTTP package to check whether a file exists on a web server from the database. SET SERVEROUTPUT ON DECLARE   url       VARCHAR2(256) := 'http://www.oracle.com/index1.html';   username  VARCHAR2(256);   password  VARCHAR2(256);   req       UTL_HTTP.REQ;   resp      UTL_HTTP.RESP; BEGIN   req := UTL_HTTP.BEGIN_REQUEST(url);   IF (username IS NOT NULL) THEN     UTL_HTTP.SET_AUTHENTICATION(req, username, password);   END IF;   resp := UTL_HTTP.GET_RESPONSE(req);   DBMS_OUTPUT.PUT_LINE('response -->' || resp.status_code); END; / If the f...
Post a Comment
Read more

11.2 PLSQL New Feature : How to use DBMS_PARALLEL_EXECUTE to Update Large Tables in Parallel

The DBMS_PARALLEL_EXECUTE package enables you to incrementally update the data in a large table in parallel, in two high-level steps: 1. Group sets of rows in the table into smaller chunks. 2. Apply the desired UPDATE statement to the chunks in parallel, committing each time you have finished processing a chunk. This technique is recommended whenever you are updating a lot of data. Its advantages are: o You lock only one set of rows at a time, for a relatively short time, instead of locking the entire table. o You do not lose work that has been done if something fails before the entire operation finishes. o You reduce rollback space consumption. o You improve performance Different Ways to Spilt Workload : 1. CREATE_CHUNKS_BY_NUMBER_COL: Chunks the table associated with the given task by the specified column. 2. CREATE_CHUNKS_BY_ROWID : Chunks the table associated with the given task by ROWID 3. CREATE_CHUNKS_BY_SQL : Chunks the table associated with the given task by me...
Post a Comment
Read more