• Jon Olav Hauglid's avatar
    Bug #56292 Deadlock with ALTER TABLE and MERGE tables · 51a81b6f
    Jon Olav Hauglid authored
    ALTER TABLE on a MERGE table could cause a deadlock with two
    other connections if we reached a situation where:
    
    1) A connection doing ALTER TABLE can't upgrade to MDL_EXCLUSIVE on the
    parent table, but holds TL_READ_NO_INSERT on the child tables.
    2) A connection doing DELETE on a child table can't get TL_WRITE on it
    since ALTER TABLE holds TL_READ_NO_INSERT.
    3) A connection doing SELECT on the parent table can't get TL_READ on 
    the child tables since TL_WRITE is ahead in the lock queue, but holds
    MDL_SHARED_READ on the parent table preventing ALTER TABLE from upgrading.
    
    For regular tables, this deadlock is avoided by having ALTER TABLE
    take a MDL_SHARED_NO_WRITE metadata lock on the table. This prevents
    DELETE from acquiring MDL_SHARED_WRITE on the table before ALTER TABLE
    tries to upgrade to MDL_EXCLUSIVE. In the example above, SELECT would
    therefore not be blocked by the pending DELETE as DELETE would not be
    able to enter TL_WRITE in the table lock queue.
    
    This patch fixes the problem for merge tables by using the same metadata
    lock type for child tables as for the parent table. The child tables will
    in this case therefore be locked with MDL_SHARED_NO_WRITE, preventing
    DELETE from acquiring a metadata lock and enter into the table lock queue.
    
    Change in behavior: By taking the same metadata lock for child tables
    as for the parent table, LOCK TABLE on the parent table will now also
    implicitly lock the child tables. Since LOCK TABLE on the parent table
    now takes more than one metadata lock, it is possible for LOCK TABLE
    ... WRITE on the parent table or child tables to give ER_LOCK_DEADLOCK
    error.
    
    Test case added to mdl_sync.test.
    Merge.test/.result has been updated to reflect the change to LOCK TABLE.
    51a81b6f
mdl_sync.result 83.2 KB