Dec 27, 2019
1 min read
MySQL add CONSTRAINT without lock
Want to avoid ADD CONSTRAINT that take ages and delay users’ transactions ? Your database gets into metadata lock and “copying to tmp table” ? Other update are stuck in “Waiting for table metadata lock”
Here is how to fix that :
To add a column youn eed to specify inplace algorythm :
ALTER TABLE table1 ADD column30 DATETIME DEFAULT NULL, algorithm=inplace, lock=none;For constraint you only need to deactivate foreing key check and MySQL will use the inplace algorithme. The folowwing works on a MySQL 5.6 running on an AWS Aurora :
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE table_a ADD CONSTRAINT FK_1234567890ABCDEF FOREIGN KEY (column_id) REFERENCES table_b(id) ON DELETE CASCADE;
SET FOREIGN_KEY_CHECKS = 1;Source :
The INPLACE algorithm is supported when foreign_key_checks is disabled. Otherwise, only the COPY algorithm is supported.
InnoDB and Online DDL :
