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... Continue Reading →

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... Continue Reading →

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.... Continue Reading →

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... Continue Reading →

Create a free website or blog at WordPress.com.

Up ↑