Search code examples
mysqlinnodbddldml

MySQL 5.7 alter table DDL statement locking, but should allow concurrent DML

The following query consists of changes that each should allow concurrent DML:

ALTER TABLE sometable
  DROP INDEX index1_on_column1,
  DROP INDEX index2_on_column2,
  DROP INDEX index3_on_column1_and_column2,
  DROP COLUMN column1,
  DROP COLUMN column2;

The table has about 80 million entries. When I ran the query it looks like it blocked access/locked.

Anyone know why/how this would have locked the table?

Adding an explicit LOCK=NONE should enforce that it can be done (or throw an error if it's unable), but from the docs it's not clear that it's a mandatory statement to prevent locking.

Solution

  • So basically, without explicity LOCK=NONE you can't be certain of how MySql will choose to perform the DML change. If it can't proceed without a lock and you specify LOCK=NONE you'll get an error when your run them command, but if you don't specify LOCK=NONE and locks are unneeded it's basically a toss up whether Mysql will execute the alter table.