mysql並發insert deadlock分析以及解決，無delete/update/for update

標籤：

關於並發insert操作發生deadlock這個情況，一直有很多爭議，而且網上的文章所有的例證和類比其實不一定反映了真實的情況，例如：
https://www.percona.com/blog/2012/03/27/innodbs-gap-locks/
http://www.cnblogs.com/LBSer/p/5183300.html 
http://www.cnblogs.com/bamboos/p/3532150.html 
不少的類似例子應該來說其實不少有猜測和為了重現而重現的目的。
事實上開兩個視窗，不做delete/update/select for update，僅select/insert是100%可以重現死結情境的，也可以不出現。
start transaction A視窗select from X where x=1，不存在則insert X表記錄1。假設x存在索引。
start transaction B視窗select from X where x=2，不存在則insert X表記錄2。
這取決於當前使用的交易隔離等級，不同的交易隔離等級下有不同的行為。

 

 

 

 

A 會話
USE memdb;
SHOW VARIABLES LIKE ‘%autocommit%‘;
SET tx_isolation=‘SERIALIZABLE‘;
SHOW VARIABLES LIKE ‘%tx%‘;
START TRANSACTION;
SELECT * FROM `abcdefg` WHERE def=19 AND ghi=19 AND jkl=19;
-- 暫停，等B視窗前半部分執行了在執行下一部分
INSERT INTO `abcdefg`(abc,def,ghi,jkl,mnp) VALUES(‘19‘,19,19,19,1);
COMMIT;

 USE memdb;
SHOW VARIABLES LIKE ‘%autocommit%‘;
SET tx_isolation=‘SERIALIZABLE‘;
SHOW VARIABLES LIKE ‘%tx%‘;
START TRANSACTION;
SELECT * FROM `abcdefg` WHERE def=18 AND ghi=18 AND jkl=18;
-- 暫停，等A視窗前半部分執行了在執行下一部分
INSERT INTO `abcdefg`(abc,def,ghi,jkl,mnp) VALUES(‘18‘,18,18,18,1);
COMMIT; 

 

 

原因在mysql的行鎖實現主要分為記錄鎖、Gap鎖、Next-key鎖等。 這三種鎖定的範圍不同，並且行鎖不代表實現時鎖定了所有已存在的符合where中明確說明的條件的行，還有可能包括隱式、不存在的記錄。

 因為 SERIALIZABLE使用的是GAP鎖，所以，insert時>=當前索引的所有記錄都被加上了S鎖，故此問題發生 。

這也是我們遇到的，非常純粹的只有insert操作的死結的例子，對於使用oracle、sql server的開發人員來說時，遇到時有時候相當的不理解。

具體見各類鎖的區別http://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html。

mysql並發insert deadlock分析以及解決，無delete/update/for update