Well this will depend where your transactions are going.
What do you mean by "where your transactions are going?".
I'm using a UserTransaction from a servlet to demarcate transactions, i.e. with tx.begin() and tx.commit().
If a bug occurs relating to a SQL statement, then an exception is thrown and i'd like the db connection to be put back into a useable state, ready for the next request.
I meant where you are executing the transactions, in your case in a servlet. (My method above for example might be used in a Session Bean).
Are you getting this db connection via jboss's connection pool or straight lookup?
I'm doing the following:
1. Obtain UserTransaction InitialContext.lookup("java:comp/UserTransaction"). This is called from a servlet.
2. call begin on UserTransaction
3. call various business logic that calls BMP Entity beans directory -- i.e. no SLSB facade in between. The entity beans obtain a DataSource first to get a db transaction.
4. call commit on UserTransaction
If an error happens in step 3., I had previously called setRollbackOnly and skipped step 4. The database transaction was then in a usable state, i.e. it has been rolled back in the DBMS and the app server also seemed to be ok.
I've done a bit more reading and have found notes saying that you're only supposed to use container managed transactions with entity beans.
Based on that info, I removed the UserTransaction code, and made sure by BMP Entity beans were using "Required" as their transaction attribute.
I then repeated the test.
This time, the same business logic error happened of course but two differences occurred:
a) Every call to an Entity bean seemed to result in a new database transaction, instead of previously, all calls within the scope of the tx.begin/tx.commit being in a database transaction.
b) At least now the db transaction was left in a usable state, i.e. rolled back in the database, BUT i'm screwed because not all sql statements were rolled back and so the database is left in an inconsistent state.
I could imagine that creating a truckload of SLSBs and wrapping my calls to the Entity beans with SLSBs might solve the problem but i'm hoping i don't have to do this.
Is anyone able to help me shed light on this?