MySql Gap Lock Deadlock on Inserts

Question

I'm getting Deadlocks from gap locks on a table when inserting into it frequently from multiple sources. Here is an overview of my processes.

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

The output of SHOW Create table vehicle_image; is:

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

And the last Deadlock given by 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)

I am running many of these processes simultaneously but never running two process that are using the same VehicleID. I'm really confused as to why I'm getting Deadlocks.

I have temporarily solved the problem by using the Isolation level READ COMMITTED, but I have read that this requires changes to replication in that you must do row level replication.

I have read other questions on here that are similar to mine, but I am somewhat new to SQL and still cannot understand Why this is occurring.

Similar Questions:
- Deadlock on MySQL insert statments
- MySQL InnoDB Deadlock For 2 simple insert queries

UPDATE:

I found out that using READ COMMITTED has not actually solved the issue. I still haven't figured out why the deadlocks are occurring and I really don't know how to diagnose any further than I currently have. I'm continuing to get Deadlocks in my production system. Any help would be appreciated.

Oreo · Accepted Answer · 2024-11-27T13:03:56.650

I'm not a MySQL expert, but by the look of your Deadlock logs,
even though you are INSERTing different vehicle IDs per statement,
those require the whole datapage (238326) of the VehicleID non-clustered index to be locked.

The fact you are occasionally getting deadlocks means that
within 1 page you have multiple vehicle IDs,
so there is a small chance that 2 different processes will need a lock for the same page.

Best thing to advise is to keep your transactions as small as possible.

If there is some way you can do the following, it'll help lessen the chance of a deadlock:

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

If you can, try to change that index's fill factor to 95%, and test to see if you get fewer deadlocks.

A more extreme test would be to remove that index completely while INSERTing,
then recreate it when done.

score 3 · Answer 2 · answered Apr 06 '18 at 08:06

MySQL not only locks the affected row, but also the affected index row and the gap between the index rows (as described here). Since primary keys are always indexed and you use them in your updates I suspect that multiple transactions trying to update multiple rows each result in overlapping index gap locks which in turn create the deadlock.

To resolve this I also recommend Oreos advice to keep a transaction as small as possible. If the updated rows are independent from each other you should use a seperate transaction for each of them.

score 3 · Answer 3 · answered Jul 31 '20 at 10:30

The problem is the gap lock in MySQL in Repeatable Read isolation level.

In your first UPDATE statement, it will hold the gap locks before & after SOMEID. This will prevent insert into these gaps from other transactions. The two different transactions can hold gap lock on same gap, but each transaction will block other's insert statement. That's why the deadlock occurs.

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

The solution is as @Oreo mentioned, you could separate them into two transactions.

However, I could not understand why the deadlock still occurs in Read Committed mode. Per MySQL official document , the gap lock is disabled in READ COMMITTED. Maybe you could provide more logs in that situation, and we could look into it further.

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.

score 1 · Answer 4 · answered Oct 13 '24 at 21:41

The gap lock on the vehicleID index is causing this problem. Two transactions can acquire X gap lock on the same gap. Since you are trying to insert post the update query the lock persists. So you have 3 ways to solve it:

VehicleImageID is the autoincrement ID/Primary key in this case. Use a select query to first fetch the primaryKeys to update. And then run update on the primary key. This will prevent the gap lock.
Use READ COMMITTED isolation level which will prevent gap lock in this case.
Use 2 different transactions for update and insert if you are absolutely sure that there would never be 2 concurrent queries updating/inserting the same vehicleID entry.

MySql Gap Lock Deadlock on Inserts

4 Answers4

Linked