1

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)).

-1

If possible, listen to the mantra: "Don't queue it, just do it."

Otherwise, keep queuing and normalization code outside of the main transactions.

As for logging, I don't think there is a solution other than writing to a MyISAM table for your log.

Back to your question...

There is no way in MySQL to have nested, independent, transactions on the same connection. Two connections would allow for it, but that could be messy for other reasons.

  • any better now? – Rick James Mar 4 '16 at 20:54
  • What about MySQL-compatible Amazon Aurora DB? It only uses InnoDB storage engine, no MyISAM. Do we have any options there to write into log from stored procedures independently from main transactions? Or just "forget about it"? – Oleg Khaykin Dec 26 '18 at 16:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.