Mysql 8 metadata locks on foreign key tables during alter

Question

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

Answer 1

To make your ALTER TABLE statement do no locking, or return an error if the operations being performed are not possible without locking, do:

ALTER TABLE ..., LOCK=NONE;

Answer 2

This is caused by the new metadata locking from MySQL 8, which now extends to foreign keys.

You can try setting max_write_lock_count to a lower number. There are more details in this post.

We tried this approach, which worked for some things, like DROP TABLE, but didn't work with ADD FOREIGN KEY.