MySQL 5.6 - table locks even when ALGORITHM=inplace is used

mysql table-locking
jeffreyveon picture jeffreyveon · Feb 13, 2019 · Viewed 7.9k times · Source

I'm running the following ALTER command on a MySQL 5.6 database on a large table with 60 million rows:

ALTER TABLE `large_table` ADD COLUMN `note` longtext NULL, 
ALGORITHM=INPLACE, LOCK=NONE;

Despite specifying both ALGORITHM=INPLACE and LOCK=NONE, the table gets locked and essentially takes down the app until the migration is complete.

I verified that the table was indeed locked by checking the value of the In_use column on the output of the SHOW OPEN TABLES command. It was set to 1.

From what I gather in the MySQL documentation, this operation should not be locking the table. And, MySQL is supposed to fail the command if it is not able to proceed without a lock. I upgraded the database to MySQL 5.7 to see if it's any better, but I face the same problem on 5.7 too.

Is this an expected behavior? How do I find out what's going wrong here?

Answer

Rick James picture Rick James · Feb 18, 2019

I assume you were not doing some other DDL on that table at about the same time?

For the future:

8.0.12 has ALTER TABLE .. ALGORITHM=INSTANT for ADD COLUMN. See Discussion and ALTER Reference and Online DDL Reference

The following limitations apply when the INSTANT algorithm is used to add a column:

  • Adding a column cannot be combined in the same statement with other ALTER TABLE actions that do not support ALGORITHM=INSTANT.
  • A column can only be added as the last column of the table. Adding a column to any other position among other columns is not supported.
  • Columns cannot be added to tables that use ROW_FORMAT=COMPRESSED.
  • Columns cannot be added to tables that include a FULLTEXT index.
  • Columns cannot be added to temporary tables. Temporary tables only support ALGORITHM=COPY.
  • Columns cannot be added to tables that reside in the data dictionary tablespace.
  • Row size limits are not evaluated when adding a column. However, row size limits are checked during DML operations that insert and update rows in the table.

Multiple columns may be added in the same ALTER TABLE statement.

If you can't upgrade then consider Percona's pt-online-schema-change or a new, competing, product gh-ost (which uses the binlog).

Related questions

Create an index on a huge MySQL production table without table locking

I need to create an index on a ~5M rows MySQL table. It is a production table, and I fear a complete block of everything if I run a CREATE INDEX statement... Is there a way to create that index …

Read More
Why is table-level locking better than row-level locking for large tables?

According to the MySQL manual: For large tables, table locking is often better than row locking, Why is this? I would presume that row-level locking is better because when you lock on a larger table, you're locking more data.

Read More
Mysql - Serialization failure: 1213 Deadlock found when try to get lock; try restarting transaction

I have menus, categories and products tables. I am using mysql 5.5, All tables are innoDB and in all cases id is the primary key (int) with autoincrement. menus table id, name, status categories table id, menu_id, name products table …

Read More