悲观锁不适用于访问 MySQL 的 Spring Data [英] PESSIMESTIC LOCK is not working with Spring Data accessing MySQL

问题描述

我正在使用 Spring Boot 构建计划作业数据处理应用程序.主要逻辑将在一个预定的作业中，该作业需要一批记录并对其进行处理.我应该运行应用程序的 2 个实例，不应两次选择相同的记录.我尝试使用 NO WAIT 的悲观锁来解决任何记录选择冲突.事情没有按预期工作.两个实例都选择相同的记录，尽管我期望只有一个实例锁定和处理一些记录，而另一个实例跳过第一个实例锁定的内容.Spring Boot 版本:2.2.4.RELEASE

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

数据库:MySQL

首先我尝试使用@Lock 和@QueryHint 注释:

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);

即使使用 WAIT_FOREVER，行为也没有变化，就好像 @QueryHints 被完全忽略一样..我尝试的另一个选项是使用 NativeQuery:

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;
...
}

调用者服务组件使用@Transactional 进行注释以强制为每次执行创建新事务:

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

推荐答案

它运行说问题是由于在 MySql 中使用了不正确的方言造成的.该版本的方言MySQLDialect"在创建表时假定MyISAMStorageEngine"作为默认存储引擎.该引擎不支持任何类型的交易.唯一支持事务的存储引擎是InnoDB"，当使用MySQL55Dialect"、MySQL57Dialect"或MySQL8Dialect"等其他方言时，它被选为默认选项

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"

这篇关于悲观锁不适用于访问 MySQL 的 Spring Data的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！