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…
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…
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.…
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…
The following 2 links explains transaction isolation. http://www.acs.ilstu.edu/docs/Oracle/server.101/b10743/consist.htm http://www.oracle.com/technology/oramag/oracle/05-nov/o65asktom.html
