当前位置:首页>网站建设>正文

MySQL数据库卡表问题分析并解决

发布时间:2020-02-22 18:10:49 作者:小超越君

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

1
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 应该还不至于这么脆弱,应该是哪里设置的问题,或者是代码写的有问题;我大概看了一下数据库设置、表结构和代码,大概是这样的(不是真实的表和代码,我按照问题的过程重新写的):

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

1
2
3
4
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 语句的执行计划。

1
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 增加索引,再让我们看看加完索引之后的执行计划:

1
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,变成了行级锁,再次压测,问题解决!

文章内容欢迎转载,请保留本文链接:https://www.zhe94.com/599.html

专注高端湛江网站建设及湛江SEO优化服务 立即咨询