误用autocommit引起的业务hang住

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介:

背景

有用户报告一个普通的select 语句被hang住了,执行超时。查明之后发现是autocommit使用不当导致。

这里将case简化,说明复现步骤及原因。

复现

session1 建表并插入数据:

create table if not exists t(id int primary key, c int);

set autocommit=0;

insert into t values(1,1);

insert into t values(2,2);

insert into t values(3,3);

commit;

select count(*) from t;

这个执行流程的目的很直观,建表、插入数据、查询结果。貌似没有问题。

维持session1不断,新建一个连接session2,执行 create table if not exists t(id int primary key, c int);

此时该语句处于等待状态.

再新建一个连接session3, 执行select count(*) from t; 该语句处于等待状态.

于是从业务上看就是一个select 语句被hang住。

原因分析

MySQL Tips:  如果服务中某些语句无法执行完成,追查问题时第一步要先保留现场,pstack <pid of mysqld> > tmplog之一个常用的方法。

​这两个等待线程的栈如:

#0  0x000000310ce0b7bb in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0

#1  0x000000000063ba46 in MDL_wait::timed_wait(THD*, timespec*, bool, char const*) ()

#2  0x000000000063e095 in MDL_context::acquire_lock(MDL_request*, unsigned long) ()

 可以看到,堵在MDL_wait.

 简单说明下什么是MDL。试想,如果一个语句在执行一个表上的查询过程中,表结构被改了,或者表被drop,这样会得到一个错误的结果。因此在一个事务持续期间,就需要对访问的表结构作保护。这个就是meta data lock (MDL).

很容易理解的,对表数据作增删改查,需要对MDL加读锁,修改表结构、删除表等操作则加写锁。

MySQL Tips: MDL是5.5才加入的机制,5.1版本下本文的case不会复现。

 MySQL Tips: 事务中MDL申请时机是在首次使用时,释放时机是在事务结束后。

也就是说文章开头的这个case,原因是session2等待在加写锁过程。而session3虽然只是加读锁,但与session2冲突,也需要等待。

session1的事务

也就是说session1还持有表t的MDL读锁。但我们的事务明明已经提交(commit)了。这里就涉及到一个常见的误解。以前有看过文章说,可以用set autocommit=0开启一个事务。其实这个描述不准确.

MySQL Tips: set autocommit=0是将本线程设置为非自动提交模式。在每个事务结束后,下个语句开始时自动新建一个事务。

这就意味着,session1最后的那个select count(*)操作,实际上之前隐含了一个begin操作。由于该事务没有提交,因此session1持有表t的MDL读锁。

 因此对于业务方的建议就是,及时提交这些读事务,或断开连接。

MySQL Tips: 连接断开时,MySQL会自动回滚当前未提交的事务。

由于本case里面session1的最后一个事务只是一个select语句,因此回滚不影响业务。

小结

1) 显式的启动事务的方法是begin或start transaction; 提交事务的方法是commit;

2) set autocommit=0的好处是在频繁开启事务的场景下,减少一次begin的交互。

3) 注意set autocommit=0修改了线程变量,会影响本线程存活期间的事务行为。

4) set autocommit=1可以提交事务并改变值,可以与set autocommit=0成对出现。

阿里云RDS feature

当出现如上的错误用法时,修改业务代码需要一段时间。

针对这种case,阿里云RDS版本提供了trx_idle_timeout参数,设置空闲事务停留时间。当超过设置的值(单位为秒)后,连接自动断开。

比如设置为10,在示例的case里面,sessino1在空闲10s后断开连接,session2继续执行,之后session3继续执行。

该值默认为0。

 

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
关系型数据库 MySQL Java
多线程执行事务中再加锁导致的bug----------记一次线上问题优化
多线程执行事务中再加锁导致的bug----------记一次线上问题优化
90 0
间歇性宏图大志,持续性混吃等死...
间歇性宏图大志,持续性混吃等死...
70 0
|
监控 关系型数据库 MySQL
如何避免长事务对业务的影响?
大家好前面我们大概了解了关于事务的隔离级别,实现方式,启动方式的细节,长事务与短事务的区别。今天我们延伸介绍一下,如果因为失误用了长事务,那我们在工作中如何发现问题,排查问题?
|
SQL 关系型数据库 MySQL
InnoDB 啥时候不执行事务?
InnoDB 啥时候不执行事务?
188 0
InnoDB 啥时候不执行事务?
|
Java 数据库连接 测试技术
BUG记录-多线程对事务的影响有多么大?
BUG记录-多线程对事务的影响有多么大?
166 0
|
SQL 存储 消息中间件
一个线上SQL死锁异常分析:深入了解事务和锁
引发死锁的原因是什么?如何避免?本文详细介绍了和死锁有关的知识点,通过深入分析MySQL事务和锁的机制,结合案例背景,找到了问题的所在,并梳理了解决方案,详解其原理。希望对同学们有所启发。
一个线上SQL死锁异常分析:深入了解事务和锁
|
SQL 缓存 关系型数据库
MySQL:FTWRL一个奇怪的堵塞现象和堵塞总结
本案例由徐晨亮提供,并且一起探讨。 本文中FTWRL = “flush table with read lock” 关于常用操作加MDL LOCK锁类型参考文章:http://blog.itpub.net/7728585/viewspace-2143093/ 欢迎关注我的《深入理解MySQL主从原理 32讲 》,如下: 一、两个不同的现象 首先建立一张有几条数据的表就可以了,我这里是baguait1表了。
1108 0