Clarification on mysql deadlock situation

Question

Following deadlock is encountered while load testing a application. After going through MySQL documentation and other online resources what I figured out was that

1) Transaction (2) holds exclusive lock for PERMISSION_ID index.

2) Transaction (1) comes and try to acquire insert intention exclusive lock for PERMISSION_ID. But put on to wait since exclusive lock is already with (2)

3) Then transaction (2) also wants to acquire insert intention exclusive lock for PERMISSION_ID which place it behind (1) in waiting queue. Thus deadlock.

What I am not sure about my explanation is assumption in step (2) where request to insert intention exclusive lock for PERMISSION_ID waits for exclusive lock to be released. Is it correct or am I missing something here. Please explain.

*** (1) TRANSACTION: TRANSACTION 22466201, ACTIVE 0 sec inserting mysql tables in use 2, locked 2 LOCK WAIT 10 lock struct(s), heap size 1184, 16 row lock(s), undo log entries 2 MySQL thread id 12287, OS thread handle 0x2b4ea1f61700, query id 130397444 10.0.1.222 admin Sending data

INSERT INTO ROLE (PERMISSION_ID, ROLE_NAME, DOMAIN_ID) VALUES (4188, 'everyone', (SELECT DOMAIN_ID FROM DOMAIN WHERE TENANT_ID=1 AND DOMAIN_NAME='INTERNAL'))

*** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 10787 page no 139 n bits 176 index PERMISSION_ID of table IDENTITY.ROLE trx id 22466201 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 22466203, ACTIVE 0 sec inserting mysql tables in use 2, locked 2 8 lock struct(s), heap size 1184, 14 row lock(s), undo log entries 1 MySQL thread id 12279, OS thread handle 0x2b4ea1700700, query id 130397446 10.0.1.222 admin Sending data

INSERT INTO ROLE (PERMISSION_ID, ROLE_NAME, DOMAIN_ID) VALUES (4187, 'anonymous', (SELECT DOMAIN_ID FROM DOMAIN WHERE TENANT_ID=1 AND DOMAIN_NAME='SYSTEM'))

*** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 10787 page no 139 n bits 176 index PERMISSION_ID of table IDENTITY.ROLE trx id 22466203 lock_mode X 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 10787 page no 139 n bits 176 index PERMISSION_ID of table IDENTITY.ROLE trx id 22466203 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)

Answer 1

Your assumption is correct. Nevertheless, you need to pay attention to what is being locked.

Both transactions are locking

• space id 10787
• page no 139
• n bits 176
• index PERMISSION_ID of table IDENTITY.ROLE

The DB Connections 12287 and 12279 ran one INSERT each

Here is what I notice: In each INSERT, you are running SELECT DOMAIN_ID FROM DOMAIN WHERE TENANT_ID=1 AND DOMAIN_NAME='...' to fill in DOMAIN_ID. This would cause additional locks because a query's result is needed to fill DOMAIN_ID while other indexes are made to wait for the materialization of ROLE's primary key. This deadlock lasts a little longer that normal because of this intermittent need to run a SELECT.

SUGGESTION

Instead of doing

INSERT INTO ROLE (PERMISSION_ID, ROLE_NAME, DOMAIN_ID) VALUES (4188, 'everyone',
(SELECT DOMAIN_ID FROM DOMAIN WHERE TENANT_ID=1 AND DOMAIN_NAME='INTERNAL'));

you should retrieve the DOMAIN_ID separately into a variable and then use it in the INSERT

SELECT DOMAIN_ID INTO @domain_id FROM DOMAIN
WHERE TENANT_ID=1 AND DOMAIN_NAME='INTERNAL';
INSERT INTO ROLE (PERMISSION_ID, ROLE_NAME, DOMAIN_ID) VALUES (4188,'everyone',@domain_id);

This might reduce the need to pile up locks