MySQL Bugs: #51591: deadlock in the plugins+status+variables

Bug #51591	deadlock in the plugins+status+variables
Submitted:	28 Feb 2010 23:27	Modified:	18 Jun 2010 1:09
Reporter:	Sergei Golubchik	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	Staale Smedseng	CPU Architecture:	Any

Description:
Mutexes are locked in the following order

LOCK_global_system_variables -> LOCK_plugin
(in THD::init -> plugin_thdvar_init)

LOCK_plugin -> LOCK_status
(in plugin_initialize -> add_status_vars)

LOCK_status -> LOCK_global_system_variables
(fill_status -> show_status_array)

this can deadlock.

How to repeat:
see the code

Suggested fix:
copy the fix from mysql-next-mr, it's in the plugin_initialize():
don't hold LOCK_plugin over plugin->init() and add_status_var() calls.

Thank you for the report.

Verified as described.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/105546

3452 Staale Smedseng	2010-04-13
      Bug #51591 deadlock in the plugins+status+variables
      
      Potential deadlock situation involving LOCK_plugin,
      LOCK_global_system_variables and LOCK_status.
      
      This patch backports the a from next-mr, unlocking
      LOCK_plugin before calling plugin->init() and
      add_status_vars().

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/105870

3457 Staale Smedseng	2010-04-16
      Bug#51591 deadlock in the plugins+status+variables
            
      Potential deadlock situation involving LOCK_plugin,
      LOCK_global_system_variables and LOCK_status.
            
      This patch backports the fix from next-mr, unlocking
      LOCK_plugin before calling plugin->init() and
      add_status_vars().

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/106006

3354 Staale Smedseng	2010-04-19
      Bug#51591 deadlock in the plugins+status+variables
      
      Unlocking/locking of LOCK_plugin in ha_ndbcluster.cc not 
      needed anymore (but missing from the initial patch).

Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:kristofer.pettersson@sun.com-20100420181920-24ar0rrqdbytwte8) (merge vers: 5.1.47) (pib:16)

Noted in 5.1.47 changelog.

Locking involving the LOCK_plugin, LOCK_global_system_variables, and
LOCK_status mutexes could deadlock.

Hi, I have a question.
Can the following behaviour be related to this bug?

DB Server:
Linux CentOS 5.4
MySQL 5.1.44

Web Server:
FreeBSD 7.2
medium traffic website

Engine: MySQL & InnoDB

Almost everyday one or more MySQL threads get stuck in "Sending data" status
and lock the table from further writes but not from reads (Read Lock)

The hung thread is unkillable, even if it shows the status Killed.

I tried to kill the related apache thread on web server but it does not help,
so the lock is all internal to MySQL process and the apache thread hanging as well is just a 'victim'.

The only way to get rid of the stuck mysql threads is restarting the server,
which will not react to normal restart and a kill -9 is needed!

This is very very serious bug preventing normal operations of a website and causing a lot of unwanted downtime.

Do you think this behaviour is related to this bug?
Will upgrading to 5.1.47 fix it?

Thanks
Claudio Nanni

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:alik@sun.com-20100422150658-fkhgnwwkyugtxrmu) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:alexey.kopytov@sun.com-20100429203306-tg0wz4y2xyx8edrl) (merge vers: 5.5.5-m3) (pib:16)

Noted in 5.5.5, 6.0.14 changelogs.

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)