高并发场景下更新数据库报错，记录一次 MySQL 死锁问题的解决

vlambda
2021-05-12

高并发场景下更新数据库报错，记录一次 MySQL 死锁问题的解决

👇👇 关注后回复 “进群” ，拉你进程序员交流群 👇👇

架构师大咖

架构师大咖，打造有价值的架构师交流平台。分享架构师干货、教程、课程、资讯。架构师大咖，每日推送。

0篇原创内容

Official Account

算法专栏

算法专栏，每日推送。算法是程序员内功，分享算法知识、文章、工具、算法题、教程等

0篇原创内容

Official Account

作者 l 会点代码的大叔（CodeDaShu）

今天隔壁项目组的开发小姐姐找到我，说她们项目正在做压力测试，更新 MySQL 数据库的一张表时，总是发生死锁，日志大概是这个样子的：

org.springframework.dao.DeadlockLoserDataAccessException: ### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction### The error may involve com.taikang.biz.persist.dao.PolicyMapper.updateChdrin-Inline### The error occurred while setting parameters

她们压测的并发量不高，我心想 MySQL 应该还不至于这么脆弱，应该是哪里设置的问题，或者是代码写的有问题；我大概看了一下数据库设置、表结构和代码，大概是这样的（不是真实的表和代码，我按照问题的过程重新写的）：

表结构很简单，主要是三个字段，自增主键、订单号和保单号：

CREATE TABLE policy( id INT NOT NULL AUTO_INCREMENT , orderno VARCHAR(50) , policyno VARCHAR(20) , PRIMARY KEY (id))ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

DAO 层的代码也比较简单，业务逻辑是通过订单号定位到一条数据，修改其保单号：

@Update("UPDATE policy set policyno = #{policyNo} WHERE orderno = #{orderNo}")public void updatePolicy(@Param("orderNo") String orderNo, @Param("policyNo") String policyNo);

在压测的过程中，每次的报文中，订单号都是不相同的，也就是 where 条件中的 orderNo 是不相同的。

1. 先查看存储引擎

MySQL 的 InnoDB 引擎在更新数据时采用的是行级锁，而 MyISAM 引擎只有表级锁；我们公司的数据库都是由基础设施的团队搭建及维护的，MySQL 默认的 InnoDB 引擎应该不会被修改。

但是考虑到 MySQL 的存储引擎不是在建库时定义，而是在建表的时候声明（同库可以包含不同存储引擎类型的表），万一是谁不小心指定错了呢，所以保险起见还是扫一眼 policy 表的存储引擎。

ENGINE=InnoDB ，没有问题。

顺便总结一下 InnoDB 引擎和 MyISAM 引擎的区别，要不然这篇文章字数太少，觉得对不起你们...

MyISAM 只支持表级锁，InnoDB 支持行级锁和表级锁，默认行级锁；
MyISAM 不支持事务，执行速度也就更快一些；InnoDB 支持事务，可以 commit、rollback ，具有崩溃后的修复能力；
MyISAM 不支持外键，InnoDB 支持外键（尽管实际应用中我们很少使用，一般都是在业务上进行约束）；
MyISAM 不支持 MVCC , InnoDB 支持 MVCC ；大家可以把 MVCC 看做是乐观锁的一种实现。

2. 执行计划

直接看一下 UPDATE 语句的执行计划。

id select_type table type possible_keys key key_len ref rows Extra -- ----------- ------ ----- ------------- ------- ------- ------ ------ ----------- 1 SIMPLE policy index (null) PRIMARY 4 (null) 784896 Using where

我们注意其中的 "rows" 这个指标，它代表着 MySQL 认为执行这条 SQL 语句必须要逐行检查和判断的数据条数，而“786432”就是 policy 表的数据条数；

这也就是说：当执行 UPDATE 操作的时候，如果 where 条件不能走索引（可能是没有索引，也可能是因为错误的写法导致 SQL 不走索引），那么 MySQL 会使用表级锁；因此我们压测过程中，高并发的场景下，表级锁会导致数据库死锁。

找到原因之后，解决起来也就非常简单了，那就是给 orderno 增加索引，再让我们看看加完索引之后的执行计划：

id select_type table type possible_keys key key_len ref rows Extra -- ----------- ------ ----- ------------- ----------- ------- ----- ---- ----------- 1 SIMPLE policy range idx_orderno idx_orderno 153 const 1 Using where

rows = 1，变成了行级锁，再次压测，问题解决！

-End-

最近有一些小伙伴，让我帮忙找一些面试题资料，于是我翻遍了收藏的 5T 资料后，汇总整理出来，可以说是程序员面试必备！所有资料都整理到网盘了，欢迎下载！

高并发场景下更新数据库报错，记录一次 MySQL 死锁问题的解决