How to do MySQL metadata Lock experiment
This article will explain in detail how to carry out the MySQL metadata lock experiment, the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.
When DDL statements (such as changing the table structure, DROP table, and so on) are executed against an existing table, metadata lock waiting occurs if a transaction associated with the table is not committed.
At this point, the transaction associated with this table needs to be committed before the metadata lock is released.
Create a table
Mysql > CREATE TABLE `travelrecord` (
-> `id` bigint (20) NOT NULL
-> `user_ id` varchar (100) DEFAULT NULL
-> `traveldate` date DEFAULT NULL
-> `room` decimal (10jin0) DEFAULT NULL
-> `days` int (11) DEFAULT NULL
-> PRIMARY KEY (`id`)
->) ENGINE=InnoDB DEFAULT CHARSET=gbk
Query OK, 0 rows affected (0.09 sec)
Conversation one
Open a transaction to query the newly created table
Mysql > begin
Query OK, 0 rows affected (0.00 sec)
Mysql > select * from travelrecord
Empty set (0.00 sec)
Conversation two
Lock wait will occur for the table just created by drop
Mysql > drop table travelrecord
Conversation one
Query session
Mysql > show processlist
+-+
| | Id | User | Host | db | Command | Time | State | Info |
+-+
| | 3 | root | 127.0.0.1 Query 34875 | sale | Query | 0 | init | show processlist |
| | 24 | root | 127.0.0.1 Query 34876 | sale | Query | 1058 | Waiting for table metadata lock | drop table travelrecord |
+-+
2 rows in set (0.00 sec)
Commit transaction
Mysql > commit
Query OK, 0 rows affected (0.03 sec)
Conversation two
Lock is released and DROP statement executes smoothly
Mysql > drop table travelrecord
Query OK, 0 rows affected (58 min 40.02 sec)
There is no lock in the database
Mysql > show processlist
+-+ +
| | Id | User | Host | db | Command | Time | State | Info |
+-+ +
| | 3 | root | 127.0.0.1 Sleep 34875 | sale | Sleep | 76 | | NULL |
| | 24 | root | 127.0.0.1 Query 34876 | sale | Query | 0 | init | show processlist |
+-+ +
2 rows in set (0.00 sec)
On how to carry out the MySQL metadata lock experiment to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.
