Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Autonomous Transactions and table locks

518523Jan 10 2007 — edited Jan 10 2007
Does 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;

/
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 7 2007
Added on Jan 10 2007
4 comments
1,163 views