my application(java spring-core) has several threads running concurrently , accessing db, getting exception in peaktime
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 lock; try restarting transaction 07:43:33,808 error [org.hibernate.event.def.abstractflushingeventlistener] not synchronize database state session org.hibernate.exception.lockacquisitionexception: not insert: [com.xminds.bestfriend.frontend.model.question] @ org.hibernate.exception.sqlstateconverter.convert(sqlstateconverter.java:107) @ org.hibernate.exception.jdbcexceptionhelper.convert(jdbcexceptionhelper.java:66) @ org.hibernate.persister.entity.abstractentitypersister.insert(abstractentitypersister.java:2436) @ org.hibernate.persister.entity.abstractentitypersister.insert(abstractentitypersister.java:2856) @ org.hibernate.action.entityinsertaction.execute(entityinsertaction.java:79) @ org.hibernate.engine.actionqueue.execute(actionqueue.java:273) @ org.hibernate.engine.actionqueue.executeactions(actionqueue.java:265) @ org.hibernate.engine.actionqueue.executeactions(actionqueue.java:184) @ org.hibernate.event.def.abstractflushingeventlistener.performexecutions(abstractflushingeventlistener.java:321) @ org.hibernate.event.def.defaultflusheventlistener.onflush(defaultflusheventlistener.java:51) @ org.hibernate.impl.sessionimpl.flush(sessionimpl.java:1216) @ org.hibernate.impl.sessionimpl.managedflush(sessionimpl.java:383) @ org.hibernate.transaction.jdbctransaction.commit(jdbctransaction.java:133) @ org.springframework.orm.hibernate3.hibernatetransactionmanager.docommit(hibernatetransactionmanager.java:656) @ org.springframework.transaction.support.abstractplatformtransactionmanager.processcommit(abstractplatformtransactionmanager.java:754) @ org.springframework.transaction.support.abstractplatformtransactionmanager.commit(abstractplatformtransactionmanager.java:723) @ org.springframework.transaction.support.transactiontemplate.execute(transactiontemplate.java:147) @ com.xminds.bestfriend.consumers.base.onmessage(base.java:96) @ org.springframework.jms.listener.adapter.messagelisteneradapter.onmessage(messagelisteneradapter.java:339) @ org.springframework.jms.listener.abstractmessagelistenercontainer.doinvokelistener(abstractmessagelistenercontainer.java:535) @ org.springframework.jms.listener.abstractmessagelistenercontainer.invokelistener(abstractmessagelistenercontainer.java:495) @ org.springframework.jms.listener.abstractmessagelistenercontainer.doexecutelistener(abstractmessagelistenercontainer.java:467) @ org.springframework.jms.listener.abstractpollingmessagelistenercontainer.doreceiveandexecute(abstractpollingmessagelistenercontainer.java:325) @ org.springframework.jms.listener.abstractpollingmessagelistenercontainer.receiveandexecute(abstractpollingmessagelistenercontainer.java:263) @ org.springframework.jms.listener.defaultmessagelistenercontainer$asyncmessagelistenerinvoker.invokelistener(defaultmessagelistenercontainer.java:1058) @ org.springframework.jms.listener.defaultmessagelistenercontainer$asyncmessagelistenerinvoker.executeongoingloop(defaultmessagelistenercontainer.java:1050) @ org.springframework.jms.listener.defaultmessagelistenercontainer$asyncmessagelistenerinvoker.run(defaultmessagelistenercontainer.java:947) @ java.lang.thread.run(thread.java:662) caused by: com.mysql.jdbc.exceptions.jdbc4.mysqltransactionrollbackexception: deadlock found when trying lock; try restarting transaction @ sun.reflect.nativeconstructoraccessorimpl.newinstance0(native method) @ sun.reflect.nativeconstructoraccessorimpl.newinstance(nativeconstructoraccessorimpl.java:39) @ sun.reflect.delegatingconstructoraccessorimpl.newinstance(delegatingconstructoraccessorimpl.java:27) @ java.lang.reflect.constructor.newinstance(constructor.java:513) @ com.mysql.jdbc.util.handlenewinstance(util.java:411) @ com.mysql.jdbc.util.getinstance(util.java:386) @ com.mysql.jdbc.sqlerror.createsqlexception(sqlerror.java:1065) @ com.mysql.jdbc.mysqlio.checkerrorpacket(mysqlio.java:4074) @ com.mysql.jdbc.mysqlio.checkerrorpacket(mysqlio.java:4006) @ com.mysql.jdbc.mysqlio.sendcommand(mysqlio.java:2468) @ com.mysql.jdbc.mysqlio.sqlquerydirect(mysqlio.java:2629) @ com.mysql.jdbc.connectionimpl.execsql(connectionimpl.java:2719) @ com.mysql.jdbc.preparedstatement.executeinternal(preparedstatement.java:2155) @ com.mysql.jdbc.preparedstatement.executeupdate(preparedstatement.java:2450) @ com.mysql.jdbc.preparedstatement.executeupdate(preparedstatement.java:2371) @ com.mysql.jdbc.preparedstatement.executeupdate(preparedstatement.java:2355) @ com.mchange.v2.c3p0.impl.newproxypreparedstatement.executeupdate(newproxypreparedstatement.java:105) @ org.hibernate.jdbc.nonbatchingbatcher.addtobatch(nonbatchingbatcher.java:46) @ 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 }
mysql's innodb engine sports row-level locking, can lead deadlocks when code inserting or updating single row (specially if there several indexes on table being updated). best bet design code around in order retry transaction if fails due deadlock. useful info mysql deadlock diagnose , possible workarounds available here.
an interesting implementation of deadlock retry via aop in spring available here. way need add annotation method want retry in case of deadlock.
Comments
Post a Comment