A good reminder to look at the overall picture.

I have had to write a stored procedure to prepare invoices. There are several sub-tables such as general_ledger_distribution, invoice_discount, sales_invoice_product etc. If something fails, both the main table and all sub-tables MUST revert to their original state. This in turn calls for a lengthy transaction. When there are 800 invoices to be generated, this takes about 15 seconds but is only run, at best, once or twice a day.Mind you, the process is yet to be optimized as there is an obvious lack of indexes on foreign keys, etc. There is also a second stored procedure which takes an incoming customer purchase order containing 800 distinct orders. A lot of information and validation is required, such as comparing the EDI price against the customer_item_price table, etc.

Not using a transaction means going back to each table involved to delete records involved in the failure of other processes. And, should one DELETE fail, this requires additional recovery to wipe out the unwanted records. A transaction will not help here, since it only results in all of the DELETE steps being rolled back due to the failure of one step. Then all unwanted records remain in the database.