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

Editor to share with you what are the differences between MySQL5.6 and MySQL5.7 in the sentence lock table... read locking. I hope you will get something after reading this article. Let's discuss it together.

Background: recently, while testing the lock between lock table xxx read and DML, I suddenly found that the display of show full processlist and show engine innodb status\ G of mysql5.6 and mysql5.7 is different.

One is for wait for table lock and the other is waiting for table metadata lock, which is very puzzling, so I tracked it down.

Phenomenon description:

1.MySQL5.6

SESSION 1:

SESSION 2:

Output of show full processlist and show engine innodb status\ G (shown as waiting for table level lock)

2.MySQL 5.7

SESSION 1:

SESSION 2:

Show full processlist and show engine innodb status\ G (shown as waiting for table metadata lock)

Analysis:

In MySQL5.6, we can see from both show engine innodb status and show full processlist that the status is waiting for table level lock, indicating that it is a transaction table-level lock.

In MySQL5.7, we only see lock waiting from show full processlist, which is waiting for table metadata lock, indicating that this is a metadata lock (metadata lock) rather than a transaction lock.

According to my understanding, the session1 statement lock table locktest1 read will add MDL_SHARED_READ in the MDL stage, while the update statement of session2 will add MDL_SHARED_WRITE in the MDL phase, so there will be no conflict in the MDL stage. Next, session1 will put an S lock (table lock) on the locktest1 table, while session2 will add an IX lock on the locktest1 table. Because the IX lock conflicts with the session1 table lock S lock, the result is waiting for table level lock. However, the following 5.7shows waiting for metadata lock, which occurs in the MDL phase of lock waiting, which puzzles me. Has the MySQL5.7 locking mechanism changed? Or is it an official display bug of oracle (oracle is not supposed to make such a low-level error)?

Since mysql5.7.3, a new metadata_locks table has been added to the performance_schema library to monitor the locking of MDL (metadata lock). Let's track it down:

From the figure, we can see that MySQL5.7 adds SHARED_READ_ONLY to lock table locktest1 read (while the new type of MySQL5.7), which is mutually exclusive with the SHARED_WRITE of the update statement, causing update to wait in the MDL phase, so the state is waiting for table metadata lock.

So why are SHARED_READ_ONLY and SHARED WRITE mutually exclusive? Let's find the description from the source code, as follows:

/ *

A shared metadata lock for cases when we need to read data from table

And block all concurrent modifications to it (for both data and metadata).

Used by LOCK TABLES READ statement.

, /

MDL_SHARED_READ_ONLY

That is, MDL_SHARED_READ_ONLY blocks all concurrent modifications (including data and metadata).

After reading this article, I believe you have a certain understanding of "what is the difference between MySQL5.6 and MySQL5.7 sentence lock table... read locking". If you want to know more about it, welcome to follow the industry information channel. Thank you for your reading!

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.