Autonomous Transactions and table locks
518523Jan 10 2007 — edited Jan 10 2007Does a commit statement in an Autonomous Transaction block in PL/SQL release locks aquired in the master transaction block?
e.g
CREATE OR REPLACE PACKAGE test_auto_trans AS
PROCEDURE mainproc;
PROCEDURE testproc;
END test_auto_trans;
/
CREATE OR REPLACE PACKAGE BODY test_auto_trans AS
/*****************Main Procedure*********************/
PROCEDURE mainproc AS
BEGIN
LOCK TABLE a,b,c IN EXCLUSIVE MODE nowait;
/*some processing involving tables a,b,c here. no commit done yet*/
testproc();
/* will the locks on a,b,c still be available here? */
EXCEPTION
WHEN others THEN
dbms_output.put_line('Error');
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
RAISE;
END mainproc;
/*****************test Procedure*********************/
PROCEDURE testproc AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
/*some processing using tables a,b,c here*/
commit;
EXCEPTION
WHEN others THEN
dbms_output.put_line('Error');
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
RAISE;
END testproc;
END test_auto_trans;
/