I have a table which locks rows as per accountId and a lock status column having values 0 and 1.

CREATE TABLE lock (
   id INT(11),
   accountId INT(11),
   isLocked TINYINT(2),
);

The requirement is the that multiple threads could try to take the lock. However, only one of them will be successful. I am implementing lock by using a pessimistic write lock which fires a “SELECT … FOR UPDATE” query.

The code flow is like below

Controller -> Service -> DAO -> DB

DAO contains @Transactional annotation so that read and write will happen in a single transaction.

The DAO code is like below

@Override
@Transactional
public boolean getLock(Account account) {
    boolean isLockAcquired = false;
    QLock qLock = QLock.lock;

    JPAQuery<Lock> lockJpaQuery = new JPAQuery<>(entityManager);
         
    lockJpaQuery.setLockMode(LockModeType.PESSIMISTIC_WRITE).from(qLock)
                .where(qLock.account.id.eq(account.getId()));

    Lock lock = lockJpaQuery.fetchOne();

    if (lock.getIsLocked() == 0) {
        lock.setIsLocked(1);
        isLockAcquired = true;
    }
    lockRespository.save(lock);
    return isLockAcquired;
}

Once the thread completes the work, it uses below code to release the lock.

@Override
@Transactional
public boolean releaseLock(Account account) {
        
    boolean isLockReleased = false;

    JPAQuery<Lock> lockJpaQuery = new JPAQuery<>(entityManager);
    QLock qLock = QLock.lock;

    lockJpaQuery.setLockMode(LockModeType.PESSIMISTIC_WRITE).from(qLock)
                .where(qLock.account.id.eq(account.getId()));

    Lock lock = lockJpaQuery.fetchOne();

    if(lock.getIsLocked == 1) {
        lock.setIsLocked(0);
        lockRespository.save(lock);
        isLockReleased = true;
    }
    return isLockReleased;
}

An example call for this method from service is below.

boolean lockStatus = dao.getLock(account);

while(!lockStatus) {
    lockStatus = dao.getLock(account);
}

.... Do some work

lockStatus = dao.releaseLock(account);

Now to test this code, I started server in debug mode and acquired lock by the first request and set isLocked to 1. The code would have acquired the lock, changed the isLocked field and releases the pessimistic write lock.
As transaction is only upto the getLock method, pessimistic write lock should be removed and now other threads can take the lock on the row. I can also see isLocked value changed from 0 to 1.

Now, I fired one more request to see if it will get the lock or not. As expected it didn’t as even if it can hold pessimistic write lock on the row, it can’t modify it as its value is already 1. So, it went into the while loop.

So, I switched the first thread and complete its process and again acquired the lock and set the value of isLocked to 0 and then it exited. The value of isLocked also changed in the database.

Post this when I proceeded with the execution of second thread, the getLock method still returns false. When I debugged in the method itself, it is returning a Lock object which is having isLocked=1 due to which it is not able to modify it as per condition and unable to get the lock.

My issue here is even when the first thread has updated the value why second thread still fetching the old value?

Steps I followed to check:

1. I printed the query fired by second thread and ran it on db client. It worked fine.
2. I changed my isolation level from REPEATABLE-READ to READ-COMMITTED.
3. I checked whether a transaction is being created or not using TransactionAspectSupport.

Important Points:

1. I am using MySQL 8.0.
2. Isolation level is READ-COMMITTED.
3. Service class method don’t have @Transactional annotation.
4. The value changes from the first thread are visible in database.
5. I tried resetting the entityManager cache, but it didn’t work.
6. I am using QueryDSL.