這篇文章主要介紹了MySQL中InnoDB的間隙鎖問題,提醒用戶注意死鎖情況,需要的朋友可以參考下

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

1. mysql> SHOW CREATE TABLE preferences /G 
2. *************************** 1. row *************************** 
3. Table: preferences 
4. Create Table: CREATE TABLE `preferences` ( 
5. `numericId` int(10) unsigned NOT NULL, 
6. `receiveNotifications` tinyint(1) DEFAULT NULL, 
7. PRIMARY KEY (`numericId`) 
8. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 
9. 1 row in set (0.00 sec) 
10. mysql> BEGIN; 
11. Query OK, 0 rows affected (0.00 sec) 
12. mysql> SELECT COUNT(*) FROM preferences; 
13. +----------+ 
14. | COUNT(*) | 
15. +----------+ 
16. | 0 | 
17. +----------+ 
18. 1 row in set (0.01 sec) 
19. mysql> UPDATE preferences SET receiveNotifications='1' WHERE numericId = '2'; 
20. Query OK, 0 rows affected (0.01 sec) 
21. Rows matched: 0 Changed: 0 Warnings: 0 

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

1. ---TRANSACTION 4A18101, ACTIVE 12 sec 
2. 2 lock struct(s), heap size 376, 1 row lock(s) 
3. MySQL thread id 3, OS thread handle 0x7ff2200cd700, query id 35 localhost msandbox 
4. Trx read view will not see trx with id >= 4A18102, sees < 4A18102 
5. TABLE LOCK table `test`.`preferences` trx id 4A18101 lock mode IX 
6. 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是另一個，查詢的順序如下：

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

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