MySQL死锁怎么解决

MySQL 死锁通常是由于多个事务相互持有锁并等待对方释放而产生的。解决 MySQL 死锁的方法主要包括以下几种策略:


一、立即解决死锁#

  1. 查看当前死锁

    SHOW ENGINE INNODB STATUS\G;
    

    这个命令可以查看最近一次的死锁信息,包括事务 ID、锁的等待情况、SQL 语句等。

  2. 手动终止事务 找到发生死锁的事务 ID,然后终止其中一个:

    KILL <线程ID>;
    

    或者:

    ROLLBACK;
    

    这会释放事务持有的锁,解除死锁。


二、预防和优化死锁#

1. 尽量使用索引减少锁范围#

  • 确保查询时使用索引,避免全表扫描导致锁住大量数据。

  • 例如,UPDATEDELETE 语句应尽量使用索引字段:

    UPDATE users SET name='New Name' WHERE id=1;
    
  • 而不是:

    UPDATE users SET name='New Name' WHERE age=30;
    

    (如果 age 不是索引,这可能导致表锁)


2. 尽量使用短事务#

  • 避免长时间持有锁,尽快提交事务:

    BEGIN;
    -- 业务逻辑
    COMMIT;
    
  • 不要在事务中执行过多的逻辑操作或等待用户输入。


3. 按照相同顺序访问表和行#

  • 确保多个事务访问相同资源时,按照相同的顺序执行 SQL。

  • 例如:

    BEGIN;
    UPDATE account SET balance = balance - 100 WHERE id = 1;
    UPDATE account SET balance = balance + 100 WHERE id = 2;
    COMMIT;
    

    而不是:

    BEGIN;
    UPDATE account SET balance = balance + 100 WHERE id = 2;
    UPDATE account SET balance = balance - 100 WHERE id = 1;
    COMMIT;
    
  • 这样可以避免循环依赖,减少死锁的可能性。


4. 降低事务隔离级别#

  • 默认隔离级别是 REPEATABLE READ,在某些情况下可以降级为 READ COMMITTED 来减少死锁:

    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
    

5. 使用 FOR UPDATE 进行锁定#

  • 在事务中使用 SELECT ... FOR UPDATE 预先锁定数据,防止死锁:

    SELECT * FROM orders WHERE id = 1 FOR UPDATE;
    

6. 分批提交数据#

  • 避免一次性更新太多数据,尽量分批:

    UPDATE orders SET status = 'shipped' WHERE id BETWEEN 100 AND 200;
    

7. 使用 LOCK IN SHARE MODE 共享锁#

  • 在读取数据时,使用共享锁减少冲突:

    SELECT * FROM products WHERE id = 1 LOCK IN SHARE MODE;
    

总结#

  • 短事务:尽快 COMMIT
  • 使用索引:减少锁的范围
  • 访问顺序统一:避免循环依赖
  • 降级事务隔离级别:减少锁冲突
  • 手动加锁:使用 SELECT ... FOR UPDATELOCK IN SHARE MODE
  • 分批提交:降低锁竞争

这样可以有效减少和解决 MySQL 死锁问题。

本作品采用《CC 协议》,转载必须注明作者和本文链接
《L03 构架 API 服务器》
你将学到如 RESTFul 设计风格、PostMan 的使用、OAuth 流程,JWT 概念及使用 和 API 开发相关的进阶知识。
《L01 基础入门》
我们将带你从零开发一个项目并部署到线上,本课程教授 Web 开发中专业、实用的技能,如 Git 工作流、Laravel Mix 前端工作流等。