JPA悲观锁不起作用 [英] JPA Pessimistic Lock Not Working

问题描述

我正在使用Spring Boot，JPA，Oracle 12C和下面的类型查询来选择要处理的新"项目.选择新建"项目后，我会更新其状态，因此不再符合选择的资格，但是我发现并发问题是同一项目被拾取.

I'm using Spring Boot, JPA, Oracle 12C and a Typed Query below to select 'NEW' items to process. Once I've selected a 'NEW' item, I update its status so it's no longer eligible for selection but I'm seeing a concurrency issue with the same items getting picked up.

我阅读了

I read here that i needed to set a 'LockModeType.PESSIMISTIC_WRITE' on the query to prevent other Threads from selecting the same row but it doesn't appear to be working.

我错过了下面的内容吗?还是需要其他配置来防止并发线程从表中检索相同的行?是与锁定级别有关的问题还是实体管理器没有得到更新/刷新?

Have I missed something below or do i need another configuration to prevent concurrent threads from retrieving the same rows from my Table? Is the issue to do with the lock level or the Entity Manager not getting updated/refreshed?

我的@Transactional服务:

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

我的存储库层中的查询:

The Query in my repository layer:

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

推荐答案

我可以确认这一观察.我已经使用H2-Database测试了几种锁定模式，并且所有锁定模式均按预期工作.两种悲观的锁定模式都无法与Oracle数据库结合使用.因此，问题是:此代码有什么问题?

I can confirm this observation. I have several Lock-Modes tested with a H2-Database, and all worked as expected. Neither of the pessimistic Lock-Modes worked correctly in combination with an Oracle database. Therefore, the question: what is wrong with this code?

对于Oracle，虽然第一个并发代码会阻止第二个并发代码，但其中两个并发代码执行会产生相同的数据:

With Oracle two of these concurrent code executions yield the same data although the first should block the second one:

// 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: 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=?

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