• Jon Olav Hauglid's avatar
    Backport of revno: 3685 · 3d062adf
    Jon Olav Hauglid authored
    Bug #48210 FLUSH TABLES WITH READ LOCK deadlocks
               against concurrent CREATE PROCEDURE
    
    This deadlock occured between
    a) CREATE PROCEDURE (or other commands listed below)
    b) FLUSH TABLES WITH READ LOCK
    
    If the execution of them happened in the following order:
    - a) opens a table (e.g. mysql.proc)
    - b) locks the global read lock (or GRL)
    - a) sleeps inside wait_if_global_read_lock()
    - b) increases refresh_version and sleeps waiting 
         for old tables to go away
    
    Note that a) must start waiting on the GRL before FLUSH increases
    refresh_version. Otherwise a) won't wait on the GRL and instead
    close its tables for reopen, allowing FLUSH to complete and thus
    avoid the deadlock.
    
    With this patch the deadlock is avoided by making CREATE PROCEDURE
    acquire a protection against global read locks before it starts
    executing. This means that FLUSH TABLES WITH READ LOCK will have
    to wait until CREATE PROCEDURE completes before acquiring the global
    read lock, thereby avoiding the deadlock.
    
    This is implemented by introducing a new SQL command flag called
    CF_PROTECT_AGAINST_GRL. Commands marked with this flag will
    acquire a GRL protection in the beginning of mysql_execute_command().
    This patch adds the flag to CREATE, ALTER and DROP for PROCEDURE
    and FUNCTION, as well as CREATE USER, DROP USER, RENAME USER and 
    REVOKE ALL. All these commands either call open_grant_tables() or
    open_system_table_for_updated() which make them susceptible for
    this deadlock.
    
    The patch also adds the CF_PROTECT_AGAINST_GRL flag to a number
    of commands that previously acquired GRL protection in their
    respective SQLCOM case in mysql_execute_command().
    
    Test case that checks for GRL protection for CREATE PROCEDURE
    and CREATE USER added to mdl_sync.test.
    3d062adf
mdl_sync.test 14.8 KB