Querying for InnoDB Lock contention

In the previous post, I showed how the "virtual tables" that the InnoDB Plugin use for finding lock contention works, and what they look like. I also showed what an InnoDB lock graph looks will look like when monitored from MyQuery 3.3, which is not yet, but soon, released.

So, we are looking at three tables, the transaction table, where each transaction has no, one or more locks. For our intents and purposes, we will disregard transactions without locks, so we can join transactions table to the locks table. Then we need to link up the locks table to the lock waiters, to see if the lock in question is waiting on another lock, Each lock is either not waiting on one, or, as we have seen, more, locks, or it is not waiting at all. As a lock might not be waiting at all, we need an OUTER join here.

So the query I am using, the result of which is massaged a fair bit before I shown the lock tree, but this is the query I use to get the data:

SELECT t.trx_id, l.lock_id, w.blocking_lock_id, l.lock_table, l.lock_index,
t.trx_query, l.lock_data
  FROM information_schema.innodb_trx AS t
  JOIN information_schema.innodb_locks AS l ON t.trx_id = l.lock_trx_id
  LEFT OUTER JOIN information_schema.innodb_lock_waits AS w
  ON l.lock_id = w.requested_lock_id

/Karlsson