ＭySQL 你到底是在鎖屁喔

剛剛在研究這個簡單的範例：

CREATE TABLE `posts` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `content` varchar(255) DEFAULT NULL,
 `user_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `index_posts_on_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `posts` (`id`, `content`, `user_id`)
VALUES
 (1,’All Might’,1),
 (2,’All Might’,2),
 (3,’All Might’,3),
 (4,’All Might’,4),
 (5,’All Might’,5);

Session A：

BEGIN;
SELECT `posts`.* FROM `posts` WHERE `posts`.`user_id` IN (1,2) FOR UPDATE;

Session B：

SELECT `posts`.* FROM `posts` WHERE `posts`.`user_id` = 2 FOR UPDATE

當 Session A 還沒結束 transaction 時，Session B 進入了 Lock Wait。posts`.`user_id有打 index，照理來說應該不會鎖住才對，因為走 index_posts_on_user_id 的 index tree 根本不會碰到 user_id = 5 的 index。

追查了之後才發現，因為表太小，MySQL 沒有用 index_posts_on_user_id index，而是走 PRIMARY KEY 的 index tree。所以就掃描整張表，導致所有 record 都被鎖住了。

可以把上面範例中的 Session A ，分別改用這兩種來比較就知道差異了：

BEGIN;
SELECT `posts`.* FROM `posts` FORCE INDEX(PRIMARY) WHERE `posts`.`user_id` IN (1,2) FOR UPDATE;
BEGIN;
SELECT `posts`.* FROM `posts` FORCE INDEX(index_posts_on_user_id) WHERE `posts`.`user_id` IN (1,2) FOR UPDATE;