Will MySQL modify the table structure to lock the table?

〇, about DDL, DML and DCL

DDL (Data Definition Languages) Statements: Data Definition Languages, these statements define the definitions of different data segments, databases, tables, columns, indexes and other database objects.
Commonly used statement keywords mainly include create, drop, alter and so on.

DML (Data Manipulation Language) Statements: Data manipulation statements used to add, delete, update and query database records and to check data integrity.
Commonly used statement keywords mainly include insert, delete, udpate, and select. (Add, delete, modify and check)

DCL (Data Control Language) statement: data control statement, a statement used to control the direct permission and access level of different data segments. These statements define access rights and security levels for databases, tables, fields, and users.
The main statement keywords include grant, revoke, and so on.

---

1. DDL implementation

Before MySQL5.6 version, there are two main ways to execute DDL: Copy mode and In-place mode

Copy method to perform DDL operations

1. Create a temporary table with exactly the same structure definition as the original table
2. Add MDL (meta data lock, metadata lock) lock to the original table, prohibit adding, deleting and modifying data in the table, and allow query
3. Execute DDL statements on temporary tables
4. According to the increasing order of primary key ID, read the data from the original table line by line and insert it into the temporary table
5. Upgrade the lock on the original table to prohibit read and write operations on the data in the original table
6. Delete the original table, rename the temporary table to the original table name, and complete the DDL operation

In-place execution of DDL operations

In-place method is also known as Fast Index Creation . Compared with the Copy method, the In-place method does not copy data, so the execution speed is greatly accelerated. However, this method only supports to add and delete secondary , and needs to lock the entire table like the Copy method. The following takes adding an index as an example to briefly introduce the implementation process of the In-place method:

1. Create a new indexed data dictionary
2. Add MDL (meta data lock, metadata lock) lock to the original table, prohibit adding, deleting and modifying data in the table, and allow query
3. According to the order of the clustered index, query the data, find the required index column data, sort and insert it into a new index page
4. Wait for all read-only transactions that open the current table to commit
5. end of index creation

2. Online DDL

After MySQL 5.6 , the new feature Online DDL was added to support the parallel operation of DML statements during DDL execution and improve the throughput of the database.
Compared with Copy mode and In-place mode, Online mode can read and write data in the table when executing DDL.
Online DDL can effectively improve the impact on the database during DDL:

• During Online DDL, queries and DML operations can be executed normally in most cases, and the table lock time will be greatly reduced to ensure the scalability of the database as much as possible;
• DDL that allows in-place operations avoids excessive disk IO and CPU resources occupied by rebuilding tables, reduces the overall load on the database, and maintains high performance and high throughput of the database during DDL;
• DDL that allows in-place operations occupies less buffer pool than operations that require COPY to temporary files, avoiding temporary performance degradation in previous DDL processes, because data needs to be copied to temporary tables before, and this process will occupy buffer pools. Some frequently accessed data in memory will be cleaned up.

Online DDL implementation can also be essentially divided into 2 ways: Copy way and In-place way:

• For SQL that does not support Online DDL, use the Copy method, such as delete the primary key , to modify the column data type , to change the table character set , etc. These operations will lead to changes in the record format, and it is impossible to implement Online DDL through a simple full + incremental method.

• For SQL that supports Online DDL, the In-place method is used. MySQL uses " to modify the row record format " as the standard, and divides the In-place method into two categories:

  • If the rows modified format, it is necessary to rebuild the table, such add a primary key , adding, deleting fields , practice format the ROW_FORMAT , the OPTIMIZE optimization table other operations, this manner is referred to the rebuild mode .
  • If you do not modify the row record format, only modify the metadata of the table, you do not need to rebuild the table, such as add, delete, rename the secondary index , set, delete the default value of the field , rename the field , Rename table and other operations, this way is called no-rebuild way .

For more details, please refer to the official document: https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html

---

3. Online DDL implementation process

Online DDL mainly includes 3 stages, Prepare stage, Execute stage, Commit stage.
The following will mainly introduce the three stages of the online DDL execution process.

Prepare stage

• Hold EXCLUSIVE-MDL lock, prohibit reading and writing of DML statements
• According to the DDL type, determine the execution method (Copy, Online-rebuild, Online-no-rebuild)
• Create new frm and ibd temporary files (ibd temporary files are only required for rebuild type)
• Allocate row_log space to record DML operations generated in the DDL Execute phase (required only for rebuild types)

Execute stage

• Downgrade EXCLUSIVE-MDL locks to allow DML statements to read and write
• Scan all data pages of the primary key and secondary index of the original table, generate a B+ tree, and store it in a temporary file
• Record DML operations generated in the DDL Execute phase to row_log (required only for rebuild types)

Commit stage

• Upgrade to EXCLUSIVE-MDL lock, prohibit reading and writing of DML statements
• Apply the DML operations recorded in row_log to the temporary file to get a data file with the same logical data as the original table (only required for rebuild type)
• Rename frm and idb temporary files, replace the original table, delete the original table file
• Commit the transaction (brush the redo log of the transaction), and the change is complete

It can be seen from the above process that both the Prepare stage and the Commit stage are prohibited from reading and writing, and only Execute allows reading and writing, so why is it said that the Online DDL method can read write the data in the table during the execution process?
In fact, because the Prepare and Commit stages are very short compared to the Execute stage, it can basically be considered that reading and writing are allowed throughout the process.
Both the Prepare phase and the Commit phase prohibit reading and writing, mainly to ensure data consistency.

---

Fourth, Online DDL syntax and optional parameters

ALTER TABLE …. , ALGORITHM[=]{DEFAULT|INPLACE|COPY}, LOCK[=]{DEFAULT|NONE|SHARED|EXCLUSIVE}

Example:

ALTER TABLE tablename DROP COLUMN age,ALGORITHM=INPLACE,LOCK=DEFAULT;

ALGORITHM option

COPY : Use Copy mode to perform DDL operations. DML operations are not allowed during DDL execution.
INPLACE : Use In-place mode to perform DDL operations. During DDL execution, DML operations are allowed.
DEFAULT : The default option, according to the DDL operation type, the DDL execution method is automatically selected, the In-place method is preferred, and is selected. If the conditions are not met, the Copy method is .

LOCK option

EXCLUSIVE : Add an exclusive lock (X lock) to the entire table, DML operations are not allowed
SHARED : Add a shared lock (S lock) to the entire table, allowing query operations, but not allowing data modification operations
NONE : Do not lock the table, both query operations and data change operations are allowed, that is, all DML operations are allowed, and concurrency is the best in this mode
DEFAULT : The default option, according to the type of DDL operation, the minimum degree of locking, as much as possible to support DML operations