我的应用程序(java spring-core)有多个线程同时运行并访问数据库,在某些高峰时间出现异常
07:43:33,400 WARN [org.hibernate.util.JDBCExceptionReporter] SQL Error: 1213, SQLState: 40001 07:43:33,808 ERROR [org.hibernate.util.JDBCExceptionReporter] Deadlock found when trying to get lock; try restarting transaction 07:43:33,808 ERROR [org.hibernate.event.def.AbstractFlushingEventListener] Could not synchronize database state with session org.hibernate.exception.LockAcquisitionException: could not insert: [com.xminds.bestfriend.frontend.model.Question] at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:107) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2436) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2856) at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:79) at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:273) at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:265) at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:184) at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321) at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51) at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1216) at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:383) at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:133) at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:656) at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:754) at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:723) at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:147) at com.xminds.bestfriend.consumers.Base.onMessage(Base.java:96) at org.springframework.jms.listener.adapter.MessageListenerAdapter.onMessage(MessageListenerAdapter.java:339) at org.springframework.jms.listener.AbstractMessageListenerContainer.doInvokeListener(AbstractMessageListenerContainer.java:535) at org.springframework.jms.listener.AbstractMessageListenerContainer.invokeListener(AbstractMessageListenerContainer.java:495) at org.springframework.jms.listener.AbstractMessageListenerContainer.doExecuteListener(AbstractMessageListenerContainer.java:467) at org.springframework.jms.listener.AbstractPollingMessageListenerContainer.doReceiveAndExecute(AbstractPollingMessageListenerContainer.java:325) at org.springframework.jms.listener.AbstractPollingMessageListenerContainer.receiveAndExecute(AbstractPollingMessageListenerContainer.java:263) at org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.invokeListener(DefaultMessageListenerContainer.java:1058) at org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.executeOngoingLoop(DefaultMessageListenerContainer.java:1050) at org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.run(DefaultMessageListenerContainer.java:947) at java.lang.Thread.run(Thread.java:662) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) at java.lang.reflect.Constructor.newInstance(Constructor.java:513) at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) at com.mysql.jdbc.Util.getInstance(Util.java:386) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1065) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4074) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4006) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2468) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2629) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2719) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2450) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2371) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2355) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105) at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:46) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2416) ... 25 more
我的代码看起来
try { this.consumerTransactionTemplate.execute(new TransactionCallbackWithoutResult(){ @Override protected void doInTransactionWithoutResult( TransactionStatus status) { process(); } }); } catch(Exception e){ logger.error("Exception occured " , e); //TODO: Exception handling }
MySQL的InnoDB引擎具有行级锁定功能,即使您的代码正在插入或更新单个行(尤其是要更新的表上有多个索引),也可能导致死锁。最好的选择是围绕它设计代码,以便在由于死锁而失败的情况下重试事务。一些有关MySQL死锁诊断的有用信息以及可能的解决方法在此处提供。
Spring提供了一个有趣的通过AOP重试死锁的实现。这样,您只需将注释添加到要在出现死锁的情况下重试的方法。