The general principle of mysqldump and the influence of DDL operation in the backup process of mysqldump

• MySQL Version: 5.7.18
  Isolation level: REPEATABLE-READ
  If the isolation level is not RR, an error will be reported when starting the transaction snapshot read:

root@localhost : (none) 10:38:48> show variables like '%isola%';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)

root@localhost : (none) 10:38:50> START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ ;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Warning (Code 138): InnoDB: WITH CONSISTENT SNAPSHOT was ignored because this phrase can only be used with REPEATABLE READ isolation level.

root@localhost : (none) 10:39:01> set global tx_isolation='REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)
#Exit client reentry
root@localhost : (none) 10:44:17> show variables like '%isola%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

root@localhost : (none) 10:44:38> START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ ;
Query OK, 0 rows affected (0.00 sec)

The general principle of mysqldump

• 1.FLUSH /!40101 LOCAL / TABLES 
  Close all tables open in memory and clean up the query cache.
• 2.FLUSH TABLES WITH READ LOCK 
  Add a global read lock, only read, not update operation.
• 3.SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 
  Setting the transaction isolation level of the current session to RR, RR avoids unrepeatable reading and hallucination, and ensures that all the same queries in a transaction read the same data during backup.
• 4.START TRANSACTION /!40100 WITH CONSISTENT SNAPSHOT / 
  Getting a snapshot of the current database, which is determined by single-transaction in mysqldump, is similar to opening a transaction and performing a SELECT operation on all tables. This ensures that the data obtained by selecting * from the table at any point in time is consistent with the data obtained by performing START TRANSACTION WITH CONSISTENT SNAPSHOT.
• 5.SHOW MASTER STATUS 
  master-data determines that binlog status information, including MASTER_LOG_FILE and MASTER_LOG_POS, is recorded at the start of backup.
• 6.UNLOCK TABLES 
  To release the lock, that is to say, the statement mysqldump actually has a lock table, but it's only locked for a short time.
• 7.SHOW CREATE DATABASE IF NOT EXISTS test 
  Generate database creation statements.
• 8.SAVEPOINT sp 
  Set up SAVEPOINT, then back up each table and roll back to the SAVEPOINT.
• 9.show create table test 
  Generating TABLE statement
• 10.SELECT /!40001 SQL_NO_CACHE / * FROM test 
  This statement queries all the data of table test1 and generates the corresponding insert statement in the backup file.  
  The role of SQL_NO_CACHE is that query results are not cached in the query cache.
• 11.SHOW TRIGGERS LIKE 'test'； 
  Backup trigger.
• 12.ROLLBACK TO SAVEPOINT sp 
  If ROLLBACK TO SAVEPOINT sp is not executed, the concurrent DDL operation may be blocked, and the DDL operation can continue after the statement is executed.
• 13.SHOW FUNCTION STATUS WHERE Db = 'test' 
  SHOW CREATE FUNCTION mycat_seq_currval 
  SHOW PROCEDURE STATUS WHERE Db = 'test' 
  Backup stored procedures and functions

The first case

session 1                                                                   session 2
test 05:37:00> START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ ;
Query OK, 0 rows affected (0.00 sec)
test 05:37:02> savepoint sp;     
Query OK, 0 rows affected (0.00 sec)                                             
test 05:37:08> show create table uniq_test;  
Query OK, 0 rows affected (0.00 sec)        
                                                                            test 05:37:14> alter table uniq_test add column name7 varchar(10);
                                                                            Query OK, 0 rows affected (0.32 sec)
test 05:37:24> select * from uniq_test;
ERROR 1412 (HY000): Table definition has changed, please retry transaction
test 05:37:39> rollback to savepoint sp;
Query OK, 0 rows affected (0.00 sec)

• START TRANSACTION /!40100 WITH CONSISTENT SNAPSHOT after / to select * from uniq_test before, in the middle, if session 2 performs ddl operation, then session 1 then performs select * from uniq_test; it will report an error: Table definition has changed, retry transaction 
  The file embodied in mysqldump (unlock tables statement to SELECT /!40001 SQL_NO_CACHE/ * FROM test statement) has no data, no insert statement, and the table structure is there.

The second case

session 1                                                                   session 2
test 05:37:00> START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ ;
Query OK, 0 rows affected (0.00 sec)
test 05:37:02> savepoint sp;      
Query OK, 0 rows affected (0.00 sec)                                            
test 05:37:08> show create table uniq_test; 
Query OK, 0 rows affected (0.00 sec)         
test 05:37:24> select * from uniq_test;
+----+------+-------+-------+-------+-------+-------+
| id | name | name1 | name2 | name3 | name4 | name5 |
+----+------+-------+-------+-------+-------+-------+
|  1 | NULL | NULL  | NULL  | NULL  | NULL  | NULL  |
|  1 | NULL | NULL  | NULL  | NULL  | NULL  | NULL  |
|  2 | NULL | NULL  | NULL  | NULL  | NULL  | NULL  |
|  3 | NULL | NULL  | NULL  | NULL  | NULL  | NULL  |
+----+------+-------+-------+-------+-------+-------+
4 rows in set (0.00 sec)

                                                                            test 05:37:44> alter table uniq_test add column name6 varchar(10);

test 05:38:10> rollback to savepoint sp;
                                                                            test 05:38:46> alter table uniq_test add column name6 varchar(10);
                                                                            Query OK, 0 rows affected (21.73 sec)

• In the middle of selecting * from uniq_test; then to rollback to save point sp; before that, if session 2 performs ddl operation, the ddl operation of session 2 will hang, and then show process list:

test 05:37:52> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+-----------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State                           | Info                                                |
+----+------+-----------+------+---------+------+---------------------------------+-----------------------------------------------------+
| 21 | root | localhost | test | Query   |    4 | Waiting for table metadata lock | alter table uniq_test add column name6 varchar(10) |
| 22 | root | localhost | test | Query   |    0 | starting                        | show processlist                                    |
+----+------+-----------+------+---------+------+---------------------------------+-----------------------------------------------------+
2 rows in set (0.00 sec)

You can see that the DDL operation is waiting for the metadata lock. When rollback to savepoint sp is executed in session 1, the DDL operation of session 2 will continue. So that's why savepoint is set up. After opening a transaction, only rollback or commit can release the lock, but this big transaction START TRANSACTION /!40100 WITH CONSISTENT SNAPSHOT/ can not simply rollback or commit the whole transaction, so we adopt: before backing up the table, set up a savepoint to rollback the table every time a table is backed up, thereby releasing the metadata lock of the table, thereby releasing the metadata lock of the table. The impact on concurrent DDL operations is minimized.

The show create table uniq_test statement does not hold metadata locks.