Shulou(Shulou.com)05/31 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.