Upgrade to Pro — share decks privately, control downloads, hide ads and more …

MySQLのロックについて(基本編)

 MySQLのロックについて(基本編)

社内勉強会で使用した資料になります

8847086af047cbf895ab3277b59529fe?s=128

ANDPAD inc

July 03, 2020
Tweet

More Decks by ANDPAD inc

Other Decks in Programming

Transcript

  1. .Z42-ͷϩοΫʹ͍ͭͯ ʙجຊฤʙ

  2. ໨࣍  ϩοΫ͕ඞཁͳཧ༝  .Z42-ͷϨίʔυϩοΫ  ֎෦ΩʔͱϩοΫ  σουϩοΫͱݪҼͷௐࠪํ๏ ࠓ೔औΓ্͛ͳ͍͜ͱ

    ɾ%%-ͷϩοΫɺNFUBEBUBMPDL ɾΪϟοϓϩοΫωΫετΩʔϩοΫ ɾγϟυʔϩοΫ
  3. ͳͥɺϩοΫ͕ඞཁ͔ʁ wτϥϯβΫγϣϯΛฒྻͰ࣮ߦ͢ΔͨΊ

  4. ໨࣍  ϩοΫ͕ඞཁͳཧ༝  .Z42-ͷϨίʔυϩοΫ  ֎෦ΩʔͱϩοΫ  σουϩοΫͱݪҼͷௐࠪํ๏

  5. ϨίʔυϩοΫ ▪σʔλ mysql> select * from lockt; +----+-------+----------+---------+ | pk

    | name | favorite | addr | +----+-------+----------+---------+ | 1 | Taro | Apple | Tokyo | | 2 | Jiro | Orange | Tokyo | | 3 | Alice | Orange | London | | 4 | John | Pine | NewYork | +----+-------+----------+---------+ 4 rows in set (0.00 sec) ▪ΫΤϦʔ mysql> UPDATE lockt SET favorite = ‘Banana' 
 WHERE favorite = 'Orange' AND addr = ‘Tokyo'; Ͳͷߦ͕ϩοΫ͞ΕΔʁ ▪ςʔϒϧఆٛ CREATE TABLE `lockt` ( `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `favorite` varchar(255) DEFAULT NULL, `addr` varchar(255) DEFAULT NULL, PRIMARY KEY (`pk`)) ENGINE=InnoDB;
  6. ϨίʔυϩοΫ ▪ςʔϒϧఆٛ CREATE TABLE `lockt` ( `pk` bigint(20) unsigned NOT

    NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `favorite` varchar(255) DEFAULT NULL, `addr` varchar(255) DEFAULT NULL, PRIMARY KEY (`pk`)) ENGINE=InnoDB; ▪ϩοΫ mysql> UPDATE lockt SET favorite = ‘Banana' 
 WHERE favorite = 'Orange' AND addr = ‘Tokyo'; +----+-------+----------+---------+ | pk | name | favorite | addr | +----+-------+----------+---------+ | 1 | Taro | Apple | Tokyo | | 2 | Jiro | Orange | Tokyo | | 3 | Alice | Orange | London | | 4 | John | Pine | NewYork | +----+-------+----------+---------+ 4 rows in set (0.00 sec)
  7. ϨίʔυϩοΫ ▪ϩοΫ mysql> UPDATE lockt SET favorite = ‘Banana' 


    WHERE favorite = 'Orange' AND addr = ‘Tokyo'; +----+-------+----------+---------+ | pk | name | favorite | addr | +----+-------+----------+---------+ | 1 | Taro | Apple | Tokyo | | 2 | Jiro | Orange | Tokyo | | 3 | Alice | Orange | London | | 4 | John | Pine | NewYork | +----+-------+----------+---------+ 4 rows in set (0.00 sec) ▪ςʔϒϧఆٛ CREATE TABLE `lockt` ( `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `favorite` varchar(255) DEFAULT NULL, `addr` varchar(255) DEFAULT NULL, PRIMARY KEY (`pk`),
 KEY `idx_addr` (`addr`)) ENGINE=InnoDB;
  8. ϨίʔυϩοΫ ▪ςʔϒϧఆٛ CREATE TABLE `lockt` ( `pk` bigint(20) unsigned NOT

    NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `favorite` varchar(255) DEFAULT NULL, `addr` varchar(255) DEFAULT NULL, PRIMARY KEY (`pk`),
 KEY `idx_addr` (`addr`)) ENGINE=InnoDB; ▪ϩοΫ mysql> UPDATE lockt SET favorite = ‘Banana' 
 WHERE favorite = 'Orange' AND addr = ‘Tokyo'; +----+-------+----------+---------+ | pk | name | favorite | addr | +----+-------+----------+---------+ | 1 | Taro | Apple | Tokyo | | 2 | Jiro | Orange | Tokyo | | 3 | Alice | Orange | London | | 4 | John | Pine | NewYork | +----+-------+----------+---------+ 4 rows in set (0.00 sec) ΠϯσοΫε௥ՃͰ ϩοΫͷཻ౓͕มΘΔ
  9. ϨίʔυϩοΫ ▪ςʔϒϧఆٛ CREATE TABLE `lockt` ( `pk` bigint(20) unsigned NOT

    NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `favorite` varchar(255) DEFAULT NULL, `addr` varchar(255) DEFAULT NULL, PRIMARY KEY (`pk`),
 KEY `idx_fav_addr` (`favorite`,`addr`) ) ENGINE=InnoDB; ▪ϩοΫ mysql> UPDATE lockt SET favorite = ‘Banana' 
 WHERE favorite = 'Orange' AND addr = ‘Tokyo'; +----+-------+----------+---------+ | pk | name | favorite | addr | +----+-------+----------+---------+ | 1 | Taro | Apple | Tokyo | | 2 | Jiro | Orange | Tokyo | | 3 | Alice | Orange | London | | 4 | John | Pine | NewYork | +----+-------+----------+---------+ 4 rows in set (0.00 sec)
  10. ϨίʔυϩοΫ ▪ςʔϒϧఆٛ CREATE TABLE `lockt` ( `pk` bigint(20) unsigned NOT

    NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `favorite` varchar(255) DEFAULT NULL, `addr` varchar(255) DEFAULT NULL, PRIMARY KEY (`pk`),
 KEY `idx_fav_addr` (`favorite`,`addr`) ) ENGINE=InnoDB; ▪ϩοΫ mysql> UPDATE lockt SET favorite = ‘Banana' 
 WHERE favorite = 'Orange' AND addr = ‘Tokyo'; +----+-------+----------+---------+ | pk | name | favorite | addr | +----+-------+----------+---------+ | 1 | Taro | Apple | Tokyo | | 2 | Jiro | Orange | Tokyo | | 3 | Alice | Orange | London | | 4 | John | Pine | NewYork | +----+-------+----------+---------+ 4 rows in set (0.00 sec)
  11. ࣮ߦܭը ▪ΠϯσοΫε͋Γ mysql> EXPLAIN UPDATE lockt SET favorite = 'Banana'


    WHERE favorite = 'Orange' AND addr = 'Tokyo'; +----+-------------+-------+-------+-------------------+-------------+------+ | id | select_type | table | type | key | ref | rows | +----+-------------+-------+-------+-------------------+-------------+------+ | 1 | SIMPLE | lockt | range | idx_favorite_addr | const,const | 1 | +----+-------------+-------+-------+-------------------+-------------+------+ 1 row in set (0.00 sec) ▪ΠϯσοΫεͳ͠
 mysql> EXPLAIN UPDATE lockt SET favorite = 'Banana'
 WHERE favorite = 'Orange' AND addr = 'Tokyo'; +----+-------------+-------+-------+---------+------+------+ | id | select_type | table | type | key | ref | rows | +----+-------------+-------+-------+---------+------+------+ | 1 | SIMPLE | lockt | index | PRIMARY | NULL | 4 | +----+-------------+-------+-------+---------+------+------+ 1 row in set (0.00 sec) ϑϧεΩϟϯ
  12. *OOP%#ͷϩοΫ w ʮ৚݅ʹ߹கͨ͠ߦʯʹର͢ΔϩοΫͰ͸ͳ͘
 ʮΠϯσοΫεʹର͢ΔϩοΫʯ w ϩοΫ͢Δൣғ͸࣮ߦܭըʹґଘ͢Δ w ద੾ʹνϡʔχϯά͞Ε͍ͯͳ͍ΫΤϦ͸༨ܭͳߦ΋ϩοΫ͢Δ

  13. େྔ%&-&5&࣌ͷϩοΫճආ  ର৅ϨίʔυͷओΩʔΛ4&-&$5  খ෼͚ʹͯ͠࡟আɻ͜ͷ࣌ɺQL͸ιʔτ͢Δ͜ͱ
 %&-&5&'30.UBCMF8)&3&QLJO    

    ʜ 

  14. ໨࣍  ϩοΫ͕ඞཁͳཧ༝  .Z42-ͷϨίʔυϩοΫ  ֎෦ΩʔͱϩοΫ  σουϩοΫͱݪҼͷௐࠪํ๏

  15. ֎෦Ωʔ w ࢠςʔϒϧ΁*/4&35ɺ61%"5&ͨ͠ࡍɺ਌ςʔϒϧͷڞ༗ϩοΫΛऔΔ

  16. ֎෦Ωʔ ▪userςʔϒϧ +----+-----------+ | pk | user_name | +----+-----------+ |

    1 | user1 | | 2 | user2 | | 3 | user3 | +----+-----------+ ▪user_itemςʔϒϧ +----+---------+-----------+ | pk | user_id | item_name | +----+---------+-----------+ | 1 | 1 | item1 | | 2 | 2 | item2 | +----+---------+-----------+ CREATE TABLE `user_item` ( `pk` bigint(20) unsigned NOT NULL 
 AUTO_INCREMENT, `user_id` bigint(20) unsigned NOT NULL, `item_name` varchar(255) NOT NULL, PRIMARY KEY (`pk`), UNIQUE KEY `pk` (`pk`), KEY `user_id` (`user_id`), CONSTRAINT `user_item_ibfk_1` 
 FOREIGN KEY (`user_id`) 
 REFERENCES `user` (`pk`) ) ENGINE=InnoDB
  17. ֎෦Ωʔ ▪userςʔϒϧ +----+-----------+ | pk | user_name | +----+-----------+ |

    1 | user1 | | 2 | user2 | | 3 | user3 | +----+-----------+ 3 rows in set (0.00 sec) mysql> BEGIN; mysql> INSERT INTO user_item ɹɹɹɹ(pk, user_id, item_name)
 VALUES(3, 3, 'item3'); ϩοΫ͞ΕΔ
  18. ֎෦Ωʔ ▪userςʔϒϧ +----+-----------+ | pk | user_name | +----+-----------+ |

    1 | user1 | | 2 | user2 | | 3 | user3 | +----+-----------+ 3 rows in set (0.00 sec) mysql> BEGIN; mysql> UPDATE user_item SET user_id = 1
 WHERE pk = 2; ϩοΫ͞ΕΔ
  19. ໨࣍  ϩοΫ͕ඞཁͳཧ༝  .Z42-͸ʮߦϩοΫʯͰ͸ͳ͍  ֎෦ΩʔͱϩοΫ  σουϩοΫͱݪҼͷௐࠪํ๏

  20. ϩοΫʹؔ͢ΔΤϥʔ w &3303 ): -PDLXBJUUJNFPVUFYDFFEFE w ͜Ε͸ϩοΫ଴ͪͰλΠϜΞ΢τʢσουϩοΫͰ͸ͳ͍ʣ w &3303 

    %FBEMPDLGPVOEXIFOUSZJOHUPHFUMPDL w ͜Ε͸σουϩοΫ
  21. ϩοΫͷछྨ w ڞ༗ϩοΫ 4MPDL  w ޙଓͷڞ༗ϩοΫ͸ڐ͢ w ഉଞϩοΫ͸ϒϩοΫ͢Δ w

    ഉଞϩοΫ 9MPDL  w ڞ༗ϩοΫɺഉଞϩοΫͱ΋ʹϒϩοΫ͢Δ
  22. σουϩοΫͱ͸ w ಉ͡ϦιʔεʢߦʣͷϩοΫΛऔΓ߹͍ͬͯΔঢ়ଶ w σʔλϕʔεͷෆ۩߹Ͱ͸ͳ͍ 53"/4"$5*0/ 53"/4"$5*0/ BEGIN UPDATE ߦA

    UPDATE ߦB BEGIN UPDATE ߦB UPDATE ߦA ߦ" ߦ#
  23. σουϩοΫͱ͸ w ಉ͡ϦιʔεʢߦʣͷϩοΫΛऔΓ߹͍ͬͯΔঢ়ଶ w σʔλϕʔεͷෆ۩߹Ͱ͸ͳ͍ 53"/4"$5*0/ 53"/4"$5*0/ BEGIN UPDATE ߦA

    UPDATE ߦB BEGIN UPDATE ߦB UPDATE ߦA ߦ" ߦ# σουϩοΫ͕ൃੜͨ͠৔߹ɺ .Z42-͸ยํͷτϥϯβΫγϣϯΛ Τϥʔʹ͠ɺ΋͏ยํΛٹ͏
  24. ڝ߹͍ͯ͠ΔτϥϯβΫγϣϯͷௐࠪํ๏ w .Z42-ͷΤϥʔϩά΋͘͠͸ɺ4)08&/(*/&*//0%#45"564 ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2020-06-18 15:32:07

    7f76dd7c1700 *** (1) TRANSACTION: TRANSACTION 2464, ACTIVE 15 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1184, 6 row lock(s), undo log entries 1 MySQL thread id 6, OS thread handle 0x7f76dd77f700, query id 150 localhost root updating update lockt2 set name = 'hogehoge' where pk = 2 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 22 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`lockt2` trx id 2464 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 6; hex 000000000204; asc ;; 1: len 6; hex 000000000992; asc ;; 2: len 7; hex e8000001bd0110; asc ;; 3: len 8; hex 0000000000000001; asc ;; 4: len 4; hex 5461726f; asc Taro;; 5: len 5; hex 4170706c65; asc Apple;; 6: len 5; hex 546f6b796f; asc Tokyo;; *** (2) TRANSACTION: TRANSACTION 2465, ACTIVE 5 sec starting index read mysql tables in use 1, locked 1 4 lock struct(s), heap size 1184, 6 row lock(s), undo log entries 1 MySQL thread id 4, OS thread handle 0x7f76dd7c1700, query id 151 localhost root updating update lockt1 set name = 'hogehoge' where pk = 2 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 22 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`lockt2` trx id 2465 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;; <snip> *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 23 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`lockt1` trx id 2465 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 6; hex 000000000208; asc ;; 1: len 6; hex 00000000099b; asc ;; 2: len 7; hex ef000001c20110; asc ;; 3: len 8; hex 0000000000000001; asc ;; 4: len 4; hex 5461726f; asc Taro;; 5: len 5; hex 4170706c65; asc Apple;; 6: len 5; hex 546f6b796f; asc Tokyo;; *** WE ROLL BACK TRANSACTION (2)
  25. αϯϓϧ 53"/4"$5*0/ 53"/4"$5*0/ BEGIN UPDATE lockt1 WHERE pk=2 UPDATE lockt2

    WHERE pk=2 BEGIN UPDATE lockt2 WHERE pk=2 UPDATE lockt1 WHERE pk=2 MPDLU MPDLU
  26. ڝ߹͍ͯ͠ΔτϥϯβΫγϣϯͷௐࠪํ๏ ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2020-06-18 15:32:07 7f76dd7c1700 ***

    (1) TRANSACTION: TRANSACTION 2464, ACTIVE 15 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1184, 6 row lock(s), undo log entries 1 MySQL thread id 6, OS thread handle 0x7f76dd77f700, query id 150 localhost root updating update lockt2 set name = 'hogehoge' where pk = 2 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 22 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`lockt2` trx id 2464 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 6; hex 000000000204; asc ;; 1: len 6; hex 000000000992; asc ;; 2: len 7; hex e8000001bd0110; asc ;; 3: len 8; hex 0000000000000001; asc ;; 4: len 4; hex 5461726f; asc Taro;; 5: len 5; hex 4170706c65; asc Apple;; 6: len 5; hex 546f6b796f; asc Tokyo;; *** (2) TRANSACTION: TRANSACTION 2465, ACTIVE 5 sec starting index read mysql tables in use 1, locked 1 4 lock struct(s), heap size 1184, 6 row lock(s), undo log entries 1 MySQL thread id 4, OS thread handle 0x7f76dd7c1700, query id 151 localhost root updating update lockt1 set name = 'hogehoge' where pk = 2 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 22 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`lockt2` trx id 2465 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;; <snip> *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 23 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`lockt1` trx id 2465 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 6; hex 000000000208; asc ;; 1: len 6; hex 00000000099b; asc ;; 2: len 7; hex ef000001c20110; asc ;; 3: len 8; hex 0000000000000001; asc ;; 4: len 4; hex 5461726f; asc Taro;; 5: len 5; hex 4170706c65; asc Apple;; 6: len 5; hex 546f6b796f; asc Tokyo;; *** WE ROLL BACK TRANSACTION (2) τϥϯβΫγϣϯͷ࠷ޙͷΫΤϦͷΈදࣔ͞ΕΔ ଞͷΫΤϦ͸ਪଌ͠ͳ͍ͱ͍͚ͳ͍ 53"/4"$5*0/ 53"/4"$5*0/ BEGIN ʢਪଌ͕ඞཁʣ UPDATE lockt2 BEGIN UPDATE lockt1 MPDLU MPDLU ʢਪଌ͕ඞཁʣ
  27. ڝ߹͍ͯ͠ΔτϥϯβΫγϣϯͷௐࠪํ๏ ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2020-06-18 15:32:07 7f76dd7c1700 ***

    (1) TRANSACTION: TRANSACTION 2464, ACTIVE 15 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1184, 6 row lock(s), undo log entries 1 MySQL thread id 6, OS thread handle 0x7f76dd77f700, query id 150 localhost root updating update lockt2 set name = 'hogehoge' where pk = 2 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 22 page no 3 n bits 72 index `GEN_CLUST_INDEX` of
 table `test`.`lockt2` trx id 2464 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 6; hex 000000000204; asc ;; 1: len 6; hex 000000000992; asc ;; 2: len 7; hex e8000001bd0110; asc ;; 3: len 8; hex 0000000000000001; asc ;; 4: len 4; hex 5461726f; asc Taro;; 5: len 5; hex 4170706c65; asc Apple;; 6: len 5; hex 546f6b796f; asc Tokyo;; *** (2) TRANSACTION: TRANSACTION 2465, ACTIVE 5 sec starting index read mysql tables in use 1, locked 1 4 lock struct(s), heap size 1184, 6 row lock(s), undo log entries 1 MySQL thread id 4, OS thread handle 0x7f76dd7c1700, query id 151 localhost root updating update lockt1 set name = 'hogehoge' where pk = 2 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 22 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`lockt2` trx id 2465 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;; <snip> *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 23 page no 3 n bits 72 index `GEN_CLUST_INDEX` of 
 table `test`.`lockt1` trx id 2465 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 6; hex 000000000208; asc ;; 1: len 6; hex 00000000099b; asc ;; 2: len 7; hex ef000001c20110; asc ;; 3: len 8; hex 0000000000000001; asc ;; 4: len 4; hex 5461726f; asc Taro;; 5: len 5; hex 4170706c65; asc Apple;; 6: len 5; hex 546f6b796f; asc Tokyo;; τϥϯβΫγϣϯͷ࠷ޙͷΫΤϦͷΈදࣔ͞ΕΔ ଞͷΫΤϦ͸ਪଌ͠ͳ͍ͱ͍͚ͳ͍ 53"/4"$5*0/ 53"/4"$5*0/ BEGIN ʢਪଌ͕ඞཁʣ UPDATE lockt2 BEGIN UPDATE lockt1 MPDLU MPDLU ʢਪଌ͕ඞཁʣ ଴ͬͯΔ ଴ͬͯΔ
  28. ڝ߹͍ͯ͠ΔτϥϯβΫγϣϯͷௐࠪํ๏ ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2020-06-18 15:32:07 7f76dd7c1700 ***

    (1) TRANSACTION: TRANSACTION 2464, ACTIVE 15 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1184, 6 row lock(s), undo log entries 1 MySQL thread id 6, OS thread handle 0x7f76dd77f700, query id 150 localhost root updating update lockt2 set name = 'hogehoge' where pk = 2 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 22 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`lockt2` trx id 2464 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 6; hex 000000000204; asc ;; 1: len 6; hex 000000000992; asc ;; 2: len 7; hex e8000001bd0110; asc ;; 3: len 8; hex 0000000000000001; asc ;; 4: len 4; hex 5461726f; asc Taro;; 5: len 5; hex 4170706c65; asc Apple;; 6: len 5; hex 546f6b796f; asc Tokyo;; *** (2) TRANSACTION: TRANSACTION 2465, ACTIVE 5 sec starting index read mysql tables in use 1, locked 1 4 lock struct(s), heap size 1184, 6 row lock(s), undo log entries 1 MySQL thread id 4, OS thread handle 0x7f76dd7c1700, query id 151 localhost root updating update lockt1 set name = 'hogehoge' where pk = 2 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 22 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`lockt2` trx id 2465 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;; <snip> *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 23 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`lockt1` trx id 2465 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 6; hex 000000000208; asc ;; 1: len 6; hex 00000000099b; asc ;; 2: len 7; hex ef000001c20110; asc ;; 3: len 8; hex 0000000000000001; asc ;; 4: len 4; hex 5461726f; asc Taro;; 5: len 5; hex 4170706c65; asc Apple;; 6: len 5; hex 546f6b796f; asc Tokyo;; *** WE ROLL BACK TRANSACTION (2) 53"/4"$5*0/ 53"/4"$5*0/ BEGIN BEGIN MPDLU MPDLU ʢਪଌ͕ඞཁʣ ʢਪଌ͕ඞཁʣ UPDATE lockt2 UPDATE lockt1 ଴ͬͯΔ ϩοΫͯ͠Δ
  29. ڝ߹͍ͯ͠ΔτϥϯβΫγϣϯͷௐࠪํ๏ ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2020-06-18 15:32:07 7f76dd7c1700 ***

    (1) TRANSACTION: TRANSACTION 2464, ACTIVE 15 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1184, 6 row lock(s), undo log entries 1 MySQL thread id 6, OS thread handle 0x7f76dd77f700, query id 150 localhost root updating update lockt2 set name = 'hogehoge' where pk = 2 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 22 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`lockt2` trx id 2464 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 6; hex 000000000204; asc ;; 1: len 6; hex 000000000992; asc ;; 2: len 7; hex e8000001bd0110; asc ;; 3: len 8; hex 0000000000000001; asc ;; 4: len 4; hex 5461726f; asc Taro;; 5: len 5; hex 4170706c65; asc Apple;; 6: len 5; hex 546f6b796f; asc Tokyo;; *** (2) TRANSACTION: TRANSACTION 2465, ACTIVE 5 sec starting index read mysql tables in use 1, locked 1 4 lock struct(s), heap size 1184, 6 row lock(s), undo log entries 1 MySQL thread id 4, OS thread handle 0x7f76dd7c1700, query id 151 localhost root updating update lockt1 set name = 'hogehoge' where pk = 2 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 22 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`lockt2` trx id 2465 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;; <snip> *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 23 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`lockt1` trx id 2465 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 6; hex 000000000208; asc ;; 1: len 6; hex 00000000099b; asc ;; 2: len 7; hex ef000001c20110; asc ;; 3: len 8; hex 0000000000000001; asc ;; 4: len 4; hex 5461726f; asc Taro;; 5: len 5; hex 4170706c65; asc Apple;; 6: len 5; hex 546f6b796f; asc Tokyo;; *** WE ROLL BACK TRANSACTION (2) 53"/4"$5*0/ 53"/4"$5*0/ BEGIN BEGIN MPDLU MPDLU ʢਪଌ͕ඞཁʣ ʢਪଌ͕ඞཁʣ UPDATE lockt2 UPDATE lockt1 ଴ͬͯΔ ϩοΫͯ͠Δ ʢਪଌ͕ඞཁʣ )0-%45)&-0$, 4 ͸53"/4"$5*0/ଆ͔͠ දࣔͯ͘͠Εͳ͍
  30. ਪଌํ๏ w " Τϥʔʹͳ͍ͬͯΔτϥϯβΫ γϣϯɻΞϓϦͷϩά͔ΒḷΕ Δɻ·ͨɺ5ͷΫΤϦͱڝ߹͢Δ ΫΤϦͰ͋Δ͸ͣɻ w # 5ͷΫΤϦͱڝ߹͢ΔΫΤϦͰ

    ͋Δ͸ͣɻ5ͷΫΤϦ͕ϩοΫΛ ͍࣋ͬͯΔςʔϒϧ΁ͷ֎෦Ωʔ Λ࣋ͭΫΤϦͷՄೳੑ΋͋Δɻ w $ 5͕଴͍ͬͯΔϩοΫΛऔͬͯ ͍Δ͸ͣ 53"/4"$5*0/ 53"/4"$5*0/ BEGIN BEGIN MPDLU MPDLU ʢBʣ ʢAʣ UPDATE lockt2 UPDATE lockt1 ଴ͬͯΔ ϩοΫͯ͠Δ (C)
  31. ਪଌํ๏ w " Τϥʔʹͳ͍ͬͯΔτϥϯ βΫγϣϯɻΞϓϦͷϩά͔ ΒḷΕΔɻ·ͨɺ5ͷΫΤϦ ͱڝ߹͢ΔΫΤϦͰ͋Δ͸ͣɻ w # 5ͷΫΤϦͱڝ߹͢ΔΫΤ

    ϦͰ͋Δ͸ͣ w $ 5͕଴͍ͬͯΔϩοΫΛ औ͍ͬͯΔ͸ͣ 53"/4"$5*0/ 53"/4"$5*0/ BEGIN BEGIN MPDLU MPDLU ʢBʣ ʢAʣ UPDATE lockt2 UPDATE lockt1 ଴ͬͯΔ ϩοΫͯ͠Δ (C) ܇࿅͕ඞཁɾɾɾ
  32. σουϩοΫͷରॲํ๏ w τϥϯβΫγϣϯΛ࠶࣮ߦ͢ΔʢϦτϥΠʣ w ͓खܰɺΦεεϝ w '&ˠ"1*ˠ%#ͳΒɺ'&͔Β"1*ʹϦτϥΠ͢Δͱָ w ΫΤϦΛमਖ਼͢Δ w

    ߋ৽͢ΔॱংΛἧ͑Δ w τϥϯβΫγϣϯΛখ͘͢͞Δ
  33. ·ͱΊ

  34. ·ͱΊ w .Z42- *OOP%# ͸ݫີͳߦϩοΫͰ͸ͳ͍ w ࣮ߦܭըΛҙࣝ͢Δ w ओΩʔͰߋ৽͢Δ w

    ֎෦ΩʔΛ࣋ͭςʔϒϧ΁ͷߋ৽͸਌ςʔϒϧͷϩοΫΛऔΔ w σουϩοΫ͸Τϥʔϩά͔Βղੳ͢Δ
  35. 5IBOLT