XA transactions and state information

[Mamta Satoor <ms...@gmail.com>]

Hi,
 From Dan's explanation on Derby-421 (starting an XA transaction resets the 
isolation level set with SET CURRENT ISOLATION) about various transaction 
states, what I understand is that local transaction and global transaction 
have to maintain their own states. I wrote a simple test program to check 
this behavior and it seems like Derby is not doing so. 
 My test program gets a XA connection which is part of the local 
transaction. Derby's default isolation level is CS and that is what the 
connection object has at this point. I change the isolation level to UR 
within the local transaction. Now, the XA connection joins a global 
transaction. I had expected that the isolation level of the connection 
inside the global transaction would be the default isolation level CS but 
that is not the case, instead, it is set to the isolation level UR which is 
what the local transaction had it. Within the global transaction, I change 
the isolation level to RS and then exit the global transaction so the 
connection is now attached to the local transaction. The isolation level at 
this point is set to RS rather than UR. This seems incorrect. Any comments?
 Following is the code snippet
 
EmbeddedXADataSource dscsx = new EmbeddedXADataSource();
dscsx.setDatabaseName("c:/dellater/db1");
XADataSource dsx = dscsx;
XAConnection xac = dsx.getXAConnection();
con = xac.getConnection();
s = con.createStatement();
System.out.println("default isolation level should be CS? " + 
con.getTransactionIsolation());
dumpRS(s.executeQuery("VALUES CURRENT ISOLATION"));
System.out.println("change the isolation level with 
setTransactionIsolation");
con.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
System.out.println("new isolation level should be UR? " + 
con.getTransactionIsolation());
dumpRS(s.executeQuery("VALUES CURRENT ISOLATION"));

XAResource xar = xac.getXAResource();
Xid xid = new cdsXid(1, (byte) 35, (byte) 47);
xar.start(xid, XAResource.TMNOFLAGS);

s = con.createStatement();
System.out.println("isolation level inside global transaction is " + 
con.getTransactionIsolation());
dumpRS(s.executeQuery("VALUES CURRENT ISOLATION"));
System.out.println("change the isolation level inside global transaction 
with setTransactionIsolation");
con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
System.out.println("new isolation level should be RS? " + 
con.getTransactionIsolation());
dumpRS(s.executeQuery("VALUES CURRENT ISOLATION"));

xar.end(xid, XAResource.TMSUCCESS);
xar.rollback(xid);

System.out.println("isolation level outside global transaction should be UR? 
" + con.getTransactionIsolation());
dumpRS(s.executeQuery("VALUES CURRENT ISOLATION"));
thanks,
Mamta

Re: XA transactions and state information

[Daniel John Debrunner <dj...@debrunners.com>]

Mamta Satoor wrote:

> Just to be sure, so it is correct for local connection to pick up the
> global transaction's isolation level in the example scenario below?

>> Within the global
>> transaction, I change the isolation level to RS and then exit the global
>> transaction so the connection is now attached to the local transaction.
>> The isolation level at this point is set to RS rather than UR. This

No, the local connection is not picking up the global transaction's
isolation level. It is reflecting the state of the Connection object.

Isolation level and the other states affected are states of the
Connection object. So the application has stated through the
setTransactionIsolation call that its *Connection* is at RS level. Thus
any subsequent transaction that it *starts* will be at RS level.

This difference comes when the application using this Connection joins
an *existing* global transaction that is at a *different* isolation
level (say RR) to the Connection.  Changing the isolation level of the
transaction to match the Connection (RS) seems wrong and in Derby would
force a commit, which is definitely not the desired behaviour as that
would end the global transaction. So getTransactionIsolation has to
return RR as it would be misleading (and incorrect?) to return RS. But
the Connection object is still at RS, so when the new *local*
transaction is started it will be at RS. I also believe that
setIsolationLevel within a global transaction will throw an exception if
the transaction has performed some work.

A similar but simpler issue exists with auto commit mode, again a state
of the Connection. But in this case, when the Connection object is
attached to a global transaction the auto commit mode must be false by
definition of the JBDC spec. But on reverting to a local transaction the
state of auto-commit reverts to the state that the Connection object had
before attaching the global transaction. This revert behaviour is
defined by the last paragraph of section 12.4 of the JDBC 3.0 spec.

Then the model of defined auto-commit revert behaviour was followed for
the other Connection state, such as isolation level. I think when Derby
was closed source at IBM we worked these issues with the JDBC EG through
the IBM rep.

Dan.

Re: XA transactions and state information

[Mamta Satoor <ms...@gmail.com>]

Just to be sure, so it is correct for local connection to pick up the global 
transaction's isolation level in the example scenario below?
> Within the global
> transaction, I change the isolation level to RS and then exit the global
> transaction so the connection is now attached to the local transaction.
> The isolation level at this point is set to RS rather than UR. This
 thanks,
Mamta
 On 8/3/05, Daniel John Debrunner <dj...@debrunners.com> wrote: 
> 
> Mamta Satoor wrote:
> 
> > Hi,
> >
> > From Dan's explanation on Derby-421 (starting an XA transaction resets
> > the isolation level set with SET CURRENT ISOLATION) about various
> > transaction states, what I understand is that local transaction and
> > global transaction have to maintain their own states. I wrote a simple
> > test program to check this behavior and it seems like Derby is not doing
> > so.
> >
> > My test program gets a XA connection which is part of the local
> > transaction. Derby's default isolation level is CS and that is what the
> > connection object has at this point. I change the isolation level to UR
> > within the local transaction. Now, the XA connection joins a global
> > transaction. I had expected that the isolation level of the connection
> > inside the global transaction would be the default isolation level CS
> > but that is not the case, instead, it is set to the isolation level UR
> > which is what the local transaction had it. Within the global
> > transaction, I change the isolation level to RS and then exit the global
> > transaction so the connection is now attached to the local transaction.
> > The isolation level at this point is set to RS rather than UR. This
> > seems incorrect. Any comments?
> 
> I think the behave you are seeing is correct and consistent with my
> description in 421.
> 
> Any *new* transaction started by the application needs to pick up the
> state of the application's Connection handle, which is what happens in
> this case. The only issue comes when the application connects to an
> *existing* global transaction with a different isolation level (or other
> state).
> 
> Dan.
> 
>

Re: XA transactions and state information

[Daniel John Debrunner <dj...@debrunners.com>]

Mamta Satoor wrote:

> Hi,
>  
> From Dan's explanation on Derby-421 (starting an XA transaction resets
> the isolation level set with SET CURRENT ISOLATION) about various
> transaction states, what I understand is that local transaction and
> global transaction have to maintain their own states. I wrote a simple
> test program to check this behavior and it seems like Derby is not doing
> so.
>  
> My test program gets a XA connection which is part of the local
> transaction. Derby's default isolation level is CS and that is what the
> connection object has at this point. I change the isolation level to UR
> within the local transaction. Now, the XA connection joins a global
> transaction. I had expected that the isolation level of the connection
> inside the global transaction would be the default isolation level CS
> but that is not the case, instead, it is set to the isolation level UR
> which is what the local transaction had it. Within the global
> transaction, I change the isolation level to RS and then exit the global
> transaction so the connection is now attached to the local transaction.
> The isolation level at this point is set to RS rather than UR. This
> seems incorrect. Any comments?

I think the behave you are seeing is correct and consistent with my
description in 421.

Any *new* transaction started by the application needs to pick up the
state of the application's Connection handle, which is what happens in
this case. The only issue comes when the application connects to an
*existing* global transaction with a different isolation level (or other
state).

Dan.