MySQL Deadlock information debugging

Question

I have 2 tables EXPERIMENT and ENTITIES. ENTITIES has an id field which references primary Id of table EXPERIMENT.

I inserted multiple experiments along with the child entities concurrently and got deadlocks.

show engine innodb status shows the debugging information. I am unable to find why the deadlock occured. I guess that it happened because the child entities is verifying the Foreign_Key in the experiment table, but that doesn't look like it should generate deadlock.

I am using AUTO INCREMENT for the id's and SERIALIZABLE transaction ISOLATION.

Here is the relevant section from the innodb status :

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-05-14 12:34:45 0x7000060f3000
*** (1) TRANSACTION:
TRANSACTION 162916, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 10 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 2
MySQL thread id 183, OS thread handle 123145405919232, query id 2464 localhost 127.0.0.1 root update
INSERT INTO _ENTITIES (TYPE, FK_EXPERIMENT_ID, CONTENT, CREATED_USER_ID) VALUES ('VARIATION', 42, '{"variantName":"Variant 1","actions":[{"blockId":0,"type":"SendEmail","criteria":{"and":[{"operator":"EQ","attr":"_id","val":"Test","ruleId":1,"category":"default"},{"operator":"EQ","attr":"productLanguage","val":"CS_CZ","ruleId":1,"category":"contextual"}]},"order":1,"surfaceActionName":"EMAIL","params":{"verified":true,"selectedTemplate":"Design-Paid-Portfolio-A"},"name":"Action Block 1","treatmentId":"","default":true},{"blockId":1,"type":"wait","criteria":{"and":[{"operator":"EQ","attr":"_id","val":"Test","ruleId":1,"category":"default"},{"operator":"EQ","attr":"productLanguage","val":"CS_CZ","ruleId":1,"category":"contextual"}]},"order":1,"surfaceActionName":"wait","params":{"unit":"hour","data":10,"verified":true},"name":"Action Block 1","treatmentId":"","default":true}],"variantPercentage":80}', 'uk
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6728 page no 4 n bits 96 index ENTITIES_EXPERIMENT_ID of table `test_database`.`_entities` trx id 162916 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 162906, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
8 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 164, OS thread handle 123145403969536, query id 2549 localhost 127.0.0.1 root update
INSERT INTO _ENTITIES (TYPE, FK_EXPERIMENT_ID, CONTENT, CREATED_USER_ID) VALUES ('VARIATION', 33, '{"variantName":"Variant 1","actions":[{"blockId":0,"type":"SendEmail","criteria":{"and":[{"operator":"EQ","attr":"_id","val":"Test","ruleId":1,"category":"default"},{"operator":"EQ","attr":"productLanguage","val":"CS_CZ","ruleId":1,"category":"contextual"}]},"order":1,"surfaceActionName":"EMAIL","params":{"verified":true,"selectedTemplate":"Design-Paid-Portfolio-A"},"name":"Action Block 1","treatmentId":"","default":true},{"blockId":1,"type":"wait","criteria":{"and":[{"operator":"EQ","attr":"_id","val":"Test","ruleId":1,"category":"default"},{"operator":"EQ","attr":"productLanguage","val":"CS_CZ","ruleId":1,"category":"contextual"}]},"order":1,"surfaceActionName":"wait","params":{"unit":"hour","data":10,"verified":true},"name":"Action Block 1","treatmentId":"","default":true}],"variantPercentage":80}', 'uk
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6728 page no 4 n bits 96 index ENTITIES_EXPERIMENT_ID of table `test_database`.`_entities` trx id 162906 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 6728 page no 4 n bits 96 index ENTITIES_EXPERIMENT_ID of table `test_database`.`_entities` trx id 162906 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)
------------

How should i interpret this and why did deadlock occur?

The code used is :

Integer generatedId = experimentDAO.add(experimentQO);
......

for..
    entitiesDAO.add(entitiesQO);
....
ExperimentQO experimentQO = experimentDAO.get(generatedId);

Exception occurs while adding the entity.

Answer 1

What I see is:

• Trx #1 is waiting for an X mode insert intention lock (a kind of gap lock) on the ENTITIES_EXPERIMENT_ID index.
• Trx #2 holds an S lock on the ENTITIES_EXPERIMENT_ID index, which is blocking Trx #1
• Trx #2 is also waiting for an insert intention lock on the ENTITIES_EXPERIMENT_ID index.

We might assume Trx #1 is also holding an S lock on the same index. S locks are shared, so multiple transactions can acquire S locks on the same row (or gap) concurrently.

If both transactions acquired S locks first, and then both tried to request X locks, then they would enter into a situation where both were waiting on the other one, with no way to break the deadlock.

It's possible that both INSERT statements acquired the S locks as a first step. Or it's possible that you did some other queries that acquire S locks in the same transaction prior to the INSERTs, so both transactions are still holding their respective S locks.

You haven't shown the table definition, so there might be some foreign key constraints, that would cause S locks to be acquired for rows referenced indirectly.