JPA and MySQL transaction isolation level

Question

I have a native query that does a batch insert into a MySQL database:

    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: while it reads from t2 with insert .. select, another process tries to insert a row into t2.

I don't care about the consistency of reads from t2 when doing an insert .. select and want to set the transaction isolation level to READ_UNCOMMITTED.

How do I go about setting it in JPA?

---

Update

So I ended up creating a regular SQL connection for this case as it seemed to me the simplest option. Thanks everyone!

Answer 1

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

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

Answer 2

In JPA you don't. JDO is the only standard that supports setting txn isolation. Obviously going for particular implementations methods can allow it, but then you become non-portable

Answer 3

Since you are using BMT, you can do the following using a datasource to get the connection. and set the iso. level.

DataSource source = (javax.sql.DataSource) jndiCntxt.lookup("java:comp/env/jdbc/myds");
Connection con = source.getConnection( );
con.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);