死鎖解決 · 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) ~~~