死锁解决 · JAVA

[TOC] # 简介使用mysqladmin debug查看，能看到所有产生锁的线程，但无法判断哪个才是根因；开启innodb\_lock\_monitor后，再使用show engine innodb status查看，能够找到锁阻塞的根因。 # 查询锁表 ~~~ show OPEN TABLES where In_use > 0; ~~~ 新建一个会话执行如下的显示锁示例 ~~~ LOCK TABLES account_data.account READ; SELECT SLEEP(160); UNLOCK TABLES account_data.account; ~~~ 另开启一个会话检查锁表情况： ~~~ mysql> show OPEN TABLES where In_use > 0; +--------------+---------+--------+-------------+ | Database | Table | In_use | Name_locked | +--------------+---------+--------+-------------+ | account_data | account | 1 | 0 | +--------------+---------+--------+-------------+ 1 row in set (0.00 sec) mysql> select * from information_schema.innodb_locks; Empty set, 1 warning (0.00 sec) ~~~ 查看表锁的情况 ~~~ mysql> show status like 'table%'; +----------------------------+---------+ | Variable_name | Value | +----------------------------+---------+ | Table_locks_immediate | 100 | | Table_locks_waited | 11 | +----------------------------+---------+ ~~~ * `Table_locks_immediate`: 表示产生表级锁定的次数,表示可以立即获取锁的查询次数,每立即获取锁值加1 * `Table_locks_waited`: 出现表级锁定争用而发生等待的次数(不能立即获取锁的次数,每等待一次锁值加1),此值较高表示有严重的表级锁争用的情况 # 查询innodb的事务锁 **在5.5中，information\_schema 库中增加了三个关于锁的表（innoDB引擎）** ~~~ innodb_trx ## 当前运行的所有事务 innodb_locks ## 当前出现的锁 innodb_lock_waits ## 锁等待的对应关系 ~~~ 先来看一下这三张表结构： ~~~ root@127.0.0.1 : information_schema 13:28:38> desc innodb_locks; +————-+———————+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +————-+———————+——+—–+———+——-+ | lock_id | varchar(81) | NO | | | |#锁ID | lock_trx_id | varchar(18) | NO | | | |#拥有锁的事务ID | lock_mode | varchar(32) | NO | | | |#锁模式 | lock_type | varchar(32) | NO | | | |#锁类型 | lock_table | varchar(1024) | NO | | | |#被锁的表 | lock_index | varchar(1024) | YES | | NULL | |#被锁的索引 | lock_space | bigint(21) unsigned | YES | | NULL | |#被锁的表空间号 | lock_page | bigint(21) unsigned | YES | | NULL | |#被锁的页号 | lock_rec | bigint(21) unsigned | YES | | NULL | |#被锁的记录号 | lock_data | varchar(8192) | YES | | NULL | |#被锁的数据 +————-+———————+——+—–+———+——-+ rows in set (0.00 sec) root@127.0.0.1 : information_schema 13:28:56> desc innodb_lock_waits; +——————-+————-+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +——————-+————-+——+—–+———+——-+ | requesting_trx_id | varchar(18) | NO | | | |#请求锁的事务ID | requested_lock_id | varchar(81) | NO | | | |#请求锁的锁ID | blocking_trx_id | varchar(18) | NO | | | |#当前拥有锁的事务ID | blocking_lock_id | varchar(81) | NO | | | |#当前拥有锁的锁ID +——————-+————-+——+—–+———+——-+ rows in set (0.00 sec) root@127.0.0.1 : information_schema 13:29:05> desc innodb_trx ; +—————————-+———————+——+—–+———————+——-+ | Field | Type | Null | Key | Default | Extra | +—————————-+———————+——+—–+———————+——-+ | trx_id | varchar(18) | NO | | | |#事务ID | trx_state | varchar(13) | NO | | | |#事务状态： | trx_started | datetime | NO | | 0000-00-00 00:00:00 | |#事务开始时间； | trx_requested_lock_id | varchar(81) | YES | | NULL | |#innodb_locks.lock_id | trx_wait_started | datetime | YES | | NULL | |#事务开始等待的时间 | trx_weight | bigint(21) unsigned | NO | | 0 | |# | trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | |#事务线程ID | trx_query | varchar(1024) | YES | | NULL | |#具体SQL语句 | trx_operation_state | varchar(64) | YES | | NULL | |#事务当前操作状态 | trx_tables_in_use | bigint(21) unsigned | NO | | 0 | |#事务中有多少个表被使用 | trx_tables_locked | bigint(21) unsigned | NO | | 0 | |#事务拥有多少个锁 | trx_lock_structs | bigint(21) unsigned | NO | | 0 | |# | trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 | |#事务锁住的内存大小（B） | trx_rows_locked | bigint(21) unsigned | NO | | 0 | |#事务锁住的行数 | trx_rows_modified | bigint(21) unsigned | NO | | 0 | |#事务更改的行数 | trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 | |#事务并发票数 | trx_isolation_level | varchar(16) | NO | | | |#事务隔离级别 | trx_unique_checks | int(1) | NO | | 0 | |#是否唯一性检查 | trx_foreign_key_checks | int(1) | NO | | 0 | |#是否外键检查 | trx_last_foreign_key_error | varchar(256) | YES | | NULL | |#最后的外键错误 | trx_adaptive_hash_latched | int(1) | NO | | 0 | |# | trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 | |# +—————————-+———————+——+—–+———————+——-+ rows in set (0.01 sec) ~~~ 查看正在锁的事务 ~~~ SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; ~~~ 查看等待锁的事务 ~~~ SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; ~~~ # 查看InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况 ~~~ mysql> show status like 'InnoDB_row_lock%'; +-------------------------------+--------+ | Variable_name | Value | +-------------------------------+--------+ | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 159372 | | Innodb_row_lock_time_avg | 39843 | | Innodb_row_lock_time_max | 51154 | | Innodb_row_lock_waits | 4 | +-------------------------------+--------+ rows in set (0.01 sec) ~~~ * Innodb_row_lock_current_waits当前正在等待锁定的数量 * Innodb_row_lock_time从系统启动到现在锁定总时间长度 * Innodb_row_lock_time_avg每次等待所花平均时间 * Innodb_row_lock_time_max从系统启动到现在等待最长的一次所花的时间 * Innodb_row_lock_waits系统启动后到现在总共等待的次数 # 使用mysqladmin debug查看所有产生锁的线程 ~~~ # mysqladmin -S /tmp/mysql3306.sock debug ~~~ 然后在error日志中，会看到： ~~~ Thread database.table_name Locked/Waiting Lock_type 3 test.t3 Locked - read Low priority read lock 7 test.emp Locked - write High priority write lock ~~~ 这种方法中，能找到线程ID=3和7是阻塞者，但还是不太准确，判断不出来线程7也是被线程ID=3阻塞的。 # innodb存储引擎监控 innodb存储引擎监控分为四种,表监控,表空间监控,锁监控,状态监控. 可以在mysql客户端开启监控选项,然后信息将会输出在mysql的错误日志内. `innodb_monitor`和`innodb_lock_monitor`会每隔15秒会向错误日志中记录InnoDB监控信息,`innodb_table_monitor`和`innodb_tablespace_monitor`是每隔64秒. 1. 对innodb状态监控 ~~~ create table innodb_monitor (a int) engine=innodb; --开启监控 drop table innodb_monitor; --关闭监控 ~~~ 2. 对innodb锁监控 ~~~ create table innodb_lock_monitor (a int) engine=innodb; --开启监控 drop table innodb_lock_monitor; --关闭监控 ~~~ 3. 对innodb的表监控 ~~~ create table innodb_table_monitor(a int) engine=innodb; --开启监控 drop tables innodb_table_monitor; --关闭监控 ~~~ 4. 对innodb表空间监控 ~~~ create table innodb_tablespace_monitor (a int) engine=innodb; --开启监控 drop table innodb_tablespace_monitor; --关闭监控 ~~~ # 查看锁阻塞线程的信息 1. 使用show processlist查看 ~~~ mysql> show processlist; ~~~ 2. 直接使用show engine innodb status查看输出中，显示除了大量的内部信息，它输出就是一个单独的字符串，没有行和列，内容分为很多小段，每一段对应innodb存储引擎不同部分的信息，其中有一些信息对于innodb开发者来说非常有用，但是，许多信息，如果你尝试去理解，并且应用到高性能innodb调优的时候，你会发现它们非常有趣，甚至是非常有必要的。打开开关`innodb_lock_monitor`用来查看一条语句执行的时候，使用命令`show engine innodb status`对系统中的lock信息。 ~~~ MySQL [test]> CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB; ## 随便在一个数据库中创建这个表，就会打开lock monitor Query OK, 0 rows affected, 1 warning (0.07 sec) MySQL [test]> show warnings\G *************************** 1. row *************************** Level: Warning Code: 131 Message: Using the table name innodb_lock_monitor to enable diagnostic output is deprecated and may be removed in future releases. Use INFORMATION_SCHEMA or PERFORMANCE_SCHEMA tables or SET GLOBAL innodb_status_output=ON. row in set (0.00 sec) ~~~ 说明：这个在5.6中有一个warning，但不影响使用。 --- 然后再使用show engine innodb status查看： ~~~ mysql> show engine innodb 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 aaaa set 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 aaaa set 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 0 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| ;; Record lock, heap no 59 PHYSICAL RECORD: n_fields 23; compact format; info bits 0 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 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 0 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 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 ============================ ~~~ 分析 ~~~ MySQL thread id是线程id ~~~ 上面的两个死锁还是很简单就能处理的，分析这个表的时候，发现，研发在设计之时，忘记设置主键ID，而innodb存储引擎在这种情况下，会导致update的过程中，将当前表进行表锁，从而出现了死锁的情况解决方法：查看表结构，统计表数据，因为数据量不大，增加主键ID，解决问题。 # 内在死锁检测工具 innodb有一个内在的死锁检测工具，当死锁超过一定时间后，会回滚其中一个事务 ~~~ innodb_lock_wait_timeout ~~~ 默认参数:`innodb_lock_wait_timeout`设置锁等待的时间是50s, 设置这个第一：`innodb_lock_wait_timeout` 锁定等待时间改大 my.ini文件： ~~~ #innodb_lock_wait_timeout = 50 ~~~ 修改为 ~~~ innodb_lock_wait_timeout = 500 ~~~ # 查询进程 ~~~ mysql> show processlist; ~~~ 查询到相对应的进程，然后 kill id ~~~ mysql> kill 446; ~~~ 验证（kill后再看是否还有锁） ~~~ mysql> show open tables where in_use > 0; Empty set (0.01 sec) ~~~