MySQLのロックについて（基本編）

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

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.
    UBCMF
    8)&3&
    QL
    JO
        ʜ 

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
    
    ): 
    -PDL
    XBJU
    UJNFPVU
    FYDFFEFE
    w ͜Ε͸ϩοΫ଴ͪͰλΠϜΞ΢τʢσουϩοΫͰ͸ͳ͍ʣ
    w &3303
    
     
    %FBEMPDL
    GPVOE
    XIFO
    USZJOH
    UP
    HFU
    MPDL
    w ͜Ε͸σουϩοΫ
21. ϩοΫͷछྨ w ڞ༗ϩοΫ 4
    MPDL
    w ޙଓͷڞ༗ϩοΫ͸ڐ͢
    w ഉଞϩοΫ͸ϒϩοΫ͢Δ
    w ഉଞϩοΫ
    9
    MPDL
    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-%4
    5)&
    -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