Update 操作 Where 字段未加索引引发的服务不可用

开发环境一个微服务模块所有接口不可用，对应页面无法访问。之前一直没有问题，早上打开页面就出现了500异常信息，并且其它微服务模块接口正常，页面也能访问。

错误排查

1、连接服务器，查看日志发现如下错误信息

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.

报错信息显示数据库连接超时，初步判断是请求量太高导致数据库连接数不够。进一步验证，查看 yml 配置文件中 Hikari 数据库连接池的配置，发现并没有配置最大连接数（maximum-pool-size），那默认最大连接数为10。至此想到的一个解决方案就是增加数据库连接池的最大连接数，但是还需要进一步查看数据库的运行情况。

2、连接数据库，查看 MySQL 运行是否正常

// 查看当前运行的所有事务
select * from information_schema.INNODB_TRX;

// 当前出现的锁
select * from infromation_schema.INNODB_LOCKS;

运行第一条SQL语句，查询到 10 条记录（正好对应上线程池的 10 条连接数），其中 9 个事务状态为 LOCK WAIT （trx_state列），1 个事务状态为 RUNNING 。这 10 个事务都是对同一个表执行更新操作（trx_query列展示事务正在执行的SQL语句）。再运行第二条SQL语句，也是查询到 10 条记录，并且所有记录的锁模式都是X（排它锁，lock_mode列）。

INNODB_TRX表信息

INNODB_LOCKS表信息

到这里就明确了数据库中只有一个事务在执行，大量事务占用连接资源并处于等待状态，因为当前所有事务都是对同一个表进行更新操作，可能的情况就是对表加了表锁。加上了表锁，说明更新操作进行了全表扫描，没有走索引。之后查看执行的SQL语句，再查看建表信息，发现where 后的字段果然没有加索引。

UPDATE table SET a = ?,b = ? WHERE (id = ?)

解决

为该字段加上索引

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

问题

1、为什么服务运行了一段时间后，出现了这个问题？

服务刚启动的时候，连接池资源是够用的，业务也能正常使用。业务中这条更新语句会被频繁调用。当第一个事务执行更新操作的时候，此时有新的事务也要执行更新操作，后面的事务就要被阻塞（表锁）。调用次数多，处理速度慢，阻塞的事务越来越多，事务又占有数据库连接，可用的数据库连接数越来越少，当未来的某一天所有的数据库连接都在执行这条更新语句的时候，就出现了这种问题。

2、update不走索引，为什么会锁表？

数据库的事务隔离级别是“可重复读”。这个隔离级别下，多个事务并发的时候，会出现幻读的问题，因此 InnoDB 通过 next-key 锁（记录锁和间隙锁）来解决幻读现象。当我们执行 update 语句时，实际上是会对记录加排它锁（X锁）的，其他事务对持有排它锁的记录进行修改时会被阻塞，而且这个锁并不是执行完 update 语句就会释放，而是会等事务结束时才释放。

在 InnoDB 事务中，对记录加锁的基本单位是 next-key 锁，但是会因为一些条件退化成间隙锁，或者记录锁。加锁的位置准确的说是加在索引上的而不是记录行上。

在 update 语句的 where 条件使用了索引，那么 next-key 锁会退化成记录锁，也就是只会给一行记录加锁。

在 update 语句的 where 条件没有使用索引，就会全表扫描，于是就会对所有记录加上 next-key 锁（记录锁 + 间隙锁），相当于把整个表锁住了。那么锁就会持续很长一段时间，直到事务结束。