Posts Tagged ‘INNODB_TRX’

InnoDB Plugin Version History

Thursday, December 1st, 2011

I’m often wondering what version of the InnoDB Plugin is included with which version of MySQL (or MariaDB). The MySQL changelogs used to denote which version of the InnoDB plugin was included with that particular release of MySQL, but sadly this is no longer the case.

Therefore I’ve compiled a comprehensive list which contains all of this info, and then some (and note all InnoDB Plugin changelog links are provided at the bottom).

Hope you find it helpful :)

MySQL 5.6:

MySQL     Plugin     Status Date
5.6.5 1.2.5 Milestone 8 04/10/2011
5.6.4 1.2.4 Milestone 7 12/20/2011
5.6.3 1.2.3 Milestone 6 10/03/2011
5.6.2 1.2.2 Milestone 5       04/11/2011

MySQL 5.5:

MySQL     Plugin     Status Date
5.5.24 1.1.8 GA 05/07/2012
5.5.23 1.1.8 GA 04/12/2012
5.5.22 1.1.8 GA 03/21/2012
5.5.21 1.1.8 GA 02/17/2012
5.5.20 1.1.8 GA 01/10/2012
5.5.19 1.1.8 GA 12/08/2011
5.5.18 1.1.8 GA 11/16/2011
5.5.17 1.1.8 GA 10/19/2011
5.5.16 1.1.8 GA 09/15/2011
5.5.15 1.1.8 GA 07/28/2011
5.5.14 1.1.8 GA 07/05/2011
5.5.13 1.1.7 GA 05/31/2011
5.5.12 1.1.6 GA 05/05/2011
5.5.11 1.1.6 GA 04/07/2011
5.5.10 1.1.5 GA 03/15/2011
5.5.9 1.1.5 GA 02/07/2011
5.5.8 1.1.4 GA 12/03/2010
5.5.7 1.1.3 GA 10/14/2010
5.5.6 1.1.2 GA 09/13/2010
5.5.5 1.1.1 Early Adopter    06/06/2010 *3
5.5.4 1.1.0 Beta 04/09/2010 *2
5.5.3 1.0.6 RC 03/24/2010
5.5.2 1.0.6 RC 02/12/2010
5.5.1 1.0.6 RC 01/04/2010
5.5.0 1.0.5 RC 12/07/2009 *1

*1: The InnoDB Plugin is included in MySQL 5.5 releases as the built-in version of InnoDB.
*2: InnoDB has been upgraded to version 1.1. This version is considered of Beta quality.
*3: InnoDB has been upgraded to version 1.1.1. This version is considered of “early adopter” quality.

MySQL 5.1:

MySQL     Plugin     Status     Date
5.1.62 1.0.17 GA 03/21/2012
5.1.61 1.0.17 GA 01/10/2012
5.1.60 1.0.17 GA 11/16/2011
5.1.59 1.0.17 GA 09/15/2011
5.1.58 1.0.17 GA 07/05/2011
5.1.57 1.0.16 GA 05/05/2011
5.1.56 1.0.15 GA 03/01/2011
5.1.55 1.0.15 GA 02/07/2011
5.1.54 1.0.14 GA 11/26/2010
5.1.53 1.0.13 GA 11/03/2010
5.1.52 1.0.13 GA 10/11/2010
5.1.51 1.0.12 GA 09/10/2010
5.1.50 1.0.11 GA 08/03/2010
5.1.49 1.0.10 GA 07/09/2010
5.1.48 1.0.9 GA 06/02/2010
5.1.47 1.0.8 GA 05/06/2010
5.1.46 1.0.7 GA 04/06/2010
5.1.45 1.0.6 RC 03/01/2010
5.1.44 1.0.6 RC 02/04/2010
5.1.43 1.0.6 RC 01/15/2010
5.1.42 1.0.6 RC 12/15/2009
5.1.41 1.0.5 RC 11/05/2009 *4
5.1.40 1.0.4 Beta 10/06/2009
5.1.39 1.0.4 Beta 09/04/2009
5.1.38 1.0.4 Beta 09/01/2009 *3
x.x.xx 1.0.3 Alpha 03/11/2009
x.x.xx 1.0.2 Alpha 12/01/2008
x.x.xx 1.0.1 Alpha 05/08/2008 *2
x.x.xx 1.0.0 Alpha 04/15/2008 *1

*1: The initial release of the InnoDB Plugin is based on the built-in InnoDB in MySQL version 5.1.
*2: These 4 versions (1.0.0 – 1.0.3) were only available as separate downloads.
*3: 1st Release of Plugin (see *1* below for more details)
*4: InnoDB Plugin has been upgraded to version 1.0.5. This version is considered of Release Candidate (RC) quality.

MariaDB 5.5:

MariaDB     Plugin     Status    Date            Comments
5.5.24 1.1.8-24.1 GA 05/24/12 XtraDB+
5.5.24 1.1.8 GA 05/24/12 InnoDB
5.5.23 1.1.8-24.1 GA 04/11/12 XtraDB+
5.5.23 1.1.8 GA 04/11/12 InnoDB
5.5.22 1.1.8-24.1 RC 03/29/12 XtraDB+
5.5.22 1.1.8 RC 03/29/12 InnoDB
5.5.21 1.1.8-24.1 Beta 03/16/12 XtraDB+
5.5.21 1.1.8 Beta 03/16/12 InnoDB
5.5.20 1.1.8-24.1     Alpha 02/26/12 XtraDB+
5.5.20 1.1.8 Alpha 02/26/12 InnoDB

MariaDB 5.3:

MariaDB     Plugin     Status    Date            Comments
5.3.7 1.0.17-13.0 GA 05/04/12 XtraDB+
5.3.7 1.0.17 GA 05/04/12 InnoDB
5.3.6 1.0.17-13.0 GA 04/09/12 XtraDB+
5.3.6 1.0.17 GA 04/09/12 InnoDB
5.3.5 1.0.17-13.0 GA 02/29/12 XtraDB+
5.3.5 1.0.17 GA 02/29/12 InnoDB
5.3.4 1.0.17-13.0 GA 02/15/12 XtraDB+
5.3.4 1.0.17 GA 02/15/12 InnoDB
5.3.3 1.0.17-13.0 GA 12/21/11 XtraDB+
5.3.3 1.0.17 GA 12/21/11 InnoDB
5.3.2 1.0.15-12.7     GA 10/14/11 XtraDB+
5.3.2 1.0.16 GA 10/14/11 InnoDB

MariaDB 5.2:

MariaDB     Plugin     Status    Date            Comments
5.2.12 1.0.17-13.0 GA 04/06/12 XtraDB+
5.2.12 1.0.17 GA 04/06/12 InnoDB
5.2.11 1.0.17-13.0 GA 04/02/12 XtraDB+
5.2.11 1.0.17 GA 04/02/12 InnoDB
5.2.10 1.0.17-13.0 GA 12/05/11 XtraDB+
5.2.10 1.0.17 GA 12/05/11 InnoDB
5.2.9 1.0.15-12.7     GA 09/22/11 XtraDB+
5.2.9 1.0.16 GA 09/22/11 InnoDB

MariaDB 5.1:

MariaDB     Plugin     Status    Date            Comments
5.1.62 1.0.17-13.0 GA 04/06/12 XtraDB+
5.1.62 1.0.17 GA 04/06/12 InnoDB
5.1.61 1.0.17-13.0 GA 04/02/12 XtraDB+
5.1.61 1.0.17 GA 04/02/12 InnoDB
5.1.60 1.0.17-13.0 GA 12/05/11 XtraDB+
5.1.60 1.0.17 GA 12/05/11 InnoDB
5.1.55 1.0.15-12.5     GA 03/01/11 XtraDB+ *1
5.1.55 1.0.15 GA 03/01/11 InnoDB

*1: XtraDB+ differs some from XtraDB as it contains even further enhancements on top of XtraDB. If you use the default (i.e., do not enable the InnoDB plugin in the my.cnf file), then you will be using XtraDB+. If you enable the InnoDB Plugin in the my.cnf file, then you are enabling the standard InnoDB Plugin. Please see the following 2 links for more details on these differences:

http://kb.askmonty.org/en/mariadb-53-asynchronous-io-on-windows-with-innodb (work by Wlad)
http://www.facebook.com/note.php?note_id=10150211546215933 (work by KNielsen)

There are even some more optimizer additions, but no graphs yet to report.

 
Changelogs:

Note: The plugin versions had their own changelogs initially, but eventually the changes were just mixed into the general changelogs for the MySQL Server.

Note: Also, some plugin versions span multiple MySQL versions, so those have multiple links below.

Plugin 1.1:

1.1.0	http://dev.mysql.com/doc/innodb/1.1/en/innodb-changes-11.html
1.1.1	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-5.html
1.1.2	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-6.html
1.1.3	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-7.html
1.1.4	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-8.html
1.1.5	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-9.html
	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-10.html
1.1.6	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-11.html
	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-12.html
1.1.7	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-13.html
1.1.8	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-14.html
	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-15.html
	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-16.html
	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-17.html
	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-18.html
	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-19.html

Plugin 1.0:

1.0.0	http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-changes-100.html
1.0.1	http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-changes-101.html
1.0.2	http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-changes-102.html
1.0.3	http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-changes-103.html
1.0.4	http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-changes-104.html
1.0.5	http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-changes-105.html
1.0.6	http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-changes-106.html
1.0.7	http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-changes-107.html
1.0.8	http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-changes-108.html
1.0.9	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-48.html
1.0.10	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-49.html
1.0.11	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-50.html
1.0.12	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-51.html
1.0.13	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-52.html
	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-53.html
1.0.14	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-54.html
1.0.15	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-55.html
	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-56.html
1.0.16	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-57.html
1.0.17	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-58.html
	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-59.html
	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-60.html

Misc. Notes:

5.1.38 is first release of the plugin

http://dev.mysql.com/doc/refman/5.1/en/news-5-1-38.html

And for reference, here is the text from that announcement regarding the InnoDB Plugin:

C.1.1. Changes in MySQL 5.1.38

  As of MySQL 5.1.38, the InnoDB Plugin is included in MySQL
  releases, in addition to the built-in version of InnoDB that
  has been included in previous releases. This version of the
  InnoDB Plugin is 1.0.4 and is considered of Beta quality.

  The InnoDB Plugin offers new features, improved performance
  and scalability, enhanced reliability and new capabilities
  for flexibility and ease of use. Among the features of the
  InnoDB Plugin are "Fast index creation," table and index
  compression, file format management, new INFORMATION_SCHEMA
  tables, capacity tuning, multiple background I/O threads, and
  group commit.

  For information about these features, see the InnoDB Plugin
  Manual at

http://www.innodb.com/products/innodb_plugin/plugin-documentation

  For general information about using InnoDB in MySQL,
  see Section 13.6, "The InnoDB Storage Engine."

  The InnoDB Plugin is included in source and binary
  distributions, except RHEL3, RHEL4, SuSE 9 (x86, x86_64,
  ia64), and generic Linux RPM packages.

  To use the InnoDB Plugin, you must disable the built-in
  version of InnoDB that is also included and instruct the
  server to use InnoDB Plugin instead. To accomplish this, use
  the following lines in your my.cnf file:

    [mysqld]
    ignore-builtin-innodb
    plugin-load=innodb=ha_innodb_plugin.so

  For the plugin-load option, innodb is the name to associate
  with the plugin and ha_innodb_plugin.so is the name of the
  shared object library that contains the plugin code. The
  extension of .so applies for Unix (and similar) systems. For
  HP-UX on HPPA (11.11) or Windows, the extension should be .sl
  or .dll, respectively, rather than .so.

  If the server has problems finding the plugin when it starts
  up, specify the pathname to the plugin directory. For
  example, if plugins are located in the lib/mysql/plugin
  directory under the MySQL installation directory and you have
  installed MySQL at /usr/local/mysql, use these lines in your
  my.cnf file:

    [mysqld]
    ignore-builtin-innodb
    plugin-load=innodb=ha_innodb_plugin.so
    plugin_dir=/usr/local/mysql/lib/mysql/plugin

  The previous examples show how to activate the storage engine
  part of InnoDB Plugin, but the plugin also implements several
  InnoDB-related INFORMATION_SCHEMA tables. (For information
  about these tables, see

http://www.innodb.com/doc/innodb_plugin-1.0/innodb-information-schema.html)

  To enable these tables, include additional name=library
  pairs to the plugin-load option:

    [mysqld]
    ignore-builtin-innodb
    plugin-load=innodb=ha_innodb_plugin.so
      ;innodb_trx=ha_innodb_plugin.so
      ;innodb_locks=ha_innodb_plugin.so
      ;innodb_cmp=ha_innodb_plugin.so
      ;innodb_cmp_reset=ha_innodb_plugin.so
      ;innodb_cmpmem=ha_innodb_plugin.so
      ;innodb_cmpmem_reset=ha_innodb_plugin.so

  The plugin-load option here is formatted on multiple lines
  for display purposes but should be written in my.cnf using a
  single line without spaces in the option value. On Windows,
  substitute .dll for each instance of the .so extension.

  After the server starts up, verify that InnoDB Plugin has
  been loaded by using the SHOW PLUGINS statement. For example,
  if you have loaded the storage engine and the
  INFORMATION_SCHEMA tables, the output should include lines
  similar to these:

    mysql> SHOW PLUGINS;
    +---------------------+--------+--------------------+---------------------...
    | Name                | Status | Type               | Library             ...
    +---------------------+--------+--------------------+---------------------...
    | InnoDB              | ACTIVE | STORAGE ENGINE     | ha_innodb_plugin.so ...
    | INNODB_TRX          | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so ...
    | INNODB_LOCKS        | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so ...
    | INNODB_CMP          | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so ...
    | INNODB_CMP_RESET    | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so ...
    | INNODB_CMPMEM       | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so ...
    | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so ...
    +---------------------+--------+--------------------+---------------------...

  If you build MySQL from a source distribution, InnoDB Plugin
  is one of the storage engines that is built by default. Build
  MySQL the way you normally do; for example, by using the
  instructions at Section 2.10, "MySQL Installation Using a
  Source Distribution." After the build completes, you should
  find the plugin shared object file under the
  storage/innodb_plugin directory, and make install should
  install it in the plugin directory. Configure MySQL to use
  InnoDB Plugin as described earlier for binary distributions.

Misc Links:

http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-plugin-introduction.html
http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-changes.html
http://dev.mysql.com/doc/refman/5.5/en/innodb-5-5.html
http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-plugin-installation.html

 
 

Advanced InnoDB Deadlock Troubleshooting – What SHOW INNODB STATUS Doesn’t Tell You, and What Diagnostics You Should be Looking At

Thursday, November 10th, 2011

One common cause for deadlocks when using InnoDB tables is from the existence of foreign key constraints and the shared locks (S-lock) they acquire on referenced rows.

The reason I want to discuss them though is because they are often a bit tricky to diagnose, especially if you are only looking at the SHOW ENGINE INNODB STATUS output (which might be a bit counter-intuitive since one would expect it to contain this info).

Let me show a deadlock error to illustrate (below is from SHOW ENGINE INNODB STATUS\g):

------------------------
LATEST DETECTED DEADLOCK
------------------------
111109 20:10:03
*** (1) TRANSACTION:
TRANSACTION 65839, ACTIVE 19 sec, OS thread id 4264 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1024, 3 row lock(s), undo log entries 1
MySQL thread id 3, query id 74 localhost 127.0.0.1 root Updating
UPDATE parent SET age=age+1 WHERE id=1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6833 page no 3 n bits 72 index `PRIMARY` of table
`test`.`parent` trx id 65839 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

*** (2) TRANSACTION:
TRANSACTION 65838, ACTIVE 26 sec, OS thread id 768 starting index read,
thread declared inside InnoDB 500
mysql tables in use 1, locked 1
7 lock struct(s), heap size 1024, 4 row lock(s), undo log entries 2
MySQL thread id 4, query id 75 localhost 127.0.0.1 root Updating
UPDATE parent SET age=age+1 WHERE id=2
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6833 page no 3 n bits 72 index `PRIMARY` of table
`test`.`parent` trx id 65838 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6833 page no 3 n bits 72 index `PRIMARY` of table
`test`.`parent` trx id 65838 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

*** WE ROLL BACK TRANSACTION (1)

Now, we do see a lot about what caused the deadlock above, but we are only seeing *half* of the picture.

Allow me to explain ..

First of all, note transaction #1 has been running for 19 seconds, while transaction #2 for 26 seconds. So, the output is referring to the newer transaction as #1 and the older as #2 (also somewhat counter-intuitive, but good to be aware of).

Now, what we can see clearly is this:

Transaction #1 (“UPDATE parent .. WHERE id=1″) is waiting on a lock from Transaction #2 (“UPDATE parent .. WHERE id=2″).

Thus TX #2 holds a lock (RECORD LOCKS space id 6833 page no 3 n bits 72 index, heap no 2), but is waiting on (RECORD LOCKS space id 6833 page no 3 n bits 72 index, heap no 3 – held by TX #1).

Clearly the 2 updates should not cause a conflict in and of themselves. Thus we know something must have happened earlier in the transaction(s).

For reference, here is how to reproduce it:

CREATE TABLE `parent` (
`id` INT NOT NULL AUTO_INCREMENT,
`age` INT NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `child` (
`id` INT NOT NULL AUTO_INCREMENT,
`age` INT NOT NULL,
`parent_id` INT NOT NULL,
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
CONSTRAINT `fk_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
) ENGINE=InnoDB;

INSERT INTO parent (id, age) VALUES (1, 50);
INSERT INTO parent (id, age) VALUES (2, 60);
INSERT INTO child (id, age, parent_id) VALUES (1, 20, 1);
INSERT INTO child (id, age, parent_id) VALUES (2, 20, 1);

Then, open 2 connections (T1 and T2 – note order is opposite compared to what is shown in SHOW INNODB STATUS):

T1:

BEGIN;
UPDATE child SET age=age+1, parent_id=2 WHERE id=1;
UPDATE parent SET age=age+1 WHERE id=1;

T2:

BEGIN;
UPDATE child SET age=age+1, parent_id=2 WHERE id=2;
UPDATE parent SET age=age+1 WHERE id=1;

<-- T2 Hangs

T1:

UPDATE parent SET age=age+1 WHERE id=2;

<-- Deadlock (T1 completes, T2 is rolled back)

But why does this deadlock? Well, it is due to the foreign key. In fact, this example would not deadlock at all if no foreign key was defined on `parent`.`id`.

So then what exactly is preventing T2 from completing?

Here is what is happening behind the scenes, so-to-speak:

T1#1:

Obtains the following 2 locks:

X lock on `child`  where id=1 <-- due to the actual "UPDATE child" statement itself
S lock on `parent` where id=2 <-- due to the FK on parent.id

(Note this S lock means other S locks can be obtained on this row, but not X locks -- which is the crux of this issue).

T1#2:

Obtains the following lock:

X lock on `parent` where id=1 <-- due to the actual "UPDATE parent" statement itself

T2#1:

Obtains the following 2 locks:

X lock on `child`  where id=2 <-- due to the actual "UPDATE child" statement itself
S lock on `parent` where id=2 <-- due to the FK (again, this is okay since it is also a S-lock)

T2#2:

Tries to obtain the following, but hangs due to the existing X-lock from T1#2:

X lock on parent where id=1

T1#3:

Tries to obtain the following lock:

X lock on parent where id=2

However, since there are 2 S-locks on this row already (one from T1 and T2), and T1 now wants an X-lock on the same row, then there is a conflict.

Now this would normally just wait for the S-locks to be released, but since T2 is already "hung" waiting on the other lock to be released from T1, we now have the deadlock.

T1 wins the dispute, T2 rolls back thus releasing its locks, and T1 completes.

So all in all, quite a bit is going on there, but you only see about half of this information from the LATEST DETECTED DEADLOCK section of SHOW ENGINE INNODB STATUS output. And had I not posted the SHOW CREATE TABLE status (and prior tx statements), it'd be unclear as to what happened exactly.

Well, so how do you find out exactly what happened when locking problems happen to you?

SHOW INNODB STATUS only tells you so much. Furthermore, once the deadlock occurs, the winner moves on, and the loser is rolled back. *Meaning*, there is no longer any information about these "transactions" in the output as they are in the "past" now.

Therefore, in general, if you're having any locking issues (deadlocks, lock wait timeouts, hangs due to semaphore waits, and so forth), do yourself a favor and capture all of the following outputs at the time, if possible, to give you the best likelihood in tracking down the issue:

  1. SHOW ENGINE INNODB STATUS
    • This is generally very good, but it can get truncated, and simply may not contain every bit of info you need.
  2. Enable InnoDB Lock Monitor (enable the InnODb Lock Monitor by simply creating any innodb table named innodb_lock_monitor)
    • This logs a lot of extra lock information in the SHOW ENGINE INNODB STATUS output, but it can get truncated too.
  3. Run "mysqladmin debug"
    • Logs all lock info to the error log. Great because it logs all locks (i.e., none truncated) and it logs LOCK TABLE locks, which do not appear in SHOW INNODB STATUS even if on an InnoDB table, because LOCK TABLE is external to the InnoDB storage engine. Not so great because a bit cryptic to read, and I wouldn't solely reply on it, as it's often most helpful in conjuntion with other details).
  4. SHOW FULL PROCESSLIST
    • This will show all connected threads. Specifically, when it comes to 'hidden' locks, it would show a user that has been connected, but idle (but who could have issued a LOCK TABLE command).
  5. Error log
    • Of course, always check out the error log for messages and/or anything out of the ordinary. (Not to mention extra data will be logged to it from "mysqladmin debug" and innodb_lock_monitor.)
  6. SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS, INNODB_LOCK_WAITS, and INNODB_TRX tables
  7. SHOW CREATE TABLE outputs for each table involved

 
 
 

Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

Tuesday, June 29th, 2010

Tracking down InnoDB lock information using the new Information Schema tables provided with the InnoDB plugin has never been easier.

Long story short, the other day I was trying to identify what transaction was holding the lock for a particular UPDATE. The UPDATE would not complete and kept timing out with “Lock wait timeout exceeded; try restarting transaction”.

Of course I checked the output of SHOW ENGINE INNODB STATUS. From that output, I could tell it was the replication thread holding the lock, *since* it was the only other transaction running. But I could not verify this with output, it just had to be the case.

Next I enabled the InnoDB lock monitor, and examined that output too. Here, it showed more detail on the locks being held, however, it only shows the first 10 locks held by the replication thread. In this case, the replication thread was holding more than 2000 record locks from a large, open transaction. Needless to say, the lock I was looking for was not amongst the 10 displayed.

So I enabled InnoDB plugin (just added the following 2 lines to my config file and restarted mysqld):

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

After the above, you’ll notice the following new tables in Information Schema:

mysql> SHOW TABLES IN INFORMATION_SCHEMA LIKE 'INNODB_%';
+-----------------------------------------+
| Tables_in_information_schema (INNODB_%) |
+-----------------------------------------+
| INNODB_CMP_RESET                        |
| INNODB_TRX                              |
| INNODB_CMPMEM_RESET                     |
| INNODB_LOCK_WAITS                       |
| INNODB_CMPMEM                           |
| INNODB_CMP                              |
| INNODB_LOCKS                            |
+-----------------------------------------+
7 rows in set (0.00 sec)

For this particular problem, I wanted to see what was in INNODB_TRX, INNODB_LOCKS, and INNODB_LOCK_WAITS:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
+--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-----------+
| trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query |
+--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-----------+
| 124616 | LOCK WAIT | 2010-06-23 13:51:52 | 124616:1301:1572:6 | 2010-06-23 13:51:52 | 2 | 496854 | NULL |
| 123900 | RUNNING | 2010-06-23 09:30:59 | NULL | NULL | 506 | 3 | NULL |
+--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-----------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+--------------------+-------------+-----------+-----------+-------------------------+------------+------------+-----------+----------+-----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+--------------------+-------------+-----------+-----------+-------------------------+------------+------------+-----------+----------+-----------+
| 124616:1301:1572:6 | 124616 | X | RECORD | `db1`.`t1` | `PRIMARY` | 1301 | 1572 | 6 | 4100 |
| 123900:1301:1572:6 | 123900 | S | RECORD | `db1`.`t1` | `PRIMARY` | 1301 | 1572 | 6 | 4100 |
+--------------------+-------------+-----------+-----------+-------------------------+------------+------------+-----------+----------+-----------+
2 rows in set (0.01 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
+-------------------+--------------------+-----------------+--------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+--------------------+-----------------+--------------------+
| 124616 | 124616:1301:1572:6 | 123900 | 123900:1301:1572:6 |
+-------------------+--------------------+-----------------+--------------------+
1 row in set (0.00 sec)

Finally, I had conclusive evidence that it was indeed the replication thread (trx id 123900) holding the lock which was blocking the UPDATE.

I also found the exact row which was being held, and all of the details, such as it was a S (shared) record lock, and the the lock was on the primary key of table db1.t1. It even tells you the lock_space (1301), the lock_page (1572), the lock_rec (6) , and the actual data, lock_data (4100).

The other key piece was the time (or trx_started) for the running transaction, 123900. With that, we knew exactly where to check the binlogs for the exact statement where this all began.

In conclusion, I wish I had started here to begin with, so keep this in mind for the future. And this is just one example of how beneficial these new InnoDB Plugin Information Schema tables can be for troubleshooting. You can find even more details/examples at the following location:

http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-information-schema-examples.html


Click here to give this article a “Thumbs Up” on Planet MySQL !

  


Period Panties by Period Panteez Menstrual Underwear Menstruation PMS Panty