当 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机制回收,不会造成影响。

原创声明,本文系作者授权云+社区-专栏发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

编辑于

我来说两句

1 条评论
登录 后参与评论

相关文章

来自专栏阿杜的世界

RocketMQ学习-NameServer-2

上篇文章主要梳理了NameServer的启动器和配置信息,并复习了JVM中的关闭钩子这个知识点。这篇文章看下NameServer的其他模块。建议带着如下三个问题...

571
来自专栏CLEAN_CODER

Django 数据库事务

Django框架提供了好几种方式来控制和管理数据库事务。(以下Django框架会简化为Django,读者可自行脑补框架两字)

882
来自专栏后台开发+音视频+ffmpeg

dpvs源码分析(续二)

在上一篇<dpvs源码分析(续)>中,我们以tcp为例,讲到了连接的建立,同时也提到了full-nat,snat这些术语。在该篇中,我们再来讲讲连接建立的过程。

2105
来自专栏芋道源码1024

分布式事务 TCC-Transaction 源码解析 —— 事务存储器

本文主要基于 TCC-Transaction 1.2.3.3 正式版 1. 概述 2. 序列化 2.1 JDK 序列化实现 2.2 Kyro 序列化实现 2.3...

3626
来自专栏Android源码框架分析

听说你Binder机制学的不错,来面试下这几个问题(二)

1345
来自专栏Linyb极客之路

多线程的应用场景

多线程用于堆积处理,就像一个大土堆,一个推土机很慢,那么10个推土机一起来处理,当然速度就快了,不过由于位置的限制,如果20个推土机,那么推土机之间会产生相互的...

332
来自专栏后端之路

dubbo缓存代码分析

dubbo是Ali出品的soa框架,属于互联网企业常见的rpc选择框架。 前几篇分析了多级缓存的相关代码,本篇就dubbo的缓存进行梳理。 dubbo的缓存针...

2407
来自专栏名山丶深处

springboot集成schedule(深度理解)

2055
来自专栏抠抠空间

scrapy-redis分布式爬虫

1142
来自专栏小樱的经验随笔

HDU 2602 Bone Collector(01背包裸题)

Bone Collector Time Limit: 2000/1000 MS (Java/Others)    Memory Limit: 32768/327...

3436

扫码关注云+社区