Does Mysql ALTER TABLE lock the table when adding fields?

Before MySQL 5.6

Update steps

• Add a write lock to the original table
• Redefine an empty temporary table according to the definition of the original table and the execution statement.
• Add index to temporary table (if any).
• Then copy the data in the original table to the temporary table one by one.
• After all records in the original table are copied to the temporary table, the original table is deleted. Then rename the temporary table to the original table name.
• In this case, the entire DDL process will lock the table.

After MySQL 5.6

Update steps

• Add a write lock to the original table
• Redefine an empty temporary table according to the definition of the original table and the execution statement. And apply for rowlog space.
• Copy the original table data to the temporary table. At this time, all table data modification operations (additions, deletions, and modifications) will be stored in the rowlog. At this time, the client can operate the table.
• After all the original table data is copied, all changes in the rowlog will be synchronized to the temporary table. The client cannot operate during this process.
• When all records in the original table are copied to the temporary table, all add, delete and modify operations of the client during the copy period are synchronized to the temporary table. Then rename the temporary table to the original table name.

Summarize

ALTER TABLE adding a column will lock it. However, after MySQL version 5.6, the ONLINE DDL function was added, which can greatly shorten the time that the table is unavailable.

Notice

When adding fields to ALTER TABLE. If the amount of data in the table is very large. Do not set the default value.
For example, there are currently tables with more than 20 million data volumes. If you add a field, add a default value. After executing the Online DDL, Mysql will update the default value of the entire table data, that is,

UPDATE `table_name` SET new_col = [default value] WHERE TRUE

This is equivalent to updating 2000w+ data in the same transaction. That is to say, this transaction will lock the entire table and will not be committed until all data records are updated with default values.

This time is very long, and because all records in the table are locked, the table will be unavailable for a very long time.

The author has experimented with 16 cores, 32G, and the default configuration of Mysql. 5 million data plus one field.

Without the default value, the entire DDL update process takes 66 seconds. Moreover, during the entire update process, query, modification, and addition operations of the table are available. There is virtually no impact on the usability of the table.

With the default value, the entire DDL update process takes 213 seconds. After testing, it was found that after about 100 seconds, the query, modification, and addition operations of the table would fall into a waiting state.

This is the end of this article about whether to lock the table when adding fields in Mysql ALTER TABLE. For more relevant content about adding fields in Mysql ALTER TABLE, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope you will support 123WORDPRESS.COM in the future!