MySQL数据更新中断后事务锁定

问题：1、数据在更新的时候，出现更新不了的问题

           2、程序问题：Lock wait timeout exceeded; try restarting transaction

问题描述：

1. ### Error updating database. Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
2. ### The error may involve defaultParameterMap
3. ### The error occurred while setting parameters
4. ### SQL: UPDATE dis_order_traffic SET traffic_no = ? , remarks = ? , status = ? , vehicle_no = ? , member_name = ? , update_by = ? , update_date = ? , del_flag = ? , company_id = ? WHERE id = ?
5. ### Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
6. ; SQL []; Lock wait timeout exceeded; try restarting transaction; nested exception is java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
7. org.springframework.dao.CannotAcquireLockException:
8. ### Error updating database. Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
9. ### The error may involve defaultParameterMap
10. ### The error occurred while setting parameters
11. ### SQL: UPDATE dis_order_traffic SET traffic_no = ? , remarks = ? , status = ? , vehicle_no = ? , member_name = ? , update_by = ? , update_date = ? , del_flag = ? , company_id = ? WHERE id = ?
12. ### Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
13. ; SQL []; Lock wait timeout exceeded; try restarting transaction; nested exception is java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
14. at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java: 259)
15. at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java: 73)
16. at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java: 73)
17. at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java: 371)
18. at com.sun.proxy.$Proxy23.update(Unknown Source)
19. at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java: 254)
20. at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java: 54)
21. at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java: 52)
22. at com.sun.proxy.$Proxy50.update(Unknown Source)
23. at com.cl.runtime.service.CrudService.save(CrudService.java: 101)
24. at com.cl.runtime.service.CrudService$$FastClassBySpringCGLIB$$ 7767ab0.invoke(<generated>)
25. at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java: 204)
26. at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java: 718)
27. at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java: 157)
28. at org.springframework.transaction.interceptor.TransactionInterceptor$ 1.proceedWithInvocation(TransactionInterceptor.java: 99)
29. at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java: 281)
30. at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java: 96)
31. at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java: 179)
32. at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java: 654)
33. at com.zyyw.pscq.modules.dispatch.service.OrderTrafficService$$EnhancerBySpringCGLIB$$ 1f1634fb.save(<generated>)
34. at com.cl.runtime.service.CrudService$$FastClassBySpringCGLIB$$ 7767ab0.invoke(<generated>)
35. at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java: 204)
36. at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java: 718)
37. at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java: 157)
38. at org.springframework.transaction.interceptor.TransactionInterceptor$ 1.proceedWithInvocation(TransactionInterceptor.java: 99)
39. at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java: 281)
40. at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java: 96)
41. at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java: 179)
42. at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java: 654)
43. at com.zyyw.pscq.modules.dispatch.service.OrderTrafficService$$EnhancerBySpringCGLIB$$b089fdd1.save(<generated>)
44. at com.zyyw.pscq.modules.dispatch.service.DeliveryTaskService.createTraffic(DeliveryTaskService.java: 101)
45. at com.zyyw.pscq.modules.dispatch.service.DeliveryTaskService$$FastClassBySpringCGLIB$$ 91d7c9af.invoke(<generated>)
46. at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java: 204)
47. at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java: 718)
48. at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java: 157)
49. at org.springframework.transaction.interceptor.TransactionInterceptor$ 1.proceedWithInvocation(TransactionInterceptor.java: 99)
50. at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java: 281)
51. at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java: 96)
52. at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java: 179)
53. at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java: 654)
54. at com.zyyw.pscq.modules.dispatch.service.DeliveryTaskService$$EnhancerBySpringCGLIB$$ca7ed831.createTraffic(<generated>)
55. at com.zyyw.pscq.modules.dispatch.service.DeliveryTaskService$$FastClassBySpringCGLIB$$ 91d7c9af.invoke(<generated>)
56. at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java: 204)
57. at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java: 718)
58. at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java: 157)
59. at org.springframework.transaction.interceptor.TransactionInterceptor$ 1.proceedWithInvocation(TransactionInterceptor.java: 99)
60. at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java: 281)
61. at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java: 96)
62. at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java: 179)
63. at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java: 654)
64. at com.zyyw.pscq.modules.dispatch.service.DeliveryTaskService$$EnhancerBySpringCGLIB$$c6eebd47.createTraffic(<generated>)
65. at com.zyyw.pscq.modules.dispatch.web.DeliveryTaskController.save(DeliveryTaskController.java: 384)
66. at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
67. at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
68. at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
69. at java.lang.reflect.Method.invoke(Unknown Source)
70. at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java: 222)
71. at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java: 137)
72. at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java: 110)
73. at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java: 814)
74. at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java: 737)
75. at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java: 85)
76. at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java: 959)
77. at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java: 893)
78. at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java: 970)
79. at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java: 872)
80. at javax.servlet.http.HttpServlet.service(HttpServlet.java: 648)
81. at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java: 846)
82. at javax.servlet.http.HttpServlet.service(HttpServlet.java: 729)
83. at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java: 292)
84. at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java: 207)
85. at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java: 52)
86. at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java: 240)
87. at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java: 207)
88. at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java: 61)
89. at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java: 108)
90. at org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java: 137)
91. at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java: 125)
92. at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java: 66)
93. at org.apache.shiro.web.servlet.AbstractShiroFilter.executeChain(AbstractShiroFilter.java: 449)
94. at org.apache.shiro.web.servlet.AbstractShiroFilter$ 1.call(AbstractShiroFilter.java: 365)
95. at org.apache.shiro.subject.support.SubjectCallable.doCall(SubjectCallable.java: 90)
96. at org.apache.shiro.subject.support.SubjectCallable.call(SubjectCallable.java: 83)
97. at org.apache.shiro.subject.support.DelegatingSubject.execute(DelegatingSubject.java: 383)
98. at org.apache.shiro.web.servlet.AbstractShiroFilter.doFilterInternal(AbstractShiroFilter.java: 362)
99. at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java: 125)
100. at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java: 346)
101. at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java: 262)
102. at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java: 240)
103. at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java: 207)
104. at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java: 121)
105. at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java: 107)
106. at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java: 240)
107. at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java: 207)
108. at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java: 212)
109. at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java: 106)
110. at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java: 502)
111. at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java: 141)
112. at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java: 79)
113. at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java: 616)
114. at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java: 88)
115. at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java: 528)
116. at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java: 1099)
117. at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java: 672)
118. at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java: 1520)
119. at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java: 1476)
120. at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
121. at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
122. at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java: 61)
123. at java.lang.Thread.run(Unknown Source)
124. Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
125. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java: 1084)
126. at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java: 4232)
127. at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java: 4164)
128. at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java: 2615)
129. at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java: 2776)
130. at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java: 2838)
131. at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java: 2082)
132. at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java: 1307)
133. at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java: 2931)
134. at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java: 440)
135. at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java: 2929)
136. at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java: 118)
137. at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java: 493)
138. at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
139. at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
140. at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
141. at java.lang.reflect.Method.invoke(Unknown Source)
142. at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java: 62)
143. at com.sun.proxy.$Proxy132.execute(Unknown Source)
144. at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java: 44)
145. at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java: 69)
146. at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java: 48)
147. at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java: 105)
148. at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java: 71)
149. at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
150. at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
151. at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
152. at java.lang.reflect.Method.invoke(Unknown Source)
153. at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java: 62)
154. at com.sun.proxy.$Proxy130.update(Unknown Source)
155. at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java: 152)
156. at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
157. at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
158. at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
159. at java.lang.reflect.Method.invoke(Unknown Source)
160. at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java: 358)
161. ... 106 more

解决办法：


去事务表中查看正在锁定的事务线程：


SELECT * FROM information_schema.INNODB_TRX;


然后查看线程信息：


show full processlist;

发现：


+---------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+
| 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 |
+---------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+
| 60F3D22 | RUNNING   | 2017-06-14 16:04:19 | NULL                  | NULL             |          1 |                 321 | NULL      | NULL                |                 0 |                 0 |                1 |                   376 |               0 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                     10000 |
| 60F2F35 | RUNNING   | 2017-06-14 15:49:04 | NULL                  | NULL             |          5 |                 289 | NULL      | NULL                |                 0 |                 0 |                3 |                   376 |               1 |                 2 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                     10000 |
+---------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+
2 rows in set


线程编号：321,289出现了锁定，
KILL掉：kill 321;
kill 289;


解决。


ps:对数据库进行这些操作需要有较高的权限



参考：http://blog.csdn.net/mchdba/article/details/38313881

版权声明：本文为博主原创文章，未经博主允许不得转载。 https://blog.csdn.net/FanJizhi/article/details/73289875