JPA and MySQL transaction isolation level

I have a native query that does开发者_如何学JAVA 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!

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);

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

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);