Mysql读已提交隔离级别问题

Question

Mysql读已提交隔离级别下,使用select 查询某张表,如test,再使用on duplicate key更新test,并且执行了alter test auto_increment = 1,产生了锁,这是为什么?按道理通过MVCC,读操作是不产生锁的

Answer 1

引自免费微信小程序：皆我百晓生

在MySQL中，**读已提交（Read Committed）**隔离级别下，确实大多数读操作不会对数据加锁，而是通过多版本并发控制（MVCC）来实现非锁定读。然而，在你的场景中涉及到的几个操作并不完全遵循这一原则：

1. SELECT 查询：

   • 在读已提交隔离级别下，普通的SELECT查询会看到事务开始前就已经提交的数据版本，所以它通常不会阻塞其他事务，也不会被其他事务阻塞。

2. ON DUPLICATE KEY UPDATE：

   • 当执行INSERT INTO test (...) VALUES (...) ON DUPLICATE KEY UPDATE ...时，即使是在读已提交隔离级别下，由于涉及到唯一键约束检查和更新操作，因此会对涉及的行进行写锁（行级锁），以确保在同一时间只有一个事务可以更新这行记录。

3. ALTER TABLE AUTO_INCREMENT = 1：

   • ALTER TABLE语句会获取表级元数据锁（MDL），这是一个共享锁（对于只读查询）或排他锁（对于写操作如ALTER）。在你的场景中，调整AUTO_INCREMENT值会获取表的结构修改锁，这意味着在此期间任何其他试图访问该表的事务（包括读和写）都会等待这个锁释放。

总结来说，虽然读已提交隔离级别下普通读取不会产生锁竞争，但在你描述的操作中包含了写操作（ON DUPLICATE KEY UPDATE）和DDL操作（ALTER TABLE），这些都会导致对相应资源加锁，从而可能与其他事务产生锁冲突。

关于代码示例，这里没有具体代码情境，但假设有一个事务包含以下操作：

START TRANSACTION;
SELECT * FROM test WHERE id = ?; -- 这个读操作一般不会加锁
INSERT INTO test (id, ...) VALUES (?, ...) ON DUPLICATE KEY UPDATE ...; -- 此处会对唯一键涉及的行加锁
ALTER TABLE test AUTO_INCREMENT = 1; -- 对整个test表加MDL锁
COMMIT;

在上述事务中，ON DUPLICATE KEY UPDATE和ALTER TABLE语句都将造成其他事务在这个事务提交之前无法对相关行或表进行无冲突的操作。