When I do alter table commands Im getting lots of dead locks. Where commands are waiting for metadata lock on tables that are not part of the alter table statement. The tables are linked through a foreign key but we are not changing the foreign key.
Is there a way to run alter tables without bringing mysql to its knees?
Minimal Example
Given these two tables tables with a foreign key relationship.
create table parent(id int primary key);
create table child(
id int primary key,
`parent_id` int,
constraint `parent_id_fk` foreign key (`parent_id`) references `parent` (`id`)
);
When a connection reads some data it acquires a metadata locks.
start transaction read only;
select * from child;
-- commit later
A seperate connection then tries to run an alter statement on the parent table. This wants a metadata lock on child even though we are not touching child or the id.
alter table parent add column x int null;
We can see these locks in
mysql> select * from performance_schema.metadata_locks ;
+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+
| TABLE | performance_schema | metadata_locks | NULL | 47604062188640 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6213 | 24981 | 15 |
| GLOBAL | NULL | NULL | NULL | 47604063432464 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | sql_base.cc:5412 | 23547 | 607 |
| BACKUP LOCK | NULL | NULL | NULL | 47604007764640 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5419 | 23547 | 607 |
| SCHEMA | test | NULL | NULL | 47604063432224 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5399 | 23547 | 607 |
| TABLE | test | parent | NULL | 47604068904032 | SHARED_UPGRADABLE | TRANSACTION | GRANTED | sql_parse.cc:6213 | 23547 | 607 |
| TABLESPACE | NULL | test/parent | NULL | 47604068895072 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | lock.cc:793 | 23547 | 607 |
| SCHEMA | test | NULL | NULL | 47604068902992 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | sql_table.cc:1117 | 23547 | 607 |
| TABLE | test | child | NULL | 47604007764800 | SHARED_UPGRADABLE | STATEMENT | GRANTED | sql_table.cc:1109 | 23547 | 607 |
| TABLE | test | #sql-1f53_5ad9 | NULL | 47604063517984 | EXCLUSIVE | STATEMENT | GRANTED | sql_table.cc:16153 | 23547 | 607 |
| TABLE | test | child | NULL | 47604063429264 | EXCLUSIVE | STATEMENT | PENDING | sql_table.cc:1109 | 23547 | 608 |
| TABLE | test | child | NULL | 47604010642320 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6213 | 24466 | 120 |
Dead locks in production
In our production system which has more tables and foreign keys we are seeing dead locks, where the alter table acquires a locks for table A but cant get table B. Table B is locked by a thread that needs table A. For us its a bit of a race condition, but its exacerbated by mysql needing a lock on all of the linked tables instead of just the one is is altering.
This seems to be new behaviour of mysql 8. I wish I didnt upgrade. We are on mysql 8.0.20 with innodb tables.
Is there a way to run these alters without locking up the database ? Ideally I want to keep my foreign keys and I dont want to use a migration manager like Percona. My tables arent even that big.
Thanks
