InnoDB Plugin (With some Windows focus) - Part 2

In the last post on this subject, I discussed the benefits of the InnoDB plugin for operational things, such as creating and dropping indexes, and how much faster and non-obtrusive on other operations this is with the plug-in version of InnoDB, compared to the built-in InnoDB.

This time, I will discuss another benefit of the plugin, which is the operational metadata views provided by the plug-in. A way overdue feature of InnoDB is the ability to inspect locks and lock waiters, to be able to effectively manage lock concurrency issues. In any database using even a moderately complex schema, and having a reasonable amount of writes, concurrenctý issues will often happen. And the issue is that if you cannot monitor them properly, then you may not know what is happening. Users have sessions waiting for locks, then tires of waiting and abandons the operations, sometimes leaving locks of their own around!

Another issue is that end-users cannot usually tell the differece between a straight performace problem, such as a too small cache, a slow disk or a bad network, and a concurrency issue, i.e. waiting for a lock. And the difference in how we find and fix these issues is distinctly different!

OK, so now let's have a look in practice on hwo this works. To begin with, we need to configure the InnoDB plugin monitoring INFORMATION_SCHEMA tables, and here I will install all of them, not only the ones related to locking. The setting needed in the [mysql] section in the my.ini file, to support the InnoDB plugin and all the corresponding monitoring tables are then:
ignore_builtin_innodb
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;innodb_cmp=ha_innodb_plugin.dll;innodb_cmp_reset=ha_innodb_plugin.dll;innodb_cmpmem=ha_innodb_plugin.dll;innodb_cmpmem_reset=ha_innodb_plugin.dll"

Note that the "plugin_load" parameter must be on a single line! Now, restart the MySQL server and we can start to have some fun!

First create an InnoDB table for our testing:
CREATE TABLE locktest (
c1 int(11) NOT NULL AUTO_INCREMENT,
c2 char(10) DEFAULT NULL,
PRIMARY KEY (c1),
KEY c2 (c2)
) ENGINE=InnoDB;

And insert some data:
INSERT INTO locktest VALUES(1, 1);
INSERT INTO locktest VALUES(2, 2);
INSERT INTO locktest VALUES(10, 16);
INSERT INTO locktest VALUES(11, 16);
INSERT INTO locktest VALUES(15, 18);

And we are set to do some testing. First, open two sessions on the database where the testing table was created, and then, in the first session, do a SELECT .. FOR UPDATE in a transaction:
BEGIN WORK;
SELECT * FROM locktest WHERE c1 > 8 FOR UPDATE;

Now, in the other session, lets try to update one of the locked rows:
UPDATE locktest SET c2 = 17 WHERE c1 = 11;

And then, in a third session, lets see what we have in the lock and lock_wait tables:
use information_schema
SELECT lock_mode, lock_type, lock_table, lock_data FROM innodb_locks;
+-----------+-----------+-------------------------+-----------+
| lock_mode | lock_type | lock_table | lock_data |
+-----------+-----------+-------------------------+-----------+
| X | RECORD | `plugintest`.`locktest` | 11 |
| X | RECORD | `plugintest`.`locktest` | 11 |
+-----------+-----------+-------------------------+-----------+
2 rows in set (0.00 sec)
and:
SELECT * FROM innodb_lock_waits;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 6007 | 6007:0:16396:12 | 6001 | 6001:0:16396:12 |
+-------------------+-------------------+-----------------+------------------+
1 row in set (0.00 sec)

As we can see, InnoDB will only show locks that are waited on, but that is cool. Look at the lock_data column in the innodb_locks table, you see the actual data being waied on there. The lock_mode column is X (the two rows are for the table and index respectively). In your case, you may see X,GAP for the index lock. That is because you have index gap locking on, or in other words, you have not set innodb_locks_unsafe_for_binlog.

All in all, tracking down concurrency issues with these tables is A LOT easier than the old "show innodb status" way of dealing with things.

/Karlsson