mysql - FOR UPDATE with IN 子句导致 LOCK WAIT TIMEOUT

Question

我在使用 SELECT ... FOR UPDATE 语句时遇到 LOCK WAIT TIMEOUT 问题。

我准备了我不明白的场景。你能告诉我为什么前三个 block 立即执行，但最后一个要等待吗？

谢谢。

-- I'm using MariaDb

-- T1 is the mysql terminal window 1
-- T2 is the mysql terminal window 2

CREATE TABLE `test` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL
) COMMENT='' ENGINE='InnoDB' COLLATE 'utf8_bin';

INSERT INTO test SET name='foo';

T2: START TRANSACTION;
T1: START TRANSACTION;
T1: SELECT * FROM test WHERE id IN (1) FOR UPDATE;
T2: INSERT INTO test SET name='foo'; -- executed immediately

T2: START TRANSACTION;
T1: START TRANSACTION;
T1: SELECT * FROM test WHERE id IN (1,2) FOR UPDATE;
T2: INSERT INTO test SET name='foo'; -- executed immediately

T2: START TRANSACTION;
T1: START TRANSACTION;
T1: SELECT * FROM test WHERE id IN (1,2,3) FOR UPDATE;
T2: INSERT INTO test SET name='foo'; -- executed immediately

T2: START TRANSACTION;
T1: START TRANSACTION;
T1: SELECT * FROM test WHERE id IN (1,2,3,4) FOR UPDATE;
T2: INSERT INTO test SET name='foo'; -- waits for T1 to commit

T2: commit;
T1: commit;

Answer 1

InnoDB 行级锁是 index-locks in reality .一个较少记录的特性^* 是:如果锁定 SELECT 不(或不能)使用(用户定义的)索引，则只能使用默认的聚集索引，并且整个表都被锁定。

优化器很可能决定索引对检查 IN() 参数的“长”列表没有用(可能是因为需要扫描表的大部分)。这会导致您检测到意外的副作用。

这个假设可以通过检查 execution plan 得到证实。 .