I want to lock some row in my Postgres database so it cannot even be read until the process is completed. I read it here that it is achievable by using JPA PESSIMISTIC_WRITE, but it's not working as expected because the data is still readable.

Controller

I have 2 function in my controller the first one is for locking the data and the second one is test function to verify if the lock working correctly.

@GetMapping(value = "/lockdata")
public String controllerLockData() {
    serviceImplementation.lockData();
    return "sucess";
}

@GetMapping(value = "/test-lock")
public String testLock() {
    serviceImplementation.testReadUpdate();
    return "sucess";
}

serviceImplementation

@Override
@Transactional
public void lockData() {
    SomeTableEntity table = entityManager.find(SomeTableRepository.class, 42366L, LockModeType.PESSIMISTIC_WRITE);
    try {
        TimeUnit.SECONDS.sleep(60); // #1st Breakpoints, 60 second delay so I can do some testing in this 60 second
    } catch (InterruptedException e) {
        throw new RuntimeException(e);
    }

    table.setColumnOne("0"); // #2nd Breakpoints
    tableRepository.save(table);
}

@Override
@Transactional
public void testLock() {
    SomeTableEntity table = tableRepository.getOne(42366L);
    table.setColumnOne("1"); //#3rd breakpoint
    tableRepository.save(table);
    int a = 1; //#4th breakpoint
}

This is how I do the testing.

1. Set column one value to "2" (for validating later)
2. Call /lockdata and wait until #1st breakpoint is hit to make sure that the code is executed then continue the process in the IDE. Now I have 60 second time for doing the testing and expect the lock is already applied.
3. Call /test-lock, #3rd breakpoint is hit immediately with the right data while I expect it shouldn't, because I expect it will wait until the lock is lifted.
4. Continue IDE process. #4th breakpoint is hit, that means the save is already executed.
5. Check data before 1 minute using pgAdmin. The select query is success (the same as before it should be waiting for the lock to be lifted). Column one value is "2". This means the update is waiting for the lock to be lifted.
6. After 1 minute #2nd breakpoint hit, then continue IDE process.
7. Check data again using pgAdmin. Column one value is "1".

This means the update process is executed correctly but somehow the lock is not working for read. From the article that I previously mentioned it may be due to multi-version concurrency control. If it is so then PESSIMISTIC_READ and PESSIMISTIC_WRITE has no different. How can I really lock the row so no other process can select it?

Am I doing it wrong or misunderstood something?

Thank you.

CodePudding user response：

As you read in the article, it depends of database configuration. Now a standard is to configure database in REPEATABLE READ because it's give better performance, and less locks. isolation level. AS you can see in postgres simple select are never lock.

But your test while work if testlock function use a LockModeType.PESSIMISTIC_READ.