Bug #78500 dict_sys->mutex lock
Submitted: 21 Sep 2015 12:59 Modified: 5 Nov 2018 14:35
Reporter: Dmitriy Krasnikov Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.6.24 OS:Oracle Linux (6.6)
Assigned to: CPU Architecture:Any

[21 Sep 2015 12:59] Dmitriy Krasnikov 
Description:
Hello,
We have queries using index_merge (intersect, union) that cause global system lock, so other queries cannot be executed.
We have the following info messages in SHOW ENGINE INNODB STATUS.

How to repeat:
unknown
[21 Sep 2015 13:01] Dmitriy Krasnikov 
innodb status

Attachment: innodb (application/octet-stream, text), 24.07 KiB.
[21 Sep 2015 13:02] Dmitriy Krasnikov 
Explain:
Using intersect(ptype_id_show_url_family_id,ptype_group_id); Using where; Using temporary; Using filesort

From slow log:
# Query_time: 24.685484  Lock_time: 0.000078  Rows_sent: 24  Rows_examined: 4524  Rows_affected: 0
# Bytes_sent: 4581
[11 Oct 2015 9:20] Valeriy Kravchuk 
Do you have PRIMARY KEYs explicitly defined on tables that are accessed by threads waiting on dict_sys->mutex lock?
[13 Oct 2015 13:23] Dmitriy Krasnikov 
Yes have Primary keys
[5 Oct 2018 14:35] MySQL Verification Team 
Hi,

Would you be so kind to upload to this bug a full dump of the tables involved in the query.

If any of the tables is VERY long, upload the dump with the minimum number of rows that will reproduce the problem.

Also, you wrote "so other queries cannot be executed" ... That is expected behaviour ..... the problem is in the speed of the query execution.
[6 Nov 2018 1:00] Bugs System 
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".