CODEWITHSUNDEEP
javaspringhibernatetransactionspessimistic-locking
Hemanth
Hemanth

Reputation: 121

How to resolve PessimisticLockingFailureException

I'm frequently getting PessimisticLockingFailureException. I gone through the https://docs.jboss.org/hibernate/orm/4.0/devguide/en-US/html/ch05.html and understood that we need to mention the lock explicitly in the code. Where as in my code, I nowhere mentioned to lock the object. But I'm frequently getting the below error:

org.springframework.dao.PessimisticLockingFailureException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.PessimisticLockException: could not execute statement
    at org.springframework.orm.hibernate5.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:240) ~[spring-orm-5.2.9.RELEASE.jar:5.2.9.RELEASE]
    at org.springframework.orm.hibernate5.HibernateTransactionManager.convertHibernateAccessException(HibernateTransactionManager.java:804) ~[spring-orm-5.2.9.RELEASE.jar:5.2.9.RELEASE]
    at org.springframework.orm.hibernate5.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:639) ~[spring-orm-5.2.9.RELEASE.jar:5.2.9.RELEASE]
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:743) ~[spring-tx-5.2.9.RELEASE.jar:5.2.9.RELEASE]
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:711) ~[spring-tx-5.2.9.RELEASE.jar:5.2.9.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:633) ~[spring-tx-5.2.9.RELEASE.jar:5.2.9.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:386) ~[spring-tx-5.2.9.RELEASE.jar:5.2.9.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118) ~[spring-tx-5.2.9.RELEASE.jar:5.2.9.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.9.RELEASE.jar:5.2.9.RELEASE]
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95) ~[spring-aop-5.2.9.RELEASE.jar:5.2.9.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.9.RELEASE.jar:5.2.9.RELEASE]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.2.9.RELEASE.jar:5.2.9.RELEASE]
    at com.sun.proxy.$Proxy168.delete(Unknown Source) ~[?:?]

This is my below code:

class Suppress {
Job job = serviceImpl.getJob(jobId);
String name = job.getJobName();
//do something with name
serviceImpl.delete(job)
}

@Transactional(propagation = Propagation.REQUIRED, readOnly = true, rollbackFor = Throwable.class)
class ServiceImpl{
getJob(int id){
    daoImpl.get(id)
}

delete(Job job){
   daoImpl.delete(job)
}
}

Can someone help me why am I getting this exception very frequently and what should be the resolution.?

Upvotes: 1

Views: 12743

Answers (2)

Christian Beikov
Christian Beikov

Reputation: 16400

You are getting this exception because the lock wait timeout was reached. This means that your delete operation waited for the (implicit) lock that the database needs but couldn't get it in time. The reason for that can be that some other long running transactions lock the same object. There is not much you can do here. If there is contention with respect to updating/deleting the same record, only one transaction can "win".

Upvotes: 2

InsertKnowledge
InsertKnowledge

Reputation: 1028

Ok, so first of all you are locking the code because that's what @Transactional does - it makes sure the methods are in a transaction and locks the needed resources when accessing them.

Let's talk about your use of @Transactional:

  • You don't need to specify propagation because the type you've specified is the default type anyway.
  • You don't need to specify rollbackFor = Throwable because that is the default as well
  • It's usually better to put the annotation on top of the methods instead of the class and in your case it makes no sense to put it on the class because you are using readOnly = true which is telling hibernate you're not going to make any write changes on the db and deleting entities (your delete method) is considered a write access. So it's better to just annotate every method separately and leave the readOnly option only for get methods.

Now the reason you're getting that exception so often is because you have a nested @Transactional class. If you move the ServiceImpl class to a different file and just import and use it in the Suppress class the exception should disappear.

Upvotes: 1

Related Questions