MySql 插入時的間隙鎖死鎖

當從多個來源頻繁插入表時，我從表上的間隙鎖中得到死鎖。這是我的流程的概述。

START TRANSACTION
 UPDATE vehicle_image
 SET active = 0
 WHERE vehicleID = SOMEID AND active = 1

 Loop:
   INSERT INTO vehicle_image (vehicleID, vehicleImageFilePath, vehicleImageSplashFilePath
     ,vehicleImageThumbnailFilePath, vehicleImageMiniFilePath, mainVehicleImage, active)
   VALUES (%s, %s, %s, %s, %s, %s, 1);
END TRANSACTION

的輸出SHOW Create table vehicle_image;是：

CREATE TABLE `vehicle_image` (
 `vehicleImageID` int(11) NOT NULL AUTO_INCREMENT,
 `vehicleID` int(11) DEFAULT NULL,
 `vehicleImageFilePath` varchar(200) DEFAULT NULL,
 `vehicleImageSplashFilePath` varchar(200) DEFAULT NULL,
 `vehicleImageThumbnailFilePath` varchar(200) DEFAULT NULL,
 `vehicleImageMiniFilePath` varchar(200) DEFAULT NULL,
 `mainVehicleImage` bit(1) DEFAULT NULL,
 `active` bit(1) DEFAULT b'1',
 `userCreated` int(11) DEFAULT NULL,
 `dateCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `userModified` int(11) DEFAULT NULL,
 `dateModified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 PRIMARY KEY (`vehicleImageID`),
 KEY `active` (`active`),
 KEY `mainvehicleimage` (`mainVehicleImage`),
 KEY `vehicleid` (`vehicleID`)
) ENGINE=InnoDB AUTO_INCREMENT=22878102 DEFAULT CHARSET=latin1

最後一個死鎖由SHOW engine innodb status：

LATEST DETECTED DEADLOCK
------------------------
2018-03-27 12:31:15 11a58
*** (1) TRANSACTION:
TRANSACTION 5897678083, ACTIVE 2 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 873570, OS thread handle 0x124bc, query id 198983754 ec2-34-239-240-179.compute-1.amazonaws.com 34.239.240.179 image_processor update
INSERT INTO vehicle_image (vehicleID, vehicleImageFilePath, vehicleImageSplashFilePath, vehicleImageThumbnailFilePath, vehicleImageMiniFilePath, mainVehicleImage, active)
VALUES (70006176, 'f180928(1)1522168276.230837full.jpg', 'f180928(1)1522168276.230837splash.jpg', 'f180928(1)1522168276.230837thumb.jpg', 'f180928(1)1522168276.230837mini.jpg', 1, 1)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 875 page no 238326 n bits 472
 index `vehicleid` of table `ipacket`.`vehicle_image` trx id 5897678083
 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 378 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 842c365a; asc  ,6Z;;
1: len 4; hex 815d03bc; asc  ]  ;;

*** (2) TRANSACTION:
TRANSACTION 5897678270, ACTIVE 1 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 873571, OS thread handle 0x11a58, query id 198983849 ec2-35-171-169-21.compute-1.amazonaws.com 35.171.169.21 image_processor update
INSERT INTO vehicle_image (vehicleID, vehicleImageFilePath, vehicleImageSplashFilePath, vehicleImageThumbnailFilePath, vehicleImageMiniFilePath, mainVehicleImage, active)
VALUES (70006326, '29709(1)1522168277.4443843full.jpg', '29709(1)1522168277.4443843splash.jpg', '29709(1)1522168277.4443843thumb.jpg', '29709(1)1522168277.4443843mini.jpg', 1, 1)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 875 page no 238326 n bits 464
 index `vehicleid` of table `ipacket`.`vehicle_image` trx id 5897678270
 lock_mode X locks gap before rec
Record lock, heap no 378 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 842c365a; asc  ,6Z;;
1: len 4; hex 815d03bc; asc  ]  ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 875 page no 238326 n bits 472
 index `vehicleid` of table `ipacket`.`vehicle_image` trx id 5897678270
 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 378 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 842c365a; asc  ,6Z;;
1: len 4; hex 815d03bc; asc  ]  ;;

*** WE ROLL BACK TRANSACTION (2)

我同時執行許多這些程序，但從不執行使用相同的兩個程序VehicleID。我真的很困惑為什麼我會遇到死鎖。

我已經通過使用 Isolation level暫時解決了這個問題READ COMMITTED，但我已經讀到這需要更改複製，因為您必須進行行級複製。

我在這裡閱讀了與我類似的其他問題，但我對 SQL 有點陌生，仍然無法理解為什麼會發生這種情況。

類似的問題：

• MySQL 插入語句上的
  死鎖
• MySQL InnoDB 死鎖 對於 2 個簡單的插入查詢

更新：

我發現 usingREAD COMMITTED並沒有真正解決這個問題。我仍然沒有弄清楚為什麼會發生死鎖，而且我真的不知道如何進一步診斷。我的生產系統中繼續出現死鎖。任何幫助，將不勝感激。

我不是 MySQL 專家，但從您的死鎖日誌來看，即使您在每個語句中插入不同的車輛 ID，也需要將**非聚集索引的****整個數據頁 (238326)VehicleID**鎖定。

您偶爾會遇到死鎖的事實意味著在 1 個頁面內您有多個車輛 ID，因此 2 個不同程序需要鎖定同一頁面的可能性很小。

最好的建議是讓您的交易盡可能小。

如果有某種方法可以執行以下操作，它將有助於減少死鎖的機會：

START TRANSACTION;
 UPDATE vehicle_image SET active = 0 WHERE vehicleID = SOMEID and active = 1;
END TRANSACTION;
Loop:
 START TRANSACTION;
 INSERT INTO vehicle_image (vehicleID, vehicleImageFilePath,
   vehicleImageSplashFilePath, vehicleImageThumbnailFilePath,
   vehicleImageMiniFilePath, mainVehicleImage, active)
 VALUES (%s, %s, %s, %s, %s, %s, 1);  
 END TRANSACTION;
--EndLoop here

如果可以，請嘗試將該索引的填充因子更改為 95%，並測試您是否獲得更少的死鎖。

一個更極端的測試是在插入時完全刪除該索引，然後在完成後重新創建它。

引用自：https://dba.stackexchange.com/questions/202455