1

I'm getting this strange deadlock from MySQL:

------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 2300749061, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1

INSERT INTO `event_entities` ( `entity_id`, `event_id`) VALUES ( '16011341', '8064913' )
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 38454477 page no 22728 n bits 640 index `event_id` of table `event_entities` trx id 2300749061 lock mode S waiting
*** (2) TRANSACTION:
INSERT INTO `account_entities` ( `entity_id`, `account_id`) VALUES ( '16093815', '4590372' )
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 38454477 page no 22728 n bits 640 index `event_id` of table `event_entities` trx id 2300748502 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 38454560 page no 21704 n bits 896 index `account_id` of table `account_entities` trx id 2300748502 lock mode S waiting
*** WE ROLL BACK TRANSACTION (1)

Here's what those tables look like:

CREATE TABLE `event_entities` (
  `event_id` int(11) NOT NULL,
  `entity_id` int(11) NOT NULL,
  PRIMARY KEY (`event_id`,`entity_id`),
  KEY `entity_id` (`entity_id`),
  CONSTRAINT `event_entities_fk_1` FOREIGN KEY (`event_id`) REFERENCES `events` (`event_id`) ON UPDATE CASCADE,
  CONSTRAINT `event_entities_fk_2` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`entity_id`) ON UPDATE CASCADE
)

CREATE TABLE `account_entities` (
  `account_id` int(11) NOT NULL,
  `entity_id` int(11) NOT NULL,
  PRIMARY KEY (`account_id`,`entity_id`),
  KEY `entity_id` (`entity_id`),
  CONSTRAINT `account_entities_fk_1` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`account_id`) ON UPDATE CASCADE,
  CONSTRAINT `account_entities_fk_2` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`entity_id`) ON UPDATE CASCADE
)

I don't get why the insert into account_entities is holding a lock on event_entities, but I'm sure it has something to do with both tables having a FK to entities. Can anyone explain what might be going on?

Tyler S
  • 11
  • 3
  • Deadlocks occur when there are multiple lock requests that occur out of order and end up waiting for locks previously acquired by each other. I would infer that your transaction (2) had previously done some locking operation on `event_entities` and still holds that lock. – Bill Karwin Mar 18 '20 at 19:37
  • Show us all the SQL in each of the transactions. Then it will be easier to point to what is out of order, or whether it is something else. – Rick James Mar 29 '20 at 02:52

1 Answers1

0

It's most likely an earlier query in transaction 2 that is creation lock on index event_id of table event_entities.

Please enable general_log and post the all SQL statements in the transactions.

jmerinen
  • 11
  • 2