Serialize Effect of Autonomous Transaction

View All Scripts Login to Run Script

Script Name Serialize Effect of Autonomous Transaction
Description You can use the SET TRANSACTION statement to begin a read-only or read-write transaction, establish an isolation level, or assign the current transaction to a specified rollback segment. In the context of autonomous transactions, use SET TRANSACTION ISOLATION LEVEL SERIALIZABLE if you do not want the session that invoked the autonomous transaction subprogram to see the effects of that subprogram's changes - until that session commits.
Area PL/SQL General
Contributor Steven Feuerstein
Created Tuesday January 12, 2016

Statement 1

Local Copy of Employees Table

CREATE TABLE employees AS SELECT * FROM hr.employees

Table created.

Statement 2
Remove all rows from the employees table and immediately commit. But since I included the AUTONOMOUS_TRANSACTION pragma, any other changes in the session will not be committed.
A Mean Autonomous Transaction Procedure
```
CREATE OR REPLACE PROCEDURE fire_em_all  
IS  
   PRAGMA AUTONOMOUS_TRANSACTION;  
BEGIN  
   DELETE FROM employees;  
  
   COMMIT;  
END; 
```
```
Procedure created.
```

Statement 3

By setting the isolation level to SERIALIZABLE, the effect of the autonomous transaction (removing all rows from employees) will not be visible in the outer block until a commit or rollback occurs in that block.

Impact of Serialization of Transaction

DECLARE  
   num   INTEGER;  
BEGIN  
   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;  
  
   SELECT COUNT (*)  
     INTO num  
     FROM employees;  
  
   DBMS_OUTPUT.put_line ('Before isolated AT delete ' || num);  
   fire_em_all;  
  
   SELECT COUNT (*)  
     INTO num  
     FROM employees;  
  
   DBMS_OUTPUT.put_line ('After isolated AT delete ' || num);  
   COMMIT; -- ROLLBACK;  
  
   SELECT COUNT (*)  
     INTO num  
     FROM employees;  
  
   DBMS_OUTPUT.put_line ('After MT commit ' || num);  
END;

Before isolated AT delete 107
After isolated AT delete 107
After MT commit 0

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database