一個需要避免的 InnoDB 間隙鎖

在為一個客戶排除死結問題時我遇到了一個有趣的包括InnoDB間隙鎖的情形。對於一個WHERE子句不匹配任何行的非插入的寫操作中，我預期事務應該不會有鎖，但我錯了。讓我們看一下這張表及樣本UPDATE。

mysql> SHOW CREATE TABLE preferences \G
*************************** 1. row ***************************
      Table: preferences
Create Table: CREATE TABLE `preferences` (
  `numericId` int(10) unsigned NOT NULL,
  `receiveNotifications` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`numericId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT COUNT(*) FROM preferences;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)
mysql> UPDATE preferences SET receiveNotifications='1' WHERE numericId = '2';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0  Changed: 0  Warnings: 0

InnoDB狀態顯示這個UPDATE在主索引記錄上持有了一個X鎖：

---TRANSACTION 4A18101, ACTIVE 12 sec
2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 3, OS thread handle 0x7ff2200cd700, query id 35 localhost msandbox
Trx read view will not see trx with id >= 4A18102, sees < 4A18102
TABLE LOCK table `test`.`preferences` trx id 4A18101 lock mode IX
RECORD LOCKS space id 31766 page no 3 n bits 72 index `PRIMARY` of table `test`.`preferences` trx id 4A18101 lock_mode X

這是為什麼呢，Heikki在其bug報告中做瞭解釋，這很有意義，我知道修複起來很困難，但略帶厭惡地我又希望它能被差異化處理。為完成這篇文章，讓我證明下上面說到的死結情況，下面中mysql1是第一個會話，mysql2是另一個，查詢的順序如下：

mysql1> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql1> UPDATE preferences SET receiveNotifications='1' WHERE numericId = '1';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql2> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql2> UPDATE preferences SET receiveNotifications='1' WHERE numericId = '2';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql1> INSERT INTO preferences (numericId, receiveNotifications) VALUES ('1', '1'); -- This one goes into LOCK WAIT
mysql2> INSERT INTO preferences (numericId, receiveNotifications) VALUES ('2', '1');
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

現在你看到導致死結是多麼的容易，因此一定要避免這種情況——如果來自於事務的INSERT部分導致非插入的寫操作可能不匹配任何行的話，不要這樣做，使用REPLACE INTO或使用READ-COMMITTED事務隔離。

推薦閱讀：

InnoDB儲存引擎的啟動、關閉與恢複

MySQL InnoDB獨立資料表空間的配置

MySQL Server 層和 InnoDB 引擎層 體繫結構圖

InnoDB 死結案例解析

MySQL Innodb獨立資料表空間的配置