Shulou(Shulou.com)06/01 Report--

When we encounter a fault, we often think about how to solve the fault, rather than thinking about the cause of the fault from the root of the fault. As a result, we can only get the fish and lose the fish. Today, we're going to share a case study of USE DB blocking failures.

Fault description

Today, a friend encountered a serious failure in the database. The fault environment is as follows:

MYSQL 5.6.16

RR isolation level

GITD shuts down

The performance is as follows:

Use db cannot enter the database

Show table status cannot query table information

Schema.processlist, there are a lot of Waiting for table metadata lock.

In an urgent situation, he killed a lot of threads and found that he still could not recover, and finally killed a thing that was not submitted in time to return to normal. Only a screenshot of the following image is left:

Fault information extraction

Going back to the above figure, we can summarize the sentence types as follows:

1 、 CREATE TABLE An AS SELECT B

Its STATE is sending data.

2 、 DROP TABLE A

Its STATE is Waiting for table metadata lock.

3. SELECT * FROM A

Its STATE is Waiting for table metadata lock.

4. SHOW TABLE STATUS [like 'A']

Its STATE is Waiting for table metadata lock.

Information analysis

It is not easy to analyze this case list because it is a combination of MYSQL layer MDL LOCK and RR mode innodb row lock, and we have to be sensitive to schema.processlist 's STATE.

It is recommended to read my following article to learn MDL LOCK:

Https://www.jb51.net/article/131383.htm

In this section, MDL LOCK is validated in the following two ways:

Method 1: the author adds log output to the MDL LOCK source code locking function, and if you want to analyze the types of statements plus MDL LOCK, you can only use this way, because MDL LOCK locking is often flash, and performance_schema.metadata_locks has no way to observe it.

Method 2: use version 5.7 performance_schema.metadata_locks to observe when there is a traffic jam.

The method of opening mdl monitoring in packs is as follows:

1. The analysis of CREATE TABLE An AS SELECT B to the sending data of table B

The state of sending data can actually mean a lot. From my current understanding of it, it is a general term for the upper layer of MYSQL to exchange data between the INNODB layer and the MySQL layer when this kind of statement is used for SELECT type statements, so it may include:

You do need to access a large amount of data and may need to be optimized.

Because the acquisition of row lock in the INNODB layer needs to wait, such as our common SELECT FOR UPDATE.

At the same time, we should also note that in RR mode, the locking method of SELECT B is consistent with that of INSERT...SELECT. I will not repeat it:

From his reaction to the situation because he killed a long-term unsubmitted thing in the end, so he because it is case 2. And the whole CREATE TABLE An AS SELECT B statement can not be obtained because some databases on the B table are locked, so the whole statement is in the sending data state.

2. Analysis of SHOW TABLE STATUS [like 'A'] Waiting for table metadata lock

This is the most important link in this case, SHOW TABLE STATUS [like'A'] is blocked and its STATE is Waiting for table metadata lock and notice here is table because there are many MDL LOCK types. In the article I introduced by MDL, I mentioned that when desc has a table, it will MDL_SHARED_HIGH_PRIO (SH). In fact, it will also be MDL_SHARED_HIGH_PRIO (SH) when it comes to SHOW TABLE STATUS.

Mode one

Mode two

The existence of MDL_SHARED_HIGH_PRIO (SH) can be observed in both ways and what I simulate is in a clogged situation.

But MDL_SHARED_HIGH_PRIO (SH) is a very high priority MDL LOCK type that looks like this:

Compatibility:

Blocking queue priority:

There is no other possibility for it to be blocked except by MDL_EXCLUSIVE (X). So this is a very important breakthrough.

Third, the analysis of CREATE TABLE An AS SELECT B adding MDL LOCK to A table.

This is also something I didn't know before, and it takes the most time in this column. I have analyzed earlier that there is only one possibility for SHOW TABLE STATUS [like'A'], which only reads MDL_SHARED_HIGH_PRIO (SH) MDL LOCK, to block on MDL LOCK, and that is the MDL_EXCLUSIVE (X) on table A.

So I'm starting to suspect that this DDL statement will MDL_EXCLUSIVE (X) on table A before the end of the statement, and then actually test it, as expected:

Mode one

Mode two

It's a pity that MDL_EXCLUSIVE (X) is not shown in performance_schema.metadata_locks, but as MDL_SHARED (S) is what we can see in my output log that we have upgraded MDL_SHARED (S) to MDL_EXCLUSIVE (X). And judging from the previous compatibility list, only MDL_EXCLUSIVE (X) blocks MDL_SHARED_HIGH_PRIO (SH). So we should be able to confirm that an upgrade has been done here, otherwise SHOW TABLE STATUS [like 'A'] will not be blocked.

IV. Analysis of SELECT * FROM A Waiting for table metadata lock

You may think that SELECT will not be locked, but that is at the innodb level, and the MDL_SHARED_READ (SR) at the MYSQL layer is as follows:

Mode one

Mode two

You can see that MDL_SHARED_READ (SR) does exist and is currently blocked.

Its compatibility is as follows:

Obviously MDL_SHARED_READ (SR) and MDL_SHARED_HIGH_PRIO (SH) are incompatible and need to wait.

5. Analysis of DROP TABLE A Waiting for table metadata lock

This is easy to analyze because table A has an X lock and DROP TABLE A must have a MDL_EXCLUSIVE (X) lock which is of course incompatible with MDL_EXCLUSIVE (X). As follows:

Mode one

Mode two

Among them, EXCLUSIVE is what we call MDL_EXCLUSIVE (X). It does exist and is currently in a jam.

6. Why is use db blocked?

If you use the mysql client without the-An option (or no-auto-rehash), you should at least do the following when USE DB:

1. The MDL (SH) lock on each table under db is as follows (call MDL_context::acquire_lock to give the information in case of congestion)

Mode one

Mode two

You can see that USE DB is indeed blocked because of MDL_SHARED_HIGH_PRIO (SH).

2. Add table cache to each table and open the table (call open_table_from_share ())

Then this situation is exactly the same as when SHOW TABLE STATUS [like 'A'] is blocked, and it is also due to MDL lock incompatibility.

Analysis and carding

With the previous analysis, we can sort out the reasons for this failure as follows:

There is a DML that has not been submitted for a long time on form B.

The statement adds innodb row lock to some of the data in Table B at the innodb layer.

The blockage of CREATE TABLE An AS SELECT B is caused by step 1

Because SELECT B must lock the data satisfied on table B in RR mode, wait is triggered because step 1 has been locked, and STATE is sending data.

Other statements are blocked by step 2

Because CRATE TABLE An AS SELECT B MDL_EXCLUSIVE (X) before table An is created, this lock blocks all other statements about table A, including DESC/SHOW TABLE STATUS/USE DB (non-A), which is only on MDL_SHARED_HIGH_PRIO (SH) MDL LOCK. STATE is unified as Waiting for table metadata lock.

Simulation test

Test environment:

5.7.14

GITD shuts down

RR isolation level

Use scripts:

The steps are as follows:

Session1

Session2session3session4-use test;---use test;begin; delete from b show table status like innodb row lock test;create table an asselect * from b; (due to table b MDL LOCK congestion)-show table status like'atraffic; (due to table a MDL LOCK congestion)-use test (due to table a congestion)

Finally, the waiting status we see is as follows:

In this way, we can perfectly simulate the online state, and if we kill everything in session1, we will naturally unlock everything. Let's take a look at the output in performance_schema.metadata_locks:

We can see the output above, but we need to note that LOCK_TYPE: SHARED it is impossible to block LOCK_TYPE: SHARED_HIGH_PRIO (you can refer to the appendix or my previous article on MDL LOCK analysis) as analyzed above, this is actually an upgrade operation to upgrade to MDL_EXCLUSIVE (X).

Summary

In RC mode, table B in CREATE TABLE A SELECT B will not have any INNODB ROW LOCK, but if table B is very large, table A will also be under the protection of MDL_EXCLUSIVE (X), thus triggering USE DB\ SHOW TABLE STATUS waiting.

You can't use statements like CREATE TABLE A SELECT B if you open GTID.

For DML/DDL mixed systems, you must pay attention to concurrency, as in this example, if you notice the situation of high concurrency, you can find ways to avoid it.

This column shows once again that things that have not been committed for a long time can lead to tragedy, so it is recommended to monitor transactions that do not end for more than N seconds.

Appendix

MDL LOCK TYPE

Compatibility matrix

Waiting queue priority matrix

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.