插入上的MySql间隙锁死锁

Question

当经常从多个来源插入到表中时，我会从表上的间隙锁中获得死锁。以下是我的流程概述。

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的两个进程。我真的很困惑为什么我会陷入僵局。

我已经通过使用隔离级别READ COMMITTED暂时解决了这个问题，但是我读到这需要对复制进行更改，因为您必须进行行级复制。

我在这里读过与我类似的其他问题，但我对SQL有点陌生，仍然无法理解为什么会发生这种情况。

类似的问题：

• MySQL插入状态的死锁

• 两个简单插入查询的MySQL InnoDB死锁

更新：

我发现使用READ COMMITTED并没有真正解决这个问题。我仍然没有弄清楚为什么会出现死锁，而且我真的不知道如何比现在更深入地进行诊断。我的生产系统继续出现死锁。任何帮助都将不胜感激。

Answer 1

我不是MySQL专家，但是从死锁日志的角度来看，即使每个语句是INSERTing不同的车辆ID，这些都需要锁定VehicleID非聚集索引的全部数据(238326)。

您偶尔会遇到死锁，这意味着在1页内您有多个车辆ID，因此两个不同的进程需要同一个页面的锁的可能性很小。

最好的建议是保持你的交易尽可能小。

如果有什么方法可以这样做，它将有助于减少出现死锁的可能性：

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%，并测试是否获得更少的死锁。

更极端的测试将是在INSERTing时完全删除该索引，然后在完成时重新创建该索引。

Answer 2

MySQL不仅锁定受影响的行，还锁定受影响的索引行和索引行之间的间隙(如这里所描述的)。由于主键总是被索引，并且在更新中使用它们，所以我怀疑试图更新多行的多个事务会导致重叠的索引间隙锁，从而导致死锁。

为了解决这个问题，我还建议奥利奥建议保持一个尽可能小的交易。如果更新的行彼此独立，则应对每个行使用单独的事务。

Answer 3

问题是MySQL中的间隙锁定在可重复读取隔离级别上。

在您的第一个UPDATE语句中，它将在SOMEID之前和之后保持间隙锁。这将防止insert从其他事务进入这些空白。这两个不同的事务可以在相同的gap上持有gap锁，但是每个事务都会阻止其他事务的insert语句。这就是为什么会出现僵局。

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

解决方案是@Oreo提到的，您可以将它们分为两个事务。

但是，我不明白为什么死锁仍然发生在Read Committed模式中。根据MySQL正式文件，在READ提交中禁用间隙锁。也许您可以在这种情况下提供更多的日志，我们可以进一步研究它。

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED. Under these circumstances, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.