MySQL Bugs: #61670: Locked Transaction does not appear in I_S tables of InnoDB (5.1 plugin and 5.5)

Bug #61670	Locked Transaction does not appear in I_S tables of InnoDB (5.1 plugin and 5.5)
Submitted:	28 Jun 2011 12:54	Modified:	29 Jun 2011 3:37
Reporter:	Rituparna Kashyap	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S2 (Serious)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	information_schema, InnoDB plugin, innodb_trx, transaction

Description:
Transaction information does not appear in the I_S innodb_trx, if the transaction is blocked by a table lock in other connection. But the same transaction information is available in SHOW ENGINE INNODB STATUS. 

Found the same problem in both MySQL 5.1.49-1ubuntu8.1 (InnoDB Version 1.0.10) and 5.5.13(InnoDB Version 1.1.7) 

How to repeat:
Connection A :

LOCK TABLE sakila.actor WRITE;

Connection B :

USE sakila;
START TRANSACTION;
UPDATE actor SET first_name='ABC' WHERE actor_id=1;
COMMIT;

There is no row corresponding to the above transaction in I_S innodb_trx.

SELECT * FROM information_schema.`INNODB_TRX`;

gives no row.

Where as SHOW ENGINE INNODB STATUS gives  

------------
TRANSACTIONS
------------
Trx id counter 88F
PURGE done FOR trx's n:o < 88F undo n:o < 0
History list length 9
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started, process no 15212, OS thread id 139859345880848
MySQL thread id 71, query id 4073 localhost 127.0.0.1 root
show engine innodb status
---TRANSACTION 88D, not started, process no 15212, OS thread id 139859345479440
mysql tables in use 1, locked 1
MySQL thread id 67, query id 4072 localhost 127.0.0.1 root Table lock
UPDATE actor SET first_name='ABC' WHERE actor_id=1
---TRANSACTION 88B, not started, process no 15212, OS thread id 139859346081552
mysql tables in use 1, locked 1
MySQL thread id 34, query id 4068 localhost 127.0.0.1 root

the transaction isn't active inside innodb.. have you see bug #59678 ?

So the I_S tables established by InnoDB are not a full replacement for SHOW ENGINE INNODB STATUS?  This is not how it has been 'marketed'.

Peter
(not a MySQL person)

Manual (http://dev.mysql.com/doc/refman/5.5/en/innodb-trx-table.html) clearly says:

"The INNODB_TRX table contains information about every transaction currently executing inside InnoDB..."

As you can see in the INNODB STATUS, transactions are not started yet *inside InnoDB*, so there are no rows in that table.