JPA悲观锁不起作用

Question

我正在使用Spring Boot、JPA、Oracle 12C和下面的类型化查询来选择要处理的“新”项目。一旦我选择了“新”项目，我就会更新其状态，使其不再符合选择条件，但我看到一个并发问题，相同的项目被选中。

我在这里读到，我需要设置一个'LockModeType.PESSIMISTIC_WRITE'的查询，以防止其他线程选择相同的行，但它似乎不起作用。

我是否遗漏了下面的内容，或者我是否需要另一个配置来防止并发线程从表中检索相同的行？问题是否与锁级别或实体管理器未更新/刷新有关？

我的@Transactional服务：

@Override
@Transactional(isolation = Isolation.READ_COMMITTED, rollbackFor=RuntimeException.class)
public MyObject retrieveItemByStatus(StatusEnum status) {
    return myRepository.retrieveItemByStatus(status);
}

我的存储库层中的查询：

@Override
public MyObject retrieveItemByStatus(StatusEnum status) {

    String sql = "SELECT t FROM myTable t WHERE status = :status ORDER BY id ASC";      
    try {
        TypedQuery<MyObject> query = em.createQuery(sql, MyObject.class).setParameter("status", status);
        query.setLockMode(LockModeType.PESSIMISTIC_WRITE);
        query.setFirstResult(0);
        query.setMaxResults(1);
        MyObject myObject = (MyObject) query.getSingleResult();
        if (myObject != null) {
            myObject.setStatus(StatusEnum.IN_PROGRESS);
            MyObject myUpdatedObject = em.merge(myObject);                              
            return myUpdatedObject;
        }
    } catch (IllegalArgumentException iae) {
        //some logging
    } catch(NoResultException nrf) {            
        //some logging
    } catch(Exception ex) {
        //some logging
    }       
    return null;
}

Answer 1

AFAIK你不能在oracle中阻止“读取”…悲观锁对应于＜code＞select for update＜/code＞语句，它不会阻止其他＜code＞选择＜/code＞语句…它只会强制它读取旧版本的数据（在＜code＞运行select forupdate＞/code＜之前）…它将只阻止其他＜code＞选择更新＜/code>语句（因此其他具有悲观锁的查询）

Answer 2

我可以证实这一观察。我用 H2 数据库测试了几种锁定模式，并且都按预期工作。这两种悲观的锁定模式都无法与 Oracle 数据库结合使用。因此，问题来了：这段代码有什么问题？

使用 Oracle 时，其中两个并发代码执行会产生相同的数据，尽管第一个应该阻止第二个：

// Every Thread gets its own Hibernate session:
final Session session = HibernateSessionHolder.get();

session.getTransaction().begin();
final List<EventDeliveryDataDB> eddList = 
        session.createCriteria(EventDeliveryDataDB.class)
            .setLockMode(LockMode.PESSIMISTIC_WRITE) // with READ the same
            .add(eq("progress", NEW))
            .list();
eddList.stream().forEach(eddElem -> eddElem.setProgress(IN_PROGRESS));
session.getTransaction().commit();

Hibernate日志：

Hibernate: select this_.DD_ID as DD_ID1_2_0_, this_.CHANNEL_NAME as CHANNEL_NAME2_2_0_, this_.created as created3_2_0_, this_.DELIVERY_TIME as DELIVERY_TIME4_2_0_, this_.ERROR_CODE as ERROR_CODE5_2_0_, this_.ERROR_MESSAGE as ERROR_MESSAGE6_2_0_, this_.EVENT_ID as EVENT_ID7_2_0_, this_.MAX_RETRIES as MAX_RETRIES8_2_0_, this_.PROGRESS as PROGRESS9_2_0_, this_.PROGRESS_ID as PROGRESS_ID10_2_0_, this_.RECIPIENT_CRID as RECIPIENT_CRID11_2_0_, this_.RETRY_COUNTER as RETRY_COUNTER12_2_0_, this_.RETRY_TIME as RETRY_TIME13_2_0_, this_.updated as updated14_2_0_ from HR.NOS_DELIVERY_DATA this_ where this_.PROGRESS=?
Hibernate: select this_.DD_ID as DD_ID1_2_0_, this_.CHANNEL_NAME as CHANNEL_NAME2_2_0_, this_.created as created3_2_0_, this_.DELIVERY_TIME as DELIVERY_TIME4_2_0_, this_.ERROR_CODE as ERROR_CODE5_2_0_, this_.ERROR_MESSAGE as ERROR_MESSAGE6_2_0_, this_.EVENT_ID as EVENT_ID7_2_0_, this_.MAX_RETRIES as MAX_RETRIES8_2_0_, this_.PROGRESS as PROGRESS9_2_0_, this_.PROGRESS_ID as PROGRESS_ID10_2_0_, this_.RECIPIENT_CRID as RECIPIENT_CRID11_2_0_, this_.RETRY_COUNTER as RETRY_COUNTER12_2_0_, this_.RETRY_TIME as RETRY_TIME13_2_0_, this_.updated as updated14_2_0_ from HR.NOS_DELIVERY_DATA this_ where this_.PROGRESS=?
Hibernate: select DD_ID from HR.NOS_DELIVERY_DATA where DD_ID =? for update
Hibernate: select DD_ID from HR.NOS_DELIVERY_DATA where DD_ID =? for update
Hibernate: update HR.NOS_DELIVERY_DATA set CHANNEL_NAME=?, created=?, DELIVERY_TIME=?, ERROR_CODE=?, ERROR_MESSAGE=?, EVENT_ID=?, MAX_RETRIES=?, PROGRESS=?, PROGRESS_ID=?, RECIPIENT_CRID=?, RETRY_COUNTER=?, RETRY_TIME=?, updated=? where DD_ID=?
Hibernate: update HR.NOS_DELIVERY_DATA set CHANNEL_NAME=?, created=?, DELIVERY_TIME=?, ERROR_CODE=?, ERROR_MESSAGE=?, EVENT_ID=?, MAX_RETRIES=?, PROGRESS=?, PROGRESS_ID=?, RECIPIENT_CRID=?, RETRY_COUNTER=?, RETRY_TIME=?, updated=? where DD_ID=?