I have a number of tables that have triggers on them that insert records in queueing and logging tables. The problem is that those inserts are then part of the same transaction. That means that any IUD on the former tables, can be locked by other IUD that trigger inserts into the same queue, and that IUD that fails will not be logged, since those inserts will be rolled back as well.
I don't care much for entries in the queue where the triggering IUD was rolled back; the queue is read by some process that invalidates / refreshes cache and similar actions. Should anything have been rolled back, some cache will be refreshed with the same data. No big deal.
The business case here is a website with a great number of sets that consist of single products. Any one product can be in up to 15,000 sets. If the price of a product changes, the prices of all those sets need to be recalculated. I don't want to do that on the same transaction when saving the price of a product, because that would take too much time and lock too much of the database, so I enqueue the recalculation.
For logging, there are some actions I want to log, even (especially) if they fail. I can't do that now.
Were this Oracle, which I used until recently, I would've used
PRAGMA AUTONOMOUS_TRANSACTION to handle the insert in a separate transaction, that would always commit no matter what the other transaction did.
Can I emulate this behaviour by using MyISAM tables for the logs and the queues, with
concurrent_insert set to
ALWAYS, while the rest of my tables are InnoDB? (I expect multiple inserts into them, that's the whole issue, while they're seldom read (logs), or only once (queues)).