MySQL鎖阻塞分析,mysql鎖阻塞
日常維護中,經常會碰到線程被阻塞,導致資料庫響應非常慢,下面就看看如何擷取是哪個線程導致了阻塞的。
blog地址:http://blog.csdn.net/hw_libo/article/details/39080809
1. 環境說明RHEL 6.4 x86_64 + MySQL 5.6.19
交易隔離等級:RR
2. 測試過程
3. 查看鎖阻塞線程資訊這裡用幾中方法進行分析:
3.1 使用show processlist查看
MySQL [(none)]> show processlist;+----+------+-----------+------+---------+------+--------------+------------------------------------------+| Id | User | Host | db | Command | Time | State | Info |+----+------+-----------+------+---------+------+--------------+------------------------------------------+| 2 | root | localhost | NULL | Query | 0 | init | show processlist || 3 | root | localhost | test | Query | 70 | Sending data | select count(*) from t3 a,t3 b || 4 | root | localhost | test | Query | 65 | updating | delete from emp where empno=7788 || 7 | root | localhost | test | Query | 68 | updating | update emp set sal=3500 where empno=7788 |+----+------+-----------+------+---------+------+--------------+------------------------------------------+4 rows in set (0.00 sec)
如果資料庫存在較多線程的話,這種方法確實不太好確認的。
3.2 直接使用show engine innodb status查看
------------TRANSACTIONS------------Trx id counter 4131Purge done for trx's n:o < 4119 undo n:o < 0 state: running but idleHistory list length 126LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 0, not startedMySQL thread id 2, OS thread handle 0x7f953ffff700, query id 115 localhost root initshow engine innodb status---TRANSACTION 4130, ACTIVE 41 sec starting index readmysql tables in use 1, locked 1LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)MySQL thread id 4, OS thread handle 0x7f953ff9d700, query id 112 localhost root updatingdelete from emp where empno=7788------- TRX HAS BEEN WAITING 41 SEC FOR THIS LOCK TO BE GRANTED: ## 等待了41sRECORD LOCKS space id 16 page no 3 n bits 88 index `PRIMARY` of table `test`.`emp` trx id 4130 lock_mode X locks rec but not gap waitingRecord lock, heap no 9 PHYSICAL RECORD: n_fields 10; compact format; info bits 0 ## 線程4在等待往test.emp中的主鍵上加X鎖,page num=3 0: len 4; hex 80001e6c; asc l;; 1: len 6; hex 000000001018; asc ;; 2: len 7; hex 91000001420084; asc B ;; 3: len 5; hex 53434f5454; asc SCOTT;; 4: len 7; hex 414e414c595354; asc ANALYST;; 5: len 4; hex 80001d8e; asc ;; 6: len 4; hex 208794f0; asc ;; 7: len 4; hex 80000bb8; asc ;; 8: SQL NULL; 9: len 4; hex 80000014; asc ;;---------------------TRANSACTION 4129, ACTIVE 45 sec starting index readmysql tables in use 1, locked 1LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)MySQL thread id 7, OS thread handle 0x7f953ff6c700, query id 111 localhost root updatingupdate emp set sal=3500 where empno=7788------- TRX HAS BEEN WAITING 45 SEC FOR THIS LOCK TO BE GRANTED: ## 等待了45sRECORD LOCKS space id 16 page no 3 n bits 88 index `PRIMARY` of table `test`.`emp` trx id 4129 lock_mode X locks rec but not gap waitingRecord lock, heap no 9 PHYSICAL RECORD: n_fields 10; compact format; info bits 0 ## 線程7在等待往test.emp中的主鍵上加X鎖,page num=3 0: len 4; hex 80001e6c; asc l;; 1: len 6; hex 000000001018; asc ;; 2: len 7; hex 91000001420084; asc B ;; 3: len 5; hex 53434f5454; asc SCOTT;; 4: len 7; hex 414e414c595354; asc ANALYST;; 5: len 4; hex 80001d8e; asc ;; 6: len 4; hex 208794f0; asc ;; 7: len 4; hex 80000bb8; asc ;; 8: SQL NULL; 9: len 4; hex 80000014; asc ;;---------------------TRANSACTION 4128, ACTIVE 51 sec2 lock struct(s), heap size 360, 1 row lock(s)MySQL thread id 3, OS thread handle 0x7f953ffce700, query id 110 localhost root cleaning up
我們知道,主要根因還是thread=3引起的,但從innodb status中卻無法分析得到這個結果。
從上面來看,線程4和線程7都在等待往test.emp中的主鍵上加X鎖,page num=3,但是線程7等待的時間為45s,而線程4等待的時間為41s,是較線程7之後申請的鎖,所以可以判斷是線程7阻塞了線程4。至於線程7為什麼出現等待,這裡分析不到根因。
3.3 使用mysqladmin debug查看
# mysqladmin -S /tmp/mysql3306.sock debug
然後在error日誌中,會看到:
Thread database.table_name Locked/Waiting Lock_type3 test.t3 Locked - read Low priority read lock7 test.emp Locked - write High priority write lock
這種方法中,能找到線程ID=3和7是阻塞者,但還是不太準確,判斷不出來線程7也是被線程ID=3阻塞的。
3.4 使用innodb_lock_monitor來擷取阻塞鎖線程
MySQL [test]> CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB; ## 隨便在一個資料庫中建立這個表,就會開啟lock monitorQuery OK, 0 rows affected, 1 warning (0.07 sec)MySQL [test]> show warnings\G*************************** 1. row *************************** Level: Warning Code: 131Message: 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.1 row in set (0.00 sec)
說明:這個在5.6中有一個warning,但不影響使用。
然後再使用show engine innodb status查看:
------------TRANSACTIONS------------Trx id counter 4667Purge done for trx's n:o < 4659 undo n:o < 0 state: running but idleHistory list length 138LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 0, not startedMySQL thread id 9, OS thread handle 0x7f813c5f7700, query id 152 localhost root initshow engine innodb status---TRANSACTION 4663, ACTIVE 78 sec starting index readmysql tables in use 1, locked 1LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)MySQL thread id 4, OS thread handle 0x7f813c628700, query id 149 localhost root updatingdelete from emp where empno=7788------- TRX HAS BEEN WAITING 78 SEC FOR THIS LOCK TO BE GRANTED: ## 等待了78sRECORD LOCKS space id 16 page no 3 n bits 88 index `PRIMARY` of table `test`.`emp` trx id 4663 lock_mode X locks rec but not gap waitingRecord lock, heap no 9 PHYSICAL RECORD: n_fields 10; compact format; info bits 0 ## 線程4在等待往test.emp中的主鍵上加X鎖,page num=3 0: len 4; hex 80001e6c; asc l;; 1: len 6; hex 000000001018; asc ;; 2: len 7; hex 91000001420084; asc B ;; 3: len 5; hex 53434f5454; asc SCOTT;; 4: len 7; hex 414e414c595354; asc ANALYST;; 5: len 4; hex 80001d8e; asc ;; 6: len 4; hex 208794f0; asc ;; 7: len 4; hex 80000bb8; asc ;; 8: SQL NULL; 9: len 4; hex 80000014; asc ;;------------------TABLE LOCK table `test`.`emp` trx id 4663 lock mode IX ## 在給主鍵行上加X鎖之前,先要在表上加意圖鎖定IXRECORD LOCKS space id 16 page no 3 n bits 88 index `PRIMARY` of table `test`.`emp` trx id 4663 lock_mode X locks rec but not gap waitingRecord lock, heap no 9 PHYSICAL RECORD: n_fields 10; compact format; info bits 0 0: len 4; hex 80001e6c; asc l;; 1: len 6; hex 000000001018; asc ;; 2: len 7; hex 91000001420084; asc B ;; 3: len 5; hex 53434f5454; asc SCOTT;; 4: len 7; hex 414e414c595354; asc ANALYST;; 5: len 4; hex 80001d8e; asc ;; 6: len 4; hex 208794f0; asc ;; 7: len 4; hex 80000bb8; asc ;; 8: SQL NULL; 9: len 4; hex 80000014; asc ;;---TRANSACTION 4662, ACTIVE 81 sec starting index readmysql tables in use 1, locked 1LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)MySQL thread id 7, OS thread handle 0x7f813c5c6700, query id 148 localhost root updatingupdate emp set sal=3500 where empno=7788------- TRX HAS BEEN WAITING 81 SEC FOR THIS LOCK TO BE GRANTED: ## 等待了81sRECORD LOCKS space id 16 page no 3 n bits 88 index `PRIMARY` of table `test`.`emp` trx id 4662 lock_mode X locks rec but not gap waitingRecord lock, heap no 9 PHYSICAL RECORD: n_fields 10; compact format; info bits 0 ## 線程7在等待往test.emp中的主鍵上加X鎖,page num=3 0: len 4; hex 80001e6c; asc l;; 1: len 6; hex 000000001018; asc ;; 2: len 7; hex 91000001420084; asc B ;; 3: len 5; hex 53434f5454; asc SCOTT;; 4: len 7; hex 414e414c595354; asc ANALYST;; 5: len 4; hex 80001d8e; asc ;; 6: len 4; hex 208794f0; asc ;; 7: len 4; hex 80000bb8; asc ;; 8: SQL NULL; 9: len 4; hex 80000014; asc ;;------------------TABLE LOCK table `test`.`emp` trx id 4662 lock mode IX ## 在給主鍵行上加X鎖之前,先要在表上加意圖鎖定IXRECORD LOCKS space id 16 page no 3 n bits 88 index `PRIMARY` of table `test`.`emp` trx id 4662 lock_mode X locks rec but not gap waitingRecord lock, heap no 9 PHYSICAL RECORD: n_fields 10; compact format; info bits 0 0: len 4; hex 80001e6c; asc l;; 1: len 6; hex 000000001018; asc ;; 2: len 7; hex 91000001420084; asc B ;; 3: len 5; hex 53434f5454; asc SCOTT;; 4: len 7; hex 414e414c595354; asc ANALYST;; 5: len 4; hex 80001d8e; asc ;; 6: len 4; hex 208794f0; asc ;; 7: len 4; hex 80000bb8; asc ;; 8: SQL NULL; 9: len 4; hex 80000014; asc ;;---TRANSACTION 4615, ACTIVE 1579 sec, thread declared inside InnoDB 1222mysql tables in use 2, locked 02 lock struct(s), heap size 360, 1 row lock(s)MySQL thread id 3, OS thread handle 0x7f813c659700, query id 147 localhost root Sending dataselect count(*) from t3 a,t3 b ## 這是線程3當前正在執行的SQLTrx read view will not see trx with id >= 4662, sees < 4659TABLE LOCK table `test`.`emp` trx id 4615 lock mode IX ## 線程3中正在擁有表上的意向IX鎖,並且有test.emp表上主鍵的行級X鎖,page num=3RECORD LOCKS space id 16 page no 3 n bits 88 index `PRIMARY` of table `test`.`emp` trx id 4615 lock_mode X locks rec but not gapRecord lock, heap no 9 PHYSICAL RECORD: n_fields 10; compact format; info bits 0 0: len 4; hex 80001e6c; asc l;; 1: len 6; hex 000000001018; asc ;; 2: len 7; hex 91000001420084; asc B ;; 3: len 5; hex 53434f5454; asc SCOTT;; 4: len 7; hex 414e414c595354; asc ANALYST;; 5: len 4; hex 80001d8e; asc ;; 6: len 4; hex 208794f0; asc ;; 7: len 4; hex 80000bb8; asc ;; 8: SQL NULL; 9: len 4; hex 80000014; asc ;;
為什麼線程3當前執行的是一個select t3表操作,但卻鎖住了test.emp表上page num=3?
有可能是線程3之前對test.emp表的操作事務沒有及時提交導致。
所以得出:線程3阻塞了線程7,而線程7又阻塞了線程4,所以根因就是線程3,讓線程3儘快提交或是kill掉即可。
4. 結論
在分析innodb中鎖阻塞時,幾種方法的對比情況:
(1)使用show processlist查看不靠譜;
(2)直接使用show engine innodb status查看,無法判斷到問題的根因;
(3)使用mysqladmin debug查看,能看到所有產生鎖的線程,但無法判斷哪個才是根因;
(4)開啟innodb_lock_monitor後,再使用show engine innodb status查看,能夠找到鎖阻塞的根因。
blog地址:http://blog.csdn.net/hw_libo/article/details/39080809
-- Bosco QQ:375612082
---- END ----
-------------------------------------------------------------------------------------------------------
著作權,文章允許轉載,但必須以連結方式註明源地址,否則追究法律責任!
怎看mysql有沒阻塞
通過下面的查詢, 來查詢當前資料庫, 有哪些事務,都鎖定哪些資源。SELECTtrx_id AS `事務ID`,trx_state AS `事務狀態`,trx_requested_lock_id AS `事務需要等待的資源`,trx_wait_startedAS `事務開始等待時間`,trx_tables_in_use AS `事務使用表`,trx_tables_locked AS `事務擁有鎖`,trx_rows_locked AS `事務鎖定行`,trx_rows_modified AS `事務更改行`FROMinformation_schema.innodb_trx ;SELECTlock_idAS`鎖ID`,lock_trx_id AS`擁有鎖的事務ID`,lock_mode AS`鎖模式`,lock_type AS`鎖類型`,lock_table AS`被鎖的表`,lock_index AS`被鎖的索引`,lock_space AS`被鎖的資料表空間號`,lock_page AS`被鎖的頁號`,lock_rec AS`被鎖的記錄號`,lock_data AS`被鎖的資料`FROMinformation_schema.innodb_locks;SELECT requesting_trx_id AS `請求鎖的事務ID`, requested_lock_id AS `請求鎖的鎖ID`, blocking_trx_id AS `當前擁有鎖的事務ID`, blocking_lock_id AS `當前擁有鎖的鎖ID`FROM innodb_lock_waits;
mysql的鎖表機制不太理解,詳細解釋
表級鎖、頁級鎖和行級鎖是不同粒度上的鎖,區別就是鎖定的範圍大小,顧名思義,表級鎖就是在整個表上請求一個鎖,頁級鎖就是在某個資料頁上請求一個鎖,行級鎖就是在某行上請求一個鎖。
更新操作一般優先順序比讀取要高,前提是讀取操作在隊列中尚未執行,如果讀取已經開始從資料中擷取資料了,這時候更新要求的鎖資源已經被讀取伺服器用戶端檔案鎖了,那它只能等讀取操作結束以後才能執行。