记录一次mysql update导致锁表问题

柔光的暖阳◎ 2023-09-29 15:02 23阅读 0赞

## 事发情况 ##

当天晚上生产服务一直在告警，刚刚开始只是一个数据保存接口耗时过长，紧接着整个服务接口都耗时，对外表现为整个服务不可用。

于是优先排查宽带情况，其次看代码逻辑，看错误日志（无一例外都是接口耗时过长报警）。

接着看监控发现goroutine明显增多，依然没有头绪。于是想到重启服务。

重启服务过程中，弹出一个告警日志-db连接数过多，已经超过筏值。这个时候问题才确定。

## 问题原因 ##

是最初的那个数据保存接口，逻辑很简单，接口拿到请求时，insert或者update进入db内。**问题在于update时未对where的条件加上索引**，导致数据量大时，会锁住全表，导致整个服务不可用。

该接口上线一个多月稳定，事发晚上数据量达到某一临界点，update时锁住全表，后面该接口的请求一直在等待前面的响应。随着请求数量不断增多，从而引发雪崩效应。

定位原因后排查日志，发现错误信息：

Lock wait timeout exceeded; try restarting transaction

## 为什么改表被锁会影响到服务其他业务响应 ##

一个服务内有配置数据库的连接数上限，而这个接口因为锁表了，导致把连接数占光。从而影响服务其他正常业务的响应。

同时go中每个http请求底层都会new一个goroutine出来，当大量请求访问时，也就解释了为什么goroutine数量会暴涨。

## 反思 ##

1.  先从排查方向来说，当时排查带宽时思路是错误的，如果带宽有问题那么其他服务应该也是异常的，而不只是这个服务。
2.  当一个服务不可用时，应当优先考虑重启该服务或者回滚到之前版本，而不是先分析错误原因。
3.  mysql的存储引擎用的是innodb，update时基于next-key lock，如果不加上索引会锁住整张表进行操作，这是个大坑。之前本人没注意过该问题
4.  一些关键的地方开发时没有及时加上短信/邮件告警及时通知开发人员，导致这么久错误才被定位到，也浪费了大量的排查时间。

## 该问题更优解决方案 ##

即使加了索引，但是mysql update时也有可能会锁住全表(可以通过explain查看)。以使用 force index(\[index\_name\]) 可以告诉优化器使用哪个索引，以此避免有几率锁全表带来的隐患。

面对这种频繁更新的需求，维护索引也是有很高的代价。最适合方案应该考虑非关系型数据库来做存储(如mongodb)

![21b8d3fa4b3f445f6ddd512208f5a0d6.png][]

[21b8d3fa4b3f445f6ddd512208f5a0d6.png]: https://img-blog.csdnimg.cn/img_convert/21b8d3fa4b3f445f6ddd512208f5a0d6.png

发表评论取消回复

表情：

评论列表（有 0 条评论，23人围观）

还没有评论，来说两句吧...

相关阅读

相关 MySQL中select * for update锁表的问题

MySQL中select \ for update锁表的问题由于InnoDB预设是Row-Level Lock，所以只有「明确」的指定主键，MySQL才会执行Row l

╰+攻爆jí腚メ/ 2024年02月18日 19:03/ 0 赞/ 41 阅读

相关记录一次mysql update导致锁表问题

事发情况当天晚上生产服务一直在告警，刚刚开始只是一个数据保存接口耗时过长，紧接着整个服务接口都耗时，对外表现为整个服务不可用。于是优先排查宽带情况，其次看代码逻辑，

柔光的暖阳◎/ 2023年09月29日 15:02/ 0 赞/ 24 阅读

相关 Oracle 一次ddl锁表等待记录

ddl执行卡住了，event显示为行锁看到当前锁会话也没有发现问题查询表对象的session，发现大量不活动的连接，plsql客户端连接多达400多怀疑是中断

矫情吗；*/ 2023年07月04日 15:35/ 0 赞/ 7 阅读

相关记一次mysql执行DDL导致锁表

背景线上某数据库意外发现缺少索引，并且该表的数据量很少，只有几万条记录而已，因此很随意地尝试给该表添加索引。原本预期该表的记录很少，添加索引的耗时应该很短，结果却直接导

灰太狼/ 2023年02月21日 01:53/ 0 赞/ 65 阅读

相关 mysql select 锁表_MySQL中select * for update锁表的问题

先前介绍过SELECT ... FOR UPDATE的用法，不过锁定(Lock)的资料是判别就得要注意一下了。由于InnoDB预设是Row-Level Lock，所以只有「明确

- 日理万妓/ 2023年01月11日 03:57/ 0 赞/ 163 阅读

相关 mysql update锁表_MySQL执行update语句是锁行还是锁表分析

我们在数据库执行update语句的时候，到底是锁表还是锁行？这里直接用MySQL上例子测试下。一、环境准备 1、新建一个表create table test\_updat

女爷i/ 2023年01月11日 03:46/ 0 赞/ 226 阅读

相关记一次生产事故-mysql执行update导致锁整表

我第一次知道，执行update的时候，where条件如果不走索引，会导致锁全表！然后这个事务执行时间较长，导致后面的除了select语句，其他语句都被锁住不能执行

系统管理员/ 2022年10月27日 02:27/ 0 赞/ 246 阅读

相关记录es一次死锁问题排查

1、问题概述业务流程 1、从kafka消费数据。 2、业务解析，组装数据。 3、将组装好的数据，使用bulkProcessor.add(indexRequ

柔情只为你懂/ 2022年09月04日 11:55/ 0 赞/ 332 阅读

相关 mysql批量更新update操作，导致锁表

sql如下： update pay_trans set return_state = 1 WHERE

Bertha 。/ 2022年05月31日 05:42/ 0 赞/ 1474 阅读

相关记录一次Mysql死锁排查事件

Navicat 修改表卡死 Mysql 死锁了我：咔咔写代码，然后IDEA正在debug…这个时候，我的debug还没结束！同事（大佬）过来我这边讨论表结构。

妖狐艹你老母/ 2022年04月22日 05:56/ 0 赞/ 284 阅读