MySQL: How to find rows locked by transaction

Question

Example:

START TRANSACTION;
UPDATE books SET printedCount=1000 WHERE id=5;

How can I find which rows of table books are locked by opened transactions (or ids, primary keys of these rows)? I want to track this information to prevent deadlocks in my high-load web application, maybe to delay some update queries if rows which can be affected by these queries are already locked.

Leastways

Answer 1

Starting with MySQL 8.0.1, this is instrumented in the performance_schema:

SELECT * from performance_schema.data_locks;

See https://dev.mysql.com/doc/refman/8.0/en/data-locks-table.html

Indium

Answer 2

SHOW ENGINE INNODB STATUS;

Unfortunately, there is no way to catch all deadlocks. Instead, run that periodically to see if there is a deadlock in progress; then work on that case.

Weider