MySQL Bugs: #77872: possible three way lock deadlock

Bug #77872	possible three way lock deadlock
Submitted:	29 Jul 2015 14:17	Modified:	27 Aug 2018 12:02
Reporter:	Richard Prohaska	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.7.7	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Helgrind builds a directed graph of locks that are taken within the MySQL server. A cycle in the graph implies the possibility of deadlock. Some of these lock deadlocks in the MySQL server are not detected at runtime but result in a server hang.

The helgrind run on the simple 'donothing' test reported a couple of lock order issues. The lock ordering rules in the MySQL server source are:

Rule #1: LOCK_system_variables_hash (R) before LOCK_global_system_variables (M)
Used in the 'alloc_and_copy_thd_dynamic_variables' function.

Rule #2: LOCK_plugin (M) before LOCK_system_variables_hash (R)
Used in the 'find_sys_var_ex function'.

Helgrind does not report all of the locks involved in the deadlock. So, we find the following lock order rule in the MySQL source.

Rule #3: LOCK_global_system_variables (M) before LOCK_plugin (M)
Used in the 'THD::init' function.

There no deadlock situation yet given the read locks on LOCK_system_variables_hash lock. It appears that helgrind does not distinguish between read and write locks on a reader/writer lock. We find another set of functions in the MySQL server than can replace lock order rule #2 and lead to a deadlock.

Modified rule #2: LOCK_plugin (M) before LOCK_system_variables_hash (W)
Used in the 'mysql_install_plugin' and 'plugin_del functions'.

Helgrind identified the possibility of a locking deadlock in MySQL 5.7 involving three locks. One can search the code for SQL operations that call these low level functions and use a tool like the random query generator to try to generate a deadlock.

How to repeat:
Hack mtr to support helgrind, create a donothing test, run donothing with mtr and helgrind turned on.

Helgrind reports: https://s3.amazonaws.com/prohaska7-pub/helgrind.lockorder.mysql-5.7.7.html

Also see http://prohaska7.blogspot.com/2015/07/helgrind-and-mysql-57.html

Same sync objects involved as in deadlocking bugs http://bugs.mysql.com/bug.php?id=63203 and https://bugs.mysql.com/bug.php?id=71236

Hi,

Thank you for your bug report.

However, we can not accept it as it is. First of all, you were using 5.7.7, which was not GA. Hence, we would like to have a report on 5.7.22. This is because there were hundreds of lock bugs that were fixed interim.

Next, we would prefer to have a test case like the one provided in the following bug report:

https://bugs.mysql.com/bug.php?id=71236

Many thanks in advance.

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".