My Application(java spring-core) has several threads running concurrently and accessing db, I am getting exception in some peaktime
我的应用程序（java spring-core）有几个并发运行的线程并访问db，我在某个高峰时间遇到异常
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
My code looks
我的代码看起来
try
{
this.consumerTransactionTemplate.execute(new TransactionCallbackWithoutResult(){
@Override
protected void doInTransactionWithoutResult(
TransactionStatus status)
{
process();
}
});
}
catch(Exception e){
logger.error("Exception occured " , e);
//TODO: Exception handling
}
3 个解决方案
#1
21
MySQL's InnoDB engine sports row-level locking, which can lead to deadlocks even when your code is inserting or updating a single row (specially if there are several indexes on the table being updated). Your best bet is to design the code around this in order to retry a transaction if it fails due to a deadlock. Some useful info about MySQL deadlock diagnose and possible workarounds is available here.
MySQL的InnoDB引擎支持行级锁定，即使代码插入或更新单行时也会导致死锁（特别是如果表上有多个索引正在更新）。最好的办法是围绕此设计代码，以便在由于死锁而失败时重试事务。有关MySQL死锁诊断和可能的解决方法的一些有用信息，请参见此处。
An interesting implementation of deadlock retry via AOP in Spring is available here. This way you just need to add the annotation to the method you want to retry in case of deadlock.
这里提供了一个有趣的实现，通过AOP在Spring中进行死锁重试。这样，您只需要将注释添加到要在死锁情况下重试的方法。
#2
5
Emir's answer is great and it describes the problem that you are getting. However I suggest you to try spring-retry.
埃米尔的答案很棒，它描述了你遇到的问题。不过我建议你试试spring-retry。
It's a brilliant framework that implements the retry pattern via annotation(s).
它是一个出色的框架，通过注释实现重试模式。
Example:
例：
@Retryable(maxAttempts = 4, backoff = @Backoff(delay = 500))
public void doSomethingWithMysql() {
consumerTransactionTemplate.execute(
new TransactionCallbackWithoutResult(){
@Override
protected void doInTransactionWithoutResult(
TransactionStatus status)
{
process();
}
});
}
In case any exception, it will retry (call) up to 4 times the method doSomethingWithMysql() with a backoff policy of 500ms
如果有任何异常，它将重试（调用）方法doSomethingWithMysql（）最多4次，退避策略为500ms
#3
-1
When you face this kind of error "deadlock detected". You should inspect your queries execution and verify if two or more concurrent transactions can cause a deadlock.
当你遇到这种错误“检测到死锁”。您应检查查询执行并验证两个或更多并发事务是否可能导致死锁。
These transactions should acquire database locks in the same order in order to avoid deadlock.
这些事务应以相同的顺序获取数据库锁，以避免死锁。
#1
21
MySQL's InnoDB engine sports row-level locking, which can lead to deadlocks even when your code is inserting or updating a single row (specially if there are several indexes on the table being updated). Your best bet is to design the code around this in order to retry a transaction if it fails due to a deadlock. Some useful info about MySQL deadlock diagnose and possible workarounds is available here.
MySQL的InnoDB引擎支持行级锁定，即使代码插入或更新单行时也会导致死锁（特别是如果表上有多个索引正在更新）。最好的办法是围绕此设计代码，以便在由于死锁而失败时重试事务。有关MySQL死锁诊断和可能的解决方法的一些有用信息，请参见此处。
An interesting implementation of deadlock retry via AOP in Spring is available here. This way you just need to add the annotation to the method you want to retry in case of deadlock.
这里提供了一个有趣的实现，通过AOP在Spring中进行死锁重试。这样，您只需要将注释添加到要在死锁情况下重试的方法。
#2
5
Emir's answer is great and it describes the problem that you are getting. However I suggest you to try spring-retry.
埃米尔的答案很棒，它描述了你遇到的问题。不过我建议你试试spring-retry。
It's a brilliant framework that implements the retry pattern via annotation(s).
它是一个出色的框架，通过注释实现重试模式。
Example:
例：
@Retryable(maxAttempts = 4, backoff = @Backoff(delay = 500))
public void doSomethingWithMysql() {
consumerTransactionTemplate.execute(
new TransactionCallbackWithoutResult(){
@Override
protected void doInTransactionWithoutResult(
TransactionStatus status)
{
process();
}
});
}
In case any exception, it will retry (call) up to 4 times the method doSomethingWithMysql() with a backoff policy of 500ms
如果有任何异常，它将重试（调用）方法doSomethingWithMysql（）最多4次，退避策略为500ms
#3
-1
When you face this kind of error "deadlock detected". You should inspect your queries execution and verify if two or more concurrent transactions can cause a deadlock.
当你遇到这种错误“检测到死锁”。您应检查查询执行并验证两个或更多并发事务是否可能导致死锁。
These transactions should acquire database locks in the same order in order to avoid deadlock.
这些事务应以相同的顺序获取数据库锁，以避免死锁。