BMT Rollback Issue

[g Ven]

Hi

I have a BMT (SLSB) wherein the code looks like:

//Get utx from session context (I have also tried getting it from a Context lookup)
try
{
utx.begin();
<dml st1>
<dml st2>
utx.commit();
utx.begin();
<dml st3>
<dml st4>
utx.commit();
}
catch(....)
{
utx.rollback();
}

st4 raises an exception which gets trapped but the changes by st3 is not getting rolled back. I am using Weblogic 9.2.

I tried getting a hook onto the transaction using weblogic.transaction.Transaction and printing the transaction object. The transaction object is unique after a begin and consistent until a commit or rollback is encountered and on a subsequent begin is unique again (on commit/rollback the transaction object is null). I have also tried printing the transaction status. The transaction status is 0 (or active) after transaction has begun and is 6 after rollback.

Can somebody help?

Thanks

[Roger Chung-Wee]

Did you obtain a UserTransaction object and begin a transaction before you obtained a JDBC database connection?

[g Ven]

I have obtained the conection before the creation of user Transaction object.

[Roger Chung-Wee]

This is why you have a problem. If you start a transaction after obtaining a JDBC connection, the connection has no relationship to the transaction. What you have is a JDBC transaction which automatically commits to the DB.

If you start a transaction before obtaining a JDBC connection, the connection is associated with the transaction context until the transaction commits or rolls back.

[g Ven]

I just tried testing the same by writing a sample client program as:
(Client side transaction)
//Get utx by doing a jndi lookup on Weblogic server

utx.begin();

//Create a DB connection (raw jdbc connection using Class.forName(..) .....)

//update stmt a
//update stmt b

utx.commit();
utx.begin();
//update stmt c
utx.rollback();

When i look at the db i see update statement c's changes.

The above scenario should not be any different from executing the same on the server. The underlying DB is teradata.
I have tried the same program with the connection object being created both before and after the "utx.begin()" at the start of program - same result

[Roger Chung-Wee]

You are getting your connection in the wrong way. You must obtain an InitialContext, do a JNDI lookup for the DataSource and then get the connection from the DataSource.

You will almost certainly want connection pooling. The DataSource also contains a pool of database connections which is created when the DataSource is created at server startup.

[g Ven]

The error was occurring because an attempt was made to use a connection object that was "shared" across transactions. When i open a new connection object in every transaction that is begun then the rollback works just fine.

The code in a nutshell looks like:

utx.begin();
//Get DB connection from DS
//dml st1
//dml st2
//Close connection
utx.commit();
utx.begin();
//Get DB connection from DS
//dml st3
//dml st4
utx.rollback();

the rollback happens to the first savepoint which is the update by st2.
(Also the mode of database connection retrieval is incorrect as i was incorrectly trying to use a raw db connection as against going thru a resource manager which was possibly causing the incorrect results - i tried using raw jdbc connections , opening it afresh each time under each transaction which failed too)

Thanks Roger !!!