2021-03-29
show engine innodb status发现死锁？
今天下午数据库卡顿，事后查询 show engine innodb status可以看到死锁，请问下如何定位到具体问题sql。
具体输出内容如下：
mysql> show engine innodb status \G; *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2021-03-29 17:28:55 0x7fd59a328700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 2 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 7341604 srv_active, 0 srv_shutdown, 32009 srv_idle srv_master_thread log flush and writes: 7372393 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 293475200 --Thread 140555388835584 has waited at row0upd.cc line 2867 for 0.00 seconds the semaphore: X-lock on RW-latch at 0x7fd633c58610 created in file buf0buf.cc line 1460 a writer (thread id 140554939680512) has reserved it in mode SX number of readers 0, waiters flag 1, lock_word: 10000000 Last time read locked in file row0sel.cc line 5124 Last time write locked in file /export/home2/pb2/build/sb_1-33648028-1555165710.26/rpm/BUILD/mysql-5.7.26/m ysql-5.7.26/storage/innobase/buf/buf0flu.cc line 1209 --Thread 140555356354304 has waited at buf0buf.cc line 3510 for 0.00 seconds the semaphore: Mutex at 0x424e3f8, Mutex BUF_POOL created buf0buf.cc:1731, lock var 1 OS WAIT ARRAY INFO: signal count 6982332497 RW-shared spins 0, rounds 1662112161, OS waits 94009520 RW-excl spins 0, rounds 4924993959, OS waits 114054236 RW-sx spins 51180899, rounds 1132632612, OS waits 23399276 Spin rounds per wait: 1662112161.00 RW-shared, 4924993959.00 RW-excl, 22.13 RW-sx ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2021-03-29 16:42:29 0x7fd57c64b700 *** (1) TRANSACTION: TRANSACTION 2119117378, ACTIVE 0 sec starting index read mysql tables in use 5, locked 4 LOCK WAIT 29 lock struct(s), heap size 3520, 3 row lock(s) MySQL thread id 11153919, OS thread handle 140555385108224, query id 13429687542 172.26.150.176 root Search ing rows for update update fsl_order_movement_unit SET unload_time = now() WHERE unload_time is null and dest = 208441180080578 560 AND unit_no IN ( 'RB006A8210329S0056' ) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 6740 page no 182463 n bits 664 index index_order_movement_unit_dest of table `tms_pro d`.`fsl_order_movement_unit` /* Partition `p202103` */ trx id 2119117378 lock_mode X locks rec but not gap waiting Record lock, heap no 67 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 82e488261f442000; asc & D ;; 1: len 8; hex 82e488272a042000; asc '* ;; 2: len 5; hex 99a934e0ad; asc 4 ;; *** (2) TRANSACTION: TRANSACTION 2119117371, ACTIVE 1 sec fetching rows mysql tables in use 5, locked 4 31 lock struct(s), heap size 3520, 6 row lock(s) MySQL thread id 11135566, OS thread handle 140554891736832, query id 13429687421 172.26.150.176 root Search ing rows for update update fsl_order_movement_unit t SET t.unload = 1, t.unload_user = '粤ABH787', t.status = '28', t.update_date = '2021-03-29 16:42:28.554' where ( 1=2 OR t.unit_no IN ( 'RB006A8210329S0380' ) ) and t.dest = 208441180080578560 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 6740 page no 182463 n bits 664 index index_order_movement_unit_dest of table `tms_pro d`.`fsl_order_movement_unit` /* Partition `p202103` */ trx id 2119117371 lock_mode X locks rec but not gap Record lock, heap no 67 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 82e488261f442000; asc & D ;; 1: len 8; hex 82e488272a042000; asc '* ;; 2: len 5; hex 99a934e0ad; asc 4 ;; Record lock, heap no 68 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 82e488261f442000; asc & D ;; 1: len 8; hex 82e488272ac42000; asc '* ;; 2: len 5; hex 99a934e0ad; asc 4 ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 6740 page no 182262 n bits 128 index PRIMARY of table `tms_prod`.`fsl_order_movement_ unit` /* Partition `p202103` */ trx id 2119117371 lock_mode X locks rec but not gap waiting Record lock, heap no 49 PHYSICAL RECORD: n_fields 41; compact format; info bits 0 0: len 8; hex 82e488272ac42000; asc '* ;; 1: len 5; hex 99a934e0ad; asc 4 ;; 2: len 6; hex 00007e4f2416; asc ~O$ ;; 3: len 7; hex 5f000dc0280b9d; asc _ ( ;; 4: len 8; hex 82e4882618842000; asc & ;; 5: len 8; hex 82e3b9e5f20c6000; asc ` ;; 6: len 8; hex 82e488261dc42000; asc & ;; 7: len 8; hex 82e488261f442000; asc & D ;; 8: SQL NULL; 9: SQL NULL; 10: len 8; hex 82e488262e442000; asc &.D ;; 11: len 8; hex 82e488262f842000; asc &/ ;; 12: len 8; hex 82e3b9e5f30c6000; asc ` ;; 13: SQL NULL; 14: SQL NULL; 15: SQL NULL; 16: SQL NULL; 17: SQL NULL; 18: SQL NULL; 19: len 18; hex 524230303641383231303332395330303536; asc RB006A8210329S0056;; 20: len 11; hex 80000000000000a50c3500; asc 5 ;; 21: len 11; hex 800000000000000105ca08; asc ;; 22: len 1; hex 80; asc ;; 23: len 1; hex 80; asc ;; 24: len 1; hex 81; asc ;; 25: len 1; hex 81; asc ;; 26: SQL NULL; 27: len 5; hex 99a93b0a2d; asc ; -;; 28: len 5; hex 99a93b0a75; asc ; u;; 29: SQL NULL; 30: SQL NULL; 31: SQL NULL; 32: len 2; hex 3238; asc 28;; 33: len 9; hex e7b2a4414248373837; asc ABH787;; 34: len 9; hex e7b2a4414248373837; asc ABH787;; 35: len 3; hex 46534c; asc FSL;; 36: len 4; hex 80000000; asc ;; 37: len 10; hex 46534c2e303134353732; asc FSL.014572;; 38: SQL NULL; 39: len 5; hex 99a93b0a76; asc ; v;; 40: len 1; hex 4e; asc N;; *** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter 2119526967 Purge done for trx's n:o < 2118881692 undo n:o < 0 state: running but idle History list length 117804 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 422066815671520, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815769104, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815768192, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815767280, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815765456, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815764544, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815763632, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815762720, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815761808, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815760896, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815759984, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815759072, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815758160, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815757248, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815756336, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815755424, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815754512, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815753600, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815752688, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815751776, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815750864, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815749952, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815749040, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815748128, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815747216, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815746304, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815745392, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815744480, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815743568, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815742656, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815741744, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815740832, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815739920, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815739008, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815738096, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815737184, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815736272, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815735360, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815734448, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815733536, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815732624, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815731712, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815730800, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815729888, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815728976, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815728064, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815727152, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815726240, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815725328, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815724416, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815723504, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815721680, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815720768, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815719856, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815718944, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815718032, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815717120, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815716208, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815715296, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815713472, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815712560, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815711648, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815710736, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815709824, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815708000, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815708912, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815706176, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815707088, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815705264, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815704352, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815702528, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815700704, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815701616, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815699792, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815697056, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815690672, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815689760, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815687936, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815687024, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815685200, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815684288, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815683376, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815682464, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815681552, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815679728, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815678816, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815676080, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815667872, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815665136, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815663312, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815662400, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815656928, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815647808, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815646896, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815642336, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815645984, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815697968, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815655104, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815696144, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815680640, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815692496, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815691584, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815686112, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815674256, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815672432, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815658752, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815666048, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815650544, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815666960, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815652368, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815670608, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815676992, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815698880, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815693408, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815695232, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815694320, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815675168, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815656016, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815649632, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815799200, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815790992, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815788256, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815645072, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815641424, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815659664, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815657840, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815648720, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815664224, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815668784, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422066815644160, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 2119526964, ACTIVE 8 sec updating or deleting mysql tables in use 1, locked 1 4 lock struct(s), heap size 1136, 47 row lock(s), undo log entries 46 MySQL thread id 11155978, OS thread handle 140555388835584, query id 13431806362 172.26.150.184 root updati ng UPDATE fsl_t_loadingdetails t_loadingdetails SET message='outckcode：NJ 备货单:JN2103290009 关联路由订单不 存在或未生成计划运单' WHERE t_loadingdetails.id=209467403062239232 ---TRANSACTION 2119526954, ACTIVE 0 sec 16 lock struct(s), heap size 1136, 12 row lock(s), undo log entries 27 MySQL thread id 11157888, OS thread handle 140555348633344, query id 13431806361 172.26.150.180 root ---TRANSACTION 2119525174, ACTIVE 265 sec starting index read mysql tables in use 1, locked 0 63 lock struct(s), heap size 8400, 3617 row lock(s), undo log entries 3617 MySQL thread id 11155488, OS thread handle 140555360614144, query id 13431806368 172.26.150.184 root Sendin g data select count(*) as count FROM fsl_ljpl_xx_ljyhxx as ljpl_xx_ljyhxx WHERE ljpl_xx_ljyhxx.yhzl = '00000000 00000C01445518' ---TRANSACTION 2119508852, ACTIVE 295 sec 18 lock struct(s), heap size 1136, 851 row lock(s), undo log entries 1733 MySQL thread id 11155421, OS thread handle 140555382712064, query id 13431806367 172.26.150.184 root starti ng INSERT INTO fsl_order_base_line (id,order_base,parent,product,product_code,product_name,box_type,box_type_c ode,box_type_name,length,width,height,weight,volume,unit_weight,unit_volume,qty,box_qty,release_qty,price,p ickup_node,pickup_node_code,pickup_node_name,dispatch_node,dispatch_node_code,dispatch_node_name,plan_arriv al_pickup_node,plan_departure_pickup_node,plan_arrival_dispatch_node,plan_departure_dispatch_node,origin,or igin_code,origin_name,origin_zone,origin_zone_code,origin_zone_name,origin_country_code,origin_country,orig in_province_code,origin_province,origin_city_code,origin_city,ori ---TRANSACTION 2119466599, ACTIVE 1472 sec 197 lock struct(s), heap size 24784, 1718 row lock(s), undo log entries 1831 MySQL thread id 11153595, OS thread handle 140555391497984, query id 13431214515 172.26.150.185 root -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (read thread) I/O thread 7 state: waiting for completed aio requests (read thread) I/O thread 8 state: waiting for completed aio requests (read thread) I/O thread 9 state: waiting for completed aio requests (read thread) I/O thread 10 state: waiting for completed aio requests (read thread) I/O thread 11 state: waiting for completed aio requests (read thread) I/O thread 12 state: waiting for completed aio requests (write thread) I/O thread 13 state: complete io for buf page (write thread) I/O thread 14 state: waiting for completed aio requests (write thread) I/O thread 15 state: waiting for completed aio requests (write thread) I/O thread 16 state: waiting for completed aio requests (write thread) I/O thread 17 state: waiting for completed aio requests (write thread) Pending normal aio reads: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0] , ibuf aio reads:, log i/o's:, sync i/o's: Pending flushes (fsync) log: 1; buffer pool: 1 405070743 OS file reads, 1207737334 OS file writes, 505803793 OS fsyncs 414.79 reads/s, 16384 avg bytes/read, 465.27 writes/s, 89.46 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 246620, seg size 246622, 11414222 merges merged operations: insert 38593624, delete mark 167262221, delete 21629939 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 9461113, node heap has 4783 buffer(s) Hash table size 9461113, node heap has 2986 buffer(s) Hash table size 9461113, node heap has 39622 buffer(s) Hash table size 9461113, node heap has 5262 buffer(s) Hash table size 9461113, node heap has 6840 buffer(s) Hash table size 9461113, node heap has 4541 buffer(s) Hash table size 9461113, node heap has 29183 buffer(s) Hash table size 9461113, node heap has 4283 buffer(s) 201350.82 hash searches/s, 526144.93 non-hash searches/s --- LOG --- Log sequence number 5311155738272 Log flushed up to 5311155738075 Pages flushed up to 5311155664989 Last checkpoint at 5311154068544 0 pending log flushes, 0 pending chkp writes 275102194 log i/o's done, 26.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 35181821952 Dictionary memory allocated 15711062 Buffer pool size 2097024 Free buffers 8082 Database pages 1991441 Old database pages 734960 Modified db pages 20454 Pending reads 0 Pending writes: LRU 0, flush list 5, single page 0 Pages made young 2850239333, not young 22783754345 865.57 youngs/s, 56237.88 non-youngs/s Pages read 405067897, created 115789061, written 864540105 414.29 reads/s, 48.48 creates/s, 426.79 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 30 / 1000 Pages read ahead 10.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 1991441, unzip_LRU len: 0 I/O sum[327312]:cur[4691], unzip sum[0]:cur[0] ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size 262112 Free buffers 1016 Database pages 249007 Old database pages 91898 Modified db pages 2430 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 363837330, not young 2979719482 116.94 youngs/s, 5426.29 non-youngs/s Pages read 51824491, created 15670686, written 235541803 41.98 reads/s, 0.50 creates/s, 46.98 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 23 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 249007, unzip_LRU len: 0 I/O sum[40914]:cur[586], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 262144 Free buffers 1006 Database pages 248975 Old database pages 91886 Modified db pages 3187 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 378602156, not young 3060388029 110.94 youngs/s, 8342.83 non-youngs/s Pages read 54055151, created 14257760, written 81725368 60.47 reads/s, 10.00 creates/s, 93.95 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 34 / 1000 Pages read ahead 7.50/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 248975, unzip_LRU len: 0 I/O sum[40914]:cur[586], unzip sum[0]:cur[0] ---BUFFER POOL 2 Buffer pool size 262112 Free buffers 1001 Database pages 248914 Old database pages 91866 Modified db pages 2438 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 326620731, not young 2690591139 91.45 youngs/s, 7272.86 non-youngs/s Pages read 47927952, created 14214162, written 82573400 49.98 reads/s, 0.00 creates/s, 28.49 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 33 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 248914, unzip_LRU len: 0 I/O sum[40914]:cur[586], unzip sum[0]:cur[0] ---BUFFER POOL 3 Buffer pool size 262144 Free buffers 1004 Database pages 248960 Old database pages 91881 Modified db pages 2400 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 351000590, not young 2824206151 118.94 youngs/s, 10484.76 non-youngs/s Pages read 49815194, created 14324632, written 134327217 56.47 reads/s, 13.49 creates/s, 82.96 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 48 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 248960, unzip_LRU len: 0 I/O sum[40914]:cur[586], unzip sum[0]:cur[0] ---BUFFER POOL 4 Buffer pool size 262112 Free buffers 1004 Database pages 248896 Old database pages 91857 Modified db pages 2540 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 360832072, not young 2896714860 82.96 youngs/s, 4283.36 non-youngs/s Pages read 50765384, created 14337166, written 93232992 48.48 reads/s, 0.00 creates/s, 37.48 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 18 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 248896, unzip_LRU len: 0 I/O sum[40914]:cur[586], unzip sum[0]:cur[0] ---BUFFER POOL 5 Buffer pool size 262144 Free buffers 1016 Database pages 248922 Old database pages 91867 Modified db pages 2543 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 367980223, not young 2727045722 98.95 youngs/s, 4910.54 non-youngs/s Pages read 50199858, created 14372992, written 79048476 43.48 reads/s, 0.00 creates/s, 21.49 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 22 / 1000 Pages read ahead 2.50/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 248922, unzip_LRU len: 0 I/O sum[40914]:cur[586], unzip sum[0]:cur[0] ---BUFFER POOL 6 Buffer pool size 262112 Free buffers 1018 Database pages 248848 Old database pages 91839 Modified db pages 2508 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 352112325, not young 2883133605 116.44 youngs/s, 8480.26 non-youngs/s Pages read 50298088, created 14312141, written 82447266 56.47 reads/s, 13.99 creates/s, 48.48 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 31 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 248848, unzip_LRU len: 0 I/O sum[40914]:cur[586], unzip sum[0]:cur[0] ---BUFFER POOL 7 Buffer pool size 262144 Free buffers 1017 Database pages 248919 Old database pages 91866 Modified db pages 2408 Pending reads 0 Pending writes: LRU 0, flush list 5, single page 0 Pages made young 349253906, not young 2721955357 128.94 youngs/s, 7036.98 non-youngs/s Pages read 50181779, created 14299522, written 75643583 56.97 reads/s, 10.49 creates/s, 66.97 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 32 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 248919, unzip_LRU len: 0 I/O sum[40914]:cur[589], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 7 read views open inside InnoDB Process ID=13994, Main thread ID=140555936835328, state: sleeping Number of rows inserted 35073052077, updated 2200983088, deleted 193296618, read 4790274730966 3324.34 inserts/s, 119.44 updates/s, 1.50 deletes/s, 194656.67 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================复制
我来答
添加附件
收藏
分享
问题补充
5条回答
默认
最新
引发死锁的SQL是下面两条
update fsl_order_movement_unit SET unload_time = now() WHERE unload_time is null and dest = 208441180080578 560 AND unit_no IN ( 'RB006A8210329S0056' ) 以及 update fsl_order_movement_unit t SET t.unload = 1, t.unload_user = '粤ABH787', t.status = '28', t.update_date = '2021-03-29 16:42:28.554' where ( 1=2 OR t.unit_no IN ( 'RB006A8210329S0380' ) ) and t.dest = 208441180080578560复制
死锁是由于两个事务分别持有index_order_movement_unit_dest 和 primary上的行锁，并申请对方持有的锁时发生的；
具体原因，可以提供下fsl_order_movement_unit的建表语句 以及 分别explain下这个update语句的执行计划
评论
有用 0 打赏 0
执行计划如下：
第二条的执行计划
表定义如下
CREATE TABLE `fsl_order_movement_unit` ( `id` bigint(20) NOT NULL COMMENT 'id', `shipment` bigint(20) DEFAULT NULL COMMENT ' ', `order_release` bigint(20) DEFAULT NULL COMMENT ' ', `origin` bigint(20) DEFAULT NULL COMMENT ' ', `dest` bigint(20) DEFAULT NULL COMMENT ' ', `origin_dock` bigint(20) DEFAULT NULL COMMENT '发货地道口', `dest_dock` bigint(20) DEFAULT NULL COMMENT '收货地道口', `order_movement` bigint(20) DEFAULT NULL COMMENT 'Order Movement', `order_movement_line` bigint(20) DEFAULT NULL COMMENT 'Order Movement Line', `order_unit` bigint(20) DEFAULT NULL COMMENT 'Order Unit', `product` bigint(20) DEFAULT NULL COMMENT '商品', `product_code` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '商品代码', `product_name` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '商品名称', `box_type` bigint(20) DEFAULT NULL COMMENT '包装', `box_type_code` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '包装代码', `box_type_name` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '包装名称', `unit_no` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '条码', `unit_weight` decimal(23,6) DEFAULT NULL COMMENT '重量', `unit_volume` decimal(23,6) DEFAULT NULL COMMENT '体积', `ship` tinyint(4) DEFAULT '0' COMMENT '配载标识', `ready` tinyint(4) DEFAULT '0' COMMENT '备货标识', `load` tinyint(4) DEFAULT '0' COMMENT '装货标识', `unload` tinyint(4) DEFAULT '0' COMMENT '卸货标识', `ready_time` datetime DEFAULT NULL COMMENT '备货确认时间', `load_time` datetime DEFAULT NULL COMMENT '装货时间', `unload_time` datetime DEFAULT NULL COMMENT '卸货时间', `3d_x` int(11) DEFAULT NULL COMMENT '3d X', `3d_y` int(11) DEFAULT NULL COMMENT '3d Y', `3d_z` int(11) DEFAULT NULL COMMENT '3d Z', `status` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '10' COMMENT '状态', `load_user` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '装货用户', `unload_user` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '卸货用户', `domain_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'Domain Name', `version` int(11) DEFAULT '0' COMMENT 'version', `insert_user` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT 'Insert User', `insert_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Insert Date', `update_user` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT 'Update User', `update_date` datetime DEFAULT NULL COMMENT 'Update Date', `unload_flag` varchar(2) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT 'N' COMMENT '更新交付标记', PRIMARY KEY (`id`,`insert_date`), KEY `fsl_idx_order_movement_unit_insert_date` (`insert_date`), KEY `indexl_order_movement_unit_shipment` (`shipment`) USING BTREE, KEY `index_order_movement_unit_dest` (`dest`) USING BTREE, KEY `index_order_movement_unit_order_movement` (`order_movement`) USING BTREE, KEY `index_order_movement_unit_order_release` (`order_release`) USING BTREE, KEY `index_order_movement_unit_order_unit` (`order_unit`) USING BTREE, KEY `index_order_movement_unit_origin` (`origin`) USING BTREE, KEY `index_order_movement_unit_product` (`product`) USING BTREE, KEY `index_order_movement_unit_unit_no` (`unit_no`) USING BTREE, KEY `index_order_movement_unit_order_movement_line` (`order_movement_line`), KEY `unload` (`unload`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='om unit' /*!50500 PARTITION BY RANGE COLUMNS(insert_date) (PARTITION p201911 VALUES LESS THAN ('2019-12-01') ENGINE = InnoDB, PARTITION p201912 VALUES LESS THAN ('2020-01-01') ENGINE = InnoDB, PARTITION p202001 VALUES LESS THAN ('2020-02-01') ENGINE = InnoDB, PARTITION p202002 VALUES LESS THAN ('2020-03-01') ENGINE = InnoDB, PARTITION p202003 VALUES LESS THAN ('2020-04-01') ENGINE = InnoDB, PARTITION p202004 VALUES LESS THAN ('2020-05-01') ENGINE = InnoDB, PARTITION p202005 VALUES LESS THAN ('2020-06-01') ENGINE = InnoDB, PARTITION p202006 VALUES LESS THAN ('2020-07-01') ENGINE = InnoDB, PARTITION p202007 VALUES LESS THAN ('2020-08-01') ENGINE = InnoDB, PARTITION p202008 VALUES LESS THAN ('2020-09-01') ENGINE = InnoDB, PARTITION p202009 VALUES LESS THAN ('2020-10-01') ENGINE = InnoDB, PARTITION p202010 VALUES LESS THAN ('2020-11-01') ENGINE = InnoDB, PARTITION p202011 VALUES LESS THAN ('2020-12-01') ENGINE = InnoDB, PARTITION p202012 VALUES LESS THAN ('2021-01-01') ENGINE = InnoDB, PARTITION p202101 VALUES LESS THAN ('2021-02-01') ENGINE = InnoDB, PARTITION p202102 VALUES LESS THAN ('2021-03-01') ENGINE = InnoDB, PARTITION p202103 VALUES LESS THAN ('2021-04-01') ENGINE = InnoDB, PARTITION p202104 VALUES LESS THAN ('2021-05-01') ENGINE = InnoDB, PARTITION p202105 VALUES LESS THAN ('2021-06-01') ENGINE = InnoDB, PARTITION p202106 VALUES LESS THAN ('2021-07-01') ENGINE = InnoDB, PARTITION p202107 VALUES LESS THAN ('2021-08-01') ENGINE = InnoDB, PARTITION p202108 VALUES LESS THAN ('2021-09-01') ENGINE = InnoDB, PARTITION p202109 VALUES LESS THAN ('2021-10-01') ENGINE = InnoDB, PARTITION p202110 VALUES LESS THAN ('2021-11-01') ENGINE = InnoDB, PARTITION p202111 VALUES LESS THAN ('2021-12-01') ENGINE = InnoDB, PARTITION p202112 VALUES LESS THAN ('2022-01-01') ENGINE = InnoDB) */复制
评论 有用 0 打赏 0
KEY index_order_movement_unit_dest (dest) USING BTREE,
可以将这个索引改成一个组合索引(dest,unit_no)
评论 有用 1 打赏 0
回答交流
Markdown
请输入正文
提交
相关推荐
MySQL5.7通过data目录恢复怎么没有存储过程？
回答 1
已采纳
正常情况下是有的，是不是你这个data目录里只有应用库没有系统库？
MySQL批量执行脚本，执行结果写到文件中，现在执行过程中有错误，但是结果文件只有结果，没有对应的SQL，怎么能拿到对应的SQL？
回答 1
可以通过在脚本中添加日志记录语句，将执行的SQL语句记录到日志文件中。
MySQL数据库如何使用自增列？
回答 2
已采纳
在MySQL数据库表中可以将某个列定义为自增列从而在该列中存储一个整数序列，通过这个整数序列中的每一个唯一的值来唯一标识表中的每一行。自增列的定义关键词为AUTOINCREMENT。一个表中只能有一个
mysql socket文件存在，但使用socket连接却报错
回答 2
一般出现这种问题，是你用rpm安装之后，又重新初始化了一个新的mysql，用msyqlurootp这样访问，mysql数据库会去找默认的socket，如果你没在/etc/my.cnf里添加【clien
MySQL什么情况下应不建或少建索引
回答 4
已采纳
1、如果表数据量太少可以不建索引，有时候全表扫描可能比索引快。2、对于DML操作很频繁的表不建议，前提是保证查询性能的情况下。3、表数据重复且分布平均的表字段，比如表记录10万行，取值只有男或者女。4
MySQL 备份还原是不是还可以在不同的版本中进行？
回答 3
已采纳
可以的,用逻辑备份导出sql语句,不但可以跨版本,还能跨系统,跨数据库.物理备份的话,就不一定了,跨小版本应该没得问题.
MySQL 怎么设置出来的 luokai/.mysql_history 用户操作MYSQL记录?
回答 1
https://dev.mysql.com/doc/refman/5.7/en/mysqllogging.htmlOnUnix,mysqlwritesthestatementstoahistoryfi
MySQL 平时操作建表都是窗体操作，没有可视化界面吗？
回答 3
已采纳
workbench，官方工具
ElasticSearch 比 MySQL 更适合复杂条件搜索的原因是什么？
回答 1
已采纳
准备的来说不是这样的。es不能关联。不是说他适合复杂。而是他是全文索引，有个倒排索引的概念在里面。每个进来的都做了分词处理，形成了索引。如果觉得mysql慢一定是索引没建立，或者说索引建立不对，再或者
MySQL用哪个图形化界面比较好？
回答 3
workbench还可以带监控功能官方出品 dbeaver大而全也可以。
问题信息
请登录之后查看
邀请回答
暂无人订阅该标签，敬请期待~~
热门文章
