How to use pessimistic_write lock with Spanner using Spring boot JPA/hibernate

Question

I am using spanner database with Spring boot and JPA. I have a requirement like, whenever employee information is read from employee table, no-one can read/update/delete the the same row information until transaction gets completed.

I got to know for such purpose "PESSIMISTIC_WRITE" lock is used in JPA. (referred this link JPA PESSIMISTIC_WRITE on applications with multiple instances)

I tried to use it by using below code but its not holding the lock. When I try to hit the same query from other instance its giving result. Ideally It should wait until first transaction gets completed but still its giving the result.

Could you please suggest me any solution for it.

I am using below code to achieve the same.

@Repository
public interface EmployeeRepository extends JpaRepository<EmployeeEntity, Integer> {

    @lock(LockModeType.PESSIMISTIC_WRITE)
    @query("SELECT c FROM Employee c WHERE c.empId = :empId)
    public EmployeeEnity getEmployee(@param("empId") Integer empId);
}

@Service
public class EmpService implement IEmpService {

@Autowired
EmployeeRepository  empRepository;

@Transactional
@Override
    public EmployeeEntity getEmployee(Integer empId) {

        return empRepository.getClientId(clientId);
        
    }
}

score 1 · Answer 1 · answered Apr 05 '22 at 12:20

You are saying that you need a lock where

no-one can read/update/delete the the same row information until transaction gets completed

That sounds like an exclusive lock to me. That is not available in Cloud Spanner.

The question is whether that is what you really need in this case. Read/write transactions in Cloud Spanner have external consistency. To summarize that very shortly for your specific question this boils down to the following:

Transaction A reads row R1.
Transaction A changes the value of R1.
Transaction A commits.

Cloud Spanner guarantees that at the moment Transaction A commits, the value of R1 is still the same as it read at step 1. If you also have a transaction B that does the following at approximately the same time:

Transaction B reads row R1.
Transaction B changes the value of R1.
Transaction B commits.

(So both transactions read and modify the same row)

Then it is guaranteed that the effect of this will be that either transaction A executed first, and then transaction B. Or that transaction B executed first, and then transaction A. (That is; it will seem that the transactions executed sequentially.) In all cases both transactions are guaranteed that the value they saw at step 1 is still unmodified by anyone else when it commits.

How to use pessimistic_write lock with Spanner using Spring boot JPA/hibernate

1 Answers1