How to resolve PessimisticLockingFailureException

Question

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.?

score 1 · Answer 1 · answered Aug 18 '21 at 15:53

1

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.

answered Aug 18 '21 at 15:53

InsertKnowledge

1,012
1
11
17

Thanks for the answer, and Yes, I moved the **ServiceImpl** Class to a different file and imported., but still getting the exception now and then. @InsertKnowlede – Hemanth Aug 19 '21 at 06:11
@Hemanth you can try putting `@Service` annotation on the ServiceImpl class and then `@Autowire` it in the Suppress class. – InsertKnowledge Aug 19 '21 at 10:05
@Hemanth you can also try changing this: `serviceImpl.delete(job);` into this `serviceImpl.delete(serviceImpl.getJob(jobId));` since you are making changes to the Job entity and that may be the cause of the exception. – InsertKnowledge Aug 19 '21 at 11:18
thanks @InsertKnowledge, I tried this., but didn't find anything difference. It will be more helpful if I understand why the issue is occuring. – Hemanth Aug 19 '21 at 16:36

score 1 · Answer 2 · answered Aug 26 '21 at 10:41

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".

How to resolve PessimisticLockingFailureException

2 Answers2