# Mysql – InnoDB MySQL table with a trigger that inserts into a MyISAM table

innodblockingmyisamMySQLtrigger

### 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?

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?
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.
{"extraction_info": {"found_math": true, "script_math_tex": 0, "script_math_asciimath": 0, "math_annotations": 0, "math_alttext": 0, "mathml": 0, "mathjax_tag": 0, "mathjax_inline_tex": 0, "mathjax_display_tex": 0, "mathjax_asciimath": 1, "img_math": 0, "codecogs_latex": 0, "wp_latex": 0, "mimetex.cgi": 0, "/images/math/codecogs": 0, "mathtex.cgi": 0, "katex": 0, "math-container": 0, "wp-katex-eq": 0, "align": 0, "equation": 0, "x-ck12": 0, "texerror": 0, "math_score": 0.16071614623069763, "perplexity": 2713.7333975876227}, "config": {"markdown_headings": true, "markdown_code": true, "boilerplate_config": {"ratio_threshold": 0.3, "absolute_threshold": 10, "end_threshold": 15, "enable": true}, "remove_buttons": true, "remove_image_figures": true, "remove_link_clusters": true, "table_config": {"min_rows": 2, "min_cols": 3, "format": "plain"}, "remove_chinese": true, "remove_edit_buttons": true, "extract_latex": true}, "warc_path": "s3://commoncrawl/crawl-data/CC-MAIN-2021-43/segments/1634323585215.14/warc/CC-MAIN-20211018221501-20211019011501-00107.warc.gz"}