InnoDB MySQL table with a trigger that inserts into a MyISAM table

Question

Background

I have a Java web application that is used by 300 - 500 users at any given time. On a separate Linux server we have a 5.7.19 MySQL Community Database Server that handles all the transactions for this application. We have a very active InnoDB table called "Requests". This table is involved in many queries. 70% are select statements, 20% are updates, 10% are inserts.

On any CRUD operation against Requests, there is a trigger that does an insert into a different table called Audit. That audit table is MyISAM. We have having these very weird performance issues once or twice a day that are caused by Lock Wait Timeouts (at least that is what I see in the log file for Apache Tomcat). So I wrote a shell script that dumps all the processes in MySQL whenever I see that entry in the Tomcat log file. What I see is that there is a full table lock Requests.

Question

Can a InnoDB table with a trigger that does a insert into a MyISAM table cause a table level lock on the InnoDB table?

Answer 1

Inserting into a MyISAM table may take a table lock on that table. Since that INSERT is coming from a Trigger on a InnoDB table, it is inside a transaction. So, if the table lock takes a long time, the transaction takes a long time, hence "lock wait timeout".

The exception on the table lock is if there have been no updates or deletes from the MyISAM table and the PK is AUTO_INCREMENT. (Or something like that. Dig in the docs for the specifics.)

Is it possible to add to audit outside the InnoDB transaction?

You say "full table lock requests" -- which table? What is the specific text?

Another thing that can mess up inserting a MyISAM table -- a big SELECT. Perhaps you do a big 'report' twice a day?

Rethink whether to use MyISAM for audit.