技本功丨一个锁等待现象的诊断案例

2024-06-14 20:18

本文主要是介绍技本功丨一个锁等待现象的诊断案例,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

 

 

作者:苏坡 袋鼠云云服务部-DBA团队 数据库工程师

 

前两日与一个客户交流,客户提出了一些对mysql隔离级别以及锁的疑问,然后问到了出现锁等待现象的排查思路。这不禁让我回想起,long long ago,做过的一个诊断案例。当年我还不是一枚老司机,折腾了两三天才完全搞定,现在回想还颇有些借鉴价值,故,分享之~

 

1 问题描述

数据库实例:主库XXXX:3306 

问题详情:客户反映,涉及到user_site表相关的程序回调操作很慢,部分操作会超时报错;

下单操作很慢甚至直接报错失败

程序端报错信息如下:

General error: 1205 Lock wait timeout exceeded; try restarting transaction"

 

2 分析

这是个典型的锁等待超时退出的报错,原因可能是某些事务未及时提交导致锁资源不被释放导致。

1、慢SQL分析

思路:通过rds控制台查看最近一段时间的慢SQL,看是否出现user_site表相关的DML或者select锁定读操作。 

查看慢SQL方式见下图

 

很可惜,并没有找到相关的慢SQL,那么就代表SQL执行都很快,事务的处理上出现了问题,因此,我们更相信是某些程序没有及时提交事务导致的了。

不过,还是有些收获的,我们发现一些潜在的问题,就是top前几的慢SQL。都是非锁定select,因此和本次现象无关,但是这几个查询操作最长需要近百秒,十分消耗CPU以及内存资源,需要优化。

2、事务阻塞分析

思路:通过一个脚本,定期采集出现阻塞的事务信息,只要再次进行相关操作,阻塞事件的参与事务信息便被计入日志,可对其进行详细分析。 

采集脚本如下:

大约半小时后,日志中抓取到相关的信息

 

通过分析日志,我们发现每隔一段时间会出现较为严重的连环锁等待现象,主要是以下两种情况:

 

 

解释下上面的日志输出:

waiting_trx_id: 被阻塞的事务ID

waiting_thread:被阻塞的mysql线程ID

waiting_query:被阻塞的SQL语句

blocking_trx_id: 阻塞者的事务ID

blocking_thread: 阻塞者的mysql线程ID

blocking_query: 阻塞者的SQL语句

 

我们发现,阻塞者总是处于非活跃状态,而后的相继操作会进入一个等待队列。之所以会发生阻塞,就是这个空事务持有着被阻塞事务所需要的锁资源,也就是说,以锁定的方式,访问到了相同的记录。

参考前面的慢SQL分析结果,进一步推论,事务内部的SQL执行应该是较快的,那么程序可能处理完SQL之后没有进行提交操作。

定位程序

与客户开发同学后沟通后,对该场景进行重现。实时输出日志,得到阻塞者的线程ID,通过information_schema.processlist视图查询出访问mysql数据库的程序。

客户开发同学仔细排查代码,很可惜也并没有发现未提交事务的代码。那么问题就可能出现在程序自身,是程序处理慢,而并非SQL。提交事务之前,程序极有可能经过一个缓慢的处理过程,至于处理什么,需要下一步做进一步的验证。

接下来,对上面的推论做验证~

3、SQL审计分析

思路:想要找到是哪块代码的问题,盲目的找无疑是大海捞针,所幸的是,RDS提供SQL审计功能,只要找到事务提交之前的SQL操作,这个问题的定位就应该清晰明了了。

通过前面排查的线程ID,搜索相关的SQL审计记录。SQL审计可以通过下图方式查看。 

 

点击数据库安全→SQL审计。输入筛选条件:database name,程序访问数据库的用户名,关键字输入线程ID,并选择有效的时间范围。 

这次,终于有些收获了,并且应该可以对本次现象做出合理的解释了。以下是可疑的数据库操作:

 

 

我们发现,事务开启后,立即进行了几个SQL操作,而这几个SQL都没有被计入慢日志,因此SQL执行很快(2S以内),从前面的日志输出我们也能看出,SQL执行是瞬间完成的,因为我们根本抓取不到事务的SQL语句。

而事务的提交操作却发生在十几分钟之后,那么现在的疑问就是解决问题的关键了。长达十几分钟的事务,而SQL执行总共不到2S,那么其余的时间程序在干什么?

有了针对性的目的后,该客户开发同学马上定位到程序并找到了问题所在,在提交之前,程序会去进行插入队列和删除缓存的操作,而这些操作,占用的都是事务的持有锁且非活动时间。

届此,该问题的分析过程全部结束,出现问题的原因就在于事务内部的非数据库操作 。

3 处理方案

根因分析

事务内部进行的不只是数据库操作,程序进行的插入队列以及删除缓存操作,让一个2S之内完成的事务,延长至十几分钟;也就意味着,这十几分钟内,凡是请求事务内部锁资源的SQL操作,必须进入锁等待状态。

处理建议

1.事务内部只进行SQL操作,程序的处理一律放在事务提交之后或开始之前。

2.如果存在逻辑问题不可修改,可为相关处理过程设计处理队列,抛出指令即可,而不用等待这个过程处理完毕再提交事务。

这篇关于技本功丨一个锁等待现象的诊断案例的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1061413

相关文章

Hadoop企业开发案例调优场景

需求 (1)需求:从1G数据中,统计每个单词出现次数。服务器3台,每台配置4G内存,4核CPU,4线程。 (2)需求分析: 1G / 128m = 8个MapTask;1个ReduceTask;1个mrAppMaster 平均每个节点运行10个 / 3台 ≈ 3个任务(4    3    3) HDFS参数调优 (1)修改:hadoop-env.sh export HDFS_NAMENOD

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题,本文将一起从代码层去分析为什么没有建立索引? 开源ERP项目地址:https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置

深入探索协同过滤:从原理到推荐模块案例

文章目录 前言一、协同过滤1. 基于用户的协同过滤(UserCF)2. 基于物品的协同过滤(ItemCF)3. 相似度计算方法 二、相似度计算方法1. 欧氏距离2. 皮尔逊相关系数3. 杰卡德相似系数4. 余弦相似度 三、推荐模块案例1.基于文章的协同过滤推荐功能2.基于用户的协同过滤推荐功能 前言     在信息过载的时代,推荐系统成为连接用户与内容的桥梁。本文聚焦于

【区块链 + 人才服务】可信教育区块链治理系统 | FISCO BCOS应用案例

伴随着区块链技术的不断完善,其在教育信息化中的应用也在持续发展。利用区块链数据共识、不可篡改的特性, 将与教育相关的数据要素在区块链上进行存证确权,在确保数据可信的前提下,促进教育的公平、透明、开放,为教育教学质量提升赋能,实现教育数据的安全共享、高等教育体系的智慧治理。 可信教育区块链治理系统的顶层治理架构由教育部、高校、企业、学生等多方角色共同参与建设、维护,支撑教育资源共享、教学质量评估、

客户案例:安全海外中继助力知名家电企业化解海外通邮困境

1、客户背景 广东格兰仕集团有限公司(以下简称“格兰仕”),成立于1978年,是中国家电行业的领军企业之一。作为全球最大的微波炉生产基地,格兰仕拥有多项国际领先的家电制造技术,连续多年位列中国家电出口前列。格兰仕不仅注重业务的全球拓展,更重视业务流程的高效与顺畅,以确保在国际舞台上的竞争力。 2、需求痛点 随着格兰仕全球化战略的深入实施,其海外业务快速增长,电子邮件成为了关键的沟通工具。

【区块链 + 人才服务】区块链集成开发平台 | FISCO BCOS应用案例

随着区块链技术的快速发展,越来越多的企业开始将其应用于实际业务中。然而,区块链技术的专业性使得其集成开发成为一项挑战。针对此,广东中创智慧科技有限公司基于国产开源联盟链 FISCO BCOS 推出了区块链集成开发平台。该平台基于区块链技术,提供一套全面的区块链开发工具和开发环境,支持开发者快速开发和部署区块链应用。此外,该平台还可以提供一套全面的区块链开发教程和文档,帮助开发者快速上手区块链开发。

JVM 常见异常及内存诊断

栈内存溢出 栈内存大小设置:-Xss size 默认除了window以外的所有操作系统默认情况大小为 1MB,window 的默认大小依赖于虚拟机内存。 栈帧过多导致栈内存溢出 下述示例代码,由于递归深度没有限制且没有设置出口,每次方法的调用都会产生一个栈帧导致了创建的栈帧过多,而导致内存溢出(StackOverflowError)。 示例代码: 运行结果: 栈帧过大导致栈内存

STL经典案例(四)——实验室预约综合管理系统(项目涉及知识点很全面,内容有点多,耐心看完会有收获的!)

项目干货满满,内容有点过多,看起来可能会有点卡。系统提示读完超过俩小时,建议分多篇发布,我觉得分篇就不完整了,失去了这个项目的灵魂 一、需求分析 高校实验室预约管理系统包括三种不同身份:管理员、实验室教师、学生 管理员:给学生和实验室教师创建账号并分发 实验室教师:审核学生的预约申请 学生:申请使用实验室 高校实验室包括:超景深实验室(可容纳10人)、大数据实验室(可容纳20人)、物联网实验

(入门篇)JavaScript 网页设计案例浅析-简单的交互式图片轮播

网页设计已经成为了每个前端开发者的必备技能,而 JavaScript 作为前端三大基础之一,更是为网页赋予了互动性和动态效果。本篇文章将通过一个简单的 JavaScript 案例,带你了解网页设计中的一些常见技巧和技术原理。今天就说一说一个常见的图片轮播效果。相信大家在各类电商网站、个人博客或者展示页面中,都看到过这种轮播图。它的核心功能是展示多张图片,并且用户可以通过点击按钮,左右切换图片。

温差电现象

温差电现象练习题 1. 两种不同的金属A和B构成温差电偶,冷端温度为0℃,热端温度为T,塞贝克系数为αAB,写出热电势的表达式。 2. 什么是温差电动势?它与哪些因素有关? 3. 解释珀尔帖效应和汤姆逊效应,并说明它们之间的区别。 4. 一块长为10cm,截面积为1mm2 的铜条,两端温度差为100K,铜的电导率为5.8×107 S/m,计算通过铜条的热流密度。 5. 一个温差电偶的热电