Bug #33601 adding a view for information_schema to identify lock waiting relations
Submitted: 31 Dec 2007 17:30 Modified: 3 Nov 2009 6:23
Reporter: Yasufumi Kinoshita Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5.1 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: Contribution

[31 Dec 2007 17:30] Yasufumi Kinoshita
Description:
Currently, if we want to know actually waiting relations among sessions using InnoDB, we must guess from only SHOW INNODB STATUS command.

This view shows the sessions waiting a lock and which sessions have or wait the lock in the forward of the queue.

I think it is useful for analyzing performance problems

How to repeat:
nothing

Suggested fix:
for example (using the following file : innodb_lock_wait_plugin.tar.gz)

mysql> INSTALL PLUGIN innodb_lock_wait SONAME 'innodb_lock_wait.so';
Query OK, 0 rows affected (0.01 sec)

mysql> select * from information_schema.innodb_lock_wait order by ID;
+----+-------+-------+------+---------------+---------+----+-------+----+------+-----+--------+--------+-------+
| ID | TRX_H | TRX_L | SECS | TABLE         | INDEX   | SP | PG    | BT | MODE | WID | WTRX_H | WTRX_L | WMODE |
+----+-------+-------+------+---------------+---------+----+-------+----+------+-----+--------+--------+-------+
|  4 |     0 | 49862 |    0 | tpcc/district | PRIMARY |  0 | 11557 | 18 | S--W |  13 |      0 |  49800 | X---  |
|  5 |     0 | 49629 |    0 | tpcc/district | PRIMARY |  0 | 11557 |  2 | S--W |   8 |      0 |  49656 | X---  |
|  9 |     0 | 49899 |    0 | tpcc/district | PRIMARY |  0 | 11557 | 18 | X--W |  13 |      0 |  49800 | X---  |
|  9 |     0 | 49899 |    0 | tpcc/district | PRIMARY |  0 | 11557 | 18 | X--W |  14 |      0 |  49821 | S--W  |
|  9 |     0 | 49899 |    0 | tpcc/district | PRIMARY |  0 | 11557 | 18 | X--W |   4 |      0 |  49862 | S--W  |
| 10 |     0 | 49856 |    0 | tpcc/district | PRIMARY |  0 | 11557 |  7 | X--W |   2 |      0 |  49678 | X---  |
| 10 |     0 | 49856 |    0 | tpcc/district | PRIMARY |  0 | 11557 |  7 | X--W |  15 |      0 |  49714 | X--W  |
| 14 |     0 | 49821 |    0 | tpcc/district | PRIMARY |  0 | 11557 | 18 | S--W |  13 |      0 |  49800 | X---  |
| 15 |     0 | 49714 |    0 | tpcc/district | PRIMARY |  0 | 11557 |  7 | X--W |   2 |      0 |  49678 | X---  |
+----+-------+-------+------+---------------+---------+----+-------+----+------+-----+--------+--------+-------+
9 rows in set (0.00 sec)

This results shows the following relations,

page: 11557, bit:2 (row)
	session 8(have X) <-- session 5(want S)
page: 11557, bit:7 (row)
	session 2(have X) <-- session 15(want X) <-- session 10(want X)
page: 11557, bit:18 (row)
	session 13(have X) <-- session 4 & 14(want S) <-- session 9(want X)

ID: MySQL thread ID (=ID in PROCESSLIST)
TRX_H, TRX_L: transaction ID
SECS: waiting period
TABLE: database/table
INDEX: index
SP: space no.
PG: page no.
BT: bit no.
MODE: lock mode (lock type, gap lock, waiting etc...)
WID: MySQL thread ID waited for
WTRX_H, WTRX_L: transaction ID waited for
WMODE lock mode waited for
[31 Dec 2007 17:31] Yasufumi Kinoshita
example plug-in

Attachment: innodb_lock_wait_plugin.tar.gz (application/gzip, text), 3.57 KiB.

[2 Jan 2008 8:14] Valeriy Kravchuk
Thank you for a reasonable feature request and plugin uploaded.
[17 Jun 2009 20:50] Liz Drachnik
Hello Yasufumi

In order for us to continue the process of reviewing your  contribution to MySQL -  Please review and sign the Sun|MySQL contributor agreement (the "SCA")

The process is explained here: 
http://forge.mysql.com/wiki/Sun_Contributor_Agreement

Thanks!
[2 Oct 2009 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[4 Nov 2009 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".