官方文档

原文

可以通过以下技术来减少死锁的出现

  • 在任何时候, 使用 SHOW ENGINE INNODB STATUS 命令来判断最近的死锁问题. 这可帮助你排查应用避免死锁.
  • 如果担心频繁死锁预警, 可通过开启 innodb_print_all_deadlocks 配置选项来收集更多的 debug 信息. 这会列出所有的死锁信息, 而不是最近的一个, 它会记录在 MySQL 的 error log 里. 当你完成 debug 时, 再禁用这个选项.
  • 总是准备好重现由于死锁而失败的事务. 死锁不是危险的, 重试就好.(例如, 可以拿出现死锁的两条 SQL, 分别在不同的会话中执行, 注意要禁止自动提交事务)
  • 保持事务短(时间), 小(提交粒度), 以减少它们冲突.
  • 在进行一系列相关的更改后立即提交事务, 以减少冲突. 特别地, 不要离开一个交互式 MySQL 会话打开未提交事务太长时间.
  • 如果你使用 locking reads (SELECT ... FOR UPDATE or SELECT ... FOR SHARE) , 可尝试使用更低级别的隔离级别, 如 READ COMMITTED
  • 当在一个事务中修改多张表, 或在同一张表中修改不同的行集合时, 在每次操作时要固定顺序.这样, 事务形成良好的顺序后就不会有死锁了. 例如, 在你的应用中, 组织数据库操作到函数, 或调用存储过程, 而不是在不同的地方编写多条相似的 insert, update 以及 delete 的语句.
  • 为你的表添加恰当的索引. 之后你的查询只需要扫描更少的索引记录, 于是更少的锁. 使用 EXPLAIN SELECT 来判断 MySQL Server 使用了哪个索引来最适合你的查询.
  • 更用更少的锁. 如果你可以允许SELECT从一个旧的快照中返回数据, 就不要添加子句 FOR UPDATE 或 FOR SHARE . 这种情况, 使用 READ COMMITTED 隔离级别好点, 因为同一事务中的每个一致读取都从自己的新快照读取的.

  • 如果都没有帮助的话, 用表级锁来序列化你的事务. 正确使用 LOCK TABLES 事务相关的表, 例如 InnoDB 表, 一个事务开始于 SET autocommit = 0 (不是 START TRANSACTION ) 接着是 LOCK TABLES , 然后直到你提交完事务后再显式调用 UNLOCK TABLES . 例如, 假设你需要写入表 t1, 以及从 t2 中读, 你可以这样子做

    SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;

​ 表级锁防止并发更新表, 在一个繁忙的系统中避免死锁, 代价是更少的响应.

  • 另一个序列化事务的方式是创建一个辅助的信号表, 它只包含一行. 每个事务在访问其他表之前都要去更新这行. 那么, 所有事务都以连续的方式发生. 注意, InnoDB 的立即死锁检测算法在这种情况下也会工作, 因为序列锁是一个行级锁. 使用 MySQL 表级锁, 必需使用 timeout 方法来解决死锁.
隔离级别 脏读 不可重复读 幻读
Read uncommitted 可能 可能 可能
Read committed 不可能 可能 可能
Repeatable read 不可能 不可能 可能
serializable 不可能 不可能 不可能

线上服务排查

如没特别说明, 都是基于 InnoDB 引擎表

查看当前事务级别

SELECT @@TX_ISOLATION ;

通过上面的文档的定位及配置, 应该可以获取了死锁的上下文信息了. 一个典型的情况是执行 INSERT ... SELECT .... , 然后另一条线程对 SELECT 所在的表进行了 DML 操作. (如 DELETE, UPDATE 等)

原因是 INSERT ... SELECT ...from tblA… 类似这种 SQL, 默认情况下, MySQL 会锁住表 tblA 的.(是整个表, 因为默认的事务级别为 Repeatable read ).

是否锁表, 可以用管理员来登录, 执行 show open tables in 数据库名 where In_use > 0 来查看.

​ 注意, In_use 的意思是 The number of table locks or lock requests there are for the table (即表锁或请求表琐的数量).

​ Name_lockec 的意思是表名是否被锁. (例如执行 rename 操作, 或 drop table 操作)

如果业务逻辑允许的话, 可以降低事务级别为 Read committed, 这样子就不会导致锁表了.(在 INSERT ... SELECT … 事务中设置). 例如

set TRANSACTION ISOLATION LEVEL READ COMMITTED;
set autocommit = 0;
start TRANSACTION;
....事务操作....
COMMIT;

关于 在 event 中调度与隔离级别

event 中每次执行事件, 都是用不同的连接的. 验证

SET global general_log = 1;
SET global log_output = 'table';
select * from mysql.general_log;

-- 创建一个存储过程
CREATE DEFINER=`用名`@`主机` PROCEDURE `test_proc`()
BEGIN
    select CONNECTION_ID();
END;;
DELIMITER ;

-- 创建几个事件
CREATE EVENT myevent1
ON SCHEDULE EVERY 1 SECOND
DO
   call proc_test();
   
CREATE EVENT myevent2
ON SCHEDULE EVERY 2 SECOND
DO
   call proc_test();
   
CREATE EVENT myevent3
ON SCHEDULE EVERY 3 SECOND
DO
   call proc_test(); 
   
-- 查看每次执行的 thread id
select thread_id, count(*) from mysql.general_log where argument = 'call proc_test()' group by thread_id

关于隔离级别, 要显式设置

set session TRANSACTION ISOLATION LEVEL READ COMMITTED;

注意, 那个 session 不能漏掉.

参考资料