Lock wait timeout exceeded; try restarting transac

原文链接:https://my.oschina.net/lhplj/blog/348642

原因是你使用的InnoDB   表类型的时候,
默认参数:innodb_lock_wait_timeout设置锁等待的时间是50s,
因为有的锁等待超过了这个时间,所以报错.

可以把这个时间加长,或者优化存储过程,事务避免过长时间的等待.

my.ini文件:
#innodb_lock_wait_timeout = 50
-》
innodb_lock_wait_timeout = 500

添加的语句要放在[mysqld]下

转载于:https://my.oschina.net/lhplj/blog/348642

展开阅读全文
博主设置当前文章不允许评论。

MySQL死锁:Lock wait timeout exceeded; try restarting transaction

06-06

使用框架:Spring3.0 + Hibernate3.5 + MySQLrnrn使用Spring事务管理器对事务管理,Service服务中使用@Transactional注解声明要使用的事务管理器rnrnrn1、下位机每隔100毫秒向上位机发送心跳,会根据主键对[b]itemgroup[/b]表先[b]Select[/b]查询,再执行[b]update[/b]更新操作(更新[b]last_online_time[/b]时间戳字段),update语句如下:rnrnUPDATE itemgroup SET alarm_num=?, downline_times=?, fault_num=?, last_check_time=?, last_downline_time=?, last_notice_time=?, last_online_time=?, online_duration=? WHERE id=?rnrn2、上位机每隔10秒会对[b]itemgroup[/b]表进行[b]Select[/b]查询,并执行[b]update[/b]操作(更新[b]last_check_time[/b]时间戳字段),update语句如下:rnrnUPDATE itemgroup SET alarm_num=?, downline_times=?, fault_num=?, last_check_time=?, last_downline_time=?, last_notice_time=?, last_online_time=?, online_duration=? WHERE id=?rnrnrn经过四五十分钟,出现rn“org.springframework.jdbc.UncategorizedSQLException: Hibernate flushing: Could not execute JDBC batch update; uncategorized SQLException for SQL [update itemgroup set alarm_num=?, downline_times=?, fault_num=?, last_check_time=?, last_downline_time=?, last_notice_time=?, last_online_time=?, online_duration=? where id=?]; SQL state [41000]; error code [1205];[color=#FF0000] Lock wait timeout exceeded; try restarting transaction; nested exception is java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction[/color][b][/b]”异常rnrn执行MySQL show full processlist命令,发现存在死锁。请问该问题如何解决?谢谢!!!!rnrnrnrn 论坛

java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transa

11-24

org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch updatern at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)rn at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)rn at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)rn at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)rn at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:266)rn at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:168)rn at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)rn at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:50)rn at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1027)rn at org.springframework.orm.hibernate3.HibernateAccessor.flushIfNecessary(HibernateAccessor.java:390)rn at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:420)rn at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)rn at org.springframework.orm.hibernate3.HibernateTemplate.update(HibernateTemplate.java:716)rn at org.springframework.orm.hibernate3.HibernateTemplate.update(HibernateTemplate.java:712)rn at com.piptrade.base.BaseDaoImpl.updateObject(BaseDaoImpl.java:38)rn at com.piptrade.admin.symbol.manager.SymbolManager.updatesymbol(SymbolManager.java:34)rn at com.piptrade.admin.symbol.action.SymbolAction.updateRecord(SymbolAction.java:288)rn at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)rn at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)rn at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)rn at java.lang.reflect.Method.invoke(Method.java:585)rn at com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:440)rn at com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:279)rn at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:242)rn at com.piptrade.admin.interceptor.AuthorityURIInterceptor.intercept(AuthorityURIInterceptor.java:69)rn at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)rn at com.opensymphony.xwork2.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:163)rn at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)rn at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)rn at com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:249)rn at org.apache.struts2.interceptor.validation.AnnotationValidationInterceptor.doIntercept(AnnotationValidationInterceptor.java:68)rn at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)rn at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)rn at com.opensymphony.xwork2.interceptor.ConversionErrorInterceptor.intercept(ConversionErrorInterceptor.java:122)rn at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)rn at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:195)rn at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)rn at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)rn at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:195)rn at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)rn at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)rn at com.opensymphony.xwork2.interceptor.StaticParametersInterceptor.intercept(StaticParametersInterceptor.java:148)rn at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)rn at org.apache.struts2.interceptor.CheckboxInterceptor.intercept(CheckboxInterceptor.java:93)rn at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)rn at org.apache.struts2.interceptor.FileUploadInterceptor.intercept(FileUploadInterceptor.java:235)rn at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)rn at com.opensymphony.xwork2.interceptor.ModelDrivenInterceptor.intercept(ModelDrivenInterceptor.java:89)rn at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)rn at com.opensymphony.xwork2.interceptor.ScopedModelDrivenInterceptor.intercept(ScopedModelDrivenInterceptor.java:128)rn at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)rn at org.apache.struts2.interceptor.ProfilingActivationInterceptor.intercept(ProfilingActivationInterceptor.java:104)rn at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)rn at org.apache.struts2.interceptor.debugging.DebuggingInterceptor.intercept(DebuggingInterceptor.java:267)rn at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)rn at com.opensymphony.xwork2.interceptor.ChainingInterceptor.intercept(ChainingInterceptor.java:126)rn at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)rn at com.opensymphony.xwork2.interceptor.PrepareInterceptor.doIntercept(PrepareInterceptor.java:138)rn at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)rn at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)rn at com.opensymphony.xwork2.interceptor.I18nInterceptor.intercept(I18nInterceptor.java:148)rn at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)rn at com.piptrade.card.base.CharaterFilter.intercept(CharaterFilter.java:56)rn at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)rn at org.apache.struts2.interceptor.ServletConfigInterceptor.intercept(ServletConfigInterceptor.java:164)rn at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)rn at com.opensymphony.xwork2.interceptor.AliasInterceptor.intercept(AliasInterceptor.java:128)rn at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)rn at com.opensymphony.xwork2.interceptor.ExceptionMappingInterceptor.intercept(ExceptionMappingInterceptor.java:176)rn at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)rn at org.apache.struts2.impl.StrutsActionProxy.execute(StrutsActionProxy.java:52)rn at org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:468)rn at org.apache.struts2.dispatcher.FilterDispatcher.doFilter(FilterDispatcher.java:395)rn at com.piptrade.filter.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:210)rn at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)rn at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)rn at com.piptrade.filter.DomainFilter.doFilter(DomainFilter.java:48)rn at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)rn at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)rn at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)rn at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)rn at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)rn at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)rn at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)rn at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)rn at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)rn at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)rn at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)rn at java.lang.Thread.run(Thread.java:595)rnrnCaused by: java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transactionrnrnorg.springframework.dao.CannotAcquireLockException: Hibernate operation: Could not execute JDBC batch update; SQL [update SYMBOLPROPERTIES set SYMBOL_NAME=?, SYMBOL_ALIAS=?, TYPE=?, CONTRACT_SIZE=?, AMOUNT=?, AMOUNT_MINI=?, AMOUNT_UNIT=?, RISK=?, RISK_MINI=?, RISK_UNIT=?, PER_PIT=?, PRICE_UNIT=?, DIGITS=?, LIMIT_STOP_LEVEL=?, SYMBOL_CODE=?, TICK_SIZE=?, AMOUNT_STANDARD=?, RISK_STANDARD=?, START_TIME=?, END_TIME=?, ENABLE=?, SORT_ORDER=?, SPREAD=?, LEVERAGE=? where ID=?]; Lock wait timeout exceeded; try restarting transaction; nested exception is java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transactionrn rn rn 论坛

Lock wait timeout exceeded

07-06

mysql> show processlist;rn+------+------+-----------------+--------------------+---------+------+----------rnrn+-------------------------------------------------------------------------------------------+rn| Id | User | Host | db | Command | Time | State | Info rnrn |rn+------+------+-----------------+--------------------+---------+------+----------rnrn+-------------------------------------------------------------------------------------------+rn| 4889 | root | localhost:54428 | NULL | Sleep | 7419 | | NULL rnrn |rn| 4890 | root | localhost:54429 | test_db | Sleep | 7416 | | NULL rnrn |rn| 4891 | root | localhost:54430 | information_schema | Sleep | 7411 | | NULL rnrn |rn| 4892 | root | localhost:54431 | information_schema | Query | 0 | starting | show processlist rnrn |rn| 4925 | root | localhost:54484 | test_db | Sleep | 14 | | NULL rnrn |rn| 4937 | root | localhost:54506 | test_db | Sleep | 5563 | | NULL rnrn |rn| 4962 | root | localhost:54551 | test_db | Sleep | 2345 | | NULL rnrn |rn| 4963 | root | localhost:54553 | test_db | Query | 35 | updating | update t_order set signState=1,signTime='2015-07-06 00:00:00' where rnrnid =111 |rn| 4977 | root | localhost:54581 | test_db | Sleep | 2680 | | NULL rnrn |rn| 4978 | root | localhost:54583 | test_db | Sleep | 2672 | | NULL rnrn |rn| 4979 | root | localhost:54587 | test_db | Sleep | 2708 | | NULL rnrn |rn| 4980 | root | localhost:54589 | test_db | Sleep | 2717 | | NULL rnrn |rn| 4981 | root | localhost:54590 | test_db | Sleep | 2726 | | NULL rnrn |rn| 4984 | root | localhost:54594 | test_db | Sleep | 1954 | | NULL rnrn |rn| 4991 | root | localhost:54601 | test_db | Sleep | 1766 | | NULL rnrn |rn| 4992 | root | localhost:54602 | test_db | Sleep | 215 | | NULL rnrn |rn| 4998 | root | localhost:54618 | test_db | Sleep | 1371 | | NULL rnrn |rn| 4999 | root | localhost:54619 | test_db | Sleep | 1959 | | NULL rnrn |rn| 5000 | root | localhost:54620 | test_db | Sleep | 14 | | NULL rnrn |rn| 5001 | root | localhost:54621 | test_db | Sleep | 1955 | | NULL rnrn |rn| 5002 | root | localhost:54622 | test_db | Sleep | 1956 | | NULL rnrn |rn| 5010 | root | localhost:54638 | test_db | Sleep | 1294 | | NULL rnrn |rn| 5011 | root | localhost:54639 | test_db | Sleep | 1338 | | NULL rnrn |rn| 5012 | root | localhost:54640 | test_db | Sleep | 1394 | | NULL rnrn |rn| 5028 | root | localhost:54669 | test_db | Sleep | 590 | | NULL rnrn |rn| 5029 | root | localhost:54670 | test_db | Sleep | 520 | | NULL rnrn |rn| 5038 | root | localhost:54685 | test_db | Sleep | 219 | | NULL rnrn |rn| 5039 | root | localhost:54686 | test_db | Sleep | 324 | | NULL rnrn |rn| 5045 | root | localhost:54697 | test_db | Sleep | 16 | | NULL rnrn |rn| 5046 | root | localhost:54698 | test_db | Sleep | 5 | | NULL rnrn |rn+------+------+-----------------+--------------------+---------+------+----------rnrn+-------------------------------------------------------------------------------------------+rn30 rows in setrnrnmysql> select * from innodb_lock_waits G;rn+-------------------+-------------------+-----------------+------------------+rn| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |rn+-------------------+-------------------+-----------------+------------------+rn| 89992 | 89992:357:6:96 | 89993 | 89993:357:6:96 |rn+-------------------+-------------------+-----------------+------------------+rn1 row in setrnrnmysql> select * from innodb_locks G;rn+----------------+-------------+-----------+-----------+---------------------------+------------+------------+-----------+----------+-----------+rn| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |rn+----------------+-------------+-----------+-----------+---------------------------+------------+------------+-----------+----------+-----------+rn| 89992:357:6:96 | 89992 | X | RECORD | `db`.`t_order` | PRIMARY | 357 | 6 | 96 | 111 |rn| 89993:357:6:96 | 89993 | X | RECORD | `db`.`t_order` | PRIMARY | 357 | 6 | 96 | 111 |rn+----------------+-------------+-----------+-----------+---------------------------+------------+------------+-----------+----------+-----------+rn2 rows in setrnrnmysql> rnrn程序经常卡死,重启tomcat后过一段时间又会卡死,在网上查了一些资料,有的说延长innodb_lock_wait_timeout=500,还是解决不了问题。rn上面是几张表的结果,应该怎样分析呢?rn到底是什么原因导致卡死呢? 论坛

lock wait timeout

06-18

最近在做service层的Junit测试,造了些数据写在一个Xml文件里面set进数据库,这样service层跑到了,Dao层的一些方法也调用到了,顺道都测到了,但是现在出现了lock wait timeout问题,做基本的查询,get那些可以,但是做到update,delete,edit等修改数据库数据时,却报lock的错误rnrnrnjava.sql.SQLException: Lock wait timeout exceeded; try restarting transactionrn at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)rn at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)rn at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558)rn at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490)rn at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)rn at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109)rn at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2648)rn at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2077)rn at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1356)rn at sun.reflect.GeneratedMethodAccessor13.invoke(Unknown Source)rn at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)rn at java.lang.reflect.Method.invoke(Unknown Source)rn at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)rn at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)rn at $java.sql.Wrapper$$EnhancerByProxool$$50e075c1.execute()rn at org.dbunit.database.statement.SimplePreparedStatement.addBatch(SimplePreparedStatement.java:80)rn at org.dbunit.database.statement.AutomaticPreparedBatchStatement.addBatch(AutomaticPreparedBatchStatement.java:70)rn at org.dbunit.operation.AbstractBatchOperation.execute(AbstractBatchOperation.java:195)rn at org.dbunit.AbstractDatabaseTester.executeOperation(AbstractDatabaseTester.java:190)rn at org.dbunit.AbstractDatabaseTester.onTearDown(AbstractDatabaseTester.java:109)rn at com.sixconnex.core.test.AbstractDbUnitTestCase.tearDown(AbstractDbUnitTestCase.java:93)rn at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)rn at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)rn at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)rn at java.lang.reflect.Method.invoke(Unknown Source)rn at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)rn at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)rn at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)rn at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:37)rn at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:82)rn at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)rn at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:240)rn at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)rn at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)rn at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)rn at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)rn at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)rn at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)rn at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)rn at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)rn at org.junit.runners.ParentRunner.run(ParentRunner.java:236)rn at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:180)rn at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:46)rn at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)rn at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)rn at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)rn at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)rn at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)rnrnrnXML数据是set进数据库的,可以看到。但后面的操作对数据库的数据无影响。还有用Debug模式可以跑完。rn 本人是新手,求各位大神帮忙,最好通俗点。。 论坛

junit测试lock wait timeout异常

12-28

rnjunit测试create update delete 等业务方法时出现mysql lock wait timeout异常java.sql.SQLException: Lock wait timeout exceeded; try restarting transactionrn at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)rn at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)rn at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558)rn at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490)rn at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)rn at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109)rn at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2648)rn at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2077)rn at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1356)rn at org.dbunit.database.statement.SimplePreparedStatement.addBatch(SimplePreparedStatement.java:67)rn at org.dbunit.database.statement.AutomaticPreparedBatchStatement.addBatch(AutomaticPreparedBatchStatement.java:57)rn at org.dbunit.operation.AbstractBatchOperation.execute(AbstractBatchOperation.java:187)rn at com.ss.base.test.SpringTxTestCase.handleTestDate(SpringTxTestCase.java:97)rn at com.ss.base.test.SpringTxTestCase.removeTestData(SpringTxTestCase.java:56)rn at com.ss.survey.service.test.SurveyOrgSetViSrvTest.finishTest(SurveyOrgSetViSrvTest.java:50)rn at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)rn at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)rn at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)rn at java.lang.reflect.Method.invoke(Unknown Source)rn at org.springframework.test.context.junit4.SpringMethodRoadie.runAfters(SpringMethodRoadie.java:297)rn at org.springframework.test.context.junit4.SpringMethodRoadie$RunBeforesThenTestThenAfters.run(SpringMethodRoadie.java:338)rn at org.springframework.test.context.junit4.SpringMethodRoadie.runWithRepetitions(SpringMethodRoadie.java:217)rn at org.springframework.test.context.junit4.SpringMethodRoadie.runTest(SpringMethodRoadie.java:197)rn at org.springframework.test.context.junit4.SpringMethodRoadie.run(SpringMethodRoadie.java:143)rn at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.invokeTestMethod(SpringJUnit4ClassRunner.java:142)rn at org.junit.internal.runners.JUnit4ClassRunner.runMethods(JUnit4ClassRunner.java:51)rn at org.junit.internal.runners.JUnit4ClassRunner$1.run(JUnit4ClassRunner.java:44)rn at org.junit.internal.runners.ClassRoadie.runUnprotected(ClassRoadie.java:27)rn at org.junit.internal.runners.ClassRoadie.runProtected(ClassRoadie.java:37)rn at org.junit.internal.runners.JUnit4ClassRunner.run(JUnit4ClassRunner.java:42)rn at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:46)rn at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)rn at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)rn at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)rn at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)rn at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)rnrn 论坛

关于MYSQL 死锁的问题 Deadlock found when trying to get lock; try restarting ..........

07-16

定时执行的MYSQL 存储过程报错:rn Deadlock found when trying to get lock; try restarting transactionrnrn这个错误是在MYSQL 的错误日志中看到的, 不知道要从哪里开始入手解决这个问题。 暂时没发现这个错误对业务逻辑有什么影响,求高手指点。 贴出过程代码如下:rnrnrn[code=SQL]rnrnDELIMITER $$rnrnUSE `sms`$$rnrnDROP PROCEDURE IF EXISTS `proc_passout`$$rnrnCREATE DEFINER=`root`@`%` PROCEDURE `proc_passout`()rnBEGINrnDECLARE flag_lt INT(4);rnDECLARE flag_dx INT(4);rnDECLARE flag_yd INT(4);rnDECLARE flag INT(4) DEFAULT 0;rnDECLARE allflag INT(4);rnDECLARE cont_flag INT(4);rnDECLARE cont_deliverflag INT;rnDECLARE i_oid VARCHAR(20) DEFAULT 0;rnDECLARE tmpName VARCHAR(20) DEFAULT '' ; rnDECLARE selectmoid CURSOR FORrnSELECT oid FROM cont WHERE DELIVERFLAG=5 ORDER BY oid ASC LIMIT 100; rnDECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET tmpName = NULL;rn/*=========================*/rnSET cont_deliverflag=9;rnOPEN selectmoid;rnFETCH selectmoid INTO i_oid;rn/*处理黑名单*/rnSET autocommit=0;rnUPDATE mt a,blacklist b SET a.deliverflag=1,a.delivertime=LEFT(NOW()+0,14),a.msgid='blacklist' rnWHERE a.phone=b.phone AND a.moid=i_oid;rnCOMMIT;rn/*=========================*/rnWHILE ( tmpName IS NOT NULL) DO rn/*===========联通========*/rnSELECT COUNT(*) INTO flag_lt FROM mt WHERE moid=i_oidrnAND SUBSTR(phone,1,3) IN(SELECT subphone FROM telcom WHERE TYPE IN(2));rn/*==========电信==========*/rnSELECT COUNT(*) INTO flag_dx FROM mt WHERE moid=i_oid rnAND SUBSTR(phone,1,3) IN(SELECT subphone FROM telcom WHERE TYPE IN(3));rn/*============总数=========*/rnSELECT COUNT(*) INTO allflag FROM mt WHERE moid=i_oid;rnIF flag_lt=allflag THEN #全是联通号rnUPDATE cont SET channelid=1,deliverflag=cont_deliverflag WHERE oid=i_oid;rnSET flag =1;rnELSEIF flag_dx=allflag THEN #全是电信号rnUPDATE cont SET channelid=10,deliverflag=cont_deliverflag WHERE oid=i_oid ; rnSET flag=1;rnELSErn/*处理联通号码*/rnIF flag_lt>0 AND flag=0 THEN rnSET @moid=seq('cont');rnSET autocommit=0;rnUPDATE mt SET moid=@moid WHERE moid=i_oidrnAND SUBSTR(phone,1,3) rnIN(SELECT subphone FROM telcom WHERE TYPE IN(2));rnINSERT INTO cont(putintime,Oid, MsgType, MsgCont, ChannelId, ChName, ChPassword, SpNumber,DeliverFlag,PRIORITY)rnSELECT PUTINTIME,@moid,MSGTYPE,MSGCONT,1,CHNAME,CHPASSWORD,SPNUMBER,cont_deliverflag,PRIORITY rnFROM cont WHERE oid=i_oid AND DELIVERFLAG=5;rnCOMMIT;rnEND IF;rn/*处理CDMA号码*/rnIF flag_dx>0 AND flag=0 THEN rnSET @moid=seq('cont');rnSET autocommit=0;rnUPDATE mt SET moid=@moid WHERE moid=i_oidrnAND SUBSTR(phone,1,3) rnIN(SELECT subphone FROM telcom WHERE TYPE IN(3));rnINSERT INTO cont(putintime,Oid, MsgType, MsgCont, ChannelId, ChName, ChPassword, SpNumber,DeliverFlag,PRIORITY)rnSELECT PUTINTIME,@moid,MSGTYPE,MSGCONT,10,CHNAME,CHPASSWORD,SPNUMBER,cont_deliverflag,PRIORITY rnFROM cont WHERE oid=i_oid AND DELIVERFLAG=5;rnCOMMIT;rnEND IF;rnEND IF;rnSELECT COUNT(*) INTO flag_yd FROM mt WHERE moid=i_oidrnAND SUBSTR(phone,1,3) IN(SELECT subphone FROM telcom WHERE TYPE IN(2,3));rn/*处理移动号码*/rnIF flag_yd=0 THEN rnSET autocommit=0;rnUPDATE cont SET DELIVERFLAG=cont_deliverflag WHERE cont.OID=i_oid AND DELIVERFLAG=5;rnCOMMIT;rnEND IF;rnFETCH selectmoid INTO i_oid;rnEND WHILE; rnCLOSE selectmoid;rn rnEND$$rnrnDELIMITER ;rnrnrnrn[/code] 论坛

ORA-02049: timeout: distributed transaction waiting for lock

09-01

我使用的是ibatis做新增,但新增数据时,反应很慢,很长时间后报这个错误,麻烦帮忙解决下rnrn2012-9-1 15:51:58 org.apache.catalina.core.StandardWrapperValve invokern严重: Servlet.service() for servlet action threw exceptionrnjava.sql.SQLException: ORA-02049: timeout: distributed transaction waiting for lockrnrn at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)rn at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)rn at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)rn at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)rn at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)rn at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:966)rn at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1170)rn at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)rn at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3445)rn at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)rn at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)rn at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)rn at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)rn at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)rn at java.lang.reflect.Method.invoke(Unknown Source)rn at com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.invoke(PreparedStatementLogProxy.java:62)rn at $Proxy2.execute(Unknown Source)rn at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:81)rn at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteUpdate(GeneralStatement.java:200)rn at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:78)rn at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.insert(SqlMapExecutorDelegate.java:447)rn at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.insert(SqlMapSessionImpl.java:82)rn at com.zte.ssb.orm.ibatis.SqlMapClientTemplate$9.doInSqlMapClient(Unknown Source)rn at com.zte.ssb.orm.ibatis.SqlMapClientTemplate.execute(Unknown Source)rn at com.zte.ssb.orm.ibatis.SqlMapClientTemplate.insert(Unknown Source)rn at com.zte.ecc.access.terminalRepair.repairbill.dao.CreateRepairBillDAO.saveRepairBill(CreateRepairBillDAO.java:79)rn at com.zte.ecc.business.terminalRepair.repairbill.service.CreateRepairBillService.saveRepairBill(CreateRepairBillService.java:83)rn at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)rn at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)rn at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)rn at java.lang.reflect.Method.invoke(Unknown Source)rn at com.zte.ssb.aop.support.AopUtils.invokeJoinpointUsingReflection(Unknown Source)rn at com.zte.ssb.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(Unknown Source)rn at com.zte.ssb.aop.framework.ReflectiveMethodInvocation.proceed(Unknown Source)rn at com.zte.ssb.transaction.interceptor.TransactionInterceptor.invoke(Unknown Source)rn at com.zte.ssb.aop.framework.ReflectiveMethodInvocation.proceed(Unknown Source)rn at com.zte.ssb.aop.framework.JdkDynamicAopProxy.invoke(Unknown Source)rn at $Proxy0.saveRepairBill(Unknown Source)rn at com.zte.ecc.ui.web.terminalRepair.repairbill.action.CreateRepairBillAction.saveRepairBill(CreateRepairBillAction.java:224)rn at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)rn at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)rn at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)rn at java.lang.reflect.Method.invoke(Unknown Source)rn at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:280)rn at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:216)rn at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)rn at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)rn at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)rn at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:507)rn at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)rn at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)rn at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)rn at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)rn at com.filter.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:125)rn at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)rn at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)rn at com.zte.dpg.ssostatus.client.SsoStatusFilter.doFilter(SsoStatusFilter.java:104)rn at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)rn at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)rn at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)rn at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)rn at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)rn at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)rn at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)rn at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)rn at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:864)rn at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:579)rn at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1665)rn at java.lang.Thread.run(Unknown Source)rn 论坛

没有更多推荐了,返回首页