MySQL Bugs: #40915: Events takes mutex in wrong order which can easily lead to deadlocks

Bug #40915	Events takes mutex in wrong order which can easily lead to deadlocks
Submitted:	21 Nov 2008 7:58	Modified:	23 Oct 2009 14:38
Reporter:	Michael Widenius
Status:	In progress
Category:	Server: General	Severity:	S1 (Critical)
Version:	5.1	OS:	Any
Assigned to:	Staale Smedseng	Target Version:	5.1+
Triage:	Triaged: D2 (Serious)

Description:
When running mysql-5.1 with the new wrong mutex usage detector, several critical wrong
use of mutex was found in the event code. Any of these can lead to a deadlock when using
events in combination with show status, create database etc.

Some examples of mutex that are taken in wrong order:

LOCK_event_metadata and LOCK_schduler_state
LOCK_event_metadata and LOCK_open
LOCK_scheduler_state and LOCK_global_system_variables
LOCK_event_queue and LOCK_scheduler_state
LOCK_event_queue and LOCK_open

How to repeat:
pull mysql-maria tree with deadlock detector

Remove MYF_NO_DEADLOCK_DETECTION flag from event mutex

Run test suite. At least:

mysql-test-run main.events_bugs main.events_trans should show some of the problems

Suggested fix:
Fix event code to ensure that mutex are always locked in the same order

Hi Monty, what is wrong in this order? There does not seem to be any inter-dependences?

locking diagram

Attachment: locking.jpg (image/jpeg, text), 17.19 KiB.

Monty, the attached diagram has no cycles.

event_scheduler are taking mutex in different order 
For example, in one case it's doing:
mutex_lock(LOCK_event_metadata) ; mutex_lock(LOCK_schduler_state);
In other cases:
mutex_lock(LOCK_schduler_state); mutex_lock(LOCK_event_metadata);
which leads to deadlock senarios

See also BUG#50483