Autonomous Transactions

David Clement

September 2004

Oracle versions 7-8

A commit releases row-level locks, which can be a significant performance problem in those cases where more than one process needs to update a row. How, then, can a program release locks without fetching across commits?

One way is to execute autonomous transactions. Oracle allows transactions within PL/SQL routines to commit without any consequences for the parent transaction (although not on the same table, of course) as follows.

          procedure myProcedure 
            (myParameter1 in varchar,
            myParameter2 in number)
          is
            pragma autonomous transaction;
          begin
            update myTable
            set myColumn = myParameter1
            where myKey = myParameter2;
            commit;
          end;