0

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?

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.

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.