Monitoring InnoDB Lock contention

When you use InnoDB, or any other RDBMS or storage engine that supports row level locking and transactions, you get to realize a few things:

• The chance of lock contention is less, as the lock is much more granual that page or table level locking.
• On the other hand, when you get into lock contention, maybe because you have structured your data in such a way so there may well be as many locks as before (for example if one small table with very few rows is updated frequently by many threads), then the effect is worse.
• With transactions, which are a good thing by the way, I'm not promoting not transactions datastores here and assuming auto commit isn't used, then locks are held longer, and as each transaction may well hold several locks, because of multiple rows being updated or multiple statements are part of one transaction for example.
• With InnoDB then, fining the root cause when you end up with lock contention, with several transactions locking each other out in a complex manner, is HARD!

Luckily, finding locks and waiters for them, and the relationship between these, is A LOT easier when you use the InnoDB Plugin instead of the builtin InnoDB (are you still using the built-in? Dont. Just don't! The plugin has better performance, is easier to monitor, has online-operations and is GA, even in 5.1, you just enable it with a few settings and you are ready to go).

With the InnoDB Plugin and the InnoDB Plugin Information schema tables installed, the latter by installing the plugins using the plugin_load my.ini setting, for example:

plugin_load="innodb=ha_innodb_plugin.dll;innodb_trx=ha_innodb_plugin.dll;innodb_locks=ha_innodb_plugin.dll;innodb_lock_waits=ha_innodb_plugin.dll"

we are ready to go.

There are 3 new tables in the INFORMATION_SCHEMA schema now, one for each object we need for this exercise:

• INNODB_TRX - This keeps track of the InnoDB transactions. Each transaction may have no, one or more locks and may also be waiting on locks (locks I say? What? More about that later)
• INNODB_LOCKS - This keeps track of all locks for all transactions. Locks are unique for transactions, so if two transactions are waiting for one lock, we will see a total of three locks, one holding the lock, and two other locks, for the same row, waiting for it.
• INNODB_LOCK_WAITS - This table has one row for each lock that is waiting for another lock.

In InnoDB, only locks that are actually interesting are show in these tables, so if a transactions holds a lock that noone is waiting for, this lock will not show up.

The are some intersting things to note about these tables. If we have 3 transactions:

• A holds a lock on a row
• B is waiting for the lock A holds.
• C is waiting for the same lock that A holds and that B is waiting for.

We will see 3 locks! Why? A holds a lock, right, so that is fine. B holds a lock and is waiting for A, so that is fine too. But does C hold a lock? Yes, it holds a lock and s waiting for the one that B holds. I told you that locks, as seen in this table, are unqiue to each transaction.
If we now assume that A just holds the lock, and isnät waiting on anything, we have how many waiters? Well, we have 2 waiting transactions, but 3 waiters! Transaction C, to complete, needs both the lock that A holds AND the lock that B holds (as we saw above, just because B is waiting for a lock on a row, doesn't maen it can also HOLD a row on that row. A different loc, but the same row). B waits for A and C waits for A and B, whch menas we have a total of three waiters! But if you look at the individual transactions, as represented by the requesting_trx_id column in the INNODB_LOCK_WAITS table, we see only two unique transactions, which is right, as although we have 3 lock waiting, only 2 transactions are actually blocked (B and C). And if we look at the column blocking_trx_id column, then we again see only 2 unique transactions blocking something, A and B this time, as C isn't really blocking something!
The actual data may look something like this, in this example:
SELECT * FROM innodb_locks;

lock_id    lock_trx_id lock_mode lock_type lock_table  lock_index lock_space lock_page lock_rec lock_data
B87:0:52:6 B87         X         RECORD    `test`.`t1` `PRIMARY`  0          52        6        5
B86:0:52:6 B86         X         RECORD    `test`.`t1` `PRIMARY`  0          52        6        5
B85:0:52:6 B85         X         RECORD    `test`.`t1` `PRIMARY`  0          52        6        5

And the the waiters:SELECT * FROM innodb_lock_waits;

requesting_trx_id requested_lock_id blocking_trx_id blocking_lock_id
B87               B87:0:52:6        B86             B86:0:52:6
B87               B87:0:52:6        B85             B85:0:52:6
B86               B86:0:52:6        B85             B85:0:52:6

One interesting column that has not been discussed so far is the column lock_data in the INNODB_LOCKS table. This shows the value that is being locked, in this case I have an integer column with the value 5 (and this is the PRIMARY KEY also, as you can see from the lock_index column).
Does this help? Yes, it is much better than using the built-in InnoDB, but there is still some way to go. A simple SELECT from these tables will be a good help, but as this data is actually in a hierarchy, it would be real nice to see this as a lock graph? Right? Well that is something I am working on for MyQuery 3.3, which is due soon. It will look something like this:
This is the same lock tree as in the example above, and how it will look, but there is something that is not right here, right? The C lock is just shown as waiting on A not B? Well, for the sake of clarity, I have cleaned that up in the GUI itself, so only relevant locks and waits are really shown.

/Karlsson