About the select Operation hang during mysql source data recovery

When mysqldump the primary database to a file, the table data in the database is relatively large, and then restore the database with the source command in the standby database,

Mysqldump-u root-P dlsp>/data/MySQL/dump/dump_dlsp. SQL

Source </data/MySQL/dump/dump_dlsp. SQL

When the data is restored, perform the Select Operation on the table to be restored.

Check it with show processlist \ G;

* *************************** 4. row ************************** ID: 11 User: Root HOST: localhost DB: dlspcommand: Query time: 334 state: Waiting for table metadata lock info: Select count (1) From lsmp_lottery

The Select Operation is waiting for the meta lock of the table.

So what caused table meta lock? In the original dump file, the table to be operated will be locked first until all the data in the Insert table is completed.

In dump_dlsp. SQL, there is such an operation before inserting data. Lock tables 'lsmp _ lottery' write, the write lock of the table. the table remains open until it is released, metadata locks for the table are always occupied.

Mysql> show open tables \ G;
*************************** 21. row ***************************
Database: dlsp
Table: lsmp_lottery
In_use: 1
Name_locked: 0

In this way, any operations (including DML) on the table will be locked.

Classification of MySQL locks included:

1. Meta-data metadata lock: Implemented in the table cache and provides isolation for DDL (Data Definition Language. A special meta-data metadata type calledName lock. (SQL layer)
2. Table-level data lock (SQL layer) Lock table XXXX write (read)
3. Storage engine-Row locks, page locks, and table locks, Version Control (implemented in the engine)
4. Global read lock-flush tables with read lock (SQL layer)

MySQL lock Execution Process