3

Could someone help me to understand this MySQL behavior?

I was trying to add a new column on a table using this statement:

ALTER TABLE `tb_example`
    ADD COLUMN `score` INT NULL,
    ALGORITHM = INPLACE,
    LOCK = NONE;

The result was, the entire application started receiving locking metadata errors and the statement was never completed.

The table is very small; at first I thought that the operation was heavy because of FKs related to other tables, index rebuild or something.

Then I tried to change the isolation level from read commited to read uncommited, once I ran the alter table again, it concluded almost instantly.

Now I know that the locking was related to the table being used by transactions. But some things I didn't undestood yet:

If the rows was being used for other read transactions and the exclusive lock needed by the online DDL could not be acquired, why were all read transactions locked and not only the DDL operation? I was expecting only the DDL operation would take more time to finish, because it will have to wait for the other transactions finish.

Thank you a lot

1 Answer 1

2

The fact that the ALTER finished quickly on your second test is a coincidence. The transaction isolation level has nothing to do with metadata locking.

Here's a demonstration to prove it:

Window 1:

mysql> create table tb_example (id serial primary key);
mysql> insert into tb_example () values ();
mysql> begin;
mysql> select * from tb_example;
+----+
| id |
+----+
|  1 |
+----+

I now have an active transaction snapshot in window 1, which means that transaction is holding onto a metadata lock on that table.

Window 2:

mysql> set transaction_isolation = 'read-uncommitted';
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE `tb_example`     ADD COLUMN `score` INT NULL,     ALGORITHM = INPLACE,     LOCK = NONE;
(hangs)

Window 1:

mysql> show processlist;
...
| 24 | root            | localhost | test | Query   |       5 | Waiting for table metadata lock | ALTER TABLE `tb_example` ...
...

This shows that DDL requires a metadata lock on the table to start, regardless of the transaction isolation level. Transaction isolation level only affects InnoDB row-level locking, because InnoDB transactions are handled in the storage engine layer. But metadata locking is handled in the SQL layer of MySQL, not in the storage engine layer.

So what happened is that there was a long-running transaction holding a metadata lock on your table. All transactions hold a shared metadata lock. So transactions can run concurrently because they don't block each other. But when you tried to ALTER the table, it requested an exclusive metadata lock. Meaning, it must be the only one to hold a metadata lock. It waits for all current transactions to finish and release their metadata locks. So any transaction still holding its own metadata lock will block ALTER TABLE (or any other DDL).

Then, because the ALTER TABLE statement was "in the queue" waiting for an exclusive metadata lock, it blocks all subsequent transactions who request a shared metadata lock. Thus the immediate pileup you saw in your application.

Metadata lock requests time out after lock_wait_timeout seconds, which defaults to 31536000 seconds, or one year.

I believe what happened on your second test is that whatever long-running transaction was blocking the ALTER before had finished, so there was no reason for the ALTER to be blocked. It proceeded immediately, and in recent versions of MySQL, adding a column is an instant change.

1
  • Thank you A LOT for the explanation! Jan 20, 2023 at 11:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.