yaki_nuka yaki_nuka - 1 year ago 219
MySQL Question

MySQL: Deadlock found when trying to get lock

The next exception raises with this configuration:

  • MySQL 5.7.10

  • Spring 4.0.5

  • Hibernate 4.1.9

  • Atomikos 3.8.0

  • Spring ThreadPoolTaskExecutor between 10 and 20 threads

I need Atomikos for transactions between two databases.

The exception is:

javax.persistence.PersistenceException: org.hibernate.exception.LockAcquisitionException: Deadlock found when trying to get lock; try restarting transaction
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1377)
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1300)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:273)
at com.app.writer.jta.dao.CustomerDAOImpl.findCustomer(CustomerDAOImpl.java:35)
at com.app.writer.dao.impl.JTAPDFWriterDAO.saveBean(JTAPDFWriterDAO.java:64)
at com.app.writer.item.writer.PDFWriter.write(PDFWriter.java:17)
at org.springframework.batch.item.support.CompositeItemWriter.write(CompositeItemWriter.java:51)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:133)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:121)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
at com.sun.proxy.$Proxy28.write(Unknown Source)
at org.springframework.batch.core.step.item.SimpleChunkProcessor.writeItems(SimpleChunkProcessor.java:175)
at org.springframework.batch.core.step.item.SimpleChunkProcessor.doWrite(SimpleChunkProcessor.java:151)
at org.springframework.batch.core.step.item.FaultTolerantChunkProcessor$3.doWithRetry(FaultTolerantChunkProcessor.java:329)
at org.springframework.retry.support.RetryTemplate.doExecute(RetryTemplate.java:263)
at org.springframework.retry.support.RetryTemplate.execute(RetryTemplate.java:193)
at org.springframework.batch.core.step.item.BatchRetryTemplate.execute(BatchRetryTemplate.java:217)
at org.springframework.batch.core.step.item.FaultTolerantChunkProcessor.write(FaultTolerantChunkProcessor.java:422)
at org.springframework.batch.core.step.item.SimpleChunkProcessor.process(SimpleChunkProcessor.java:199)
at org.springframework.batch.core.step.item.ChunkOrientedTasklet.execute(ChunkOrientedTasklet.java:75)
at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:406)
at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:330)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)
at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:271)
at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:77)
at org.springframework.batch.repeat.support.TaskExecutorRepeatTemplate$ExecutingRunnable.run(TaskExecutorRepeatTemplate.java:262)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)

I have required a PROCESS permission to check SHOW ENGINE STATUS to get mor info about the deadlock.

So, my questions are theses:

  • The log in ...CustomerDAOImpl.findCustomer is a key to get the query and the lock table. But, is the deadlock produced because of this query/table or another one?

  • The transaction with Atomikos is among two databases. Now, I notice that all related tables are in InnoDB engine, except one, which is in MyISAM. Is this right? Could it be the cause of this exception?

  • Could the deadlock happen because of the number of threads?

Of course, any help to check and analyze this exception is very appreciated.

Thanks in advance

Rohit's advices are good ones. My next actions are:

  • Get more info about deadlock by enabling SHOW ENGINE STATUS and getting the log.

  • My transaction contains some SELECT before UPDATEs. I'll extract the queries out of the transaction, so it will contain only INSERT and UPDATE sentences.

Thanks a lot.

Answer Source

1) Yes, a query(SELECT/INSERT/UPDATE/DELETE) can lock the database.
For Example: SELECT Query


//Making use of nolock as below will avoid deadlock but there are chances that you may not get the recent records just insert in your DB.


2) When you are using two or more databases then the things are going to be complicated, because DB1 will be in use to operate queries within itself(related to DB1) And DB2 is requesting DB1 to fire some queries(e.g: via Triggers) .

For example: In DB1 you are first saving some records(data) that is sent for processing to a java application. The java application is using DB2 to store the all processing units(customer data etc.) and once application completes the task and save the processing units in DB1, the DB1 informs DB2 via a Trigger that a task is now completed.
This will work fine, but as the DB server load gets increased here the things would get complex (multiple threads performing n number of transactions), So a deadlock may occur.

I guess, you are not facing deadlock issue every minute. May be 4-5 times in a day. This issue is common in large projects. Also, I guess your transaction gets rolled back when a deadlock occurs.

3) Below listed are few ways to minimize deadlocks occurring in your DB.
3.1) You can perform certain tasks after a deadlock occurs in DB by handling org.hibernate.exception.LockAcquisitionException.
For example: Send a notification email to your group once the deadlock occurs. Later, you have to re-instate the transaction manually.

3.2) Other things you can do is Database server optimization(Tuning), this will help you to minimize the deadlocks upto certain extent.

3.3) By controlling the load on the database server. There can be two type of loads on your database server as below:

  • Controlled Load (queued processing e.g JMS, here you can delay the request to process a unit).
  • Uncontrolled Load (Users/Customer are accessing your DB, here you can't restrict a user, So cannot do anything in this case).
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download