MySQL Bugs: #29126: Add locks held, remove locked records in SHOW INNODB STATUS

Bug #29126	Add locks held, remove locked records in SHOW INNODB STATUS
Submitted:	15 Jun 2007 1:49	Modified:	4 Apr 2013 6:51
Reporter:	Baron Schwartz (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	All	OS:	Any
Assigned to:	Vasil Dimov	CPU Architecture:	Any
Tags:	Contribution, innodb, locks, qc

Description:
Two of the things that I'd love to see for InnoDB are the inability to know who is blocking a transaction waiting for locks, and the too-verbose output of SHOW INNODB STATUS when there has been a deadlock. Both affect my ability to monitor what's happening inside InnoDB. Just today I had an errant process and had to do a lot of work to find out which one it was -- all I could see was processes waiting for locks, not who was holding the locks.

The best way to do this would be to add the information to the INFORMATION_SCHEMA, I know. And I've been told by several people that patches for this actually exist, but aren't prioritized.

However, I think some simpler changes could help greatly. I have two suggested changes to remove some information, and add other information, to SHOW INNODB STATUS. Neither should require much code change, I think.

I understand that Heikki and others use this information daily to debug InnoDB while developing it. It should be easy to enable this only for debug builds, I think.

I'm no C++ expert, but I may try to do this myself and submit a patch.

How to repeat:
Feature request

Suggested fix:
The first thing is to include locks-held information in the transaction section of SHOW INNODB STATUS. This information is already output to the InnoDB Lock Monitor, but this is at best a difficult method of monitoring, since it goes to a file on the server.

This change should not be hard, since the code already exists.

The second change would be to stop including full information on locks in transaction printouts. This includes transactions printed out in the "last detected deadlock" section. This data can easily overflow the size allocated for the output, so important information is truncated. Instead, I would just include the table, index, lock mode and so forth.

Here's an example of the current output:

---TRANSACTION 0 248136273, ACTIVE 475 sec, process no 14747, OS thread id 14866
87120
2 lock struct(s), heap size 320, undo log entries 2
MySQL thread id 463546, query id 388841562 mor.office 192.168.0.12 robot
TABLE LOCK table `test/test` trx id 0 248136273 lock mode IX
RECORD LOCKS space id 0 page no 2457642 n bits 136 index `PRIMARY` of table `test/test` trx id 0 248136273 lock_mode X locks rec but not gap
Record lock, heap no 68 PHYSICAL RECORD: n_fields 34; compact format; info bits
0
0: len 2; hex 033f; asc ?;; 1: len 6; hex 0............

I would omit all the "Record lock" sections, and just include the lines up to the first "Record lock" section. I think this would be more than enough information for DBAs to manage what InnoDB is doing.

Thank you for a reasonable feature request.

patch: http://lists.mysql.com/internals/35174

More related feature requests:
* reorder the output so that the large and variable length sections (Transactions, ...) are printed last
* make the max size for SHOW INNODB STATUS output a configurable parameter if it must be fixed size

Hello Baron - 

Since you seem interested in contributing to MySQL, may I suggest that you sign the Sun|MySQL contributor agreement, i.e. the SCA.

The instructions are given here:
http://forge.mysql.com/wiki/Sun_Contributor_Agreement.

I will review the SCA submission and can turn around the approval very quickly (usually same day) 

This will facilitate the handling of your contributions -- this one, and others in the future.

Thanks! 

Liz Drachnik - MySQL Program Manager

The locked record information is how we work out why there is a conflict between the two transactions, so keeping it is useful sometimes. But it would be much more convenient to have only the relevant locks shown - the specific conflict and perhaps preceding and following locks taken.

Hello,

The first request - to be able to see which transaction is blocking a waiting transaction and what locks both hold has been implemented in 2007 in InnoDB INFORMATION_SCHEMA tables innodb_trx, innodb_locks, innodb_lock_waits. See http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-information-schema-examples.html for documentation and examples.

The second request - to skip "Record lock" and below in SHOW ENGINE INNODB STATUS because the output may be truncated, I think should not be fulfilled because changing SHOW ENGINE INNODB STATUS output will likely break existing apps that parse it. Furthermore, I do not think that InnoDB truncates that output to a fixed length. If, for some reason it gets truncated outside of InnoDB, then InnoDB can be instructed to print that to stderr too by enabling the InnoDB monitor (CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB). The output to stderr will never be truncated.

Thus closing this bug entry.

The function innodb_show_status() in ha_innodb.cc does truncate the output.