Do the time of the COMMIT and ROLLBACK affect performance?

Question

Suppose I have a set of ID . For each ID , I will insert many records to many different tables based on the ID .Between inserting difference tables, different business checks will be called . If any checking fail , all the records that are inserted based on this ID will be ROLLBACK .This bulk insert action is done through using PL/SQL . Do the time of the COMMIT and ROLLBACK affect the performance and how does it affect ? For example , should I COMMIT after finish the process for one ID or COMMIT after finish all ID?

Answer 1

This is not so much of a performance decision but a process design decision. Do you want the other IDs to stay in the database when you have to roll back a faulty ID?

For obvious reasons, rollback takes longer when more rows must be rolled back. Rollback usually takes longer (sometimes much longer!) than the operations that have to be rolled back. Commit is always fast in Oracle, so it probably doesn't matter how often you commit in that regard.

Answer 2

Don't take the transaction time longer. make it short as possible as you can. Because according to your query some locks have been created. This locks may cause perfomance issues... so do it ID by ID...

Answer 3

There are two "forces" at work....

1. locking during your open transaction, oracle puts locks on the changed rows. whenever another transaction needs to update any of the locked rows, it has to wait. in the worst case, you can even build a deadlock.

2. synchronous write every commit performs a synchronous write. (there are ways to disable that, but it is usually the thing everybody wants: integrity). that synchronous write can take (much) longer then the a regular write (that can be buffered). Not to forget that there is usually an additional network round trip involved with an commit.

so, the one force says "commit as soon as possible (considering your integrity requirements)" the other says "commit as as less often as possible".

There are some other issues to consider as well, e.g. the maximum transaction size. every uncommited transaction needs some temporary space. the bigger the transaction gets, the more you need. You can also run into ORA-01555 "snapshot too old".

If there is any advice to give, then it is to implement a configurable "commit frequency" so that you can easily change it as needed.

Answer 4

Your problem description indicates you have a large set of smaller logical transactions (each new ID is a transaction). You should commit each logical transaction. The two reasons to wait to commit the entire set of transactions are:

1. If the entire set of transactions is in fact a transaction itself - all inserts must succeed for any rows to be committed. In that context, your smaller "transactions" aren't truly transactions.
2. You don't have a restart capability in your bulk load process, which in effect makes this a special case of item 1. If your bulk load process aborts, you need a way to skip successfully applied ID's.

Tom Kyte's advice is to commit each logical unit of work - the transaction.

Answer 5

One option if you need to control the individual sets but retain the ability to commit or rollback the entire transaction is to use savepoints. You can set a savepoint at the beginning of the outermost loop, then rollback to it if an error occurs. You might end up with something like this:

begin
   --Initial batch logging
   for r_record in cur_cursor loop
      savepoint s_cursor loop;
      begin
         --Process rows
      exception
         when others then
            rollback to s_cursor;
      end;
   end loop;
   --Final batch logging
exception
   when others then
      rollback;
      raise;
end;