MySQL InnoDB設定死鎖檢測的方法
死鎖是指兩個或兩個以上的程式在執行過程中,因爭奪資源而造成的一種互相等待的現象,可以認為如果一個資源被鎖定,它總會在以後某個時間被釋放。而死鎖發生在當多個程式訪問同一資料庫時,其中每個程式擁有的鎖都是其他程式所需的,由此造成每個程式都無法繼續下去。
InnoDB的併發寫操作會觸發死鎖,InnoDB也提供了死鎖檢測機制,可以透過設定innodb_deadlock_detect引數可以開啟或關閉死鎖檢測:
innodb_deadlock_detect = on 開啟死鎖檢測,資料庫發生死鎖時自動回滾(預設選項)
innodb_deadlock_detect = off 關閉死鎖檢測,發生死鎖的時候,用鎖超時來處理,透過設定鎖超時引數innodb_lock_wait_timeout可以在超時發生時回滾被阻塞的事務
還可以透過設定InnDB Monitors來進一步觀察鎖衝突詳細資訊
設定InnoDB Monitors方法
建立test庫
mysql>create database test;
Query OK, 1 row affected (0.20 sec)
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table innodb_monitor(a INT) engine=innodb;
Query OK, 0 rows affected (1.04 sec)
mysql> create table innodb_tablespace_monitor(a INT) engine=innodb;
Query OK, 0 rows affected (0.70 sec)
mysql> create table innodb_lock_monitor(a INT) engine=innodb;
Query OK, 0 rows affected (0.36 sec)
mysql> create table innodb_table_monitor(a INT) engine=innodb;
Query OK, 0 rows affected (0.08 sec)
可以透過show engine innodb status命令檢視死鎖資訊
mysql> show engine innodb status \G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2018-05-10 09:17:10 0x7f1fbc21a700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 46 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 53 srv_active, 0 srv_shutdown, 240099 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2007
OS WAIT ARRAY INFO: signal count 1987
RW-shared spins 3878, rounds 5594, OS waits 1735
RW-excl spins 3, rounds 91, OS waits 4
RW-sx spins 1, rounds 30, OS waits 1
Spin rounds per wait: 1.44 RW-shared, 30.33 RW-excl, 30.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 78405
Purge done for trx's n:o < 78404 undo n:o < 10 state: running but idle
History list length 21
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421249967052640, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
.............................................................................
.............................................................................
.............................................................................
InnoDB的併發寫操作會觸發死鎖,InnoDB也提供了死鎖檢測機制,可以透過設定innodb_deadlock_detect引數可以開啟或關閉死鎖檢測:
innodb_deadlock_detect = on 開啟死鎖檢測,資料庫發生死鎖時自動回滾(預設選項)
innodb_deadlock_detect = off 關閉死鎖檢測,發生死鎖的時候,用鎖超時來處理,透過設定鎖超時引數innodb_lock_wait_timeout可以在超時發生時回滾被阻塞的事務
還可以透過設定InnDB Monitors來進一步觀察鎖衝突詳細資訊
設定InnoDB Monitors方法
建立test庫
mysql>create database test;
Query OK, 1 row affected (0.20 sec)
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table innodb_monitor(a INT) engine=innodb;
Query OK, 0 rows affected (1.04 sec)
mysql> create table innodb_tablespace_monitor(a INT) engine=innodb;
Query OK, 0 rows affected (0.70 sec)
mysql> create table innodb_lock_monitor(a INT) engine=innodb;
Query OK, 0 rows affected (0.36 sec)
mysql> create table innodb_table_monitor(a INT) engine=innodb;
Query OK, 0 rows affected (0.08 sec)
可以透過show engine innodb status命令檢視死鎖資訊
mysql> show engine innodb status \G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2018-05-10 09:17:10 0x7f1fbc21a700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 46 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 53 srv_active, 0 srv_shutdown, 240099 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2007
OS WAIT ARRAY INFO: signal count 1987
RW-shared spins 3878, rounds 5594, OS waits 1735
RW-excl spins 3, rounds 91, OS waits 4
RW-sx spins 1, rounds 30, OS waits 1
Spin rounds per wait: 1.44 RW-shared, 30.33 RW-excl, 30.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 78405
Purge done for trx's n:o < 78404 undo n:o < 10 state: running but idle
History list length 21
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421249967052640, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
.............................................................................
.............................................................................
.............................................................................
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/15498/viewspace-2154256/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- mysql InnoDB鎖等待的檢視及分析MySql
- MySQL 5.5 InnoDB表鎖行鎖測試MySql
- mysql innodb的行鎖MySql
- mysql行鎖和死鎖檢測MySql
- mysql innodb的行鎖(2)MySql
- mysql innodb的行鎖(3)MySql
- mysql innodb的行鎖(4)MySql
- MySQL鎖:InnoDB行鎖需要避免的坑MySql
- MySQL鎖:03.InnoDB行鎖MySql
- Mysql innodb引擎(二)鎖MySql
- MySQL InnoDB 中的鎖機制MySql
- mysql事務和鎖InnoDBMySql
- mysql innodb間隙鎖示例MySql
- mysql innodb的行鎖(5) --next-Key 鎖MySql
- mysql innodb_deadlock_detect檢測和處理MySql
- MySQL:Innodb 一個死鎖案例MySql
- MySQL 5.7 查詢InnoDB鎖表MySql
- 【MySQL】InnoDB鎖機制之一MySql
- Mysql研磨之InnoDB行鎖模式MySql模式
- MySQL InnoDB如何應付死鎖MySql
- 【MySQL】InnoDB鎖機制之二MySql
- 詳解 MySql InnoDB 中意向鎖的作用MySql
- mysql innodb的行鎖(6) --不安全語句加鎖MySql
- MySQL中InnoDB鎖機制介紹及一些測試MySql
- MySQL InnoDB行鎖優化建議MySql優化
- MySQL InnoDB檢查點機制MySql
- 【MySQL】鎖——檢視當前資料庫鎖請求的三種方法MySql資料庫
- mysql鎖以及鎖資訊檢視MySql
- mysql innodb lock鎖之record lock之一MySql
- Mysql技術內幕之InnoDB鎖探究MySql
- MySQL 5.5 -- innodb_lock_wait 鎖 等待MySqlAI
- innodb_lock_monitor解決mysql死鎖MySql
- MySQL/InnoDB中,樂觀鎖、悲觀鎖、共享鎖、排它鎖、行鎖、表鎖、死鎖概念的理解MySql
- 檢視MySQL鎖等待的原因MySql
- MySQL什麼是InnoDB檢查點?MySql
- 詳解 MySql InnoDB 中的三種行鎖(記錄鎖、間隙鎖與臨鍵鎖)MySql
- 在Linux中,mysql的innodb如何定位鎖問題?LinuxMySql
- mysql innodb 索引失效問題引起表級鎖MySql索引