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)
2
  • 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
    Commented Apr 26, 2019 at 22:19
  • Is autocommit on? Are these IODKUs part of a bigger BEGIN..COMMIT, if so, let's see more.
    – Rick James
    Commented Apr 26, 2019 at 22:20

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.