MySQL事务RUNNING状态引发的Transactiontimedout:deadline问题_MySQL
事务
状态
问题
前言：
朋友说简单的查询导致Transaction timed out: deadline问题，怀疑是数据库表锁了，
1，应用故障描述Deadline问题：
--- The error occurred in META-INF/ibatis/ITEM_sqlmap.xml.
--- The error occurred while executing query.
--- Check the SELECT I.ID, I.SHOP_ID, I.ITEM_GROUP_ID, I.ITEM_GROUP_NAME, I.ITEM_NAME, I.LIST_PRICE, I.PL_PRICE, I.PROTECTION_PRICE, I.MALL, I.STAT, I.LOGISTICS, I.TYPE, I.ITEM_CATEGORY_ID, I.BRAND_ID, I.BRAND, I.FAVOUR_NUM, I.IS_SUBSCRIBE, I.VOLUME, I.WEIGHT, I.INVENTORY, I.RELEASE_DATE, I.OFF_REASON, I.IS_DEL, I.CREATED_DATE, I.UPDATED_DATE, I.SIMPLE_DESCRIPTION, I.VIRTUAL_BEGIN_DATE, I.VIRTUAL_END_DATE, I.SEQ_NUM, IPC.PICTURE_PATH FROM ITEM AS I INNER JOIN ITEM_PICTURE AS IPC ON I.ITEM_GROUP_ID = IPC.ITEM_GROUP_ID where I.ID = ? AND IPC.TYPE='1' AND IPC.IS_DEL='0' .
--- Check the SQL Statement (preparation failed).
--- Cause: org.springframework.transaction.TransactionTimedOutException: Transaction timed out: deadline was Tue Nov 25 13:33:07 CST 2014
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:204)
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForObject(MappedStatement.java:120)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:518)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:493)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:106)
at org.springframework.orm.ibatis.SqlMapClientTemplate$1.doInSqlMapClient(SqlMapClientTemplate.java:273)
at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:203)
... 41 more
Caused by: org.springframework.transaction.TransactionTimedOutException: Transaction timed out: deadline was Tue Nov 25 13:33:07 CST 2014
at org.springframework.transaction.support.ResourceHolderSupport.checkTransactionTimeout(ResourceHolderSupport.java:141)
at org.springframework.transaction.support.ResourceHolderSupport.getTimeToLiveInMillis(ResourceHolderSupport.java:130)
at org.springframework.transaction.support.ResourceHolderSupport.getTimeToLiveInSeconds(ResourceHolderSupport.java:114)
at org.springframework.jdbc.datasource.DataSourceUtils.applyTimeout(DataSourceUtils.java:275)
at org.springframework.jdbc.datasource.DataSourceUtils.applyTransactionTimeout(DataSourceUtils.java:257)
at org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy$TransactionAwareInvocationHandler.invoke(TransactionAwareDataSourceProxy.java:244)
at com.sun.proxy.$Proxy324.prepareStatement(Unknown Source)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.prepareStatement(SqlExecutor.java:497)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:175)
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteQuery(MappedStatement.java:221)
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:189)
... 47 more
2，检查Innodb存储引擎状态以及表锁状态
SHOW ENINGE INNODB STATUS；没有死锁信息以及其它异常信息；去查询系统表INNODB_LOCKS、INNODB_LOCK_WAITS表都为NULL，只有INNODB_TRX表有记录，并且处于长时间RUNNING状态，判断是因为事务没有提交或者回滚的缘故。
mysql> SELECT * FROM `INNODB_TRX`;
+----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |
+----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| 19183390 | RUNNING | 2014-11-25 15:39:30 | NULL | NULL | 9 | 940341 | NULL | NULL | 0 | 0 | 5 | 1248 | 3 | 4 | 0 | READ COMMITTED | 1 | 1 | NULL | 0 | 9762 | 0 | 0 |
| 19183153 | RUNNING | 2014-11-25 15:36:41 | NULL | NULL | 0 | 940206 | NULL | NULL | 0 | 0 | 0 | 376 | 0 | 0 | 0 | READ COMMITTED | 1 | 1 | NULL | 0 | 9411 | 0 | 0 |
| 19183139 | RUNNING | 2014-11-25 15:36:28 | NULL | NULL | 0 | 940238 | NULL | NULL | 0 | 0 | 0 | 376 | 0 | 0 | 0 | READ COMMITTED | 1 | 1 | NULL | 0 | 9937 | 0 | 0 |
+----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM `INNODB_LOCKS`;
Empty set (0.00 sec)
mysql> SELECT * FROM `INNODB_LOCK_WAITS`;
Empty set (0.00 sec)
mysql>
仔细check从中可以看出，没有表锁，没有行锁，没有锁等待，只有事务RUNNING没有提交或者回滚。临时解决办法，kill掉这些事务所在的线程。
3，问题重现，查看事务表记录
mysql> SELECT * FROM `INNODB_TRX`;
+----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |
+----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| 19196180 | RUNNING | 2014-11-25 17:41:10 | NULL | NULL | 12 | 942663 | NULL | NULL | 0 | 0 | 4 | 1248 | 2 | 8 | 0 | READ COMMITTED | 1 | 1 | NULL | 0 | 9810 | 0 | 0 |
+----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
1 rows in set (0.00 sec)
mysql>
4，去Slow log和binlog里面分析
去slow log里面看942663线程ID的slow记录，没有找到， 去看binlog里面942663线程ID的DML记录，有如下2条记录：
#141125 17:41:10 server id 230 end_log_pos 118147 CRC32 0x6f2402a1 Query thread_id=942663 exec_time=0 error_code=0
SET TIMESTAMP=1416908470/*!*/;
BEGIN
/*!*/;
# at 118147
#141125 17:41:10 server id 230 end_log_pos 118231 CRC32 0x0219bed2 Table_map: `business_db`.`SHOP_CASH_COUPON_USER_REF` mapped to number 178
# at 118231
#141125 17:41:10 server id 230 end_log_pos 118298 CRC32 0xc6665994 Write_rows: table id 178 flags: STMT_END_F
### INSERT INTO `business_db`.`SHOP_CASH_COUPON_USER_REF`
### SET
### @1=4859
### @2=284
### @3=2425
### @4='0'
### @5='2014-11-25 17:41:10'
### @6=NULL
# at 118298
#141125 17:41:10 server id 230 end_log_pos 118411 CRC32 0x93f6d105 Table_map: `business_db`.`CASH_TICKET` mapped to number 727
# at 118411
#141125 17:41:10 server id 230 end_log_pos 118703 CRC32 0xe4b314ad Update_rows: table id 727 flags: STMT_END_F
### UPDATE `business_db`.`CASH_TICKET`
### WHERE
### @1=19956
### @2=1416886592
### @3=NULL
### @4=NULL
### @5=1
### @6='2014-12-31 00:00:00'
### @7='2014-11-25 00:00:00'
### @8=NULL
### @9=NULL
### @10=NULL
### @11=NULL
### @12=NULL
### @13=NULL
### @14=NULL
### @15=NULL
### @16='5c2483b3033b30c6b948d6a971c87f1d'
### @17='CASH-TICKET-1'
### @18='0'
### @19=000000050.000000000
### @20=NULL
### @21=284
### @22='0'
### SET
### @1=19956
### @2=1416908470
### @3='PL.1qaz2wsx'
### @4=NULL
### @5=1
### @6='2014-12-31 00:00:00'
### @7='2014-11-25 00:00:00'
### @8=NULL
### @9=2425
### @10='PL.1qaz2wsx'
### @11='1qaz2wsx@163.com'
### @12='4859'
### @13=NULL
### @14=NULL
### @15=NULL
### @16='5c2483b3033b30c6b948d6a971c87f1d'
### @17='CASH-TICKET-1'
### @18='0'
### @19=000000050.000000000
### @20=1416908470
### @21=284
### @22='0'
# at 118703
#141125 17:41:10 server id 230 end_log_pos 118734 CRC32 0x6949012e Xid = 16199116
COMMIT/*!*/;
看到这个执行成功的DML操作和一直RUNNING的事务时间上比较吻合，所以通过这binlog的INSERT语句和UPDATE语句，找到应用的一个业务模块的方法，发现其异常处理模块没有及时COMMIT和ROLLBACK的。
添加上ROLLBACK处理后，再测试N次，没有出现过报错信息，而执行SELECT * FROM `INNODB_TRX`;也没有记录，这表示事务都及时COMMIT或者ROLLBACK了。
Cause: java.sql.SQLException: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred while applying a parameter map.
--- Check the ITEM.updateByInventory-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
处理方法：将BINLOG_FORMAT设置成MIXED即可，SET GLOBAL BINLOG_FORMAT = MIXED;
6，总结
这个问题看似解决了，但是可能还有更多的细节没有梳理，为什么事务开启后没有ROLLBACK或者COMMIT，后续执行SELECT就会报错呢？自己想来是DML形成排它锁X，而查询是有共享锁S，X和S是互斥的（关于Innodb锁请参考：http://blog.itpub.net/26230597/viewspace-1315111/），所以就出问题了，至于源码层的底层分析，有待以后继续深究，如果有遇到此类问题的朋友，欢迎share下你的处理思路以及分析过程，谢谢。
----------------------------------------------------------------------------------------------------------------
原博客地址： http://blog.itpub.net/26230597/viewspace-1346680/
原作者：黄杉 (mchdba)
----------------------------------------------------------------------------------------------------------------
本站声明
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn
热AI工具
Undresser.AI Undress
人工智能驱动的应用程序，用于创建逼真的裸体照片
AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。
Undress AI Tool
免费脱衣服图片
Clothoff.io
AI脱衣机
Video Face Swap
使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸！
热门文章
如何修复KB5055612无法在Windows 10中安装？
4 周前
By DDD
<🎜>：泡泡胶模拟器无穷大 - 如何获取和使用皇家钥匙
4 周前
By 尊渡假赌尊渡假赌尊渡假赌
<🎜>：种植花园 - 完整的突变指南
3 周前
By DDD
北端：融合系统，解释
4 周前
By 尊渡假赌尊渡假赌尊渡假赌
Mandragora：巫婆树的耳语 - 如何解锁抓钩
3 周前
By 尊渡假赌尊渡假赌尊渡假赌
热工具
记事本++7.3.1
好用且免费的代码编辑器
SublimeText3汉化版
中文版，非常好用
禅工作室 13.0.1
功能强大的PHP集成开发环境
Dreamweaver CS6
视觉化网页开发工具
SublimeText3 Mac版
神级代码编辑软件(SublimeText3)
处于待机状态的连接状态：已断开，原因：NIC合规性
Feb 19, 2024 pm 03:15 PM
“事件日志消息中的连接状态显示为待机状态：已断开连接，原因是符合NIC标准。这意味着系统在待机模式下，网络接口卡(NIC)已断开连接。虽然这通常是网络问题，但也可能由软件和硬件冲突引起。在接下来的讨论中，我们将探讨如何解决这一问题。”待机连接断开的原因是什么？NIC合规性？如果在Windows事件查看器中发现“ConnectivityStatusinStandby:DisConnected,Reason:NICCompliance”消息，这表示您的NIC或网络接口控制器可能存在问题。这种情况通常
陌陌状态怎么设置
Mar 01, 2024 pm 12:10 PM
陌陌这款广为人知的社交平台，为用户的日常社交提供了丰富的功能服务。在陌陌上，用户可以轻松分享生活状态、结交朋友、进行聊天等。其中设置状态功能让用户能够向其他展示自己当前的心情和状态，进而吸引更多人的关注和交流。那么究竟该如何设置自己的陌陌状态呢，下文中就为大家带来详细的内容介绍！陌陌怎么设置状态？1、打开陌陌，点击右下角更多，找到并点击每日状态。2、选择状态。3、即可显示设置的状态。
如何查看服务器状态
Oct 09, 2023 am 10:10 AM
查看服务器状态的方法有使用命令行工具、图形界面工具、监控工具、日志文件和远程管理工具等。详细介绍：1、使用命令行工具，在Linux或Unix服务器上，可以使用命令行工具来查看服务器的状态；2、使用图形界面工具，对于具有图形界面的服务器操作系统，可以使用系统提供的图形界面工具来查看服务器状态；3、使用监控工具，可以使用专门的监控工具来实时监视服务器的状态等等。
聚类算法中的聚类效果评估问题
Oct 10, 2023 pm 01:12 PM
聚类算法中的聚类效果评估问题，需要具体代码示例聚类是一种无监督学习方法，通过对数据进行聚类，将相似的样本归为一类。在聚类算法中，如何评估聚类的效果是一个重要的问题。本文将介绍几种常用的聚类效果评估指标，并给出相应的代码示例。一、聚类效果评估指标轮廓系数（SilhouetteCoefficient）轮廓系数是通过计算样本的紧密度和与其他簇的分离度来评估聚类效
PHP PDO教程：从基础到精通的进阶指南
Feb 19, 2024 pm 06:30 PM
1.PDO简介PDO是PHP的一个扩展库，它提供了一个面向对象的方式来操作数据库。PDO支持多种数据库，包括Mysql、postgresql、oracle、SQLServer等。PDO使开发人员能够使用统一的api来操作不同的数据库，这使得开发人员可以在不同的数据库之间轻松切换。2.PDO连接数据库要使用PDO连接数据库，首先需要创建一个PDO对象。PDO对象的构造函数接收三个参数：数据库类型、主机名、数据库用户名和密码。例如，以下代码创建了一个连接到mysql数据库的对象：$dsn="mysq
Java线程的五种状态详解及状态转换规则
Feb 19, 2024 pm 05:03 PM
深入了解Java线程的五种状态及其转换规则一、线程的五种状态介绍在Java中，线程的生命周期可以分为五个不同的状态，包括新建状态（NEW）、就绪状态（RUNNABLE）、运行状态（RUNNING）、阻塞状态（BLOCKED）和终止状态（TERMINATED）。新建状态（NEW）：当线程对象创建后，它就处于新建状态。此时，线程对象已经分配了足够的资源来执行任务
教你如何诊断常见问题的iPhone故障
Dec 03, 2023 am 08:15 AM
iPhone以其强大的性能和多方面的功能而闻名，它不能幸免于偶尔的打嗝或技术困难，这是复杂电子设备的共同特征。遇到iPhone问题可能会让人感到沮丧，但通常不需要警报。在这份综合指南中，我们旨在揭开与iPhone使用相关的一些最常遇到的挑战的神秘面纱。我们的分步方法旨在帮助您解决这些常见问题，提供实用的解决方案和故障排除技巧，让您的设备恢复到最佳工作状态。无论您是面对一个小故障还是更复杂的问题，本文都可以帮助您有效地解决这些问题。一般故障排除提示在深入研究具体的故障排除步骤之前，以下是一些有助于
MySQL事务处理：自动提交与手动提交的区别
Mar 16, 2024 am 11:33 AM
MySQL事务处理：自动提交与手动提交的区别在MySQL数据库中，事务是一组SQL语句的集合，要么全部执行成功，要么全部执行失败，保证了数据的一致性和完整性。在MySQL中，事务可以分为自动提交和手动提交，其区别在于事务提交的时机以及对事务的控制范围。下面将详细介绍自动提交和手动提交的区别，并给出具体的代码示例来说明。一、自动提交在MySQL中，如果没有显示
