Enabling the InnoDB Lock Monitor with XtraDB does not add any extra logging information

[Elena Stepanova]

Initially reported as https://mariadb.atlassian.net/browse/MDEV-4302.
A slightly modified quote from the original description:

<quote>
If InnoDB Lock Monitor is enabled (i.e., CREATE TABLE innodb_lock_monitor (id int) ENGINE=InnoDB), no extra lock information is logged. It is the same output as SHOW ENGINE INNODB STATUS. This differs from the standard InnoDB Plugin, as it displays much more lock information.
</quote>

MTR test case:

--source include/have_innodb.inc

create table t (id int) engine=innodb;
insert into t (id) values (1),(2),(3),(4),(5);
start transaction;
select * from t for update;

--connect (con1,localhost,root,,)

query_vertical show engine innodb status;
create table innodb_lock_monitor (id int) engine=innodb;
query_vertical show engine innodb status;
drop table innodb_lock_monitor;

--connection default
drop table t;

# End of MTR test case

A fragment from the output on MySQL 5.5.30:

TRANSACTIONS
------------
Trx id counter 504
Purge done for trx's n:o < 0 undo n:o < 0
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 3, OS thread handle 0x7f9980386700, query id 18 localhost root
show engine innodb status
---TRANSACTION 502, ACTIVE 0 sec
2 lock struct(s), heap size 376, 6 row lock(s)
MySQL thread id 2, OS thread handle 0x7f99803c7700, query id 15 localhost root
TABLE LOCK table `test`.`t` trx id 502 lock mode IX
RECORD LOCKS space id 0 page no 306 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`t` trx id 502 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000200; asc ;;
 1: len 6; hex 000000000501; asc ;;
 2: len 7; hex 82000001330110; asc 3 ;;
 3: len 4; hex 80000001; asc ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000201; asc ;;
 1: len 6; hex 000000000501; asc ;;
 2: len 7; hex 8200000133011e; asc 3 ;;
 3: len 4; hex 80000002; asc ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000202; asc ;;
 1: len 6; hex 000000000501; asc ;;
 2: len 7; hex 8200000133012c; asc 3 ,;;
 3: len 4; hex 80000003; asc ;;

...

TRANSACTIONS section from Percona-Server 5.5.29:

------------
TRANSACTIONS
------------
Trx id counter 504
Purge done for trx's n:o < 0 undo n:o < 0
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 3, OS thread handle 0x7ff0b83c8700, query id 18 localhost root
show engine innodb status
---TRANSACTION 502, ACTIVE 0 sec
2 lock struct(s), heap size 376, 6 row lock(s)
MySQL thread id 2, OS thread handle 0x7ff0b8409700, query id 15 localhost root
TABLE LOCK table `test`.`t` trx id 502 lock mode IX
RECORD LOCKS space id 0 page no 307 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`t` trx id 502 lock_mode X
----------------------------
END OF INNODB MONITOR OUTPUT
============================

[Alexey Kopytov (akopytov)]

As documented in http://www.percona.com/doc/percona-server/5.5/diagnostics/innodb_show_status.html?id=percona-server:features:innodb_show_status&redirect=1#innodb_show_verbose_locks the default XtraDB behavior is to not print verbose record lock details in SHOW ENGINE INNODB STATUS / lock monitor.

Setting innodb_show_verbose_locks restores the InnoDB behavior.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1342