0

What I want to achieve is, I would like to do a no lock query execution in select statement. But according to this answer it is impossible to achieve this with direct JPA implementation. I also understood from searches that nolock and READ_UNCOMMITTED are same. Is there any way to achieve this(no lock, READ_UNCOMMITTED) by modifying my below code. Or should I use the native query with specifying WITH(NOLOCK)

I had tried 

entityManager.createQuery(query).setLockMode(LockModeType.NONE).getResultList();

but it also not solving my issue.

My references this, this , this, this

I am using the following code to get data from table. this code works fine without nolock.

String query = "FROM Employee WHERE empId=:empId AND empStatus='failed'";

to fetch data from db

public Object getListFromQuery(String query) throws Exception {
    Object resultObject = null;
    List<Object> queryResultList = null;
    EntityManager entityManager = null;
    try {
        entityManager = entityManagerFactory.createEntityManager();
        queryResultList = entityManager.createQuery(query).getResultList();
        resultObject = (Object) queryResultList;
    } catch (Exception ex) {
        LOGGER.error("Exception : DatabaseManager :executeQueryGetList  ",ex);
        throw ex;
    } finally {
        entityManager.close();
    }
    return resultObject;
}

Database configuration

@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory()
        throws NamingException {
    LocalContainerEntityManagerFactoryBean factoryBean = new LocalContainerEntityManagerFactoryBean();
    factoryBean.setDataSource(dataSource());
    factoryBean
            .setPackagesToScan(new String[] { "com.test.middleware.entity" });
    factoryBean.setJpaVendorAdapter(jpaVendorAdapter());
    factoryBean.setJpaProperties(jpaProperties());
    factoryBean.setPersistenceUnitName("test_unit");
    return factoryBean;
}
arjuncc
  • 3,227
  • 5
  • 42
  • 77
  • 2
    Why do you want to do this? Are you ok with the results randomly returning missing and/or duplicate rows? What are you really trying to accomplish here? If it is performance, NOLOCK is NOT the answer. – Sean Lange Mar 12 '18 at 18:36
  • My issue is there are locked rows at the moment in my DB. Since I am doing a 10 second delay long polling. So When it was tried with 'no lock', it is returning data. So I was adviced to do implement 'no lock' resolve this issue. I am pretty much new in this area. – arjuncc Mar 12 '18 at 18:43
  • Please advice me any alternative to resolve this problem. It will be a lot of help to me. – arjuncc Mar 12 '18 at 18:48
  • 3
    Dealing with locked rows by ignoring them is dealing with the symptom instead of the problem. In your case you need to do some analysis and understand why you have rows being locked so frequently. Then you can figure out a way to make those processes faster to reduce the amount of locking. This is a much better approach than ignoring the locks and plunging ahead. – Sean Lange Mar 12 '18 at 18:53
  • What is your goal. You know rows are locked. You appear to have a grasp of the nolock hint. Do you understand that it can generate results that are inaccurate? Rows might not exist. Values within rows might change. Is that OK? All of this depends on what you intend to do with the information returned. If this was not discussed when you were asking for advice, then that advice may not be useful. If your advisor did not ask about this, then you should not consider that source as reliable. – SMor Mar 12 '18 at 20:57
  • For my scenario, Its polling the DB in every 10 second, and there is not that much changes expected in the tables. That is the max number of changes is 20-30 create/updates an hour. That's why I do go for it. I understood it is not the right way, but still just to understand. Is there any way to achieve this in JPA ? – arjuncc Mar 13 '18 at 02:57

0 Answers0