MySQL failing to ALTER TABLE which is being actively written to

Question

In one of the use cases of my application, I have two concurrent MySQL connections:

• one actively writing to a table named T (actually, continuously updating a single row in this table), and
• another one executing a DDL against the very same table (ALTER TABLE, adding 8 new columns and extending one column from varchar(80) to varchar(2000)). The DDL is expected to eventually complete.

The columns in the UPDATE DML are not affected by the DDL.

The table contains only a single row (the one being UPDATE'd).

Analysis

What I observe when an integration test covering this use case is run is a test time out (the table is being so actively written to, so that the DDL never completes), but only for MySQL 5.7. Normally, the test is expected to complete in under 30 seconds on our hardware (which indeed happens for MySQL 5.6 and 8.0), but for MySQL 5.7 even 200 seconds is not sufficient. I have experimented with different ALGORITHM and LOCK values (see 13.1.8 ALTER TABLE Syntax), with no luck.

When I profile my application (MySQL 5.7 case), I observe that 99% of CPU time is spent reading from a socket (i. e. waiting for MySQL to respond that the table has been altered), but the database instance is a sort of a black box to me -- of course I have performance_schema enabled and can run queries against it, but I have no idea which exact information I am looking for.

Synthesis

At the same time, I failed to reduce the problem to a minimal self-contained unit test -- the only thing I observe is 3x to 10x increase in test elapsed time for MySQL 5.7 compared to other MySQL versions, but the DDL doesn't hang forever:

All MySQL versions are either stock versions for Windows or Debian Linux downloaded from www.mysql.com with minimal changes to my.cnf, or the official Docker images.

Questions:

1. Is it indeed technically possible for MySQL to delay the execution of ALTER TABLE DDL forever? Or what I'm observing is just a very busy database instance? Is it possible to either
   • request that ALTER TABLE is executed interruptibly, i. e. an error is returned by the database if a certain time-out is exceeded, or
   • force all other connections which can potentially place even a SHARED lock on the table or some of its rows to pause, so that they don't intervene while the DDL is being executed?
2. When dealing with the original integration test timing out, how can I further diagnose the situation from MySQL side?

Answer 1

TL;DR — commit your transactions to unblock your ALTER TABLE.

---

Yes, ALTER TABLE can block for a long time. It may seem like forever. It's actually the value of lock_wait_timeout, which is 31536000 seconds by default, or 365 days.

In MySQL, DDL statements like ALTER TABLE require an exclusive metadata lock on the table. The purpose is to make sure you don't ALTER TABLE from two concurrent sessions at the same time.

DML statement like SELECT, INSERT, UPDATE, DELETE also hold "shared" metadata locks. Shared locks can be held by multiple sessions concurrently, but block exclusive locks, because exclusive locks require that they be the only one to hold any type of lock on the table.

Documentation states:

This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.

The purpose of DML statements holding a metadata lock is so they can preserve their repeatable-read view of the table without worry that another session is doing DROP TABLE or ALTER TABLE to compromise their view of the table. This locking is necessary because MySQL does not have versioned metadata (they are gradually working toward that).

This means a transaction that has run a simple SELECT and doesn't commit will block a DROP TABLE or ALTER TABLE that requires a locking change.

There is some nuance with the introduction of online DDL.

Online DDL Performance and Concurrency describes in more detail that an ALTER TABLE starts out by acquiring a shared metadata lock, so the uncommitted transaction will not block it. But the next phase may upgrade the shared metadata lock to an exclusive metadata lock, if the nature of the ALTER TABLE change requires it. At this point, the lock acquisition is blocked because the other transaction still holds its own metadata lock.

Online DDL doesn't apply to every type of ALTER TABLE operation; some still require exclusive locks. Changing a data type, for example, as you are doing, requires an exclusive lock. See Online DDL Overview for details.