MySQL是一個(gè)開(kāi)源的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，被廣泛用于Web應(yīng)用程序開(kāi)發(fā)中。在MySQL中，數(shù)據(jù)訪問(wèn)的高并發(fā)場(chǎng)景中容易發(fā)生死鎖。

死鎖是一種資源競(jìng)爭(zhēng)的結(jié)果，當(dāng)兩個(gè)或者更多的進(jìn)程互相等待對(duì)方釋放資源時(shí)就會(huì)發(fā)生死鎖。在MySQL中，出現(xiàn)死鎖后需要檢查哪些數(shù)據(jù)庫(kù)表發(fā)生了死鎖，下面介紹如何查看數(shù)據(jù)庫(kù)表死鎖。

SHOW ENGINE INNODB STATUS\G

通過(guò)上述命令，可以獲取InnoDB引擎的狀態(tài)，展示包括等待鎖的進(jìn)程、被鎖的事務(wù)、當(dāng)前事務(wù)等待的鎖以及死鎖的相關(guān)信息。

LATEST DETECTED DEADLOCK
------------------------
2021-06-02 11:11:11 0x7f8f80611700
*** (1) TRANSACTION:
TRANSACTION 123456, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 99999, OS thread handle 12345, query id 88888 localhost root Sending data
SELECT * FROM table1 WHERE ID=1 FOR UPDATE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 123 page no 456 n bits 40 index PRIMARY of table `database`.`table1` trx id 123456 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 21; compact format; info bits 0
0: len 8; hex 8000000000000001; asc         ;;
*** (2) TRANSACTION:
TRANSACTION 123457, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 99998, OS thread handle 12344, query id 88889 localhost root Sending data
SELECT * FROM table1 WHERE ID=2 FOR UPDATE
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 123 page no 456 n bits 40 index PRIMARY of table `database`.`table1` trx id 123457 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 21; compact format; info bits 0
0: len 8; hex 8000000000000002; asc         ;;

在上述代碼中，LATEST DETECTED DEADLOCK為檢測(cè)到的最新死鎖，下面包括了相關(guān)事務(wù)的詳細(xì)信息，以及被鎖定的數(shù)據(jù)表的記錄以及位置信息。

通過(guò)以上命令和代碼，可以很方便的查看MySQL數(shù)據(jù)庫(kù)表中的死鎖情況，為錯(cuò)誤排查和性能優(yōu)化提供有效參考。