How to see a list of Locks from a MySql Query?

Question

I have a very large sql query. How do I see a list of all the locks that it sets during execution in MySQL/MariaSQL InnoDB?

score 0 · Answer 1 · answered Apr 09 '18 at 19:44

0

There are some INFORMATION_SCHEMA tables for this:

In general, you can't get a list of locks, unless there's a lock-wait in progress.

That is, transaction A can hold some number of locks, but unless some transaction B is stuck waiting for it, it doesn't show up in the INFORMATION_SCHEMA.

See also How do I find which transaction is causing a "Waiting for table metadata lock" state?

answered Apr 09 '18 at 19:44

Bill Karwin

538,548
86
673
828

So in other words, this can only display the looked-for results if you queried these tables at the exact right time-spot during the execution of your analyzing query? – Florian Mertens Apr 17 '18 at 06:29
Hmmmm.... Can this be used in a more useful way for queries running under 0.1sec? Can somehow the progression/changes of this data be shown? – Florian Mertens Apr 18 '18 at 13:25
I don't know any way to do so. – Bill Karwin Apr 18 '18 at 14:42

How to see a list of Locks from a MySql Query?

1 Answers1