[MySQL] 對不存在的 row 加鎖，會造成 Deadlock

[MySQL] 對不存在的 row 加鎖，會造成 Deadlock

一個範例

初始資料設定

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `users` (`id`, `name`)
VALUES
 (1, 'Jack'),
 (10, 'Johnson');

執行的時間軸

# Session 1
BEGIN;
SELECT * FROM users where id = 5 FOR UPDATE;
# Session 2
BEGIN;
SELECT * FROM users where id = 6 FOR UPDATE;
# Session 1
INSERT INTO users (id, name) VALUES (5, 'Jordan');
# Session 2
INSERT INTO users (id, name) VALUES (6, 'Jimmy');

會產生 deadlock。

說明

Session 1 在 SELECT * FROM users where id = 6 FOR UPDATE; 時，會對 (1,10) 區間加上 gap lock，而 Session 2 在 SELECT * FROM users where id = 6 FOR UPDATE; 時也會對 (1,10) 加上 gap lock，因為 gap lock 之間不會互斥，所以兩個 session 在這個時間點都拿到 gap lock 了。

之後 Session 1 的 INSERT INTO users (id, name) VALUES (5, ‘Jordan’); 需要拿到 Insert Intention Lock，但是需要等 Session 2 釋放它的 gap lock。

同理， Session 2 的 Insert Intention Lock 也需要等 Session 1 釋放 gap lock，而是就形成 deadlock 了。

參考資料

https://bugs.mysql.com/bug.php?id=25847

https://dev.mysql.com/doc/refman/5.6/en/innodb-locking.html

https://dev.mysql.com/doc/refman/5.6/en/innodb-information-schema-examples.html

https://time.geekbang.org/column/intro/139