3

I am receiving deadlocks when running this piece of code below.

The purpose of the code is to insert a new Title into the Title table with the end result being that I need to set the defaultTitle bit if no other title has the defaultTitle bit set already.

The Title Table is foreign keyed to the Product Table (thus there is a non unique index on ProductId). The Title table looks like this:

CREATE TABLE `Title` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ProductId` int(11) DEFAULT NULL,
  `Title` varchar(100) NOT NULL,
  `DefaultBit` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`ID`),
  KEY `fk_product_Title` (`ProductId`),
  CONSTRAINT `title_fk_1` FOREIGN KEY (`ProductId`) REFERENCES `product` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

In this example transaction I want to add 2 new titles to the Title table for the same product. If I run this code in 2 different sessions at the same time, it always deadlocks.

The outcome I want is for there to be no deadlock, the first transaction sets DefaultBit to 1, the second sets DefaultBit to 0.

START TRANSACTION;

SET @prodId = 4;

SET @insTitleDefault = (SELECT  IF(COUNT(PT.ID) > 0, 0, 1) as defaultOrNot
FROM    ProductTitle PT
WHERE   PT.ProductId = @prodId);

SELECT SLEEP(2); - Added for testing to pinpoint the deadlock.

INSERT INTO Title
(`ProductId`,`Title`,`DefaultBit`)
VALUES
(@prodId ,"Some title text",@insTitleDefault);

SET @newTitleId = LAST_INSERT_ID();

SELECT * FROM Title WHERE ProductId = @prodId;

-- COMMIT; -- commenting out the commit for testing purposes, Rollback instead
ROLLBACK;

I have tried to add FOR UPDATE

SET @insTitleDefault = (SELECT  IF(COUNT(PT.ID) > 0, 0, 1) as defaultOrNot
FROM    ProductTitle PT
WHERE   PT.ProductId = @prodId FOR UPDATE);

But this does not work either and still deadlocks.

I have also tried to run the entire transaction in different Isolation Modes as per answer in Deadlock using SELECT ... FOR UPDATE in MySQL

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

This does not cause deadlocks but it also does not preserve the correct DefaultBit as it sets both transactions values to 1 (and there should only be 1 default per product).

My analysis so far:

I have run show ENGINE INNODB status; after each deadlock and get the following relevant info:

LATEST DETECTED DEADLOCK
------------------------
2015-07-30 10:05:18 3808
*** (1) TRANSACTION:
TRANSACTION 227273, ACTIVE 6 sec inserting
mysql tables in use 2, locked 2
LOCK WAIT 6 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 93, OS thread handle 0x433c, query id 3098292 localhost 127.0.0.1 user update
INSERT INTO Title(`ProductId`,`Title`,`DefaultBit`)
VALUES(@pId,"Some title text",@insTitleDefault)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 380 page no 132 n bits 752 index `fk_product_Title` of table `globalhq`.`Title` trx id 227273 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 227274, ACTIVE 4 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 2, locked 2
6 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 95, OS thread handle 0x3808, query id 3098300 localhost 127.0.0.1 user update
INSERT INTO Title(`ProductId`,`Title`,`DefaultBit`)
VALUES(@pId,"Some title text",@insTitleDefault)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 380 page no 132 n bits 752 index `fk_product_Title` of table `globalhq`.`Title` trx id 227274 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 380 page no 132 n bits 752 index `fk_product_Title` of table `globalhq`.`Title` trx id 227274 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

I gather This is what is happening

  1. TX1 is putting a S lock on the select statement
  2. TX2 is also putting a S lock on the select statement
  3. TX1 puts the IX lock on the Insert statement but is blocked from point 2
  4. TX2 puts the IX lock on the Insert statement but is blocked from point 2 and 3, hence the deadlock on TX 2.

When I add a FOR UPDATE to the select statement:

  1. TX1 is putting a IX lock on the select statement
  2. TX2 is also putting a IX lock on the select statement
  3. TX1 puts the X lock on the Insert statement but is blocked from point 2
  4. TX2 puts the X lock on the Insert statement but is blocked from point 2 and 3, hence the deadlock on TX 2.

I am not sure of the correct way forward as every method I have tried has left the procedure open to deadlocks. Any suggestions would be awesome.

Community
  • 1
  • 1
Brad Baskin
  • 1,265
  • 2
  • 14
  • 20

1 Answers1

0

i suggest you to add an index on "PT.ProductId" column .
i had the same issue and solve like above.
The principle is when an index exists , 'select for update' will lock the index, other 'select for update' or 'insert' of other transactions will be blocked until the first transaction committed. so deadlock will not happen.

frank.gao
  • 1