本文主要是介绍MySQL中show engine innodb status的详解，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
应用报错中，我们需要从获取到一些关键的信息点，死锁等情况说明，可以通过show engine innodb status中获取
mysql>show engine innodb status +----------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Type | Name | Status | +----------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | InnoDB | | ===================================== 2016-05-12 08:52:28 2b6142bc7700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 50 seconds //最近50秒内每2秒的平均值 ----------------- BACKGROUND THREAD //backgroup 线程 ----------------- srv_master_thread loops: 1332198 srv_active, 0 srv_shutdown, 64396 srv_idle srv_master_thread log flush and writes: 1396594 ---------- SEMAPHORES // 信号 ---------- OS WAIT ARRAY INFO: reservation count 118146 //os wait 的信息 ，reservation count 表示InnoDB产生了多少次OS WAIT OS WAIT ARRAY INFO: signal count 186714 // 进行OS WAIT线程，接收到多少次信号(single)被唤醒，如果这个single数值越大，几十万或者几百万，可能是很多I/0等待或者是InnoDB争用问题(关于争用问题可能与OS调度有关，可以尝试减少innodb_thread_concurrency参数) Mutex spin waits 1664035, rounds 4276317, OS waits 20348 // Mutex spin线程无法获取锁而进入Spin wait ,rounds是spin wait 进行轮询检查mutextes的次数，os wait 线程放弃spin-wait 进入挂起状态 RW-shared spins 302454, rounds 11667281, OS waits 69050 //RW-shared 共享锁， RW-excl spins 83942, rounds 4021896, OS waits 28377 // RW-excl 排他锁 Spin rounds per wait: 2.57 mutex, 38.58 RW-shared, 47.91 RW-excl // 备注：要明白Innodb如何处理互斥量(Mutexes)，以及什么是两步获得锁(two-step approach)。首先进程， 试图获得一个锁，如果此锁被它人占用。它就会执行所谓的spin wait,即所谓循环的查询”锁被释放了吗？”。 如果在循环过程中，一直未得到锁释放的信息，则其转入OS WAIT，即所谓线程进入挂起(suspended)状态。 直到锁被释放后，通过信号(singal)唤醒线程 Spin wait的消耗远小于OS waits。Spinwait利用cpu的空闲时间，检查锁的状态， OS Wait会有所谓content switch，从CPU内核中换出当前执行线程以供其它线程使用。 你可以通过innodb_sync_spin_loops参数来平衡spin wait和os wait------------------------ LATEST DETECTED DEADLOCK ------------------------ 2016-05-11 18:52:09 2b6677e07700 //死锁发生的时间 *** (1) TRANSACTION: TRANSACTION 495116414, ACTIVE 0.092 sec fetching rows mysql tables in use 1, locked 1 LOCK WAIT 14 lock struct(s), heap size 6544, 20 row lock(s), undo log entries 6 LOCK BLOCKING MySQL thread id: 870003 block 876753 MySQL thread id 876753, OS thread handle 0x2b6685903700, query id 315677415 10.168.152.132 dsc Searching rows for update update aaaaset xxx=xxx+(-1)where id=412 and xxx+(-1)>=0 //显示第一个死锁的的第一个事务 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 558 page no 5 n bits 144 index `idx_aaaa_unique` of table `test`.`aaaa` trx id 495116414 lock_mode X locks rec but not gap waiting Record lock, heap no 17 PHYSICAL RECORD: n_fields 23; compact format; info bits 0 // 以上表示死锁发生时事务1等待的锁，事务想获得aaaa表的idx_aaaa_unique索引对应的X排他锁(Innodb的锁是与索引相关)0: len 8; hex 8000000000000001; asc ;;1: len 2; hex 5748; asc WH;;2: len 8; hex 800000000000004b; asc K;;3: len 8; hex 8000000000000002; asc ;;4: len 8; hex 8000000000002725; asc '%;;5: len 8; hex 8000000000000215; asc ;;6: len 2; hex 5a50; asc ZP;;7: len 8; hex 4231363033313441; asc B160314A;;8: len 6; hex 00001d82e06a; asc j;;9: len 7; hex 1d00000235151a; asc 5 ;;10: len 8; hex 800000000000019c; asc ;;11: len 8; hex 8000000000000000; asc ;;12: len 5; hex 9998da0000; asc ;;13: len 5; hex 999f5a0000; asc Z ;;14: len 10; hex 5a303230323032303031; asc Z020202001;;15: len 12; hex e5b9bfe4b89ce5b9bfe5b79e; asc ;;16: len 4; hex 80001b2f; asc /;;17: len 7; hex 80000000000000; asc ;;18: len 23; hex 50555243484153455f4f524445525f494e5f53544f434b; asc PURCHASE_ORDER_IN_STOCK;;19: len 22; hex 53493230313630343136303030303136333531313735; asc SI20160416000016351175;;20: len 5; hex 99992b1384; asc + ;;21: SQL NULL;22: len 5; hex 99994d7c8d; asc M| ;; *** (2) TRANSACTION: // 事务2的状态 TRANSACTION 495116394, ACTIVE 0.246 sec fetching rows mysql tables in use 1, locked 1 17 lock struct(s), heap size 2936, 18 row lock(s), undo log entries 21 MySQL thread id 870003, OS thread handle 0x2b6677e07700, query id 315677426 10.168.152.132 dsc Searching rows for update update aaaaset xxx=xxx+(-2)where id=430 and xxx+(-2)>=0 *** (2) HOLDS THE LOCK(S): // 表示事务2获得的锁 RECORD LOCKS space id 558 page no 5 n bits 144 index `idx_aaaa_unique` of table `test`.`aaaa` trx id 495116394 lock_mode X locks rec but not gap Record lock, heap no 17 PHYSICAL RECORD: n_fields 23; compact format; info bits 00: len 8; hex 8000000000000001; asc ;;1: len 2; hex 5748; asc WH;;2: len 8; hex 800000000000004b; asc K;;3: len 8; hex 8000000000000002; asc ;;4: len 8; hex 8000000000002725; asc '%;;5: len 8; hex 8000000000000215; asc ;;6: len 2; hex 5a50; asc ZP;;7: len 8; hex 4231363033313441; asc B160314A;;8: len 6; hex 00001d82e06a; asc j;;9: len 7; hex 1d00000235151a; asc 5 ;;10: len 8; hex 800000000000019c; asc ;;11: len 8; hex 8000000000000000; asc ;;12: len 5; hex 9998da0000; asc ;;13: len 5; hex 999f5a0000; asc Z ;;14: len 10; hex 5a303230323032303031; asc Z020202001;;15: len 12; hex e5b9bfe4b89ce5b9bfe5b79e; asc ;;16: len 4; hex 80001b2f; asc /;;17: len 7; hex 80000000000000; asc ;;18: len 23; hex 50555243484153455f4f524445525f494e5f53544f434b; asc PURCHASE_ORDER_IN_STOCK;;19: len 22; hex 53493230313630343136303030303136333531313735; asc SI20160416000016351175;;20: len 5; hex 99992b1384; asc + ;;21: SQL NULL;22: len 5; hex 99994d7c8d; asc M| ;;Record lock, heap no 59 PHYSICAL RECORD: n_fields 23; compact format; info bits 00: len 8; hex 8000000000000001; asc ;;1: len 2; hex 5748; asc WH;;2: len 8; hex 800000000000004b; asc K;;3: len 8; hex 8000000000000002; asc ;;4: len 8; hex 800000000000276a; asc 'j;;5: len 8; hex 80000000000002c2; asc ;;6: len 2; hex 5a50; asc ZP;;7: len 9; hex 423136303231374341; asc B160217CA;;8: len 6; hex 00001d82e06a; asc j;;9: len 7; hex 1d00000235169f; asc 5 ;;10: len 8; hex 80000000000001db; asc ;;11: len 8; hex 8000000000000000; asc ;;12: len 5; hex 9998a20000; asc ;;13: len 5; hex 99a2600000; asc ` ;;14: len 10; hex 5a303230323032303031; asc Z020202001;;15: len 12; hex e5b9bfe4b89ce5b9bfe5b79e; asc ;;16: len 4; hex 80000772; asc r;;17: len 7; hex 80000000000000; asc ;;18: len 23; hex 50555243484153455f4f524445525f494e5f53544f434b; asc PURCHASE_ORDER_IN_STOCK;;19: len 22; hex 53493230313630343136303030303137333630353531; asc SI20160416000017360551;;20: len 5; hex 99992b1385; asc + ;;21: SQL NULL;22: len 5; hex 99994d7c8d; asc M| ;;*** (2) WAITING FOR THIS LOCK TO BE GRANTED: // 表示事务2等待的锁 RECORD LOCKS space id 558 page no 4 n bits 152 index `idx_aaaa_unique` of table `test`.`aaaa` trx id 495116394 lock_mode X locks rec but not gap waiting Record lock, heap no 63 PHYSICAL RECORD: n_fields 23; compact format; info bits 00: len 8; hex 8000000000000001; asc ;;1: len 2; hex 5748; asc WH;;2: len 8; hex 800000000000004b; asc K;;3: len 8; hex 8000000000000002; asc ;;4: len 8; hex 8000000000000065; asc e;;5: len 8; hex 80000000000000a8; asc ;;6: len 2; hex 5a50; asc ZP;;7: len 9; hex 423136303232314b41; asc B160221KA;;8: len 6; hex 00001d82e07e; asc ~;;9: len 7; hex 2b000001d920ad; asc + ;;10: len 8; hex 80000000000001c8; asc ;;11: len 8; hex 8000000000000000; asc ;;12: len 5; hex 9998aa0000; asc ;;13: len 5; hex 99a2680000; asc h ;;14: len 10; hex 5a303230323032303031; asc Z020202001;;15: len 12; hex e5b9bfe4b89ce5b9bfe5b79e; asc ;;16: len 4; hex 80000b14; asc ;;17: len 7; hex 80000000000000; asc ;;18: len 23; hex 50555243484153455f4f524445525f494e5f53544f434b; asc PURCHASE_ORDER_IN_STOCK;;19: len 22; hex 53493230313630343136303030303137333630353531; asc SI20160416000017360551;;20: len 5; hex 99992b1385; asc + ;;21: SQL NULL;22: len 5; hex 99994d7c8d; asc M| ;;*** WE ROLL BACK TRANSACTION (1) // 表示选择了哪个事务回滚，避免无限期死锁等待 // innodb有一个内在的死锁检测工具，当死锁超过一定时间后，会回滚其中一个事务,innodb_lock_wait_timeout 可配置死锁等待超时时间------------ TRANSACTIONS // 包含了InnoDB事务(transaction)的统计信息 ------------ Trx id counter 495910498 // 当前的transaction id ,这是个系统变量，随着每次新的transaction产生而增加 Purge done for trx's n:o < 495910389 undo n:o < 0 state: running but idle //正在进行清空的操作操作的transaction ID History list length 2606 // 记录了undo spaces 内unpurged 的事务个数 //Purge的原则就是记录没有被其它事务继续使用了LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 329193748744296, not started MySQL thread id 909825, OS thread handle 0x2b6142bc7700, query id 325773092 10.143.34.172 dsc init show engine innodb status ---TRANSACTION 329193658413160, not started MySQL thread id 909832, OS thread handle 0x2b667d881700, query id 325773024 10.168.108.146 dsc cleaning up.....此处省略...---TRANSACTION 329194102134888, not started MySQL thread id 886232, OS thread handle 0x2b6686c40700, query id 325769275 10.252.160.92 dsc cleaning up -------- FILE I/O // 显示了I/O Helper thread d的状态，包含一些统计信息 -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (read thread) I/O thread 4 state: waiting for i/o request (read thread) I/O thread 5 state: waiting for i/o request (read thread) I/O thread 6 state: waiting for i/o request (write thread) I/O thread 7 state: waiting for i/o request (write thread) I/O thread 8 state: waiting for i/o request (write thread) I/O thread 9 state: waiting for i/o request (write thread) // 以上显示了I/O Helper thread的状态 Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 // 显示各个I/O Helper thread的pending operations,pending的log和buffer pool thread的fsync()调用 2531032 OS file reads, 61115257 OS file writes, 51279005 OS fsyncs //显示了reads writes fsync() 调用次数 0.00 reads/s, 0 avg bytes/read, 46.64 writes/s, 39.30 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 3606, seg size 3608, 38466 merges // seg size 表示当前插入缓冲的大小为3608*16KB，大约为57728KB。free list len代表了空闲列表的长度，merges 表示合并次数 merged operations:insert 34642, delete mark 1008134, delete 0 //insert 插入的记录数，delete mark 打上的标记,delete 删除的次数 discarded operations:insert 0, delete mark 0, delete 0 AHI PARTITION 1: Hash table size 4980539, node heap has 161 buffer(s) AHI PARTITION 2: Hash table size 4980539, node heap has 90 buffer(s) AHI PARTITION 3: Hash table size 4980539, node heap has 225 buffer(s) AHI PARTITION 4: Hash table size 4980539, node heap has 352 buffer(s) AHI PARTITION 5: Hash table size 4980539, node heap has 3556 buffer(s) AHI PARTITION 6: Hash table size 4980539, node heap has 4393 buffer(s) AHI PARTITION 7: Hash table size 4980539, node heap has 3052 buffer(s) AHI PARTITION 8: Hash table size 4980539, node heap has 145 buffer(s) 26.62 hash searches/s, 51.78 non-hash searches/s --- LOG // 记录了transaction log 子系统的信息 --- Log sequence number 264509449071 //显示当前log sequence number表示有多少字节写入到log文件内 Log flushed up to 264509449064 //显示已经被flushed(写入磁盘)的logs Pages flushed up to 264509446093 Last checkpoint at 264509412298 //显示最后一个checkpoint 的logs 0 pending log flushes, 0 pending chkp writes 27332545 log i/o's done, 16.22 log i/o's/second // 显示pending log 的统计信息 ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 20653670400; in additional pool allocated 0 // 显示分配给innodb 的内存大小，以及additional pool 使用的大小 (0表示没有使用) Dictionary memory allocated 1905658 Buffer pool size 1228800 // buffer pool size > database pages 因为buffer pool size 还会存放lock index hash index 等一些其他系统信息 Free buffers 8192 Database pages 1208634 Old database pages 445992 Modified db pages 8 Pending reads 0 //显示了pending的reads 和writes Pending writes: LRU 0, flush list 0, single page 0 // 显示InnoDB读写和创建的页面(pages) Pages made young 842882, not young 127112054 0.00 youngs/s, 0.00 non-youngs/s Pages read 2637230, created 729565, written 30484675 0.00 reads/s, 0.34 creates/s, 24.06 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 //显示buffer pool 的命中率 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 1208634, unzip_LRU len: 0 I/O sum[9624]:cur[0], unzip sum[0]:cur[0] ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size 153600 Free buffers 1024 Database pages 151095 Old database pages 55755 Modified db pages 2 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 32021, not young 15238551 0.00 youngs/s, 0.00 non-youngs/s Pages read 326672, created 90881, written 6387155 0.00 reads/s, 0.00 creates/s, 4.80 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: 151095, unzip_LRU len: 0 I/O sum[1203]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 153600 Free buffers 1024 Database pages 151061 Old database pages 55742 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 32063, not young 15503760 0.00 youngs/s, 0.00 non-youngs/s Pages read 327353, created 91471, written 978265 0.00 reads/s, 0.00 creates/s, 0.24 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: 151061, unzip_LRU len: 0 I/O sum[1203]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 2 Buffer pool size 153600 Free buffers 1024 Database pages 151107 Old database pages 55759 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 32746, not young 14789866 0.00 youngs/s, 0.00 non-youngs/s Pages read 328273, created 91339, written 4147582 0.00 reads/s, 0.00 creates/s, 3.50 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: 151107, unzip_LRU len: 0 I/O sum[1203]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 3 Buffer pool size 153600 Free buffers 1024 Database pages 151088 Old database pages 55752 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 31950, not young 15539726 0.00 youngs/s, 0.00 non-youngs/s Pages read 329629, created 91566, written 2998238 0.00 reads/s, 0.00 creates/s, 1.68 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: 151088, unzip_LRU len: 0 I/O sum[1203]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 4 Buffer pool size 153600 Free buffers 1024 Database pages 151063 Old database pages 55743 Modified db pages 1 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 323393, not young 17803631 0.00 youngs/s, 0.00 non-youngs/s Pages read 337003, created 90967, written 3974291 0.00 reads/s, 0.08 creates/s, 4.38 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: 151063, unzip_LRU len: 0 I/O sum[1203]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 5 Buffer pool size 153600 Free buffers 1024 Database pages 151066 Old database pages 55744 Modified db pages 3 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 323468, not young 18135650 0.00 youngs/s, 0.00 non-youngs/s Pages read 335789, created 90992, written 3382034 0.00 reads/s, 0.26 creates/s, 3.04 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: 151066, unzip_LRU len: 0 I/O sum[1203]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 6 Buffer pool size 153600 Free buffers 1024 Database pages 151073 Old database pages 55747 Modified db pages 2 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 34315, not young 15008240 0.00 youngs/s, 0.00 non-youngs/s Pages read 324769, created 91064, written 5580181 0.00 reads/s, 0.00 creates/s, 4.66 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: 151073, unzip_LRU len: 0 I/O sum[1203]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 7 Buffer pool size 153600 Free buffers 1024 Database pages 151081 Old database pages 55750 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 32926, not young 15092630 0.00 youngs/s, 0.00 non-youngs/s Pages read 327742, created 91285, written 3036929 0.00 reads/s, 0.00 creates/s, 1.76 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: 151081, unzip_LRU len: 0 I/O sum[1203]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS //显示了row operations 及其他一些统计信息 -------------- 0 queries inside InnoDB, 0 queries in queue //显示了有多少个线程在InnoDB内核 52 read views open inside InnoDB // 有多少个read view 被打开，一个read view 是一致性保证MVCC "snapshot" Main thread process no. 46196, id 47719070582528, state: sleeping //显示内核main thread的状态信息。 Number of rows inserted 35803330, updated 3181469, deleted 14015545, read 7740416065 12.48 inserts/s, 0.68 updates/s, 0.30 deletes/s, 3005.82 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================
上面的两个死锁还是很简单就能处理的，分析这个表的时候，发现，研发在设计之时，忘记设置主键ID，而innodb存储引擎在这种情况下，会导致update的过程中，将当前
表进行表锁，从而出现了死锁的情况
解决方法：查看表结构，统计表数据，因为数据量不大，增加主键ID，解决问题。
这篇关于MySQL中show engine innodb status的详解的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
