2

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)

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

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.