There's a procedure which basically deletes rows containing a given key.
Depending on the key up to 40 tables are involved in a run. In each
table there may be between 2 and 65,000 rows to be deleted. Altogether
about 130,000 rows are deleted in one run from different tables. Since
they are all bound to the given key the author of the procedure has
deferred the COMMIT until the very end, after all DELETE lines in order
to maintain the atomicity of this transaction.
However, this deferred COMMIT seems to increase the processing time
by a factor of about 4. If I put a COMMIT after each of the 40 or so
DELETE statements it takes a quarter of the usual time to accomplish
the task (30 seconds instead of 2 minutes).
Although I can't see how any of the DELETE statements could possibly
fail and compromise the integrity of the data, short of a black-out which
never occurred in the last 5 years, the atomicity of this transaction is
rather fetishized as untouchable.
The relatively long execution time itself causes problems. In particular,
if this procedure is invoked by more than one user at a time, most often
one of both, usually the latter, will fail with error
"ORA-08177: can't serialize access for this transaction".
Can someone tell me if it is normal for the deferred COMMIT to have
such a strong impact on the execution time?
What can be done to alleviate the impact ?
Is there a straight way to prevent simultaneous multiple execution of
a procedure ?
I suppose I should value the answers at thrice the 500 points.