死鎖是指兩個(gè)或兩個(gè)以上的進(jìn)程在執(zhí)行過(guò)程中,因爭(zhēng)奪資源而造成的一種互相等待的現(xiàn)象,可以認(rèn)為如果一個(gè)資源被鎖定，它總會(huì)在以后某個(gè)時(shí)間被釋放。而死鎖發(fā)生在當(dāng)多個(gè)進(jìn)程訪問(wèn)同一數(shù)據(jù)庫(kù)時(shí)，其中每個(gè)進(jìn)程擁有的鎖都是其他進(jìn)程所需的，由此造成每個(gè)進(jìn)程都無(wú)法繼續(xù)下去。
InnoDB的并發(fā)寫(xiě)操作會(huì)觸發(fā)死鎖，InnoDB也提供了死鎖檢測(cè)機(jī)制，可以通過(guò)設(shè)置innodb_deadlock_detect參數(shù)可以打開(kāi)或關(guān)閉死鎖檢測(cè):


innodb_deadlock_detect = on  打開(kāi)死鎖檢測(cè)，數(shù)據(jù)庫(kù)發(fā)生死鎖時(shí)自動(dòng)回滾（默認(rèn)選項(xiàng)）
innodb_deadlock_detect = off  關(guān)閉死鎖檢測(cè)，發(fā)生死鎖的時(shí)候，用鎖超時(shí)來(lái)處理，通過(guò)設(shè)置鎖超時(shí)參數(shù)innodb_lock_wait_timeout可以在超時(shí)發(fā)生時(shí)回滾被阻塞的事務(wù)


還可以通過(guò)設(shè)置InnDB Monitors來(lái)進(jìn)一步觀察鎖沖突詳細(xì)信息
設(shè)置InnoDB Monitors方法
建立test庫(kù)
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)


可以通過(guò)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)
.............................................................................
.............................................................................
.............................................................................

文章題目：MySQLInnoDB設(shè)置死鎖檢測(cè)的方法
轉(zhuǎn)載來(lái)于：http://weahome.cn/article/poddsd.html