MySQL死锁案例分析
- 2025-06-02 16:09:00
- 丁国栋
- 原创 8
===================================== 2025-06-02 14:37:23 0x7fdeafcff700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 1 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 4595146 srv_active, 0 srv_shutdown, 2512 srv_idle srv_master_thread log flush and writes: 4597658 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 3211579 OS WAIT ARRAY INFO: signal count 7202133 RW-shared spins 0, rounds 24029227, OS waits 1309440 RW-excl spins 0, rounds 99133921, OS waits 14071 RW-sx spins 201140, rounds 1726826, OS waits 13304 Spin rounds per wait: 24029227.00 RW-shared, 99133921.00 RW-excl, 8.59 RW-sx ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2025-06-02 14:33:33 0x7fdedf1ff700 *** (1) TRANSACTION: TRANSACTION 772524087, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 85685566, OS thread handle 140657389729536, query id 2702858504 10.12.1.95 zentao_net_rw Searching rows for update UPDATE eps_contact SET `counts` = '0' wHeRe counts < '0' AND eps_contact.lang in('zh-cn', 'all') *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1335 page no 3 n bits 160 index PRIMARY of table `zentao_net`.`eps_contact` trx id 772524087 lock_mode X locks rec but not gap waiting Record lock, heap no 14 PHYSICAL RECORD: n_fields 14; compact format; info bits 0 0: len 2; hex 8480; asc ;; 1: len 6; hex 00002e0bc836; asc . 6;; 2: len 7; hex 5a000002b20a2b; asc Z +;; 3: len 30; hex 616c6c202020202020202020202020202020202020202020202020202020; asc all ;; 4: len 9; hex e9ad8fe4b8ade698be; asc ;; 5: len 11; hex 3138353631393339373236; asc 18561939726;; 6: len 24; hex 7765697a686f6e677869616e406368616e64616f2e636f6d; asc weizhongxian@chandao.com;; 7: len 10; hex 31373436373439333938; asc 1746749398;; 8: len 11; hex 3138353631393339373236; asc 18561939726;; 9: len 0; hex ; asc ;; 10: len 2; hex 800f; asc ;; 11: len 2; hex 7fff; asc ;; 12: len 30; hex 7a682d636e20202020202020202020202020202020202020202020202020; asc zh-cn ;; 13: len 4; hex 80a18e70; asc p;; *** (2) TRANSACTION: TRANSACTION 772524086, ACTIVE 0 sec updating or deleting mysql tables in use 1, locked 1 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 MySQL thread id 85685567, OS thread handle 140595202881280, query id 2702858503 10.12.1.95 zentao_net_rw updating UPDATE eps_contact SET counts=counts-1, total=total+1 wHeRe id = '1152' AND eps_contact.lang in('zh-cn', 'all') *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 1335 page no 3 n bits 160 index PRIMARY of table `zentao_net`.`eps_contact` trx id 772524086 lock_mode X locks rec but not gap Record lock, heap no 14 PHYSICAL RECORD: n_fields 14; compact format; info bits 0 0: len 2; hex 8480; asc ;; 1: len 6; hex 00002e0bc836; asc . 6;; 2: len 7; hex 5a000002b20a2b; asc Z +;; 3: len 30; hex 616c6c202020202020202020202020202020202020202020202020202020; asc all ;; 4: len 9; hex e9ad8fe4b8ade698be; asc ;; 5: len 11; hex 3138353631393339373236; asc 18561939726;; 6: len 24; hex 7765697a686f6e677869616e406368616e64616f2e636f6d; asc weizhongxian@chandao.com;; 7: len 10; hex 31373436373439333938; asc 1746749398;; 8: len 11; hex 3138353631393339373236; asc 18561939726;; 9: len 0; hex ; asc ;; 10: len 2; hex 800f; asc ;; 11: len 2; hex 7fff; asc ;; 12: len 30; hex 7a682d636e20202020202020202020202020202020202020202020202020; asc zh-cn ;; 13: len 4; hex 80a18e70; asc p;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1335 page no 5 n bits 520 index counts of table `zentao_net`.`eps_contact` trx id 772524086 lock_mode X locks rec but not gap waiting Record lock, heap no 333 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 2; hex 8000; asc ;; 1: len 2; hex 8480; asc ;; *** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter 772554475 Purge done for trx's n:o < 772554411 undo n:o < 0 state: running but idle History list length 303 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 422163356068784, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422163356067776, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422163356066768, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422163356065760, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422163356064752, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422163356063744, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422163356062736, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422163356060720, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422163356056688, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422163356058704, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422163356061728, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422163356057696, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422163356059712, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422163356054672, not started 0 lock struct(s), heap size 1136, 0 row lock(s) -------- 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 (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] , ibuf aio reads:, log i/o's:, sync i/o's: Pending flushes (fsync) log: 0; buffer pool: 0 42695132 OS file reads, 128444527 OS file writes, 116929921 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 28.97 writes/s, 28.97 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 686, seg size 688, 2940056 merges merged operations: insert 3067308, delete mark 179542, delete 22545 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 796967, node heap has 0 buffer(s) Hash table size 796967, node heap has 0 buffer(s) Hash table size 796967, node heap has 0 buffer(s) Hash table size 796967, node heap has 0 buffer(s) Hash table size 796967, node heap has 0 buffer(s) Hash table size 796967, node heap has 0 buffer(s) Hash table size 796967, node heap has 0 buffer(s) Hash table size 796967, node heap has 0 buffer(s) 0.00 hash searches/s, 75004.00 non-hash searches/s --- LOG --- Log sequence number 353062856535 Log flushed up to 353062856423 Pages flushed up to 352689247997 Last checkpoint at 352689247997 0 pending log flushes, 0 pending chkp writes 113381458 log i/o's done, 29.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 3303014400 Dictionary memory allocated 4185214 Buffer pool size 196608 Free buffers 2047 Database pages 194561 Old database pages 71780 Modified db pages 20676 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 244100107, not young 663538594 0.00 youngs/s, 0.00 non-youngs/s Pages read 42692839, created 1620361, written 13641102 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 194561, unzip_LRU len: 0 I/O sum[16]:cur[0], unzip sum[0]:cur[0] ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size 98304 Free buffers 1023 Database pages 97281 Old database pages 35890 Modified db pages 10122 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 120172637, not young 337502333 0.00 youngs/s, 0.00 non-youngs/s Pages read 21380414, created 812624, written 6759612 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 97281, unzip_LRU len: 0 I/O sum[8]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 98304 Free buffers 1024 Database pages 97280 Old database pages 35890 Modified db pages 10554 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 123927470, not young 326036261 0.00 youngs/s, 0.00 non-youngs/s Pages read 21312425, created 807737, written 6881490 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 97280, unzip_LRU len: 0 I/O sum[8]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Process ID=483148, Main thread ID=140684315055872, state: sleeping Number of rows inserted 133306552, updated 56774410, deleted 1035749, read 728022553457 93.91 inserts/s, 15.98 updates/s, 0.00 deletes/s, 174794.21 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================--
发表评论