MySQL:使用ALGORITHM=COPY等待元数据锁
我试图在MySQL中执行一个ALTER。MySQL只允许我使用ALGORITHM=COPY执行它(因为我需要更改列的类型)。
没有使用该表的查询(既不写也不读)。
但是，我不知道为什么，当我执行ALTER时，有一些查询(更新)没有使用这个表(它们在事务中)被锁定。MySQL说"mysql等待元数据锁“。
所以问题是，如果更新没有使用修改过的表，为什么查询要等待元数据锁呢？
我读了一些医生：
- https://dev.mysql.com/doc/refman/8.0/en/alter-table.html#alter-table-performance
- https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-performance.html#innodb-online-ddl-locking-options
但我不明白为什么查询会锁定元数据。
开发环境中问题的再现：
首先，做改变：
ALTER TABLE API.SEARCHES_ELEMENTS
MODIFY COLUMN TYPE ENUM('A', 'B') NOT NULL,
ALGORITHM=COPY;其次，更改其他表中的值(没有事务)：
UPDATE CLIENTS
SET NAME = CONCAT('test-', RAND())
WHERE ID_CLIENT = 1;锁：
SELECT *
FROM performance_schema.metadata_locks
INNER JOIN performance_schema.threads ON THREAD_ID = OWNER_THREAD_ID
WHERE
PROCESSLIST_ID <> CONNECTION_ID();
也许问题是因为模式上的锁？
回答 2
Stack Overflow用户
发布于 2022-08-11 12:12:19
似乎是为了避免ALGORITHM=COPY (不带就地模式的重构)
因此，而不是修改列类型
ALTER TABLE API.SEARCHES_ELEMENTS
MODIFY COLUMN TYPE ENUM('A', 'B') NOT NULL,
ALGORITHM=COPY;最好创建一个新列，复制数据并删除旧列：
ALTER TABLE API.SEARCHES_ELEMENTS
ADD COLUMN TYPE_NEW ENUM('A', 'B') NOT NULL AFTER TYPE,
ALGORITHM=INSTANT;
LOCK TABLES API.SEARCHES_ELEMENTS WRITE;
UPDATE API.SEARCHES_ELEMENTS SET TYPE_NEW = TYPE;
ALTER TABLE API.SEARCHES_ELEMENTS
RENAME COLUMN TYPE TO TYPE_OLD,
RENAME COLUMN TYPE_NEW TO TYPE,
ALGORITHM=INSTANT;
UNLOCK TABLES;
ALTER TABLE API.SEARCHES_ELEMENTS
DROP COLUMN TYPE_OLD,
ALGORITHM=INPLACE;注意:在ENUM中添加一个值可以使用algorithm=instant
通过向有效成员值列表的末尾添加新的枚举或SET成员来修改ENUM或set列的定义，只要数据类型的存储大小没有改变，就可以立即或在适当的位置执行。例如，将一个成员添加到具有8个成员的SET列中，将每个值所需的存储空间从1字节更改为2字节；这需要一个表副本。在列表中间添加成员将导致对现有成员进行重命名，这需要表副本。
Stack Overflow用户
发布于 2022-08-11 09:53:54
表元数据不仅在正在运行的查询使用时被锁定，而且如果以前在活动事务中使用过元数据，直到该事务提交或回滚，以防止表在仍然被事务引用时发生更改。
如果您正在运行至少MySQL 5.7并启用了performance_schema，则可以通过performance_schema.metadata_locks表检查当前元数据锁。
另请参阅：
https://dev.mysql.com/doc/refman/5.7/en/performance-schema-metadata-locks-table.html
https://dev.mysql.com/doc/refman/5.7/en/metadata-locking.html
https://stackoverflow.com/questions/73318275
复制相似问题
腾讯云开发者
