mysql running_[MySQL] 生产环境MySQL数据库事务一直在RUNNING

最新推荐文章于 2023-06-05 17:19:13 发布

weixin_39608559

最新推荐文章于 2023-06-05 17:19:13 发布

阅读量278

点赞数

文章标签： mysql running

本文链接：https://blog.csdn.net/weixin_39608559/article/details/113136474

版权

[MySQL] 生产环境MySQL数据库事务一直在RUNNING

标签：

mysql生产环境

| 发表时间：2014-09-22 07:52 | 作者：mchdba

出处：http://blog.csdn.net

前言：

运营人员反映，有一单子提交卡住了，页面一直没有返回。

1，刚开始怀疑是应用服务器或者db压力过高hang住了，马上去check应用服务器以及db的负载，看起来都OK，蛮低的，应该不是DB性能问题。

2，最后去看下是否是表锁住了，查看到有2个事务一直RUNNING，没有结束。,mysql> select * from INNODB_TRX\G;

*************************** 1. row ***************************

trx_id: 28573155

trx_state: RUNNING

trx_started: 2014-08-12 15:27:57

trx_requested_lock_id: NULL

trx_wait_started: NULL

trx_weight: 0

trx_mysql_thread_id: 1662333

trx_query: NULL

trx_operation_state: NULL

trx_tables_in_use: 0

trx_tables_locked: 0

trx_lock_structs: 0

trx_lock_memory_bytes: 376

trx_rows_locked: 0

trx_rows_modified: 0

trx_concurrency_tickets: 0

trx_isolation_level: READ COMMITTED

trx_unique_checks: 1

trx_foreign_key_checks: 1

trx_last_foreign_key_error: NULL

trx_adaptive_hash_latched: 0

trx_adaptive_hash_timeout: 7188

trx_is_read_only: 0

trx_autocommit_non_locking: 0

*************************** 2. row ***************************

trx_id: 28573065

trx_state: RUNNING

trx_started: 2014-08-12 15:27:51

trx_requested_lock_id: NULL

trx_wait_started: NULL

trx_weight: 11

trx_mysql_thread_id: 1662332

trx_query: NULL

trx_operation_state: NULL

trx_tables_in_use: 0

trx_tables_locked: 0

trx_lock_structs: 6

trx_lock_memory_bytes: 1248

trx_rows_locked: 1

trx_rows_modified: 5

trx_concurrency_tickets: 0

trx_isolation_level: READ COMMITTED

trx_unique_checks: 1

trx_foreign_key_checks: 1

trx_last_foreign_key_error: NULL

trx_adaptive_hash_latched: 0

trx_adaptive_hash_timeout: 9995

trx_is_read_only: 0

trx_autocommit_non_locking: 0

2 rows in set (0.00 sec)

ERROR:

No query specified

mysql>

3，通过trx_mysql_thread_id: 1662332的去查询information_schema.processlist找到执行事务的客户端请求的SQL线程

mysql> select * from information_schema.processlist where id=1662333;

+---------+--------------+-------------------+-------------+---------+------+-------+------+

+---------+--------------+-------------------+-------------+---------+------+-------+------+

+---------+--------------+-------------------+-------------+---------+------+-------+------+

1 row in set (0.00 sec)

4，通过SQL线程，找到应用程序的IP地址以及端口10.2xx.3.xx:23452

进入应用服务器，通过netstat检索端口23452找到正在运行的应用工程的PID

[[email protected] ~]# netstat -nlatp |grep 23452

tcp 0 0 ::ffff:10.xx.3.2x:23452 ::ffff:10.xx4.3.x1:3306 ESTABLISHED 12059/java

[[email protected] ~]# ps -eaf|grep 12059

tomcat 12059 1 1 Aug09 ? 01:19:36 /usr/java/jdk1.6.0_45/jre/bin/java -Djava.util.logging.config.file=/usr/local/app/apache-tomcat-6.0.37_7000/conf/logging.properties -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager -Xms4096m -Xmx4096m -Xss2048K -XX:PermSize=256m -XX:MaxPermSize=512m -XX:NewSize=1536m -XX:MaxNewSize=3072m -Djava.endorsed.dirs=/usr/local/app/apache-tomcat-6.0.37_7000/endorsed -classpath /usr/local/app/apache-tomcat-6.0.37_7000/bin/bootstrap.jar -Dcatalina.base=/usr/local/app/apache-tomcat-6.0.37_7000 -Dcatalina.home=/usr/local/app/apache-tomcat-6.0.37_7000 -Djava.io.tmpdir=/usr/local/app/apache-tomcat-6.0.37_7000/temp org.apache.catalina.startup.Bootstrap start

root 21039 21007 0 16:00 pts/2 00:00:00 grep 12059

[[email protected] ~]# cd /usr/local/app/apache-tomcat-6.0.37_7000/webapps/

[[email protected] webapps]# ll

总用量 68292

drwxrwxr-x. 6 tomcat tomcat 4096 8月 5 21:33 shopAdminWeb

-rw-r--r--. 1 tomcat tomcat 39126457 8月 5 21:33 shopAdminWeb.war

drwxrwxr-x. 4 tomcat tomcat 4096 8月 5 21:33 shop_service

-rw-r--r--. 1 tomcat tomcat 30792513 8月 5 21:33 shop_service.war

[[email protected] webapps]#

5，找到工程之后，再去查看tomcat的应用报错信息，如下：--- Check the select ID, SHOP_NAME, SHOP_CLASSIFICATION_ID, LEGAL_PERSON, CORPORATION, SHOP_TEL, ADDRESS, ZIP_CODE, LEGAL_PERSON_MOBILE, STAT, AUTHENTICATE, CREDIT, LEVEL, SCORE, LICENSE_PATH, BUSSINESS_RANGE, MALL_ID, FLOOR, BIS_COMPANY, STORE_NO, MANAGER_NAME, MANAGER_TEL, BRIEF, LOGO, FAVOUR_NUM, HAS_WAREHOUSE, DESCRIPTION_FIT, SERVICE, SPEED, BACKGROUND, BIS_CONT_ID,BIS_SHOP_ID, CREATED_DATE, UPDATED_DATE,ENGLISH_PREF from SHOP where SHOP_NAME = ? .

--- Check the SQL Statement (preparation failed).

--- Cause: org.springframework.transaction.TransactionTimedOutException: Transaction timed out: deadline was Tue Aug 12 15:31:00 CST 2014

2014-08-12 16:23:57,746 [http-7000-6:ERROR] com.plocc.framework.exception.handler.impl.LogHandlerImpl - 出现系统异常或未处理的异常, 需要维护人员处理！异常信息：数据库异常！

com.plocc.framework.exception.SystemException: 数据库异常！

at com.plocc.shop.service.impl.ShopServiceImpl.selectShopByShopName(ShopServiceImpl.java:200)

at com.plocc.shop.web.admin.ShopManagerController.addShop(ShopManagerController.java:338)

at sun.reflect.GeneratedMethodAccessor639.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219)

at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)

at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:745)

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:686)

at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)

at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925)

at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)

at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:936)

at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:838)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:643)

at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:812)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)

at com.plocc.auth.filter.AuthenticationFilter.doFilter(AuthenticationFilter.java:110)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)

at com.plocc.sso.filter.SingleSingOnFilter.doFilter(SingleSingOnFilter.java:85)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)

at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)

at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)

at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)

at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)

at java.lang.Thread.run(Thread.java:662)

6，现在很明显了，应该是事务未提交，配合开发去check所有的java代码，发现有2处地方，在Exception e里面忘记写rollback了。} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

// 这里缺乏了rollback。

}

总结：开发人员，写数据库事务的时候，记得在异常处理Exception的时候，别忘记了rollback。

作者：mchdba 发表于2014-9-21 23:52:35 原文链接

阅读：136 评论：0 查看评论

weixin_39608559

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql running_[MySQL] 生产环境MySQL数据库事务一直在RUNNING

[MySQL] 生产环境MySQL数据库事务一直在RUNNING标签：mysql生产环境| 发表时间：2014-09-22 07:52 | 作者：mchdba出处：http://blog.csdn.net前言：运营人员反映，有一单子提交卡住了，页面一直没有返回。1，刚开始怀疑是应用服务器或者db压力过高hang住了，马上去check应用服务器以及db的负载，看起来都OK，蛮低的，应该不是DB性能问...
复制链接

扫一扫