MySQL Bugs: #33601: adding a view for information_schema to identify lock waiting relations

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

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

example plug-in

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

Thank you for a reasonable feature request and plugin uploaded.

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!

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".

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".