专栏首页Coder Sam的专栏当 MySQL 连接池遇上事务(一):神秘的幽灵锁
原创

当 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 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 当 MySQL 连接池遇上事务(二):消失的记录

    ySQL连接池是一个很好的设计,通过将大量短连接转化为少量的长连接,从而提高整个系统的吞吐率。但是当跟事务一起使用时,如果使用方式不恰当时,就会发生一些奇怪的事...

    Coder Sam
  • 快速学习-Mycat 前世今生

    如果我有一个 32 核心的服务器,我就可以实现 1 个亿的数据分片,我有 32 核心的服务器么?没有,所以我至今无法实现 1 个亿的数据分片。——Mycat’s...

    cwl_java
  • 韩国黑客电影《幽灵》顶尖骇客竟然和你用的工具一样?

    小编自己都难以想象竟然能在一部韩剧里挖到这样一部既有长腿欧巴、软萌妹子的~\(≧▽≦)/~,还是讲网络犯罪和信息安全事件的佳作。

    HACK学习
  • gh-ost 在线ddl变更工具​

    作为MySQL DBA,相信我们大家都会对大表变更(大于10G 以上的)比较头疼,尤其是某些DDL会锁表,影响业务可持续性。目前通用的方案使用Percona 公...

    用户1278550
  • gh-ost 在线ddl变更工具​

    作为MySQL DBA,相信我们大家都会对大表变更(大于10G 以上的)比较头疼,尤其是某些DDL会锁表,影响业务可持续性。目前通用的方案使用Percona 公...

    田帅萌
  • 蚂蚁一面二面18道Java真题解析【附答案】!

    今年,从java转到别的行业的人不少,也有不少人挤进这个市场想要分得一杯羹。年复一年,年年如此。当然,Java程序员市场需求依然是比较大的,而且Java岗位晋升...

    Java程序猿
  • Vlog探秘!带你去腾讯全球数字生态大会现场!

    ? 今天在四季常春的昆明有一件大事发生。 人们汇聚春城,从机场到滇池国际会展中心,从地铁到大巴,所见所闻,皆是“腾讯全球数字生态大会”的消息。 ? 作为腾讯"...

    鹅老师
  • 美团面试官:讲清楚MySQL结构体系,立马发offer

    继续和大家分享,我去上海美团面试遇到的技术问题,当时,回答的也是马马虎虎的,不能说不好,也不能说好,反正就是没有给面试官一种爽的感觉。

    田维常
  • 特斯拉大半夜「见鬼」!空无一人的路上,它却看见「幽灵」秒刹车

    但实际上,其实特斯拉看到的并非是「不干净的东西」,而是被称作「幽灵」(Phantom)的一种攻击自动驾驶辅助系统(ADAS)的图像——掺杂在路边广告牌视频中。

    量子位
  • AI系统尝试学习并生成幽默的双关语

    一个自然而有趣的机器人,不仅可以阅读我们的新闻或告诉我们天气,还可以理解笑话或撰写一首诗,甚至可以讲述引人入胜的故事。但这些往往会碰触到人工智能学习的极限。

    AiTechYun
  • Python Day10

    所谓死锁:是指两个或两个以上的进程或线程在执行过程中,因争夺资源而造成的一种互相等待的现象 如下就是死锁:

    py3study
  • 7.14 VR扫描:GIGXR获美军75万美元合同;RPG游戏《幽灵线:东京》宣布延期

    (VRPinea 7月14日讯)今日重点新闻:XR学习解决方案供应商GIGXR宣布,已与美国空军签署了75万美元合同,为空军开发AR/VR化学课程;VR艺术平台...

    VRPinea
  • 记一次Netty连接池FixedChannelPool连接未释放问题的排查总结

    前几天我们又遇到了一个Netty报从连接池获取连接超时异常从而导致整个服务不可用的异常,报的具体异常信息是Exception accurred when acq...

    Bug开发工程师
  • MySQL如何管理客户端连接?线程池篇

    在之前的文章里,为大家介绍了MySQL的连接管理线程的工作方式,在这一篇里为大家介绍管理连接的第二种方式,线程池。

    MySQLSE
  • mysql问题排查实例

    最近遇到应用频繁的响应缓慢,无法正常访问。帮忙一起定位原因,最后定位到的问题说起来真的是很小的细节问题,但是就是这些小细节导致了服务不稳定,真是细节决定成败。这...

    后端技术探索
  • 20位大佬组团“踢馆”,谁超越了图灵?

    导读:在“斜杠青年”这个词出现前的半个多世纪,有一个人,不羁于科学的边界,一生传奇,一世神秘。计算机/人工智能/密码/生物形态/量子/自由意志/神谕/……这就是...

    华章科技
  • Mybatis-update - 数据库死锁 - 获取数据库连接池等待

    最近学习测试mybatis,单个增删改查都没问题,最后使用mvn test的时候发现了几个问题: update失败,原因是数据库死锁 select等待,原因是c...

    Ryan-Miao
  • 原创|线程池详解

    「第一部分 背景」 社区版的MySQL的连接处理方法默认是为每个连接创建一个工作线程的one-thread-per-connection(Per_thread...

    腾讯数据库技术
  • 五款鬼怪类VR游戏,陪你共度清明小长假(文章高能)

    VRPinea

扫码关注云+社区

领取腾讯云代金券