mysql出现死锁该如何解决和问题发现和定位?

当 MySQL 出现死锁时，可按照以下步骤进行问题发现、定位和解决：

一、问题发现

1. 监控错误日志
   MySQL 会在错误日志中记录死锁信息，包括死锁发生的时间、涉及的事务和锁等待关系。
   1. • 步骤：
        1. 执行 SHOW VARIABLES LIKE 'log_error'; 获取错误日志路径。
        2. 使用命令实时监控日志：
           • Linux：tail -f /var/log/mysql/error.log | grep -i "deadlock"
           • Windows：type C:\ProgramData\MySQL\MySQL Server 8.0\Data\hostname.err | findstr "deadlock"
      • 关键信息：死锁发生时间、涉及事务ID、锁资源描述。
2. 查询information_schema.INNODB_TRX表
   查看当前活跃的事务，找出长时间运行或持有锁的事务。
   sql

    

   SELECT * FROM information_schema.INNODB_TRX;
   

    

3. 查询 INFORMATION_SCHEMA 表

   • 命令：

     sql

     	-- 查看当前锁信息
     	SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
     	-- 查看锁等待关系
     	SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

   • 关键字段：
     • lock_trx_id：持有锁的事务ID
     • lock_mode：锁类型（如 X 表示排他锁）
     • lock_table：被锁定的表
     • lock_index：锁定的索引
4. 使用SHOW ENGINE INNODB STATUS
   1. 1. 查询锁状态，获取最近一次死锁的详细信息，包括死锁的事务、锁类型、等待资源等。
         1. • 步骤：
              1. 执行命令：SHOW ENGINE INNODB STATUS;
              2. 在输出中查找 LATEST DETECTED DEADLOCK 部分，获取最近一次死锁的详细信息。
            • 示例输出：
              plaintext

              	------------------------
              	LATEST DETECTED DEADLOCK
              	------------------------
              	2025-06-01 10:00:00 0x7f8a1b7b9700
              	*** (1) TRANSACTION:
              	TRANSACTION 12345, ACTIVE 0 sec starting index read
              	UPDATE orders SET status='shipped' WHERE order_id=1001;
              	*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
              	RECORD LOCKS space id 123 page no 4 n bits 72 index PRIMARY of table `test`.`orders`
              	*** (2) TRANSACTION:
              	TRANSACTION 67890, ACTIVE 0 sec starting index read
              	UPDATE orders SET status='processing' WHERE order_id=1002;
              	*** (2) HOLDS THE LOCK(S):
              	RECORD LOCKS space id 123 page no 4 n bits 72 index PRIMARY of table `test`.`orders`
              	*** WE ROLL BACK TRANSACTION (1)

       

5. 通过top命令查询mysql进程，通过show processList 去查看mysql服务器上正在执行的活跃线程，
   1. 主要是command字段和time字段，检查是否有大量执行时间长的慢sql
   2. state字段：sorting result / create tem table / copy create tem table 等字段导致的慢查询

二、问题定位

1. 分析死锁日志
   通过SHOW ENGINE INNODB STATUS的输出，重点关注：
   • 事务执行的 SQL 语句
   • 锁等待的顺序和资源
   • 事务的隔离级别
2. 检查事务执行顺序
   死锁通常由多个事务以不同顺序获取锁导致，例如：
   • 事务 A：锁表 T1 → 锁表 T2
   • 事务 B：锁表 T2 → 锁表 T1
3. 识别锁争用点
   确定哪些表、行或索引被频繁锁定，常见的锁类型包括：
   • 行锁（SELECT ... FOR UPDATE）
   • 间隙锁（Gap Lock）
   • 临键锁（Next-Key Lock）

三、解决方案

1. 优化事务设计

• 减少事务持有锁的时间：避免在事务中执行耗时操作（如文件读写、网络请求）。
• 保持事务短小：将大事务拆分为多个小事务。
• 统一事务执行顺序：确保所有事务以相同顺序访问资源。

2. 调整隔离级别

• 降低隔离级别：从REPEATABLE READ（默认）改为READ COMMITTED，减少间隙锁的使用。
  sql

   

  SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
  

   

3. 索引优化

• 添加合适的索引：确保查询语句使用索引，减少锁的范围（避免全表扫描）。
• 分析慢查询：通过EXPLAIN检查查询是否使用了索引。

4. 重试机制

• 应用层捕获死锁异常：当检测到死锁时，自动重试事务。
  python

   

  运行

   

  # Python示例：捕获死锁并重试
  import mysql.connector
  from mysql.connector import Error
  
  max_retries = 3
  for attempt in range(max_retries):
      try:
          with connection.cursor() as cursor:
              connection.start_transaction()
              # 执行事务操作
              connection.commit()
              break
      except Error as e:
          if e.errno == 1213:  # 死锁错误码
              print(f"Deadlock detected, retrying ({attempt+1}/{max_retries})")
              connection.rollback()
          else:
              raise

5. 设置锁超时参数

• 调整innodb_lock_wait_timeout：缩短锁等待时间，避免长时间阻塞。
  sql

  SET GLOBAL innodb_lock_wait_timeout = 5;  # 单位：秒

6. 死锁监控与告警

• 定期检查死锁日志：自动化脚本监控错误日志，发现死锁时发送告警。
• 使用性能监控工具：如pt-deadlock-logger分析死锁模式。

四、预防措施

1. 使用SELECT ... FOR UPDATE谨慎：避免在循环中频繁加锁。
2. 避免长事务：长事务持有锁的时间更长，增加死锁风险。
3. 批量操作拆分：将大批量更新拆分为多个小批次执行。
4. 监控数据库负载：高并发场景下更容易出现死锁，考虑限流或分库分表。

总结

死锁是多事务并发环境下的常见问题，通过合理的事务设计、索引优化和监控机制，可以有效减少死锁的发生。当死锁发生时，快速定位并结合重试机制可以降低对业务的影响。