mysql--when Mysqldump--single-transaction encounters an ALTER TABLE

Some production environments use mysqldump--single-transaction to perform database backups at night, while colleagues perform the ALTER TABLE operation during backup, and the part of the operation fails partially successfully, why?

The interpretation of the Single-transaction parameter in mysqldump is:

Creates a consistent snapshot by dumping all tables in a
single transaction. Works tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump Is isn't guaranteed to being
consistent for other storage Engines. While a
--single-transaction dump was in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
Statements:alter table, DROP table, RENAME table,
truncate table, As consistent snapshot is not isolated
from them. Option automatically turns off--lock-tables.

The red font part is the focus, but it's a bit confusing, or a hands-on test.

Based on the introduction of several major options for mysqldump, we back up the command executed mysqldump--single-transaction--master-data equivalent to executing the following code:

FLUSH TABLES; FLUSH TABLES with READLOCK;SETSESSIONTRANSACTION Isolation  Level Repeatable READ; STARTTRANSACTION  withconsistent SNAPSHOT; SHOW MASTER STATUS; UNLOCK TABLES; SHOW TABLES like 'XXX'SET OPTIONSql_quote_show_create=1ShwoCREATE TABLE 'XXX'SHOW Fields from 'XXX'SHOWTABLESTATUS like 'XXX'SELECT /*!40001 Sql_no_cache*/ *  fromXxxquit

When the scene 1:mysqldump starts but has not yet been backed up to the table tb001, also respond to the alter operation on the table tb001 and mysqldump export the table tb001

The alter operation completed successfully, but the mysqldump operation failed.

Scene 2:mysqldump start the backup and complete the export of the tb001, during the export of other tables, other reply to the table alter operation

The ALTER TABLE operation is blocked until mysqldump completes or fails to exit.

##========================================================================##

Summarize:

The Single-transaction parameter obtains data consistency through multiple versions of the INNODB, while ALTER TABLE, DROP table, RENAME table,truncate TABLE, and so on, can disrupt data consistency, and both operations cannot be executed concurrently.

If the Modify table operation starts in the time period after "mysqldump is turned on but not before the modified table data has been exported", the Modify table operation completes successfully, and the mysqldump execution fails;

If the Modify table operation starts within the time period when the Mysqldum has exported modified table data but before the mysqldump operation has been completed, the Modify table operation is blocked, Mysqldum can be completed successfully, and the table operator can be modified after the mysqldump operation is completed.

##========================================================================##

mysql--when Mysqldump--single-transaction encounters an ALTER TABLE