Wednesday, August 25, 2010

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


Peter said...

Good stuff! I've been looking for good information on monitoring lock graphs in MySQL for a while, and this and the previous post have been the most straight forward explanation I've found yet.

Thomas Christopher said...

Juggling money - do i might like that bills get paid each week? unit desires get priority, but long do the others need to be compelled to be compelled to wait? unit you getting charged late payday loans or order of payment fees as a results of your cherub failed to cowl all the expenses that pay period?