Java – the entity manager does not answer until the Native Query call is completed

I used hibernate and Oracle SQL in the project When I call the createnativequery method of the entity manager, the entity manager does not respond to any calls (even from different browsers) until the method returns The query takes a long time, but it is called in the new thread. Why is the entity manager blocked?

Note: when I call the jpql query, the problem disappears

@PersistenceContext
private EntityManager entityManager;

//blocking other transactions,cannot make any read from the same entityManager until this method is completed.
@Transactional(readOnly=true)
public void testMethod1(String query) {

    Query q = entityManager.createNativeQuery(query);
// CANNOT SET LOCKMODE because it is not JPQL :  q.setLockMode(LockModeType.NONE)  //throws exception
    List<Object[]> result =  q.getResultList();
}

@Transactional(readOnly=true)
public void testMethod2(String jpql) {

    Query q = entityManager.createQuery(jpql);
    List<Object> result =  q.getResultList();
}

Solution

I can reproduce your problem using other databases (SQL Anywhere in my case)

After that, I looked at hibernate JavaDocs and it said that your problem is the default behavior, because JPA requires setlockmode to be applied only to non-native queries

To solve this problem, hibernate uses the queryhints of the query:

NATIVE_ Lockmode: lock mode can be applied to native SQL queries because JPA requires query Setlockmode (javax. Persistence. Lockmodetype) throws an IllegalStateException when calling a native query

To use queryhints, you should do the following:

@Transactional(readOnly=true)
public void testMethod1(String query) {

    Query q = entityManager.createNativeQuery(query);
    q.setHint(QueryHints.NATIVE_LOCKMODE,LockModeType.NONE);
    List<Object[]> result =  q.getResultList();
}

I also found that if you use @ transactionattribute on the method, the problem may not occur, for example:

@TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)
public void testMethod1(String query) {

    Query q = entityManager.createNativeQuery(query);
    q.setHint(QueryHints.NATIVE_LOCKMODE,LockModeType.NONE);
    List<Object[]> result =  q.getResultList();
}

Please try the solution given above. If the problem is solved, please tell us and wish you good luck!