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.
Can a InnoDB table with a trigger that does a insert into a MyISAM table cause a table level lock on the InnoDB table?