Can MySQL ALTER TABLE Operations Be Performed Without Locking the Table?

Can Alter Table Operations Be Performed Without Locking the Table?

ALTER TABLE statements in MySQL impose a read-lock on the table, preventing any concurrent writes. This can be a significant inconvenience for large tables, as it can result in blocked INSERT or UPDATE statements for extended periods. Understanding whether there's a solution for "hot alters," allowing updates during the addition of a column, is crucial.

Manual Approach

In the absence of native support for hot alters, the only alternative is to manually replicate the process that many RDBMS systems employ. This involves:

1. Creating a new table.
2. Copying data from the old table in chunks, managing potential conflicts caused by ongoing INSERT/UPDATE/DELETE operations on the source table.
3. Renaming the old table, followed by renaming the new table (ideally in a transaction).
4. Recompiling stored procedures and other dependent objects to maintain execution plan validity.

Justification for the Lack of Hot Alters

It's worth considering the reasons behind MySQL's lack of native support for hot alters:

• Adding a field effectively equates to changing a field in every row, which is a significant operation.
• Field locks are more complex to implement than row locks.
• Updating the physical disk structure requires the relocation of every record.
• This process resembles a whole-table UPDATE, making it fundamentally more impactful than a regular update.

The above is the detailed content of Can MySQL ALTER TABLE Operations Be Performed Without Locking the Table?. For more information, please follow other related articles on the PHP Chinese website!