JPA/Hibernate and MySQL transaction isolation level
- by armandino
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 backed by Hibernate?
