疑问：MySQL死锁信息

向右看齐 2022-09-21 11:38 90阅读 0赞

------------------------
    LATEST DETECTED DEADLOCK
    ------------------------
    160610 10:27:30
    *** (1) TRANSACTION:
    TRANSACTION 587D9BC7A, ACTIVE 2 sec fetching rows
    mysql tables in use 1, locked 1
    LOCK WAIT 8925 lock struct(s), heap size 801208, 383459 row lock(s), undo log entries 1
    MySQL thread id 79850630, OS thread handle 0x7f3c771b9700, query id 11162553051 192.168.1.7 58rl131203 Updating
    UPDATE `gt_send_orders` SET express_sn=''
              WHERE 1=1
    AND express_generation_sn=16061028095331
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 1685685 page no 15263 n bits 120 index `PRIMARY` of table `shop_zp`.`gt_send_orders` trx id 587D9BC7A lock_mode X waiting
    Record lock, heap no 17 PHYSICAL RECORD: n_fields 58; compact format; info bits 0
     0: len 4; hex 0005c333; asc    3;;
     1: len 6; hex 000587d9bc3d; asc      =;;
     2: len 7; hex 280000170604e6; asc (      ;;
     3: len 1; hex 80; asc  ;;
     4: len 4; hex 8006af79; asc    y;;
     5: len 8; hex 80000175f2b98d54; asc    u   T;;
     6: len 8; hex 80000175f2ac3368; asc    u  3h;;
     7: len 4; hex 00000001; asc     ;;
     8: len 8; hex 000000000008751d; asc       u ;;
     9: len 1; hex 80; asc  ;;
     10: len 0; hex ; asc ;;
     11: len 4; hex 80000001; asc     ;;
     12: len 12; hex 363035383833383531363839; asc 605883851689;;
     13: len 1; hex 81; asc  ;;
     14: len 1; hex 03; asc  ;;
     15: len 1; hex 87; asc  ;;
     16: len 1; hex 02; asc  ;;
     17: len 0; hex ; asc ;;
     18: len 9; hex 8000000000000003e6; asc          ;;
     19: len 9; hex 8000000000000003d4; asc          ;;
     20: len 4; hex 57598525; asc WY %;;
     21: len 9; hex e78e8be6b5b7e8839c; asc          ;;
     22: len 11; hex 3133313134333533333636; asc 13114353366;;
     23: len 11; hex 3133313134333533333636; asc 13114353366;;
     24: len 0; hex ; asc ;;
     25: len 30; hex e6b996e58c97e79c8120e58d81e5a0b0e5b88220e88c85e7aeade58cba20; asc                               ; (total 69 bytes);
     26: len 4; hex 00000011; asc     ;;
     27: len 4; hex 00000109; asc     ;;
     28: len 4; hex 00000798; asc     ;;
     29: len 9; hex 800000000000000000; asc          ;;
     30: len 4; hex 80000000; asc     ;;
     31: len 30; hex e5b7a5e4bd9ce697a5e38081e58f8ce4bc91e697a5e4b88ee58187e697a5; asc                               ; (total 42 bytes);
     32: len 0; hex ; asc ;;
     33: len 0; hex ; asc ;;
     34: len 4; hex 80000000; asc     ;;
     35: len 4; hex d75a2590; asc  Z% ;;
     36: len 1; hex 80; asc  ;;
     37: len 1; hex 80; asc  ;;
     38: len 4; hex 80000000; asc     ;;
     39: len 1; hex 80; asc  ;;
     40: len 6; hex e4b8aae4baba; asc       ;;
     41: len 8; hex 3133313230363031; asc 13120601;;
     42: len 6; hex e4b8aae4baba; asc       ;;
     43: len 12; hex e59586e59381e6988ee7bb86; asc             ;;
     44: len 1; hex 81; asc  ;;
     45: len 1; hex 81; asc  ;;
     46: len 1; hex 80; asc  ;;
     47: SQL NULL;
     48: len 1; hex 80; asc  ;;
     49: len 4; hex 00000000; asc     ;;
     50: len 4; hex 80000000; asc     ;;
     51: len 14; hex 3136303631303238303935333037; asc 16061028095307;;
     52: SQL NULL;
     53: len 1; hex 01; asc  ;;
     54: len 4; hex 800003d4; asc     ;;
     55: len 4; hex 80000001; asc     ;;
     56: len 1; hex 80; asc  ;;
     57: len 4; hex d75984e3; asc  Y  ;;
    
    *** (2) TRANSACTION:
    TRANSACTION 587D9BC3D, ACTIVE 4 sec starting index read
    mysql tables in use 1, locked 1
    11 lock struct(s), heap size 3112, 10 row lock(s), undo log entries 10
    MySQL thread id 79745739, OS thread handle 0x7f3c78a9b700, query id 11162553776 192.168.1.7 58rl131203 Updating
    UPDATE `gt_send_orders` SET status=4,sub_status=10,last_op_time=UNIX_TIMESTAMP()          
             ,sign_order_time=1465525650
             ,rejection_status=0          
              WHERE send_order_id=352886
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 1685685 page no 15263 n bits 120 index `PRIMARY` of table `shop_zp`.`gt_send_orders` trx id 587D9BC3D lock_mode X locks rec but not gap
    Record lock, heap no 17 PHYSICAL RECORD: n_fields 58; compact format; info bits 0
     0: len 4; hex 0005c333; asc    3;;
     1: len 6; hex 000587d9bc3d; asc      =;;
     2: len 7; hex 280000170604e6; asc (      ;;
     3: len 1; hex 80; asc  ;;
     4: len 4; hex 8006af79; asc    y;;
     5: len 8; hex 80000175f2b98d54; asc    u   T;;
     6: len 8; hex 80000175f2ac3368; asc    u  3h;;
     7: len 4; hex 00000001; asc     ;;
     8: len 8; hex 000000000008751d; asc       u ;;
     9: len 1; hex 80; asc  ;;
     10: len 0; hex ; asc ;;
     11: len 4; hex 80000001; asc     ;;
     12: len 12; hex 363035383833383531363839; asc 605883851689;;
     13: len 1; hex 81; asc  ;;
     14: len 1; hex 03; asc  ;;
     15: len 1; hex 87; asc  ;;
     16: len 1; hex 02; asc  ;;
     17: len 0; hex ; asc ;;
     18: len 9; hex 8000000000000003e6; asc          ;;
     19: len 9; hex 8000000000000003d4; asc          ;;
     20: len 4; hex 57598525; asc WY %;;
     21: len 9; hex e78e8be6b5b7e8839c; asc          ;;
     22: len 11; hex 3133313134333533333636; asc 13114353366;;
     23: len 11; hex 3133313134333533333636; asc 13114353366;;
     24: len 0; hex ; asc ;;
     25: len 30; hex e6b996e58c97e79c8120e58d81e5a0b0e5b88220e88c85e7aeade58cba20; asc                               ; (total 69 bytes);
     26: len 4; hex 00000011; asc     ;;
     27: len 4; hex 00000109; asc     ;;
     28: len 4; hex 00000798; asc     ;;
     29: len 9; hex 800000000000000000; asc          ;;
     30: len 4; hex 80000000; asc     ;;
     31: len 30; hex e5b7a5e4bd9ce697a5e38081e58f8ce4bc91e697a5e4b88ee58187e697a5; asc                               ; (total 42 bytes);
     32: len 0; hex ; asc ;;
     33: len 0; hex ; asc ;;
     34: len 4; hex 80000000; asc     ;;
     35: len 4; hex d75a2590; asc  Z% ;;
     36: len 1; hex 80; asc  ;;
     37: len 1; hex 80; asc  ;;
     38: len 4; hex 80000000; asc     ;;
     39: len 1; hex 80; asc  ;;
     40: len 6; hex e4b8aae4baba; asc       ;;
     41: len 8; hex 3133313230363031; asc 13120601;;
     42: len 6; hex e4b8aae4baba; asc       ;;
     43: len 12; hex e59586e59381e6988ee7bb86; asc             ;;
     44: len 1; hex 81; asc  ;;
     45: len 1; hex 81; asc  ;;
     46: len 1; hex 80; asc  ;;
     47: SQL NULL;
     48: len 1; hex 80; asc  ;;
     49: len 4; hex 00000000; asc     ;;
     50: len 4; hex 80000000; asc     ;;
     51: len 14; hex 3136303631303238303935333037; asc 16061028095307;;
     52: SQL NULL;
     53: len 1; hex 01; asc  ;;
     54: len 4; hex 800003d4; asc     ;;
     55: len 4; hex 80000001; asc     ;;
     56: len 1; hex 80; asc  ;;
     57: len 4; hex d75984e3; asc  Y  ;;
    
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 1685685 page no 10968 n bits 120 index `PRIMARY` of table `shop_zp`.`gt_send_orders` trx id 587D9BC3D lock_mode X locks rec but not gap waiting
    Record lock, heap no 15 PHYSICAL RECORD: n_fields 58; compact format; info bits 0
     0: len 4; hex 00056276; asc   bv;;
     1: len 6; hex 0005867f49bc; asc     I ;;
     2: len 7; hex 700000c7960110; asc p      ;;
     3: len 1; hex 80; asc  ;;
     4: len 4; hex 80064c8e; asc   L ;;
     5: len 8; hex 80000175efaee42c; asc    u   ,;;
     6: len 8; hex 80000175efebf48a; asc    u    ;;
     7: len 4; hex 00000001; asc     ;;
     8: len 8; hex 0000000000066fdf; asc       o ;;
     9: len 1; hex 80; asc  ;;
     10: len 0; hex ; asc ;;
     11: len 4; hex 80000001; asc     ;;
     12: len 12; hex 363035383732373538393331; asc 605872758931;;
     13: len 1; hex 81; asc  ;;
     14: len 1; hex 03; asc  ;;
     15: len 1; hex 87; asc  ;;
     16: len 1; hex 02; asc  ;;
     17: len 0; hex ; asc ;;
     18: len 9; hex 800000000000000063; asc         c;;
     19: len 9; hex 800000000000000063; asc         c;;
     20: len 4; hex 5751aae9; asc WQ  ;;
     21: len 6; hex e69d8ee5a881; asc       ;;
     22: len 11; hex 3133393238303236363132; asc 13928026612;;
     23: len 11; hex 3133393238303236363132; asc 13928026612;;
     24: len 0; hex ; asc ;;
     25: len 30; hex e5b9bfe4b89ce79c8120e78fa0e6b5b7e5b88220e9a699e6b4b2e58cba20; asc                               ; (total 83 bytes);
     26: len 4; hex 00000013; asc     ;;
     27: len 4; hex 00000125; asc    %;;
     28: len 4; hex 00000886; asc     ;;
     29: len 9; hex 800000000000000014; asc          ;;
     30: len 4; hex 80000000; asc     ;;
     31: len 30; hex e5b7a5e4bd9ce697a5e38081e58f8ce4bc91e697a5e4b88ee58187e697a5; asc                               ; (total 42 bytes);
     32: len 0; hex ; asc ;;
     33: len 0; hex ; asc ;;
     34: len 4; hex 80000000; asc     ;;
     35: len 4; hex d757d6d0; asc  W  ;;
     36: len 1; hex 80; asc  ;;
     37: len 1; hex 80; asc  ;;
     38: len 4; hex 80000000; asc     ;;
     39: len 1; hex 80; asc  ;;
     40: len 12; hex e4b88de5bc80e58f91e7a5a8; asc             ;;
     41: len 1; hex 30; asc 0;;
     42: len 0; hex ; asc ;;
     43: len 0; hex ; asc ;;
     44: len 1; hex 81; asc  ;;
     45: len 1; hex 81; asc  ;;
     46: len 1; hex 80; asc  ;;
     47: SQL NULL;
     48: len 1; hex 80; asc  ;;
     49: len 4; hex 00000000; asc     ;;
     50: len 4; hex 80000000; asc     ;;
     51: len 15; hex 313630363038323830393534373033; asc 160608280954703;;
     52: SQL NULL;
     53: len 1; hex 01; asc  ;;
     54: len 4; hex 80000063; asc    c;;
     55: len 4; hex 80000002; asc     ;;
     56: len 1; hex 80; asc  ;;
     57: len 4; hex d751aa85; asc  Q  ;;
    
    *** WE ROLL BACK TRANSACTION (2)

事物1：

\*\*\* (1) WAITING FOR THIS LOCK TO BE GRANTED:  
RECORD LOCKS space id 1685685 page no 15263 n bits 120 index \`PRIMARY\` of table \`shop\_zp\`.\`gt\_send\_orders\` trx id 587D9BC7A lock\_mode X waiting  
Record lock, heap no 17 PHYSICAL RECORD: n\_fields 58; compact format; info bits 0

事物2：

\*\*\* (2) HOLDS THE LOCK(S):  
RECORD LOCKS space id 1685685 page no 15263 n bits 120 index \`PRIMARY\` of table \`shop\_zp\`.\`gt\_send\_orders\` trx id 587D9BC3D lock\_mode X locks rec but not gap  
Record lock, heap no 17 PHYSICAL RECORD: n\_fields 58; compact format; info bits 0

\*\*\* (2) WAITING FOR THIS LOCK TO BE GRANTED:  
RECORD LOCKS space id 1685685 page no 10968 n bits 120 index \`PRIMARY\` of table \`shop\_zp\`.\`gt\_send\_orders\` trx id 587D9BC3D lock\_mode X locks rec but not gap waiting  
Record lock, heap no 15 PHYSICAL RECORD: n\_fields 58; compact format; info bits 0

事物1请求space id 1685685 page no 15263 n bits 120的lock\_mode X，事物2 HOLDS THE LOCK(S)：space id 1685685 page no 15263 n bits 120，事物2是根据主键更新，为什么还要WAITING FOR THIS LOCK TO BE GRANTED:？？？？