MySQL锁定和隔离级别

Question

有人能帮我理解MySQL的行为吗？

我试图使用以下语句在表中添加一个新列：

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

结果是，整个应用程序开始接收锁定元数据错误，而语句从未完成。

该表非常小；起初我认为操作很重，因为FKs与其他表、索引重建或其他相关。

然后，我试图将隔离级别从改为，一旦我再次运行alter，它几乎立即结束了。

现在我知道锁定与事务所使用的表有关。但有些事情我还没有忘记：

如果行被用于其他读取事务，并且无法获得联机DDL所需的排他锁，那么为什么所有读取事务都被锁定，而不仅仅是DDL操作？我原以为只有DDL操作需要更多的时间才能完成，因为它必须等待其他事务的完成。

非常感谢

Answer 1

在你的第二次测试中，这个改动很快就完成了，这是一个巧合。事务隔离级别与元数据锁定无关。

这里有一个演示来证明这一点：

窗口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 |
+----+

我现在在window 1中有一个活动事务快照，这意味着事务保存在该表上的一个元数据锁上。

窗口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)

窗口1：

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

这表明DDL需要启动表上的元数据锁，而不管事务隔离级别如何。事务隔离级别只影响InnoDB行级锁定，因为InnoDB事务是在存储引擎层中处理的。但是元数据锁定是在MySQL的SQL层处理的，而不是在存储引擎层中处理的。

所以发生的情况是，有一个长期运行的事务在您的表上持有一个元数据锁。所有事务都持有共享元数据锁。因此事务可以并发运行，因为它们不相互阻塞。但是，当您试图更改表时，它请求一个独占元数据锁。也就是说，它必须是唯一持有元数据锁的。它等待所有当前事务完成并释放它们的元数据锁。因此，任何仍然持有自己的元数据锁的事务都将阻塞ALTER (或任何其他DDL)。

然后，由于ALTER语句“在队列中”等待独占元数据锁，因此它阻止请求共享元数据锁的所有后续事务。因此，您在应用程序中看到的即时堆积。

元数据锁请求在lock_wait_timeout秒之后超时，默认为31536000秒或一年。

我相信在您的第二个测试中所发生的事情是，无论什么长时间运行的事务在完成之前阻塞了ALTER，所以没有理由阻止ALTER。它立即进行，在MySQL的最新版本中，添加列是一个瞬间的更改。