I can run
SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
and see anything that is currently blocked and what is blocking it. However, my blocks are too sparse to reliably catch them.
It seems that there should be some way to persistently capture the blocks, so if it happens on Saturday night I can go back and see it on Monday morning. However, I have yet to find a solution (perhaps my Google Foo is FooBarred).
- Is there a simple way to log this?
- Is there a trigger to write the information to another (persistent) table?
- Is there Do I need a script that constantly runs and logs the results?
- Is there a tool that just handles this?