Using AUTONOMOUS_TRANSACTION and setting TRANSACTION ISOLATION

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.

Advertisements

Example of AUTONOMOUS_TRANSACTION

Example of how AUTONOMOUS_TRANSACTION, it allows one to isolate a DML statements, the DML statements with the AUTONOMOUS transaction are independent of the main transaction. AUTONOMOUS_TRANSACTION can be used in PL/SQL code in procedures, functions, triggers.

-- store procedure that writes message to a log table and defined AUTONOMOUS_TRANSACTION
SQL> CREATE OR REPLACE PROCEDURE scott.write_log(v_msg IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO app_log VALUES (v_msg, SYSDATE);
COMMIT;
END;
/

Procedure created.

-- Shows scott.test and scott.app_log are empty
SQL> select * FROM scott.test;

no rows selected

SQL> select * FROM scott.app_log;

no rows selected

-- Create a main transaction
SQL> insert into scott.test values (1, 2);

1 row created.

-- insert in app_log through the store procedure which uses AUTOMOUS_TRANSACTION
SQL> exec scott.write_log(‘message 1’);

PL/SQL procedure successfully completed.

-- Shows transaction is there in scott.test in that user’s session, note the main transaction is not yet committed
SQL> select * FROM scott.test;

A B
———- ———-
1 2

1 row selected.

-- rollback main transaction and show records after rollback there are no rows in scott.test and scott.app_log has the message that was inserted
SQL> rollback;

Rollback complete.

SQL> select * FROM scott.test;

no rows selected

SQL> select * FROM scott.app_log;

MSG
——————————————————————————–
LOG_DATE
———
message 1
05-FEB-11

Note: The PL/SQL scode that contains AUTONOMOUS_TRANSACTION, the transaction should have a COMMIT or ROLLBACK otherwise it will return “ORA-06519: active autonomous transaction detected and rolled back” when the code executes.
Example:
SQL> CREATE OR REPLACE PROCEDURE scott.write_log(v_msg IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO app_log VALUES (v_msg, SYSDATE);
END;
/

Procedure created.

SQL> exec scott.write_log(‘test1’);
BEGIN scott.write_log(‘test1’); END;

*
ERROR at line 1:
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at “SCOTT.WRITE_LOG”, line 6
ORA-06512: at line 1

Example of SAVEPOINT and “ROLLBACK TO..”

Examples of using “SAVEPOINT” and “ROLLBACK TO”

SQL> select * from scott.test;

no rows selected

SQL> insert into scott.test values (1, 2);

1 row created.

SQL> savepoint trans_1;

Savepoint created.

SQL> insert into scott.test values (3, 4);

1 row created.

SQL> savepoint trans_2;

Savepoint created.

SQL> insert into scott.test values (5, 6);

1 row created.

-- it will rollback upto the trans_1 so the 2 insert statements
SQL> rollback to trans_1;

Rollback complete.

SQL> select * from scott.test;

A B
———- ———-
1 2

1 row selected

How to find blocking session?

In 10g, there is a column in V$session called blocking_session to find the session id that is blocking.

Session 1) sid # 145

SQL> select userenv(‘sid’) from dual;

USERENV(‘SID’)
————–
145

SQL> create table t ( x number );

Table created.

SQL> create unique index t_idx on t(x);

Index created.

SQL> insert into t values ( 1 );

1 row created.

SQL> insert into t values ( 1 );

1 row created.

Session #2) sid – 159

SQL> select userenv(‘sid’) from dual;

USERENV(‘SID’)
————–
159

— hung as 145 is blocking as i haven’t committed as the table t has primary key
SQL> insert into t values ( 1 );

Session 1) to find the blocking_session

— shows both session are active, the second session# 159 is blocked by 145

SQL> select sid, blocking_session, status from V$session where sid IN (159, 145);

SID BLOCKING_SESSION STATUS
———- —————- ——–
145 ACTIVE
159 145 ACTIVE

— another way of find sessions blocked the status of blocked session is VALID
SQL> select sid, blocking_session, seconds_in_wait from v$session where blocking_session_status = 'VALID';

SID BLOCKING_SESSION SECONDS_IN_WAIT
———- —————- —————
159 145 130