Oracle versions 7-8
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.
The pragma tells Oracle to set up a separate, parallel transaction
(myParameter1 in varchar,
myParameter2 in number)
pragma autonomous transaction;
set myColumn = myParameter1
where myKey = myParameter2;
update statement. The row-level lock acquired
for that transaction is released with the autonomous
commit so it does not prevent any other processes from
updating the table myTable.