Mysql – emulate Oracle’s autonomous transaction in MySQL

innodbmyisamMySQLtransactiontrigger

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

Best Answer

If you have access to the Aria engine or another non-transactional engine, then yes.

Very short proof of concept, tested on MariaDB 10.5.4:

CREATE TABLE test_aria_engine ( a int, b varchar(1000) )
ENGINE=Aria DEFAULT CHARSET=utf8 PAGE_CHECKSUM=1 TRANSACTIONAL=1 TABLE_CHECKSUM=1
COMMENT='test-aria_engine with transactional on 1'

The transactional in Aria is not for real transactions, it is only last operation log, still good in case of server crash the data is consistent.

Now create an InnoDB table:

CREATE TABLE test_innodb_engine ( a int , b varchar(1000) )

And test:

BEGIN TRANSACTION;

-- insert in aria table
INSERT INTO test_aria_engine ( a, b) VALUES (4 , 'ddd');

-- insert in innodb table
INSERT INTO test_innodb_engine( a, b) VALUES ( 4 , 'dddd');

-- rollback
ROLLBACK;

After the ROLLBACK has been initiated let's check the table contents of the Aria and InnoDB tables:

SELECT * FROM test_aria_engine;

|a  |b  |
|---|---|
|4  |ddd|

So, ROLLBACK cannot rollback ARIA engine

SELECT * FROM test_innodb_table;

No result.

So in the InnoDB table ROLLBACK has done its job.

This shows how to avoid transaction logic in MariaDB or using a similar engine like MyISAM in MySQL.

Related Question