Scenario 1) Default transaction using AUTONOMOUS TRANSACTION and checking records in table called APP_LOG at each step. The store procedure write_log inserts record in the app_log table.

Session (a) In this session execute AUTONOMOUS transaction, get row count as table is empty will be 0 at first after exeuction of the procedure both session will see the records due to AUTONOMOUS transaction.
SQL> select count(1) from app_log;

COUNT(1)
———-
0

1 row selected.

SQL> exec write_log(‘message (‘ || to_char(sysdate, ‘dd-mon-yyyy hh24:mi:ss’));

PL/SQL procedure successfully completed.

SQL> select count(1) from app_log;

COUNT(1)
———-
1

1 row selected.

Session (b) – can see the value as the transaction in session(a) is autonomous transaction
SQL> select msg from app_log;

MSG
——————————————————————————–
message (06-feb-2011 13:09:05)

1 row selected.

Scenario 2) using AUTONOMOUS TRANSACTION with READ ONLY transaction. The procedure with AUTONOMOUS transaction is able to insert record in the app_log table and as expected an insert statement will fail.

Session(a) Manual insert fails and call to the store procedure write_log which execute AUTONOMOUS transaction succeeds
SQL> set transaction read only;

Transaction set.

-- manual insert fails as expected as transaction is READ ONLY
SQL> insert into app_log values (‘message (‘ || to_char(sysdate, ‘dd-mon-yyyy hh24:mi:ss’) || ‘)’, sysdate);
insert into app_log values (‘message (‘ || to_char(sysdate, ‘dd-mon-yyyy hh24:mi:ss’) || ‘)’, sysdate)
*
ERROR at line 1:
ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction

-- AUTONOMOUS transaction works
SQL> exec write_log(‘message (‘ || to_char(sysdate, ‘dd-mon-yyyy hh24:mi:ss’) || ‘)’);

PL/SQL procedure successfully completed.

Session (b) – Shows records is committed
SQL> select msg from app_log;

MSG
——————————————————————————–
message (06-feb-2011 13:09:05)
message (06-feb-2011 13:10:26)

2 rows selected.

-- Rollback/commit ran to finish the read only transaction in session (a)
SQL> rollback;

Rollback complete.

Session (b) – Verify records is seen from another session as it is committed to AUTONOMOUS TRANSACTION even though rollback was executed in session(a).
SQL> select msg from app_log;

MSG
——————————————————————————–
message (06-feb-2011 13:09:05)
message (06-feb-2011 13:10:26)

2 rows selected.

Scenario 3) using AUTONOMOUS TRANSACTION with READ WRITE transaction.
SQL> set transaction read write;

Transaction set.

SQL> select msg from app_log;

MSG
——————————————————————————–
message (06-feb-2011 13:09:05)
message (06-feb-2011 13:10:26)

2 rows selected.

SQL> exec write_log(‘message (‘ || to_char(sysdate, ‘dd-mon-yyyy hh24:mi:ss’) || ‘)’);

PL/SQL procedure successfully completed.

Session (b) – Shows 3 records as expected new one added due to AUTONOMOUS transaction

SQL> select msg from app_log;

MSG
——————————————————————————–
message (06-feb-2011 13:15:16)
message (06-feb-2011 13:09:05)
message (06-feb-2011 13:10:26)

3 rows selected.

-- manual insert works as the transaction is read write
SQL> insert into app_log values (‘message (‘ || to_char(sysdate, ‘dd-mon-yyyy hh24:mi:ss’) || ‘)’, sysdate);

1 row created.

Session (b) – Still 3 records as expected new one added due to AUTONOMOUS transaction and 4th one is not yet committed in session (a)

SQL> select msg from app_log;

MSG
——————————————————————————–
message (06-feb-2011 13:15:16)
message (06-feb-2011 13:09:05)
message (06-feb-2011 13:10:26)

3 rows selected.

SQL> commit;

Commit complete.

Session (b) – Shows 4 records as expected as 2 inserts were committed in session (a)
SQL> select msg from app_log;

MSG
——————————————————————————–
message (06-feb-2011 13:15:16)
message (06-feb-2011 13:09:05)
message (06-feb-2011 13:10:26)
message (06-feb-2011 13:15:36)

4 rows selected.

Scenario 4) using AUTONOMOUS TRANSACTION with SERIALIZABLE transaction.

Session (a)
SQL> set transaction isolation level serializable;

Transaction set.

-- Show record count
SQL> select count(1) from app_log;

COUNT(1)
———-
4

1 row selected.

-- execute AUTONOMOUS transaction with that isolation level
SQL> exec write_log(‘message (‘ || to_char(sysdate, ‘dd-mon-yyyy hh24:mi:ss’) || ‘)’);

PL/SQL procedure successfully completed.

-- Show record count has not changed
SQL> select count(1) from app_log;

COUNT(1)
———-
4

1 row selected.

Session (b) – Shows 5 transaction in session (b) whereas session (a) as seen above shows 4 due to isolation level is serializable
SQL> select msg from app_log;

MSG
——————————————————————————–
message (06-feb-2011 13:15:16)
message (06-feb-2011 13:09:05)
message (06-feb-2011 13:10:26)
message (06-feb-2011 13:15:36)
message (06-feb-2011 13:17:17)

5 rows selected.