persistence@glassfish.java.net

JPA - delete rows via createNativeQuery is not honoring transaction

From: JPA Novice <rajasethuraman_at_gmail.com>
Date: Fri, 3 Oct 2008 22:55:20 -0700 (PDT)

I am new to JPA style of JDBC coding. Thanks for your help in advance.

The following piece of code do not roll back the transaction. (Please also
check all the things I already tried during my trouble shooting)

My environment is TopLink 2.0.1, MySQL 5.1.24, Glass Fish 9.1_02,

*The table in question is using InnoDB engine which supports transaction.*

// emf is an instance of my persistence factory.
EntityManager em = emf.createEntityManager();
try {
          em.setFlushMode(FlushModeType.COMMIT);
          em.getTransaction().begin();
 
           // before this query is executed, table user_group has 2 rows
with group_code = 'testgroupcode'
          String deleteQueryStr = "DELETE FROM user_group WHERE group_code =
?";
          Query deleteQuery = em.createNativeQuery(deleteQueryStr );
          deleteQuery.setParameter(1, 'testgroupcode');
          deleteQuery.executeUpdate();

         // deliberately throwing exception to test rollback
          String abc = null;
          if (abc == null) throw new Exception("testing rollback...");

          em.getTransaction().commit();
}catch(Exception ex) {
         em.getTransaction().rollback();
         // log exception
}finally {
          em.close();
}


We have some kind of project mandate to use createNativeQuery in this query.
I know that I can use em.remove(obj). But I can't use that.

Things I already tried...
              1. Tried changing Glass Fish connection pool -> Transaction
Isolation to 'Serializable'
              2. Tried applying FlushModeType as COMMIT at query level
              3. Tried setting elideSetAutoCommits property of connection
pool to true
              4. Tried setting useLocalSessionState property of connection
pool to true
              5. Tried setting autoCommit property of connection pool to
false

None of the above helped in any way.

Is there something specific about create native query that commits without
honoring transaction ?

Thanks in advance
-- 
View this message in context: http://www.nabble.com/JPA---delete-rows-via-createNativeQuery-is-not-honoring-transaction-tp19809328p19809328.html
Sent from the java.net - glassfish persistence mailing list archive at Nabble.com.