I am using Spring Boot to build a scheduled-job data processing application. The main logic would be in a scheduled job that takes a batch of records and process them. I should be running 2 instances of the application that should not pick the same record twice. I tried to utilize the PESSIMISTIC LOCK with NO WAIT to resolve any records selection conflict. Things are not working as expected. Both instances are picking the same records, although I was expecting only one instance to lock and process a few records and the other instance skip what was locked by the first instance. Spring Boot version: 2.2.4.RELEASE
Database: MySQl
First I tried using the @Lock and @QueryHint annotations:
@Lock(value = LockModeType.PESSIMISTIC_WRITE) // adds 'FOR UPDATE' statement
@QueryHints(value={@QueryHint(name = "javax.persistence.lock.timeout", value = LockOptions.SKIP_LOCKED+"")})
Page<Transaction> findByStatus(String status, Pageable pageable);
Even with WAIT_FOREVER, there is no change in behavior as if @QueryHints are totally ignored.. The other option I tried is using NativeQuery:
@Query(value ="select * from transaction t where t.status = ?1 limit ?2 for update SKIP LOCKED",
countQuery="select count(*) from transaction t where t.status = ?1",
nativeQuery = true)
List<Transaction> findByStatusNQ(String status, Integer pageSize);
Same behavior. No locking, both app instances are selecting the same set of data This is the defined entity:
@Entity
public class Transaction {
@Id
private Long id;
private String description;
private String status;
private String managedBy;
@Temporal(TemporalType.TIMESTAMP)
private Date manageDate;
...
}
The caller service component is annotated with @Transactional to enforce creating new transaction for each execution:
@Transactional(propagation = Propagation.REQUIRES_NEW)
public List<Transaction> updateTrxStatus(String oldStatus,String newStatus){
List<Transaction> trxs = this.executeUsingNQ(oldStatus);
if(trxs.size()>0) {
logger.info( "Start updating Data");
trxs.forEach(transaction -> {
transaction.setStatus(newStatus);
transaction.setManagedBy(instanceName);
transaction.setManageDate(new Date(System.currentTimeMillis()));
});
}else{
logger.info(" Nothing to process");
}
return trxs;
}
@Transactional(propagation = Propagation.REQUIRED)
public List<Transaction> executeUsingNQ(String oldStatus){
List<Transaction> trxs = trxRepo.findByStatusNQ(oldStatus,2);
return trxs;
}
@Transactional(propagation = Propagation.REQUIRED)
public List<Transaction> executeWithPage(String oldStatus){
Pageable firstPageWithTwoElements = PageRequest.of(0, 2);
Page<Transaction> trxs = trxRepo.findByStatus(oldStatus, firstPageWithTwoElements);
return trxs.getContent();
}
Hopefully someone can help identifying whether there is some coding issue or missing coniguration!!!!
