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!
解决方案
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);