After switching a virtual IP around with keepalived we experienced a locking issue. Some client process on the server had some locks which did not get released but we could not see which query caused it.

This broke our MySQL replication in this case, it was waiting on the locks to be released while executing the binlogs.

We located the locks with

mysql> show engine innodb status\G;

The specific transaction did not show any query details but did show us a whole list of locks.

mysql> show processlist\G;

Did show us all the processes but none with running queries which could explain the locks.

After a searching a bit I could confirm that thread-id in the transaction log was the same as the process id from the processlist. This process only showed “sleep” although it had locks in place and after killing it the problem was resolved.

In short: thread-id in a transaction matches a query in the processlist.