We have been using MySQL for quite sometime (Server version: 5.6.10 MySQL Community Server) with Hibernate as ORM and Spring for transaction management.
We use pessimistic locking at a number of places. But today while debugging a concurrency issue, I found a strange issue. If two threads try to acquire a pessimistic lock on a row at the same instant, they are both able to get it. Here is the DAO method that acquires the lock:
@Override
public PurchaseOrder lockOnPurchaseOrder(Integer purchaseOrderId) {
Query query = sessionFactory.getCurrentSession().createQuery("select p from PurchaseOrder p where p.id = :purchaseOrderId);
query.setParameter("purchaseOrderId", purchaseOrderId);
query.setLockMode("p", LockMode.PESSIMISTIC_WRITE);
return (PurchaseOrder) query.uniqueResult();
}
Here is the code snippet from the service that is using it:
@Override
public void doService(int purchaseOrderId) {
purchaseService.lockOnPurchaseOrder(purchaseOrderId); // Problematic line
// If lock is acquired, so something
}
Then I tested that if one thread acquires the lock, and then other thread tries to acquire it a moment later, it blocks on this call. So this is working fine.
But if two threads simultaneously execute the problematic line, they both get the lock. I checked the mysql logs by turning on general logs, two select for update statements are getting executed.
Can somebody help me with this?