PESSIMESTIC LOCK is not working with Spring Data accessing MySQL

Question

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!!!!

Answer 1

It runs that the issue was caused by using an incorrect Dialect with MySql. That version of Dialect "MySQLDialect" assumes "MyISAMStorageEngine" as a default storage engine while creating tables. That engine does not support any type of transactions. The only storage engine that supports transactions is "InnoDB" which is being selected as the default choice when using other Dialects like "MySQL55Dialect", "MySQL57Dialect" or "MySQL8Dialect"