MySQL 8.0 Metadata Lock Extension for Foreign Keys causing requests to hang

We are using AWS RDS and we upgraded from MySQL 5.6.41 to MySQL 8.0.21 last December.

Since mid-January, we noticed some problems causing our replica to be stuck after running migrations adding foreign keys to new tables referencing our main table (in the sense that it is queried for almost all API calls).

Typically, we can have the following Django migration:

migrations.AddField(
        model_name='artifactpresenter',
        name='event',
        field=models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, to='pittsburgh.Event'),
    ),

This would translate to some kind of ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY ... REFERENCES ... DDL query (verified by checking the SQL generated by the Django ORM).

When running this migration on production, it would execute fine on our master that mostly processes write requests and a small portion of our read queries. However, our replica will suddenly hang on the referenced table (pittsburgh.Event in the above migration).

More specifically, here is what we can see from RDS Performance Insights:

As you can see, all the read requests to this pittsburgh.Event model would just hang on the MDL_context lock (a.k.a., the table metadata lock).

When this happens, our entire production pretty much goes down and our only solution at the moment is to restart the replica.

We first thought this was coming from multi-threaded replication that we inadvertently enabled. However, the issue persisted after we disabled multithreaded replication.

After digging more, I was actually able to reproduce the issue with 3 sessions:

• In one session, I start a transaction and read something from the pittsburgh.Event table, but I do not commit or rollback the transaction yet: I just leave it opened

• In another session, I run a CREATE TABLE statement adding a foreign key constraint referencing pittsburgh.Event (autocommit enabled): this statement will just hang, waiting to acquire the metadata lock on the `pit

You have two alternatives to resolve this issue:

-
COMMIT your transactions promptly. Even if the transaction only does read-only SELECT queries, it holds a metadata lock until the transaction is committed. This blocks DDL queries including the "indirect" DDL when a CREATE TABLE makes reference to the Event table.

Note that there are other similar cases, for example CREATE TRIGGER for a given table doesn't change that table, but still acquires a metadata lock. This case of metadata lock has been implemented in several past MySQL 5.x versions.

Keep your transactions as short as possible.

-
DROP the foreign key constraints. The locking caused by foreign keys often hinders concurrent access to the tables. Not only for the metadata locking issue that you're dealing with today, but another example is when you UPDATE a child table, it places a shared row lock on the parent table's row referenced by the child rows you updated. This blocks statements that require exclusive locks, such as UPDATE/DELETE on those rows in the parent table.

It's very common for sites that use MySQL to eventually give up using foreign key constraints, even though they are so useful for ensuring data integrity. They cause too many problems when you have to handle concurrent queries.

This does mean that the database will have no data integrity enforcement. That's unfortunate, and I feel bad for giving this suggestion because I am in favor of data integrity, and there's just no reliable substitute for enforcing data integrity in the database.

StackExchange Database Administrators Q#286250, answer score: 3