Hello,

I am really confused about using transactions and MySQL when using the Hibernate MBean Service and JTA. If I execute the code below after the rollback nothing happens and the object is still present within the database. I have also tried to use Hibernate's transaction functions liek:

Transaction tx = session.beginTransaction();
//Create Object
tx.rollback();

and the debug output just adds some lines saying it got the UserTransaction and then it says rollback but still nothing actually happens. I noticed that auto commit was turned on for the connection. I saw in a couple of places where it stated that hibernate would automatically turn off autocommit on any connection that it gets except when the user passes in the connection. If I'm using a JCA bound data source is this still true or do I have to call setAutCommit(False)? However this still has no effect upon if I can rollback or not.

I can get it to work correctly if I mess around with the connection directly. Like if I do:

sess.connection.autoCommit(false);
//Create object call save
sess.connection().rollback();

then the database does rollback and the changes are reverted. However I don't think this is the proper way of doing it since I thought inside of a container I would want to use JTA to manage transactions and that it would then be responsible for calling the rollback on the individual connections.

Thanks,
Eric

Hibernate version:
Hibernate 2.1 deployed as a SAR in JBoss 3.2.1 connecting to a JCA connection to a MySQL 4.0 database with InnoDB tables.

Mapping documents:
Simple test class with an ID and value attribute. The ID is generated using the native method. There is a collection associate with the class but it is not used in the test code.

Code between sessionFactory.openSession() and session.close():
Currently testing as a member function that is a SOAP service exposed via apache axis's EJB provider. There is a Servlet Filter installed that creates a Hibernate Session per request and stores it in a thread local that we then use. The EJB is defined as a Bean Managed Transaction bean so I can use the UserTransaction object that is obtained from the session context. The actual code is:

UserTransaction Tx = ctx.getUserTransaction();
tx.begin();
sess.connection().setAutoComit(false);
Session sess = HibSessionContainer.getSession();
BaseClassA testClass = new BaseClassA();
testClass.setVale("Hello World");
sess.saveOrUpdate(testClass);
tx.rollback();

Full stack trace of any exception that occurs:
No Exceptions

Name and version of the database you are using:
MySql 4.0 with Innodb Tables

Debug level Hibernate log excerpt:
(Note I added the out put to some calls to tx.getStatus)

16:18:34,668 DEBUG [SOAPTestBean] Transaction status: 0
16:18:34,688 DEBUG [SOAPTestBean] Autocommit mode is set to: true
16:18:34,688 DEBUG [SOAPTestBean] Autocommit mode is set to: false
16:18:34,688 DEBUG [SessionImpl] saveOrUpdate() unsaved instance
16:18:34,698 DEBUG [SessionImpl] saving [edu.jhu.ccbm.pathway.data.test.BaseClas
sA#<null>]
16:18:34,698 DEBUG [Cascades] processing cascades for: edu.jhu.ccbm.pathway.data
.test.BaseClassA
16:18:34,698 DEBUG [Cascades] done processing cascades for: edu.jhu.ccbm.pathway
.data.test.BaseClassA
16:18:34,708 DEBUG [EntityPersister] Inserting entity: edu.jhu.ccbm.pathway.data
.test.BaseClassA (native id)
16:18:34,708 DEBUG [BatcherImpl] about to open: 0 open PreparedStatements, 0 ope
n ResultSets
16:18:34,718 DEBUG [BatcherImpl] prepared statement get: insert into SktchBaseCl
assA (val) values (?)
16:18:34,718 DEBUG [BatcherImpl] preparing statement
16:18:34,718 DEBUG [EntityPersister] Dehydrating entity: [edu.jhu.ccbm.pathway.d
ata.test.BaseClassA#<null>]
16:18:34,748 DEBUG [StringType] binding 'HELLO WORLD' to parameter: 1
16:18:34,748 DEBUG [BatcherImpl] done closing: 0 open PreparedStatements, 0 open
ResultSets
16:18:34,778 DEBUG [BatcherImpl] closing statement
16:18:34,778 DEBUG [BatcherImpl] about to open: 0 open PreparedStatements, 0 ope
n ResultSets
16:18:34,778 DEBUG [BatcherImpl] prepared statement get: SELECT LAST_INSERT_ID()

16:18:34,788 DEBUG [BatcherImpl] preparing statement
16:18:34,788 DEBUG [EntityPersister] Natively generated identity: 14
16:18:34,798 DEBUG [BatcherImpl] done closing: 0 open PreparedStatements, 0 open
ResultSets
16:18:34,798 DEBUG [BatcherImpl] closing statement
16:18:34,798 DEBUG [Cascades] processing cascades for: edu.jhu.ccbm.pathway.data
.test.BaseClassA
16:18:34,808 DEBUG [Cascades] done processing cascades for: edu.jhu.ccbm.pathway
.data.test.BaseClassA
16:18:34,808 DEBUG [SOAPTestBean] After save Transaction status: 0
16:18:34,818 DEBUG [SOAPTestBean] After rollback Transaction status: 6

Are you doing this within an EJB? Do you _really_ use InnoDB tables?

Wow thanks for the quick response.

Yes this was done inside of an EJB I just tried messing around with the transaction attributes in the deployment file to set it "Transaction Required", although this doesn't seem to be helping the situation. I've also tried doing this outside of an EJB just in a simple test servlet with the same code and it had the same behavior.

I am certain I am using InnoDB because I am able to execute the same code with a direct JDBC connection in an outside test program using the setAutoCommit(False) and then rollback methods with the correct results so I am pretty sure the underlying database supports transactions.

Is there anything that I need to configure to get hibernate and the JDBC connection to be a part of the UserTransaction ? In the jboss-service file for the sar I have the UserTransactionName set to UserTransaction, TransactionStrategy set to JTATransactionFactory and TransactionManagerLookupStrategy set to JBossTransactionManagerLookup. For the JCA connection to mysql I just edited the template file distributed with jboss. Past that is there any other things that I need to configure to get the mysql connections that are created to be a part of the UserTransactions?

I have turned on tracing of the JBoss transaction manager and do see that the TransactyionImpl's rollback method is being called. I am wondering if the connection is a member of the UserTransaction however (I don't know if this is even possible).

One thing that I have noticed while trying to find the problem is that if I use a debugger to look at the UserTransaction object (in the code that uses that) and the contained TransactionImpl object the resource count is set to 0. However I think this might be related to XA Resources (which the mysql DS isn't its a local-tx-datasource) so this might not mean anything at all.

Unfortunately I am very unfamiliar with JTA and JCA so am sorta just poking around trying to see if I can find the problem.

Thanks again for your help! I really appreciate it.

I just did a little bit more debugging and am thinking that the problem is with getting the connections that hibernate is getting from the connection manager enlisted within the user transaction. However I am still lost because if I run the servlet version of the code that shoudn't have any pre-existing UserTransactions I still get the same behavior.

When I call beingTransaction in the servlet code it creates a new ServerVMClientUserTransaction that the JTATransaction holds on to. When I call rollback I see that the rollback method of that jboss.tm.TransactionImpl is getting invoked however nothing seems to be happening.

If within my EJB I first call ctx.getUserTransaction and tx.begin and then get the datasource via JNDI and create a new connection manually I notice that the connection manager automatically enlists the the connection with the transaction manager and then when I call rollback on the usertransaction it work correctly. However I don't see this behavior either in the servlet code when I call session.beginTransaction (where there isn't already a transaction created)or in the EJB code (where a user transaction was already created).

I have found that if within the EJB code I do the following it works:

UserTransaction tx = ctx.getUserTx();
tx.begin();

SessionFactory factory = JNDI LOOKUP CODE
sess = factory.openSession();
//Create objects
tx.rollBack()
sess.close

However this woudln't work very well in my situation because I'm using the Open Session in View pattern so I have a servlet filter that opens the session and stores it in a threadlocal. That I then retrieve inside of my ejbs or anywhere else. I am guessing the problem with using UserTransaction is that in my original code the hibernate session and its connection were getting created before the user transaction existed. However I woudl think that it woudl still get bound if I called session.beingTransaction() within the ejb code after I called UserTransaction.begin() however this is not the case. If I do this the rollback still does not occur when I called rollback on the usertransaction. Also I don't think any of this provides any clues as to why it also doesn't work in the servlet case.

Once again any ideas would be greatly appreciated!

Thanks,
Eric