Java – JPA and MySQL transaction isolation level

I have a local query that can be inserted into MySQL database in batches:

String sql = "insert into t1 (a,b) select x,y from t2 where x = 'foo'";
    EntityTransaction tx = entityManager.getTransaction();
    try {
        tx.begin();
        int rowCount = entityManager.createNativeQuery(sql).executeUpdate();
        tx.commit();
        return rowCount;
    }
    catch(Exception ex) {
        tx.rollback();
        log.error(...);
    }

This query causes a deadlock: when it uses insert When select reads from T2, another process attempts to insert a row in T2

I don't care about the consistency of the read from T2 when performing the insert Select and want to set the transaction isolation level to read_ UNCOMMITTED.

How do I set it up in JPA?

to update

So I finally created a regular SQL connection for this situation, because in my opinion, this is the simplest choice Thank you!

Solution

You need to set it at the connection level, get the session from entitymanager and do the following:

org.hibernate.Session session = (Session)entityManager.getDelegate();
Connection connection = session.connection();
connection.setTransactionIsolation(Connection.READ_UNCOMMITTED);