CODEWITHSUNDEEP
mysqlinnodbddldml
Ray
Ray

Reputation: 41448

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.

Upvotes: 0

Views: 1304

Answers (2)

Yvan
Yvan

Reputation: 2699

For large tables, you should use parallel copy, to avoid write lock. Even if read is allowed, your whole process may be locked because of one write.

Tools such as Percona Toolkit are great at this. I've used it on 2GB tables (InnoDB with foreign keys), rebuilding took 3-5 minutes, but the table was unavailable for only few seconds.

Upvotes: 1

Ray
Ray

Reputation: 41448

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.

Upvotes: 0

Related Questions