死鎖是指兩個或兩個以上的進程在執(zhí)行過程中,因爭奪資源而造成的一種互相等待的現(xiàn)象,可以認為如果一個資源被鎖定，它總會在以后某個時間被釋放。而死鎖發(fā)生在當多個進程訪問同一數(shù)據(jù)庫時，其中每個進程擁有的鎖都是其他進程所需的，由此造成每個進程都無法繼續(xù)下去。

      InnoDB的并發(fā)寫操作會觸發(fā)死鎖，InnoDB也提供了死鎖檢測機制，可以通過設(shè)置innodb_deadlock_detect參數(shù)可以打開或關(guān)閉死鎖檢測:

      還可以通過設(shè)置InnDB Monitors來進一步觀察鎖沖突詳細信息

      設(shè)置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

可以通過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)