MySQL死锁问题及如何查看

一、死锁问题简介

当两个或多个事务在相互等待对方完成操作时，就会发生死锁问题。这会导致事务无法继续执行，进而导致系统性能下降，也可能会导致应用程序崩溃。

在MySQL中，当一个事务持有一些资源（比如行级排它锁），但是它还需要另一个事务持有的资源时，就会发生等待。如果另一个事务也需要该事务持有的资源，就会形成死锁。

二、如何查看死锁问题

MySQL提供了多种方式来定位死锁问题。

1. 错误日志

当MySQL发现死锁时，它会将相关信息记录在错误日志中。可以通过检查错误日志来查看死锁信息。

2020-12-18T11:45:39.315160Z 91658 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
2020-12-18T11:45:39.315195Z 91658 [Note] InnoDB: Number of deadlocks found: 1
2020-12-18T11:45:39.315204Z 91658 [Note] InnoDB: 
*** (1) TRANSACTION:
TRANSACTION 745929, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 59, OS thread handle 140198526439680, query id 2920199 172.16.xx.xx user1 Updating
UPDATE test SET a = 5 WHERE id = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7 page no 3 n bits 72 index PRIMARY of table `test`.`test` trx id 745929 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;; 1: len 4; hex 80000003; asc     ;; 2: len 4; hex 80000001; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 745930, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 60, OS thread handle 140198526972160, query id 2920200 172.16.xx.xx user1 Updating
UPDATE test SET a = 5 WHERE id = 2
*** (2) HOLDING THE LOCK(S):
RECORD LOCKS space id 7 page no 3 n bits 72 index PRIMARY of table `test`.`test` trx id 745930 lock_mode X locks rec but not gap
Record lock, heap no 1 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;; 1: len 4; hex 80000002; asc     ;; 2: len 4; hex 80000001; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7 page no 3 n bits 72 index PRIMARY of table `test`.`test` trx id 745930 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;; 1: len 4; hex 80000003; asc     ;; 2: len 4; hex 80000001; asc     ;;

*** WE ROLL BACK TRANSACTION (2)

2. Performance Schema

通过使用Performance Schema，我们可以获取更丰富的信息来分析死锁问题。

通过以下查询来查看 Performance Schema 中的死锁：

SELECT
    l.processlist_id AS waiting_thread,
    l.object_schema,
    l.object_name,
    l.index_name,
    l.object_type,
    l.wait_duration_micro_sec,
    r.processlist_id AS blocking_thread,
    r.object_schema AS blocking_schema,
    r.object_name AS blocking_table,
    r.index_name AS blocking_index,
    r.object_type AS blocking_type,
    r.lock_type AS lock_type
FROM performance_schema.data_lock_waits l
JOIN performance_schema.data_locks r
ON l.requesting_engine_lock_id = r.engine_lock_id;

3. Information Schema

可以使用Information Schema来定位和分析死锁问题。通过以下查询，我们可以查找死锁相关的数据：

SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

三、死锁问题的解决

一旦发现死锁问题，应该尽快解决。以下是一些解决死锁问题的方法：

1. 慢查询优化

通过优化慢查询，可以减少访问同一数据的事务数，从而减少死锁的概率。

2. 减少事务持有锁的时间

减少事务持有锁的时间可以缩短死锁的时间和减少死锁的发生。

3. 加大锁粒度

加大锁粒度可以减少锁争用，从而减少死锁的发生。

4. 增加重试机制

在发生死锁时，可以增加重试机制，让事务重新执行。

5. 调整事务隔离级别

调整事务隔离级别可以减少死锁的发生。通过降低隔离级别，可以减少持有锁的时间和锁的粒度。

四、总结

死锁问题在MySQL中是非常普遍的，但是通过采用合适的方法，可以有效地减少死锁的发生和影响。在进行数据库设计和优化时，应该密切关注死锁问题。