Mysql in "SELECT ... FOR UPDATE" and insert

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

The purpose of the code is to insert a new title into the Title table, whereby I need to set the default bit if no other title already has a default bit.

The header table is external, anchored to the product table (thus there is a unique index for the 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 two different sessions at the same time, it always blocks.

As a result, I want there to be no deadlock, the first transaction sets the DefaultBit to 1, the second sets the 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 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 that doesn't work either, and there are still dead ends.

I also tried to execute the whole transaction in different isolation modes as per the 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 transaction values ​​to 1 (and there should only be one default for each product).

My analysis so far:

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

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'm about to This is what happens

• TX1 puts an S-lock on the select statement
• TX2 also puts an S-lock on the select statement
• TX1 puts an IX lock in the Insert statement, but locks from point 2
• TX2 puts an IX lock in the Insert instruction, but locks from points 2 and 3, hence a dead end in TX 2.

When I add FOR UPDATE to the select statement:

• TX1 puts IX lock in select statement
• TX2 also puts IX lock in select statement
• TX1 puts an X-lock on the Insert statement, but locks out from point 2
• TX2 puts an X lock in the Insert statement, but locks from points 2 and 3, hence a deadlock in TX 2.

I'm not sure about the right way forward as every method I tried has left the procedure open to deadlocks. Any suggestions would be great.