`
丁林.tb
  • 浏览: 788332 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
社区版块
存档分类
最新评论

autocommit=0引起的业务hang住

阅读更多

背景

有用户报告一个普通的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可以提交事务并改变值。

 

3
0
分享到:
评论
2 楼 lc15808798846 2016-01-26  
奇哥好试了下,好像这个问题在5.7 已经不存在了,SQL不会hang住
1 楼 lidayu_up 2014-06-24  
写得非常不错,周大哥,另外想请教一个问题,在一个事务commit的时候,mysql会将commit这个动作记录到redo吗?这个问题困扰我很久,有资料说redo是没有事务性的!这话正确吗

相关推荐

    PB设置OLE DB 连接SQL SERVER

    SQLca.AutoCommit = false SQLca.LogPass = logpass SQLca.LogId =logid SQLca.DBParm ="PROVIDER='SQLOLEDB', DATASOURCE='"+serverName+"'," & +"PROVIDERSTRING='Initial Catalog='"+database+"''" WINDOWS...

    mysql 事务处理及表锁定深入简析

    我们可以通过 set autocommit=0 禁止自动提交 set autocommit=1 开启自动提交 来实现事务的处理。 当你用 set autocommit=0 的时候,你以后所有的SQL都将做为事务处理,直到你用commit确认或rollback结束。 注意当你...

    浅析Mysql 数据回滚错误的解决方法

    我们可以通过set autocommit=0 禁止自动提交set autocommit=1 开启自动提交来实现事务的处理。 当你用 set autocommit=0 的时候,你以后所有的SQL都将做为事务处理,直到你用commit确认或rollback结束。注意当你结束...

    Mysql的隔离级别与锁

    1.MySQL参数autocommit生产环境设1还是0?为什么?  在生产环境中,为了事务一致性,需要把autocommit 设置为 0  这样可以进行事务全部成功后,在后一次性提交,如果某一步出错,可以rollback。  在会话进修改...

    Mysql事项,视图,函数,触发器命令(详解)

    set autocommit=0; //扣100 update bank set money=money-100 where bid=1; //回滚,begin开始的所有sql语句操作 rollback; //开启事务 begin; //关闭自动提交 set autocommit=0; //扣100 update bank set money=...

    浅析MYSQL REPEATABLE-READ隔离级别

    REPEATABLE-READ 即可重复读,set autocommit= 0或者START TRANSACTION状态下select表的内容不会改变。这种隔离级别可能导致读到的东西是已经修改过的。 比如: 回话一中读取一个字段一行a=1 在回话二里这个字段该行...

    php+mysql事务rollbackcommit示例

    //开始一个事务 mysql_query(“SET AUTOCOMMIT=0”); //设置事务不自动commit $insert=”INSERT INTO userinfo VALUES (‘aa12′,’aa’,’1′,’aaa’)”; mysql_query($insert); mysql_query(“COMMIT”);//非...

    MySQL 5.6 & 5.7最优配置文件模板(my.ini)

    整理了一份最新基于MySQL 5.6和5.7的配置文件模板,基本上可以说覆盖90%的调优选项,用户只需根据自己的服务器配置稍作修改即可,如InnoDB缓冲池的大小、IO能力(innodb_buffer_pool_size,...autocommit = 0 …………

    基于mysql事务、视图、存储过程、触发器的应用分析

    (1) 用begin,rollback,commit来实现 代码如下:begin 开始一个事务rollback 事务回滚commit 事务确认(2)直接用set来改变mysql的自动提交模式 代码如下:set autocommit=0 禁止自动提交set autocommit=1 开启...

    MySql数据库事务相关

    MqlSQL的事务是由存储引擎所决定的,比如MYISAM,MEMORY,ARCHIVE都不支持事务,而事务的存在则保证了一组查询要么全部...SET AUTOCOMMIT = OFF 或 SET AUTOCOMMIT = 0) 原子性(Atomic) 整个事务包含的所有操作要么全

    数据库大作业,利用python、sql server、tkinter实现了简单的校园二手书管理系统

    # ms = ConnDB(server=server, port=port, user=user, password=password, database=database, autocommit=True, as_dict=True) # # # 查 # sql = "select * from myUser" # # 增 # sql2 = "insert into myUser ...

    图书管理系统PB9.0实现

    新学PB9.0,这是我制作的第一个数据库应用程序,采用Sybase Adapter SQL Anywhere通过ODBC数据库借口访问数据库bookdatabase, // Profile Bookdatabase SQLCA.DBMS = "ODBC" SQLCA.AutoCommit = False ...

    TrubleYou#ZXBlog#3_事务隔离-为什么你改了我还看不见1

    1、显式启动事务语句, begin 或 start transaction 2、set autocommit=0,这个命令会将这个线程的自动提交关掉 1、务的特

    mysql事务select for update及数据的一致性处理讲解

    如果我们把autocommit关闭掉[autocommit = 0],通过程序来控制,只要一次commit就可以了,这样也才能更好的体现事务的特点! 对于需要操作数值,比如金额,个数等等! 记住一个原则:一锁二判三更新 在MySQL的InnoDB...

    php+mysql事务rollback&amp;commit示例

    执行BEGIN之后,其作用同set autocommit=0,而且之后设置set autocommit=0或1时无效。所以,为使操作清楚,一般不使用BEGIN。

    pb9与SQL2000源程序

    pb9+sql2000 //连接数据库 SQLCA.DBMS=...SQLCA.AutoCommit = false connect using sqlca; if sqlca.sqlcode&lt;0 then messagebox("Database error","数据库已断开",stopsign!) halt end if open(hydl_win)

    PB.rar_open_pb 分辨率_pb控件_site:www.pudn.com

    根据分辨率调整控件大小 SQLCA.DBMS = "ODBC" - SQLCA.AutoCommit = False - SQLCA.DBParm = "ConnectString= DSN=pb_shiyan " - connect - open(w_main) -

    mysql进阶知识点之事务

    MySQL 事务主要用于处理操作量大,复杂度高的数据。...2、set autocommit=0;关闭自动事务,每次sql语句后可以跟随rollback 回滚,不执行前一次sql 3、执行sql之前,写一个begain;sql语句1;sql语句2; 手动开启事务,可以

    mysql DB引擎myisam与innodB

    InnoDB支持事务安全、Mysq唯一支持外键的存储... 但同时InnoDB提供了两种自动提交和手动提交两种选择(SET AUTOCOMMIT=0设置),根据文档所述,自动提交的性能很差,所有的修改操作和非自动提交都有200多倍的性能差距!

Global site tag (gtag.js) - Google Analytics