第一个问题:
如果数据库是mysql,进行批量增加和修改的时候,在jdbc连接数据库的配置文件上一定要加上&allowMultiQueries=true,因为mysql数据库是默认allowMultiQueries=false,
jdbc_url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true
批量修改语句:
<span style="font-size:18px;"><update id="batchUpdate" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
update test
<set>
studentNum = ${item.studentNum}
</set>
where teacherId= '${item.teacherId}'
</foreach>
</update> </span>
第二个问题是:
我对一张表进行先进行批量修改和增加的时候,报错如下:
[ INFO] HHH000010: On release of batch it still contained JDBC statements
[ WARN] SQL Error: 1205, SQLState: 41000
[ERROR] Lock wait timeout exceeded; try restarting transaction
[ERROR] HHH000315: Exception executing batch [could not execute batch]
2016-10-21 17:23:37.479 [http-bio-8080-exec-5] SqlSessionUtils - Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6a4f5d83]
2016-10-21 17:23:37.479 [http-bio-8080-exec-5] SqlSessionUtils - Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6a4f5d83]
2016-10-21 17:23:37.507 [http-bio-8080-exec-5] DataSourceTransactionManager - Initiating transaction rollback after commit exception
org.springframework.dao.CannotAcquireLockException: could not execute batch; SQL [insert into tb_tutorapprove (comment, isDelete, operator, versionEndTime, versionStartTime, create_time, update_time, applyStudentNum, state, studentNum, teacherId, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; nested exception is org.hibernate.exception.LockTimeoutException: could not execute batch
at org.springframework.orm.hibernate4.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:154) ~[spring-orm-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.orm.hibernate4.SpringSessionSynchronization.beforeCommit(SpringSessionSynchronization.java:107) ~[spring-orm-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.transaction.support.TransactionSynchronizationUtils.triggerBeforeCommit(TransactionSynchronizationUtils.java:95) ~[spring-tx-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.transaction.support.AbstractPlatformTransactionManager.triggerBeforeCommit(AbstractPlatformTransactionManager.java:932) [spring-tx-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:744) [spring-tx-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:730) [spring-tx-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:485) [spring-tx-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:291) [spring-tx-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) [spring-tx-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655) [spring-aop-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at com.dmsd.itoo.tutor.service.impl.TeacherSetServiceImpl$$EnhancerBySpringCGLIB$$269ce583.saveOrUpdate(<generated>) [dmsd-itoo-tutor-core-1.0.0-SNAPSHOT.jar:1.0.0-SNAPSHOT]
at com.dmsd.itoo.tutor.controller.TeacherSetController.addStuNum(TeacherSetController.java:110) [classes/:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_25]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_25]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_25]
at java.lang.reflect.Method.invoke(Method.java:483) ~[na:1.8.0_25]
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:222) [spring-web-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137) [spring-web-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110) [spring-webmvc-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:814) [spring-webmvc-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:737) [spring-webmvc-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) [spring-webmvc-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959) [spring-webmvc-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893) [spring-webmvc-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:969) [spring-webmvc-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:871) [spring-webmvc-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:650) [servlet-api.jar:na]
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:845) [spring-webmvc-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:731) [servlet-api.jar:na]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303) [catalina.jar:7.0.67]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.67]
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) [tomcat7-websocket.jar:7.0.67]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) [catalina.jar:7.0.67]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.67]
at net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:200) [javamelody-core-1.58.0.jar:1.58.0]
at net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:178) [javamelody-core-1.58.0.jar:1.58.0]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) [catalina.jar:7.0.67]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.67]
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:121) [spring-web-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) [catalina.jar:7.0.67]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.67]
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220) [catalina.jar:7.0.67]
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122) [catalina.jar:7.0.67]
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505) [catalina.jar:7.0.67]
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169) [catalina.jar:7.0.67]
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103) [catalina.jar:7.0.67]
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:956) [catalina.jar:7.0.67]
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116) [catalina.jar:7.0.67]
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:423) [catalina.jar:7.0.67]
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1079) [tomcat-coyote.jar:7.0.67]
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:625) [tomcat-coyote.jar:7.0.67]
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:318) [tomcat-coyote.jar:7.0.67]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_25]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_25]
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-coyote.jar:7.0.67]
at java.lang.Thread.run(Thread.java:745) [na:1.8.0_25]
Caused by: org.hibernate.exception.LockTimeoutException: could not execute batch
at org.hibernate.dialect.MySQLDialect$1.convert(MySQLDialect.java:447) ~[hibernate-core-4.3.10.Final.jar:4.3.10.Final]
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) ~[hibernate-core-4.3.10.Final.jar:4.3.10.Final]
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126) ~[hibernate-core-4.3.10.Final.jar:4.3.10.Final]
at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.performExecution(BatchingBatch.java:136) ~[hibernate-core-4.3.10.Final.jar:4.3.10.Final]
at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.doExecuteBatch(BatchingBatch.java:114) ~[hibernate-core-4.3.10.Final.jar:4.3.10.Final]
at org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl.execute(AbstractBatchImpl.java:163) ~[hibernate-core-4.3.10.Final.jar:4.3.10.Final]
at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.executeBatch(JdbcCoordinatorImpl.java:226) ~[hibernate-core-4.3.10.Final.jar:4.3.10.Final]
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:484) ~[hibernate-core-4.3.10.Final.jar:4.3.10.Final]
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:351) ~[hibernate-core-4.3.10.Final.jar:4.3.10.Final]
at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:350) ~[hibernate-core-4.3.10.Final.jar:4.3.10.Final]
at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:56) ~[hibernate-core-4.3.10.Final.jar:4.3.10.Final]
at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1258) ~[hibernate-core-4.3.10.Final.jar:4.3.10.Final]
at org.springframework.orm.hibernate4.SpringSessionSynchronization.beforeCommit(SpringSessionSynchronization.java:104) ~[spring-orm-4.2.4.RELEASE.jar:4.2.4.RELEASE]
... 56 common frames omitted
Caused by: java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2056) ~[mysql-connector-java-5.1.26.jar:na]
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1467) ~[mysql-connector-java-5.1.26.jar:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_25]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_25]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_25]
at java.lang.reflect.Method.invoke(Method.java:483) ~[na:1.8.0_25]
at com.p6spy.engine.logging.P6LogPreparedStatementExecuteDelegate.invoke(P6LogPreparedStatementExecuteDelegate.java:42) ~[p6spy-2.1.4.jar:na]
at com.p6spy.engine.proxy.GenericInvocationHandler.invoke(GenericInvocationHandler.java:116) ~[p6spy-2.1.4.jar:na]
at org.p6spy.com.mysql.jdbc.Statement$$EnhancerByCGLIB$$b2d3c097.executeBatch(<generated>) ~[p6spy-2.1.4.jar:na]
at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.performExecution(BatchingBatch.java:127) ~[hibernate-core-4.3.10.Final.jar:4.3.10.Final]
... 65 common frames omitted
Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078) ~[mysql-connector-java-5.1.26.jar:na]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4190) ~[mysql-connector-java-5.1.26.jar:na]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4122) ~[mysql-connector-java-5.1.26.jar:na]
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570) ~[mysql-connector-java-5.1.26.jar:na]
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731) ~[mysql-connector-java-5.1.26.jar:na]
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2818) ~[mysql-connector-java-5.1.26.jar:na]
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2157) ~[mysql-connector-java-5.1.26.jar:na]
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2460) ~[mysql-connector-java-5.1.26.jar:na]
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2008) ~[mysql-connector-java-5.1.26.jar:na]
... 74 common frames omitted
2016-10-21 17:23:37.509 [http-bio-8080-exec-5] DataSourceTransactionManager - Rolling back JDBC transaction on Connection [com.mysql.jdbc.JDBC4Connection@18f9ce9d]
2016-10-21 17:23:37.517 [http-bio-8080-exec-5] DataSourceTransactionManager - Releasing JDBC Connection [com.mysql.jdbc.JDBC4Connection@18f9ce9d] after transaction
2016-10-21 17:23:37.518 [http-bio-8080-exec-5] DataSourceUtils - Returning JDBC Connection to DataSource
org.springframework.dao.CannotAcquireLockException: could not execute batch; SQL [insert into tb_tutorapprove (comment, isDelete, operator, versionEndTime, versionStartTime, create_time, update_time, applyStudentNum, state, studentNum, teacherId, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; nested exception is org.hibernate.exception.LockTimeoutException: could not execute batch
at org.springframework.orm.hibernate4.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:154)
at org.springframework.orm.hibernate4.SpringSessionSynchronization.beforeCommit(SpringSessionSynchronization.java:107)
at org.springframework.transaction.support.TransactionSynchronizationUtils.triggerBeforeCommit(TransactionSynchronizationUtils.java:95)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.triggerBeforeCommit(AbstractPlatformTransactionManager.java:932)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:744)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:730)
at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:485)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:291)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655)
at com.dmsd.itoo.tutor.service.impl.TeacherSetServiceImpl$$EnhancerBySpringCGLIB$$269ce583.saveOrUpdate(<generated>)
at com.dmsd.itoo.tutor.controller.TeacherSetController.addStuNum(TeacherSetController.java:110)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:222)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:814)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:737)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:969)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:871)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:650)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:845)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:200)
at net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:178)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:121)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:956)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:423)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1079)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:625)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:318)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.hibernate.exception.LockTimeoutException: could not execute batch
at org.hibernate.dialect.MySQLDialect$1.convert(MySQLDialect.java:447)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.performExecution(BatchingBatch.java:136)
at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.doExecuteBatch(BatchingBatch.java:114)
at org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl.execute(AbstractBatchImpl.java:163)
at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.executeBatch(JdbcCoordinatorImpl.java:226)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:484)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:351)
at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:350)
at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:56)
at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1258)
at org.springframework.orm.hibernate4.SpringSessionSynchronization.beforeCommit(SpringSessionSynchronization.java:104)
... 56 more
Caused by: java.sql.BatchUpdateException:<span style="color:#ff6666;"> Lock wait timeout exceeded; try restarting transaction</span>
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2056)
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1467)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at com.p6spy.engine.logging.P6LogPreparedStatementExecuteDelegate.invoke(P6LogPreparedStatementExecuteDelegate.java:42)
at com.p6spy.engine.proxy.GenericInvocationHandler.invoke(GenericInvocationHandler.java:116)
at org.p6spy.com.mysql.jdbc.Statement$$EnhancerByCGLIB$$b2d3c097.executeBatch(<generated>)
at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.performExecution(BatchingBatch.java:127)
... 65 more
Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4190)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4122)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2818)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2157)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2460)
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2008)
... 74 more
2016-10-21 17:23:37.538 [http-bio-8080-exec-5] DispatcherServlet - Null ModelAndView returned to DispatcherServlet with name 'action': assuming HandlerAdapter completed request handling
2016-10-21 17:23:37.538 [http-bio-8080-exec-5] DispatcherServlet - Successfully completed request
重点错误:
<span style="font-size:18px;color:#ff0000;">Caused by: java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction</span>
是以下这个sql语句未执行完,然后又去执行insert语句,导致的锁的问题。
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
update test
<set>
studentNum = ${item.studentNum}
</set>
where teacherId= '${item.teacherId}'
</foreach>
解决办法:对表中teacherid加索引。这个问题肯定不止一种解决方法,望大家提出意见。