当 MySQL 连接池遇上事务（一）：神秘的幽灵锁

MySQL连接池是一个很好的设计，通过将大量短连接转化为少量的长连接，从而提高整个系统的吞吐率。一般各个团队都会对连接池进行封装，只提供简洁的接口供上层使用。在上层看来，并不知道底层是否使用了连接池（甚至连访问数据库的IP和Port都不知道），只知道调用了一个接口，执行了指定的SQL语句，并返回执行状态和执行结果。

本来是很好的解耦分层设计，但是当上层使用方式不恰当时，就会发生一些奇怪的事。最近我们项目就遇到了这样奇怪的事情，且听我慢慢道来。

1. 背景

首先交待一些基本背景。我们项目使用了OpenResty作为API Server，并用resty.mysql作为MySQL库。resty.mysql提供了MySQL连接池功能，connect()时会首先从连接池中查找空闲的连接，如果找不到才创建一个新的连接；当使用完毕之后，可以通过set_keepalive()将当前连接放回连接池中，供保活时间内其他请求使用。

这是公共库中封装的执行SQL函数，上层执行SQL都是通过这个函数实现的。

--Do DB query
--return (false, error message) or (true, result set) 
function ConfDB:query(sql)
    local ok, db = self:get_connect()
    if not ok then
        return false, db
    end

    local res, err, errno, sqlstate = db:query(sql)
    if not res then
        return false, "mysql query failed: " .. (err or "") 
    end

    -- with 60 seconds max idle timeout
    local ok, err = db:set_keepalive(60000, 100)
    if not ok then
        return false, "failed to set keepalive: " .. (err or "") 
    end

    return true, res
end

也许有些聪明的同学已经发现了问题了，但是在奇怪的事情发生之前，没有人意识到，而且这个函数也确实稳定可靠的运行了很长时间。

2. 奇怪的事情

前一段时间，发生了几次用户在页面配置时报错，定位的结果是接口超时，而接口超时的原因是DB的表X被锁住了。本来表被锁住了也很正常，找出加锁的地方看看有什么使用不当就行了。

但是搜索了所有的代码，被锁的表X只找到了一处加锁的代码，而日志显示，这处代码的多个线程都在等已有的锁，没有任何一个线程获得了锁。

既然表级锁找不到（行级锁已排除），那么是否是数据库级别的锁呢？查看数据库备份的日志，发现mysqldump的时间点跟锁完全对不上。

这就是那把奇怪的锁，它锁住了我的表，却找不到锁的来源，我把它叫做“神秘的幽灵锁”。

3. 顺藤摸瓜

作为一个唯物主义者，我决定对MySQL的状态进行监控，来捕捉这个幽灵锁。定位的方式很简单：每秒执行几个SQL查询语句，并记录查询的结果，作为问题再现时的定位依据。具体SQL语句如下：

1. show processlist;                                    --查看当前正在执行的SQL语句
2. select * from information_schema.INNODB_TRX;         --查看当前已开启的事务
3. select * from information_schema.INNODB_LOCKS;       --查看当前事务开启的锁
4. select * from information_schema.INNODB_LOCK_WAITS;  --查看当前事务锁的等待关系

至于显式锁表的情况，上述语句不能查询，则通过加日志协助定位（MySQL 5.7以上版本也可以通过SQL查询）。

定位的结果也是相当奇怪：某个地方开启了一个事务，事务锁住了平台的表X和业务的表Y。因为平台的表X被锁，导致接口等待超时页面报错。

这就引出了好几个问题，只要能解答这几个问题，幽灵锁就会现出原型。

1) 什么地方开启的事务？

通过搜索代码发现，平台没有显式使用事务的地方，只有业务侧为了保证操作的原子性，开启了事务，初步怀疑是某个分支没有执行commit或rollback就退出。查看业务逻辑的代码，所有的异常处理分支已都加上了rollback，这就奇了怪了。

既然代码没问题，那就只能检查运行时问题了。查看OpenResty的access.log，竟然惊奇的发现接口报500错误，而在error.log查找该请求的日志，又找到了错误日志“lua entry thread aborted: runtime error”。真是山重水复疑无路，柳暗花明又一村哈。那么问题很明确了，就是这个接口开启了事务，因为某个异常没有处理导致异常退出，没有执行commit或者rollback。

2) 事务为什么会锁表？

首先，事务内并没有显式的加锁，那就只能是数据库本身加的锁了。而数据库会不会加锁，会加什么锁，则跟数据库配置相关。为了验证我的想法，我确认了一下数据库的事务隔离级别：

MySQL > select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

结果表示当前事务隔离级别是“重复读”级别（MySQL的Innodb默认事务隔离级别），那么也就解释的通了。MySQL的RR级别为了保证不允许脏读、不允许非重复读、不允许幻读（是的，MySQL作为一个成熟的数据库引擎，RR级别已经解决了幻读问题），当执行update等操作时，会对操作影响的记录加上行锁和间隙锁。而业务的SQL语句update条件没有索引，所以就导致了全表被锁了。

3) 事务是基于连接的，在异常退出后，锁为什么没有自动释放？

MySQL连接对象是在lua脚本中创建的，按理说lua entry thread abort，即使句柄没释放，也会被OpenResty的GC机制回收才对，事务不可能持续那么长时间。

有一种可能是，该连接对象是一个全局对象，或者是lua脚本级的local变量。因为OpenResty的module加载机制，lua脚本级的local变量，都只会加载一次，并且在lua脚本退出后生命周期还不会结束，相当于无形中变成了全局变量（关于这个特性也是踩过一个坑，后续再专门讲解）。但是检查MySQL连接对象，确实是函数的局部变量，也就不存在上面这个问题。

最后的最后，突然灵光一闪，我们使用的是连接池，那会不会是因为这个连接没被释放呢？顺藤摸瓜，最后找到了文章开头的那个公共库函数，总算找到罪魁祸首了。因为公共库函数每执行一个SQL后立即将连接放回连接池，而接口异常退出是在开启事务并成功执行update语句之后，在HTTP调用时抛异常，此时连接已经放回了连接池，自然没有被释放了。

4) 该事务只操作了业务的表Y，为什么会导致平台的表X被锁？

这是最后一个问题了，其实从前面几个问题的答案，已经基本可以推出这个问题的答案了。因为业务开启了事务的连接被扔回连接池，然后被平台的接口取出执行了SQL语句，导致平台的表也被加上行锁和间隙锁，从而导致任务超时。

4. 改进方案

幽灵锁已经分析的很清楚了，问题出在上层使用MySQL公共库时没意识到底层的连接池，导致使用方式不当。在上层看来是：

开启事务->执行SQL->commit

而实际底层实现是：

获取一个连接->开启事务->扔回连接池->获取一个连接->执行SQL->扔回连接池->获取一个连接->commit->扔回连接池。

这个过程无法保证每次拿到的都是同一个连接，也就存在了很大的问题。之所以之前一直没发生问题，那纯粹是运气好（至于为什么运气能一直这么好，后续文章再揭晓^v^）。

那么解决方案也就很简单了，修改业务接口使用MySQL库的方式，不用上述封装的函数，直接调用resty.mysql的接口就可以了：

local ok, db = ConfDB:get_connect()
if not ok then
    return_with_error()
end

local sql = "xxx"
local res, err, errno, sqlstate = db:query(sql)
if not res then
    return_with_error()
end

local sql = "xxx"
local res, err, errno, sqlstate = db:query(sql)
if not res then
    return_with_error()
end

......

local ok, err = db:set_keepalive(60000, 100)
if not ok then
    return_with_error()
end

接口入口获取一个MySQL连接对象，使用这个对象执行一系列SQL操作，在接口异常处理或正常结束处将连接对象扔回连接池即可。假如在处理过程中发生了异常导致接口异常退出，连接对象由于不在连接池，其他接口无法获取，并且这个连接对象会被OpenResty的GC机制回收，不会造成影响。