4

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!

armandino
  • 17,625
  • 17
  • 69
  • 81
  • I can't understand how it lead to deadlock? If tx1 execute the : insert into t1 (a, b) select x, y from t2 where x = 'foo' And while this happening the second tx make insert into "t2" table, than tx1 have a wait till tx2 will end, after tx2 has ended the tx1 continue it's work. Dedalock mean that the tx2 while making inserts into t2 also do the reading from t1? Is this yout case? i mean some thing like this lead to deadlock: tx1: insert into t1 (a, b) select x, y from t2 where x = 'foo' tx2: insert into t2 (a, b) select x, y from t1 where x = 'foo' – Pasha Jun 04 '14 at 21:04

3 Answers3

4

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);
Guillaume
  • 14,306
  • 3
  • 43
  • 40
  • that's why I said "get the session from the em" ;) I edited the code – Guillaume May 27 '10 at 19:30
  • 4
    and what if it is not hibernate? – Bozho May 28 '10 at 05:00
  • 1
    Well, he said he's using Hibernate. I guess there are similar ways to do this for other for other JPA implementations. – Guillaume May 28 '10 at 05:29
  • 2
    Hi Guillaume, I did try your approach in the end but it didn't work unfortunately; seeing that session.connection() method was deprecated, I decided not to spend time investigating. – armandino Jun 03 '10 at 17:44
3

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

DataNucleus
  • 15,497
  • 3
  • 32
  • 37
0

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);
OpenSource
  • 2,217
  • 2
  • 21
  • 22