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

This article will explain in detail what are the two stages of querying things in MySQL and DDL causing Waiting for table metadata lock. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

1. Phenomenon description:

SESSION1:

SESSION2:

SESSION3:

Remarks: (here SESSION1,SESSION2,SESSION3 is executed in sequence)

When SESSION1 is not committed, SESSION2 blocks, SESSION3 blocks

When the SESSION1 is submitted, the SESSION2 is still blocked and the SESSION3 is executed successfully (limited space here, readers can experiment on their own)

two。 Phenomenon questioning:

When session1 is not submitted, let's take a look at metadata_locks. From the following figure, we can see that it is session1's SHARE_READ that blocks EXCLUSIVE, while SESSION3's SHARE_READ is blocked by EXCLUSIVE.

When SESSION1 is submitted, let's take a look at metadata_locks (figure below): we find that SESSION2 is blocked by SESSION3, and SESSION3's EXCLUSIVE is blocked by SESSION2's SHARE_READ. Here we don't wonder, is SESSION3's SHARD_READ higher priority? (however, when I look at the source code comments of MDL_SHARE_READ, I don't find that MDL_SHARE_READ has a higher priority than MDL_EXCLUSIVE)

3. Phenomenon analysis:

With the question in the red section in the previous step, let's take a look at the various time-consuming phases of sql execution, as shown below:

Mysql > show profile

+-+ +

| | Status | Duration |

+-+ +

| | Waiting for table metadata loc | 1.001239 | |

| | After create | 0.000047 | |

| | Waiting for table metadata loc | 1.002126 | |

| | After create | 0.000047 | |

| | Waiting for table metadata loc | 1.000864 | |

| | After create | 0.000047 | |

| | Waiting for table metadata loc | 1.001443 | |

| | After create | 0.000047 | |

| | Waiting for table metadata loc | 1.001984 | |

| | After create | 0.000046 | |

| | Waiting for table metadata loc | 1.003780 | |

| | After create | 0.000049 | |

| | Waiting for table metadata loc | 1.003622 | |

| | After create | 0.000049 | |

| | Waiting for table metadata loc | 1.000299 | |

| | After create | 0.000051 | |

| | Waiting for table metadata loc | 1.001613 | |

| | After create | 0.000048 | |

| | Waiting for table metadata loc | 1.000226 | |

| | After create | 0.000077 | |

| | Waiting for table metadata loc | 1.000196 | |

| | After create | 0.000048 | |

| | Waiting for table metadata loc | 1.000574 | |

| | After create | 0.000049 | |

| | Waiting for table metadata loc | 1.001014 | |

| | After create | 0.000046 | |

| | Waiting for table metadata loc | 1.000834 | |

| | After create | 0.000047 | |

| | Waiting for table metadata loc | 1.001708 | |

| | After create | 0.000047 | |

| | Waiting for table metadata loc | 0.492941 | |

| | After create | 0.000130 | |

| | System lock | 0.000028 | |

| | preparing for alter table | 0.000184 | |

| | altering table | 0.000037 | |

| | Waiting for table metadata loc | 1.000922 | |

| | altering table | 0.000057 | |

| | Waiting for table metadata loc | 1.000320 | |

| | altering table | 0.000082 | |

| | Waiting for table metadata loc | 1.001329 | |

| | altering table | 0.000055 | |

| | Waiting for table metadata loc | 1.002728 | |

| | altering table | 0.000054 | |

| | Waiting for table metadata loc | 1.000887 | |

| | altering table | 0.000055 | |

| | Waiting for table metadata loc | 1.002754 | |

| | altering table | 0.000055 | |

| | Waiting for table metadata loc | 1.001484 | |

| | altering table | 0.000055 | |

| | Waiting for table metadata loc | 1.001034 | |

| | altering table | 0.000059 | |

| | Waiting for table metadata loc | 1.000547 | |

| | altering table | 0.000057 | |

| | Waiting for table metadata loc | 1.003391 | |

| | altering table | 0.000058 | |

| | Waiting for table metadata loc | 1.002230 | |

| | altering table | 0.000059 | |

| | Waiting for table metadata loc | 1.002789 | |

| | altering table | 0.000058 | |

| | Waiting for table metadata loc | 1.002071 | |

| | altering table | 0.000059 | |

| | Waiting for table metadata loc | 1.003891 | |

| | altering table | 0.000057 | |

| | Waiting for table metadata loc | 1.003908 | |

| | altering table | 0.000057 | |

| | Waiting for table metadata loc | 1.000404 | |

| | altering table | 0.000055 | |

| | Waiting for table metadata loc | 1.003572 | |

| | altering table | 0.000056 | |

| | Waiting for table metadata loc | 1.000270 | |

| | altering table | 0.000056 | |

| | Waiting for table metadata loc | 1.003832 | |

| | altering table | 0.000148 | |

| | Waiting for table metadata loc | 1.000791 | |

| | altering table | 0.000054 | |

| | Waiting for table metadata loc | 1.004019 | |

| | altering table | 0.000059 | |

| | Waiting for table metadata loc | 1.000523 | |

| | altering table | 0.000056 | |

| | Waiting for table metadata loc | 1.004071 | |

| | altering table | 0.000058 | |

| | Waiting for table metadata loc | 1.000656 | |

| | altering table | 0.000055 | |

| | Waiting for table metadata loc | 1.001957 | |

| | altering table | 0.000058 | |

| | Waiting for table metadata loc | 1.000260 | |

| | altering table | 0.000056 | |

| | Waiting for table metadata loc | 1.000440 | |

| | altering table | 0.000057 | |

| | Waiting for table metadata loc | 1.002061 | |

| | altering table | 0.000055 | |

| | Waiting for table metadata loc | 0.878074 | |

| | altering table | 0.000127 | |

| | committing alter table to stor | 0.031622 | |

| | end | 0.000078 | |

| | query end | 0.002045 | |

| | closing tables | 0.000041 | |

| | freeing items | 0.000143 | |

| | logging slow query | 0.000116 | |

| | cleaning up | 0.000043 | |

+-+ +

100 rows in set, 1 warning (0.00 sec)

From here, we can see that the two phases After create and altering table are the most time-consuming, and the word Waiting for table metadata lock also appears in the two stages, indicating that alter table add/drop index is blocked in these two phases (whether it can be locked by scanning once a second in terms of time).

At this stage of altering table, we know that there will be MDL_EXCLUSIVE after modifying the data, so it will block with MDL_SHARE_READ, so what is the stage of After create?

I saw a reply from the oracle staff on the official bug post of Oracle:

At certain point ALTER TABLE needs to acquire exclusive lock on table to install a new version of .FRM and to get rid of outdated TABLE/TABLE_SHARE/handler instances in Table and Table Definition caches. At this point it will wait for existing SELECTs to stop and will block any new SELECTs.

It means that at some point (traceable from the source code to the After create phase), alter table add/drop index needs to acquire an exclusive lock (MDL_EXCLUSIVE) in order to create a new .FRM and clear instances of the old TABLE,TABLE_SHARE,handler, which are in the table and table definition cache; in this way, the exclusive lock (MDL_EXCLUSIZE) required at this stage will also be mutually exclusive with MDL_SHARE_READ.

4. Summary:

When online alter table add / drop index, it may block in two stages, one is After create, the other is altering table

Usually: before the execution of the alter table statement (before the execution of the large query and the start of the alter table), the alter table statement blocks in the After create phase, and after the execution of the alter table statement (the alter table transaction is not completed, the big query starts), the alter table blocks in the altering table phase.

This is the end of this article on "querying things in MySQL and what are the two stages of Waiting for table metadata lock caused by DDL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please 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.