Best way to monitor InnoDB locks

MySQL & MariaDB Other MySQL® Questions
1

Hello.

Our app is written on Java, connected to MySQL using JBDC and iBatis does ORM for us.

Sometimes we have locks: “Lock wait timeout exceeded; try restarting transaction”.

We can restart transaction but we really want to know which object been locked by which transaction.

We can use “SHOW INNODB STATUS” is case of such errors but we do not want to reinvent the wheel.

Probably there is a way to configure MySQL to log that fact?

2

The data in “show (engine) innodb status” is your best bet. I would enable the innodb_monitor (CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB; in the mysql database). That will continually output the InnoDB status to your error log, so you can then go back and review it for the time period in question after the fact.

3

Thanks!

I’ve enabled innodb monitor and general log. Will try to investigate locks:)