Skip to content
  • Praveenkumar Hulakund's avatar
    55ceedbc
    Bug#17862905: MYSQLDUMP CREATES USELESS METADATA LOCKS · 55ceedbc
    Praveenkumar Hulakund authored
    Problem Description:
    --------------------
    While taking the backup using tool "mysqldump" with option
    "--single-transaction", MDL lock is acquired on each table dumped.
    But these locks are not released until the backup operation is
    completed. Any concurrent DDL operation on those tables will be
    blocked until backup operation is completed. Moreover such blocked
    DDL operations will also block other concurrent DML operations
    (Since DDL has priority over DML) creating pile-up.
    
    Note that officially mysqldump --single-transaction is documented as
    not working/not supported in presence of concurrent DDL statements.
    But it might work for some people in some scenarios and before 5.5,
    due to absence of MDL, combination of "mysqldump --single-transaction"
    and concurrent DDL didn't create pile-up of DML statements.
    
    Analysis:
    --------------------
    "mysqldump" start transaction with consistent snapshot and sets
    isolation level to "repeatable read" when it is used with option
    "--single-transaction".  Data of all the tables is dumped using
    SELECT statement as part of this transaction. MDL lock SR is
    taken on all these tables and held till the transaction is 
    committed or rolled back. Any other incompatible MDL lock request
    on these tables will wait until timeout or "mysqldump" operation
    is completed.
    
    As result concurrent DDL operations on the tables which were dumped
    will be blocked till the end of dump or timeout.
    
    Note that once table is dumped it won't be used again by "mysqldump".
    This fact and the fact that "mysqldump --single-transactions" produces
    backup with validity point at the start of transaction (and also
    retrieves binlog position for backup at the start of transaction) means
    that "mysqldump --single-transaction" can release MDL on tables which
    it has already dumped without introducing more problems with
    consistency.
    
    Fix:
    --------------------
    To make "mysqldump --single-transaction" to release locks once
    dumping of the table is done, modified mysqldump client code
    to dump table data after setting a savepoint. Once dumping of
    table data is over, added code to rollback to the savepoint set.
    Rolling back to savepoint will release MDL lock acquired for the
    table.
    
    But as of now, on rollback to savepoint, MDL locks are
    not released if binlog is on. This logic is added to avoid 
    dropping of tables before rollback to savepoint event is 
    written to binlog. But when binlog handlerton can clear cache
    and can safely rollback to savepoint without writing an event for
    rollback to savepoint then also we are not releasing the MDL
    locks. 
    
    This is fixed by introducing a new handlerton function call
    savepoint_rollback_can_release_mdl. We call this function to
    check with each storage engine participating in transaction
    whether it is safe to release MDL after rollback to savepoint.
    Metadata locks are released only if all the storage engines
    agreed that it is a safe thing to do.
    
    1) For InnoDB storage engine this handlerton function can allow
       release of MDL locks if transaction has not acquired any InnoDB
       locks.
    
    2) For Binlog this handlerton function can allow release of MDL
       locks if rollback to savepoint will completely remove any
       traces of transaction from cache.
    
    3) Absence of this method for any storage engine means it is not
       safe to release MDL locks.
    
    Note that this patch doesn't make "mysqldump --single-transaction"
    safe in general case in presence of concurrent DDL. Nor makes it
    officially supported in this case. It just allows to avoid problem
    with unnecessary concurrent DDL blocking and associated DML query 
    pile-up in some specific cases when it might work.
    55ceedbc
    Bug#17862905: MYSQLDUMP CREATES USELESS METADATA LOCKS
    Praveenkumar Hulakund authored
    Problem Description:
    --------------------
    While taking the backup using tool "mysqldump" with option
    "--single-transaction", MDL lock is acquired on each table dumped.
    But these locks are not released until the backup operation is
    completed. Any concurrent DDL operation on those tables will be
    blocked until backup operation is completed. Moreover such blocked
    DDL operations will also block other concurrent DML operations
    (Since DDL has priority over DML) creating pile-up.
    
    Note that officially mysqldump --single-transaction is documented as
    not working/not supported in presence of concurrent DDL statements.
    But it might work for some people in some scenarios and before 5.5,
    due to absence of MDL, combination of "mysqldump --single-transaction"
    and concurrent DDL didn't create pile-up of DML statements.
    
    Analysis:
    --------------------
    "mysqldump" start transaction with consistent snapshot and sets
    isolation level to "repeatable read" when it is used with option
    "--single-transaction".  Data of all the tables is dumped using
    SELECT statement as part of this transaction. MDL lock SR is
    taken on all these tables and held till the transaction is 
    committed or rolled back. Any other incompatible MDL lock request
    on these tables will wait until timeout or "mysqldump" operation
    is completed.
    
    As result concurrent DDL operations on the tables which were dumped
    will be blocked till the end of dump or timeout.
    
    Note that once table is dumped it won't be used again by "mysqldump".
    This fact and the fact that "mysqldump --single-transactions" produces
    backup with validity point at the start of transaction (and also
    retrieves binlog position for backup at the start of transaction) means
    that "mysqldump --single-transaction" can release MDL on tables which
    it has already dumped without introducing more problems with
    consistency.
    
    Fix:
    --------------------
    To make "mysqldump --single-transaction" to release locks once
    dumping of the table is done, modified mysqldump client code
    to dump table data after setting a savepoint. Once dumping of
    table data is over, added code to rollback to the savepoint set.
    Rolling back to savepoint will release MDL lock acquired for the
    table.
    
    But as of now, on rollback to savepoint, MDL locks are
    not released if binlog is on. This logic is added to avoid 
    dropping of tables before rollback to savepoint event is 
    written to binlog. But when binlog handlerton can clear cache
    and can safely rollback to savepoint without writing an event for
    rollback to savepoint then also we are not releasing the MDL
    locks. 
    
    This is fixed by introducing a new handlerton function call
    savepoint_rollback_can_release_mdl. We call this function to
    check with each storage engine participating in transaction
    whether it is safe to release MDL after rollback to savepoint.
    Metadata locks are released only if all the storage engines
    agreed that it is a safe thing to do.
    
    1) For InnoDB storage engine this handlerton function can allow
       release of MDL locks if transaction has not acquired any InnoDB
       locks.
    
    2) For Binlog this handlerton function can allow release of MDL
       locks if rollback to savepoint will completely remove any
       traces of transaction from cache.
    
    3) Absence of this method for any storage engine means it is not
       safe to release MDL locks.
    
    Note that this patch doesn't make "mysqldump --single-transaction"
    safe in general case in presence of concurrent DDL. Nor makes it
    officially supported in this case. It just allows to avoid problem
    with unnecessary concurrent DDL blocking and associated DML query 
    pile-up in some specific cases when it might work.
Loading