Hibernate - Table Locked after update

Question

I'm performing an update via a method using Hibernate and the EntityManager.

This update method is called multiple times (within a loop).

It seems like when I execute it the first time, it locks the table and does not free it.

When trying to update the table via SQL Developer after having closed the application, I see the table is still locked because the update is hanging.

What do you see as a solution to this problem? If you need more information, let me know.

Class

@Repository
@Transactional(propagation = REQUIRES_NEW)
public class YirInfoRepository {

    @Autowired
    EntityManager entityManager;

    @Transactional(propagation = REQUIRES_NEW)
    public void setSent(String id) {
        String query = "UPDATE_QUERY";
        Query nativeQuery = entityManager.createNativeQuery(String.format(query, id));
        nativeQuery.executeUpdate();
    }
}

---

UPDATE

After having waited more than one hour, I launched the application again and it worked fine once but now again, it hangs.

---

UPDATE 2 -- I'll give a maximum bounty to whoever helps me solve this

On another place I use an application managed entity manager and it still gives me the same type of errors.

public void fillYirInfo() {
    File inputFile = new File("path");
    try (InputStream inputStream = new FileInputStream(inputFile);
         BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(inputStream))) {
        bufferedReader.lines().skip(1).limit(20).forEach(line -> {
            String[] data = line.split(",");
            String rnr = data[0];
            String linked = data[1];
            String email = data.length > 2 ? data[2] : "";



    String insuredId = insuredPeopleRepository.getInsuredIdFromNationalId(rnr);
            int modifiedCounter = 0;

            if (!isNullOrEmpty(insuredId)) {
                EntityManager entityManager = emf.createEntityManager();
                EntityTransaction transaction = entityManager.getTransaction();
                Query nativeQuery = entityManager.createNativeQuery(
                        "QUERY"
                );
                transaction.begin();
                nativeQuery.executeUpdate();
                entityManager.flush();
                transaction.commit();
                entityManager.close();
            }

            System.out.println(modifiedCounter + " rows modified");
        });
    } catch (IOException e) {
        e.printStackTrace();
    }
}

Answer 1

Try without an update-query:

@Repository
@Transactional(propagation = REQUIRES_NEW)
public class YirInfoRepository {

    @Autowired
    EntityManager entityManager;

    @Transactional(propagation = REQUIRES_NEW)
    public void setSent(String id) {
        //guessing your class name and method..
        final YirInfo yirInfo = entityManager.find(YirInfo.class, id);
        yirInfo.setSent();
    }
}

Might not be as fast as a single update query, but it's possible to get it reasonably fast, unless the amount of data is huge. This is the preferred way of using Hibernate/JPA, instead of thinking in terms of single values and SQL queries, you work with entities/objects and (sometimes) HQL/JPQL queries.

Answer 2

You are using @Transactional annotation. This means you are using Spring Transaction. Then in your UPDATE 2 you are using transaction by yourself and managed by spring (I guess it's another project or class not managed by Spring).

In any case what I would do is to try to update your records in single spring transaction and I'd not use @Transactional in DAO layer but in service layer. Something like this:

Service layer:

@Service
public class YirInfoService {
    @Autowired
    YirInfoRepository dao;

    @Transactional(propagation = REQUIRES_NEW)
    public void setSent(List < String > ids) {
        dao.setSents(ids);
    }
}

DAO layer:

@Repository
public class YirInfoRepository {

    @Autowired
    EntityManager entityManager;

    //Here you can update by using and IN statement or by doing a cycle
    //Let's suppose a bulk operation
    public void setSents(List < String > ids) {
        String query = "UPDATE_QUERY";
        for (int i = 0; i < ids.size(); i++) {
            String id = ids.get(i);
            Query nativeQuery = entityManager.createNativeQuery(String.format(query, id));
            nativeQuery.executeUpdate();
            if (i % 20 == 0) {
                entityManager.flush();
                entityManager.clear();
            }
        }
    }
}

Answer 3

The first thing you have to understand is that for the first example, you are using a native query to update rows in the DB. In this case you are completely skipping Hibernate to do anything for you.

In your second example, you have the same thing, you are updating via an update query. You don't need to flush the entity manager as it's only necessary for transferring the pending changes made to your entity objects within that entity manager.

Plus I don't know how your example works as you are autowiring the entity manager and not using the @PersistenceContext annotation. Make sure you use this one properly because you might have misconfigured the application. Also there is no need to manually create the entity manager when using Spring as it looks in the second example. Just use @PersistenceContext to get an entity manager in your app.

You are also mixing up transaction management. In the first example, it's enough if you put the @Transactional annotation to either of your method or to the class.

For the other example, you are doing manual transaction management which makes no sense in this case. If you are using Spring, you can simply rely on declarative transaction management.

The first thing I'd check here is to integrate datasource-proxy into your connection management and log out how your statements are executed. With this info, you can make sure that the query is sent to the DB side and the DB is executing it very slowly, or you are having a network issue between your app and db.

If you find out that the query is sent properly to the DB, you want to analyze your query, because most probably it's just executed very slowly and needs some optimizations. For this, you can use the Explain plan feature, to find out how your execution plan looks like and then make it faster.