-
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.
Praveenkumar Hulakund authoredProblem 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