0

Sypnosis: percona-server 5.7.12, sometimes deadlocking

Am I right interpreting this output in a way that transaction 2 is waiting on itself ? And, if yes - why ?

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-04-18 05:34:00 0x7f2ae0b05700
*** (1) TRANSACTION:
TRANSACTION 444786051, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 5486539, OS thread handle 139822142461696, query id 307909819 192.168.10.49 enaza update
INSERT INTO `enz_external_user` SET `partner` = 180,`external_id` = 'XXXXXXXXXXXXXXXXX',`pay_system` = 7,`move_type` = 0,`token` = NULL,`service` = NULL,`oauth_id` = NULL,`geo_id` = NULL,`state` = 4281,`agreement_number` = 'XXXXXXXXXXXXXXXXX',`parent` = 13953989,`ts` = '2019-04-18 05:34:00' ON DUPLICATE KEY UPDATE `partner` = 180,`external_id` = 'XXXXXXXXXXXXXXXXX',`pay_system` = 7,`move_type` = 0,`token` = NULL,`service` = NULL,`oauth_id` = NULL,`geo_id` = NULL,`state` = 4281,`agreement_number` = 'XXXXXXXXXXXXXXXXX',`parent` = 13953989,`ts` = '2019-04-18 05:34:00'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 11226 page no 80703 n bits 344 index external_id of table `enz`.`enz_external_user` trx id 444786051 lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 444786049, ACTIVE 0 sec inserting, thread declared inside InnoDB 1
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 5486536, OS thread handle 139822135006976, query id 307909810 192.168.10.49 enaza update
INSERT INTO `enz_external_user` SET `partner` = 180,`external_id` = 'ZZZZZZZZZZZZ',`pay_system` = 7,`move_type` = 0,`token` = NULL,`service` = NULL,`oauth_id` = NULL,`geo_id` = NULL,`state` = 4281,`agreement_number` = 'ZZZZZZZZZZZZ',`parent` = 13953988,`ts` = '2019-04-18 05:34:00' ON DUPLICATE KEY UPDATE `partner` = 180,`external_id` = 'ZZZZZZZZZZZZZZ',`pay_system` = 7,`move_type` = 0,`token` = NULL,`service` = NULL,`oauth_id` = NULL,`geo_id` = NULL,`state` = 4281,`agreement_number` = 'ZZZZZZZZZZZZZZ',`parent` = 13953988,`ts` = '2019-04-18 05:34:00'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 11226 page no 80703 n bits 344 index external_id of table `enz`.`enz_external_user` trx id 444786049 lock_mode X locks gap before rec
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 11226 page no 80703 n bits 344 index external_id of table `enz`.`enz_external_user` trx id 444786049 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (1)
  • The trx_ids 444786051 and 444786049 are different. Sure, the IODKUs look identical. That probably means that two different connections ran identical statements, not that trx is waiting on itself. – Rick James Apr 26 '19 at 22:19
  • Is autocommit on? Are these IODKUs part of a bigger BEGIN..COMMIT, if so, let's see more. – Rick James Apr 26 '19 at 22:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.