About the effect of MySQL build table on DML "Go"

This article is from here

Today, a classmate asked the line has encountered a continuous build table, resulting in blocking the normal insert, update and so on. But there was no reservation on the scene. So there is a question as to why building a table affects DML.

Analysis

First of all, this phenomenon is not encountered in all scenarios (otherwise MySQL users will jump up already).

As soon as the table is built, it only involves writing the table definition file and initializing the table space. There are few operations involving redo and undo in the middle (only the InnoDB table is discussed here). So unless the disk IO response is not met, most of the time the table operation will end quickly, not "stable recurrence"

And even for IO reasons, the build table process takes longer to execute, and the table operation does not block some DML operations.

So you can only view conflicting case from the code.

Suppose session 1 is performing a CREATE table operation and is blocked on the step of writing a tablespace file due to IO reasons. Discusses the scenarios where Session2 do the following.

No primary key table insert

The insert operation requires a lock on the Dict_sys->mutex because it needs to request the system to self-increment the primary key. This lock needs to wait until the Session1 table operation is complete before releasing it.

Operations with foreign key tables

At this point session2 need to determine the foreign key consistency, need to Dict_sys->mutex lock.

Here are a few things: when the child table of a FOREIGN KEY constraint inserts data and the parent table deletes data, the wait is triggered when the associated foreign key field for both tables has been modified.

Some students said that both of our online conditions are forbidden, is not because of this lock cause blocking DML?

When a new table is opened

If the insert requires a new open table, it is necessary to remove the information from the dictionary based on the table name and also trigger the wait.

Even if you have already opened a table, the next visit needs to be reopened because the Flush table or table space elimination is performed.

Other actions that affect

along the Dict_sys->mutex we can also find that there are several operations, if it happens in Session2, will be blocked

1) 1) Flush tables

2) SELECT * from Information_schema.tables;

2) More than two because all access to the Table object list, but also better understand

3) SELECT * from Information_schema.innodb_sys_tables;

3) can actually be handled separately with another lock Sys_tables

4) Show CREATE TABLE another_table

This is because you have to determine if there are foreign key associations

Simply leave a question: why is show tables not blocked?

About the effect of MySQL build table on DML "Go"