8

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.

  • Could you please give us some more detail - in particular: Disk config, number of platters, performance characteristics? RAM, how much? CPU, number and performance? Number of transactions per second, per minute, per hour and per day? Do these rates vary over time? How, exactly, are these deadlocks affecting performance? Give us the output of SHOW PROCESSLIST;. Most of the time, REPEATABLE READ is the best isolation level for most apps, so I wouldn't be too concerned about using it. Was there a noticeable performance increase when you changed it from the default - REPEATABLE READ? – Vérace Apr 6 '18 at 1:19
  • How can that work? You have unquoted strings going into VARCHARs. – Rick James Apr 10 '18 at 0:57
  • Where's the END LOOP? – Rick James Apr 10 '18 at 1:01
  • @RickJames I do not have unquoted strings going into VARCHARS, the queries work as expected when ran 95% of the time. The END LOOP is denoted by the tabbing back to the same level. E.g. The loop starts, I run that insert statement multiple times, then the loop ends and transaction ends. Note that the loop is just pseudocode to represent what is taking place. – Brian Sizemore Apr 10 '18 at 13:42
  • @Vérace Repeatable Read is the default for this table (using innodb engine). I actually tested changing it from repeatable read to read committed which is a lower isolation level then repeatable read, but unfortunately it did not stop the deadlocks. I know that the hardware will affect the server (its an ec2 instance, i'd have to look up specifics) but I don't think that that information would be necessary to understand why the deadlocks are occuring. The sporadic nature of this also makes it hard to capture the output of show processlist; when the deadlock occurs. – Brian Sizemore Apr 10 '18 at 13:48
4
+50

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.

  • Do you have any insight as to Why the whole page would be being locked Vs just the row/s that I'm trying to insert? – Brian Sizemore Apr 5 '18 at 14:50
  • 1
    Also, I'm going to refactor my code a bit and reduce transaction time. I believe you are right that it should make a significant difference. – Brian Sizemore Apr 5 '18 at 14:51
  • Not sure how the internals of MySQL work, but this answer explains it for MS SQL. Some good MySQL tips in the MySQL Manual too. – Oreo Apr 5 '18 at 15:04
  • 1
    MySQL provides no control over fillfactor. – Rick James Apr 10 '18 at 1:00
  • 2
    After refactoring my code to Queue up my inserts and the update statement and running them very close together, it has solved my problem. Not only that, but I've been able to keep scaling this up (roughly double the previous amount of parallel processes) and it's still functioning correctly. Thank you Oreo! – Brian Sizemore Apr 10 '18 at 13:46
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.