mysql查看数据库表死锁 |mysql查看数据库表

死锁是一种资源竞争的结果，当两个或者更多的进程互相等待对方释放资源时就会发生死锁。在MySQL中，出现死锁后需要检查哪些数据库表发生了死锁，下面介绍如何查看数据库表死锁。

SHOW ENGINE INNODB STATUS\G

通过上述命令，可以获取InnoDB引擎的状态，展示包括等待锁的进程、被锁的事务、当前事务等待的锁以及死锁的相关信息。

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为检测到的最新死锁，下面包括了相关事务的详细信息，以及被锁定的数据表的记录以及位置信息。

通过以上命令和代码，可以很方便的查看MySQL数据库表中的死锁情况，为错误排查和性能优化提供有效参考。