MySQL Bugs: #54759: Transaction holds adaptive hash latch even if innodb_adaptive_hash

Bug #54759	Transaction holds adaptive hash latch even if innodb_adaptive_hash_index=0
Submitted:	24 Jun 2010 0:42	Modified:	25 Aug 2010 17:12
Reporter:	Vojtech Kurka	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S3 (Non-critical)
Version:	5.1.47	OS:	Linux (Centos, kernel 2.6.18)
Assigned to:		CPU Architecture:	Any
Tags:	adaptive hash index, adaptive hash latch, innodb_adaptive_hash_index

Description:
When I switch adaptive hash index off, queries still acquire adaptive hash latch, which limits the concurrency seriously.

Here is an example of two transactions, which seem to be serialized instead of running concurrently.

---TRANSACTION EA9D9B75, ACTIVE 1 sec, process no 20492, OS thread id 1228499264 starting index read
mysql tables in use 5, locked 0
, holds adaptive hash latch
MySQL thread id 1937802, query id 2874111939 x.x.x.202 myuser Sending data
SELECT ...
Trx read view will not see trx with id >= EA9D9B76, sees < EA9D9B70
---TRANSACTION EA9D9B70, ACTIVE 1 sec, process no 20492, OS thread id 1224239424 starting index read
mysql tables in use 5, locked 0
, holds adaptive hash latch
MySQL thread id 1937733, query id 2874111536 x.x.x.202 myuser Sending data
SELECT ...
Trx read view will not see trx with id >= EA9D9B71, sees < EA9D9B71

Maybe I am wrong and the serialization is caused by some other reason, however I consider the "holds adaptive hash latch" clause misleading when the adaptive hash index is off.

How to repeat:
SET GLOBAL innodb_adaptive_hash_index=0;
SHOW ENGINE INNODB STATUS;

SHOW ENGINE INNODB MUTEX output (see btr/btr0sea.c:170)

Attachment: show_engine_innodb_mutex.txt (text/plain), 14.04 KiB.

might be a duplicate of http://bugs.mysql.com/bug.php?id=50461

Thank you for the report.

You are correct: this is same issue as bug #50461