Update 未加索引导致的微服务模块不可用

2023/6/14 MySQL

# 前言

开发环境一个微服务模块所有接口请求报错,对应页面无法访问。其他未涉及到该模块的接口和页面访问正常。

最近未更新过该服务,之前该服务也没有发生过不可用的情况。

# 错误排查

根据所观察到的现象和临时思考判断,可以确定是单个服务出现故障,具体原因尚不清楚。

# 查看日志

日志是我们排查问题时的第一个入口,根据日志信息,可以进一步定位问题。

  • 登陆到远程服务器,执行如下命令,查看容器日志。
docker ps // 查看容器 id
docker logs -f --tail 2000 容器id		// 查看容器最后2000行日志
1
2
  • 日志中显示的报错信息如下
org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms.
1
  • 报错信息显示连接池请求数据库连接超时,有几种可能的原因会导致该错误:

    1、网络或数据库故障,这也是最先被排除的原因,因为其他微服务运行正常,说明网络连接没问题、数据库也没故障宕机。

    2、连接池配置不足,因为业务本身请求量并不大,同时也查看了连接池的相关配置,并未发现问题,因此这种原因可能性较小,但不排除;

    3、连接池最大活跃连接数达到了上限。连接池的配置基本没有问题,但有可能因为异常情况导致连接数达到了上限,这个原因可能性相对来说最高,但还要观察数据库的运行情况,收集更多信息才能进行下一步判断。

# 连接数据库

information_schema 数据库下有几张表可以帮助我们收集数据库的运行情况。

  1. 查看当前数据库运行的所有事务,确认是否有大事务长时间持有数据库连接。表 INNODB_TRX 记录了当前正在运行的事务信息,包括事务 ID、事务状态、事务开始时间、锁等待时间等。
// 查看当前运行的所有事务
select * from information_schema.INNODB_TRX;
1
2
  • 查询结果如图。发现大量事务处于 LOCK WAIT 状态,只有一个事务处于 RUNNING 状态,被阻塞的事务都是在执行更新同一张表中的记录操作。

ba585b0c760dc1b474202c3cae27dd6c.png

  1. 进一步确认,查看当前数据库出现的锁信息。表 INNODB_LOCKS 记录了当前被锁定的对象以及相关的锁信息,包括事务 ID、锁类型、锁定模式、锁定对象等。注意 MySQL 8.0 版本之后没有此表
// 当前出现的锁
select * from infromation_schema.INNODB_LOCKS;
// MySQL 8.0之后执行下面语句
select * from performance_schema.data_locks;
1
2
3
4
  • 查询结果如图。锁的记录数正好对应上面查询的事务数,并且都持有 X 锁(排他锁)

image-20221222142135836.png

# 问题定位&解决

至此,微服务获取不到数据库连接的问题原因已经明确:数据库中大量事务占用连接资源并处于阻塞状态,连接池最大连接数达到上限,无法获取新的连接来处理请求。因此只要找到事务被阻塞的原因并且解决,那么整个问题就解决了。

查看事务执行的 SQL 语句和对应的表结构,发现 where 条件后的字段没有添加索引。更新操作导致了锁表!!!

解决:为字段加上索引(一个索引引发这么大问题!)。

alter table 表名 add index 索引名(列名)
1

# 问题

  1. 为什么服务运行了一段时间后,出现了这个问题?

    服务刚运行的时候,连接池资源是够用的,业务也能正常使用。但是这条更新语句调用频繁,会不断产生新连接执行更新操作,然而同一时刻只能有一个事务执行(锁表),其他事务都会阻塞。阻塞的事务越来越多,事务又占有连接资源,可用的连接数越来越少,服务运行一段时间之后,就出现了问题。

  2. update 没加索引,为什么会锁表?

    数据库的事务隔离级别是“可重复读”。在 InnoDB 事务中,对记录加锁的基本单位是 next-key 锁(记录锁 + 间隙锁)。当 update 语句的 where 条件没有使用索引时,需要扫描整个表来找到满足 WHERE 条件的记录,于是就会对所有记录加上 next-key 锁,相当于把整个表锁住了。

  3. update 加上索引,能避免锁表吗?

    如果条件字段是唯一索引,next-key 锁会退化成记录锁,只会锁一条记录,不会锁表。

    如果条件字段不是唯一索引,得看这条语句在执行过程中,优化器最终选择的是索引扫描,还是全表扫描,如果走了全表扫描,同样还是会锁表。

# 末尾

实践是检验真理的唯一标准。问题排查与解决的过程也是理论应用于实战的过程。