mysql死锁和分库分表问题详解
记录生产mysql的问题点。
业务场景与问题描述
请求一个外部接口时，每天的请求量在900万左右。
分为请求项目和回执这两个项目。请求是用来调用外部接口，回执是接收发送的接口。
在发送请求前会先插入数据库。
在请求后，如果接口返回调用失败，会更新数据库状态为失败。
如果发送成功，则会等待上游给出回执消息后，然后更新数据库状态。
而在生产运行过程中，半年出现过两次mysql导致的mq消费者堆积的问题。
问题分析
记录两次不同的原因导致的生产问题及原因分析。
mysql死锁问题
查看mq聚合平台TPS
上生产发现mq数据一直堆积，且不断上升。而TPS仅为30左右，一直上不去。
这就会使mq消费变慢了，导致不断堆积。具体什么原因导致mq一直堆积，需要继续排查。
查看生产服务器日志
查看生产服务器日志，发现有报错dead Lock的错误。
error response from MySQLConnection [node=24, id=277499, threadId=2735941, state=borrowed, closed=false, autocommit=true, host=10.1.10.74, port=3306, database=sep_4, localPort=27744, isClose:false, toBeClose:false, MySQLVersion:5.7.25], err: Deadlock found when trying to get lock; try restarting transaction, code: 1213
具体的sql如下：
update stage set status = 'success',reply_time = '2021-03-07 10:40:11' where code = '000123' and create_time > '2021-03-03 00:00:00';
也就是说在执行服务时出现了死锁的情况。
具体有多少条以及耗时，在生产服务器看着不直观，于是就让dba将慢sql的语句和耗时查出来。
查出后发现最长的慢sql的耗时长达7780ms。
仔细查看会发现，sql会发现相同的id一个在执行中，一个在Lock Wait状态。
而这慢sql中有大量的Lock Wait状态。
什么原因导致的死锁
mysql使用的数据库引擎时InnoDB。先了解下什么是死锁：
所谓死锁: 是指两个或两个以上的进程在执行过程中,
因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.
此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等竺的进程称为死锁进程.
通过上面的排查可以看出，出现死锁的问题就是：
在执行sql更新一条数据时，会将这一行数据锁定，执行完成后会释放行锁，而没有执行的sql处于Lock Wait状态。
而程序中导致此原因在于，在发送前后和回执时，频繁操作数据库，可能会出现同时操作同一条数据的情况。
所以在执行中就出现了锁等待的情况。
分库分表未带分片键
首先告警的是stage_prod库的CPU飙到了85%。
数据库线程数是否被打满
经过查看数据库连接情况可知，数据库连接数并没有被占满。
查出慢sql和耗时
查出的问题sql：
update stage set status = 'success',reply_time = '2021-03-07 10:40:11' where create_time > '2021-03-03 00:00:00';
查看sql会发现，这条sql竟然没有带分片键code字段。而这条sql是回执时执行的。
排查生产服务器日志
代码中有做判断，如果code值不为空，sql会带上code的值。那么没带上，就需要查看为何没有带上。
查看代码会发现，code是从redis中获取的，是在发送时set到redis中的。但是没有set进去就很奇怪了。
初步怀疑是redis问题，然后就与redis维护的平台沟通，发现果真是因为redis故障导致的问题。
为什么不带分片键CPU就会飙升
首先公司用的是hotdb分库分表，因为每天的入库量是在900万左右，一个表是上亿条数据。
如果只是单纯用索引，是无法满足要求的。
分库分表hotdb，根据code值做hash分片，做了64个分片。也就是说64个数据库，分布在8台服务器上的16个实例里面。
这样可以避免各分片数据不均，理论上避免了过度集中在某个分片上。
而如果不带分片键code的sql，所有的dml操作全部下发到所有的底层库上进行执行，相当于遍历了一遍库。
这样就可能会导致CPU直接飙到99%，甚至直接导致服务器直接崩掉，这样操作是很可怕的。
解决办法
应急处理：先停掉几台服务减少数据库操作
数据持续堆积，会影响数据处理速度。那么，就要先降低操作的速度，最快速的办法就是停服务，减少数据库的操作频率。
减少数据库操作避免数据库死锁
死锁一般时由于程序上没有控制好dml操作的提交，没有及时提交.
减少重复操作同一条数据。在批量操作时减少每批dml数，保证快速提交，避免长事务，避免重复提交dml。
那么怎样减少操作呢？
合并sql
将发送前插入和发送失败时更新，直接合并到一条sql，这样就可以避免多次操作同一条数据的情况。
批量执行时减少长事务和条数
执行时发现，每次批量执行20条sql，比一次性执行200条的效率更快。
所以尽可能避免这种问题。
每条sql必须带分库分表分片键
原则就是不能因为一条数据就拖累整个数据库的操作速度。
分片键必须带上，如果不带分片键，就抛错。
增加时间区间开闭区间
用code来做分片键，用createTime做分区。那么在保证code存在的情况下，可以写上开闭区间，可以提高执行效率。
更优解：sql顺序执行
这种方案可以通过把将要执行的sql统一发到一个mq来消费执行，这样可以保证sql顺序执行，从而避免死锁的产生。
但是这个需要根据业务场景来区分。
复盘
mysql死锁问题，要尽可能避免频繁操作同一条数据，也要避免长事务；
针对分库分表问题，一定要带上分片键；
监控机制不可少；
总结
到此这篇关于mysql死锁和分库分表问题的文章就介绍到这了,更多相关mysql死锁和分库分表内容请搜索狼蚁SEO以前的文章或继续浏览狼蚁网站SEO优化的相关文章希望大家以后多多支持狼蚁SEO！