I can run

  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?
  • Are you trying to catch deadlocks? And/or 'lock wait timeout'? And/or all the miscellaneous brief locks? – Rick James May 15 '18 at 17:11
  • Is the SlowLog turned on? – Rick James May 15 '18 at 17:11
  • All the miscellaneous brief locks. SlowLog is turned on. – bubba May 15 '18 at 19:44
  • Brief locks are not a concern -- it is when they escalate into a "slow query" that becomes an issue. Perhaps long_query_time should be low, such as 0.1. The log won't tell you about the locks; instead look for any queries that are sometimes slower than you would expect. – Rick James May 15 '18 at 20:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.