[ http://issues.apache.org/jira/browse/DERBY-421?page=comments#action_12314959 ] 

Mamta A. Satoor commented on DERBY-421:
---------------------------------------

I did some debugging on this bug and here is the reason behind the current behavior.

When SQL "SET CURRENT ISOLATION = newValue" is used in XA environment, outside the global transaction, it ends up calling GenericLanguageConnectionContext.setTransactionIsolation(). 

When JDBC api Connection.setTransactionIsolation is used, in XA environment, outside the global transaction, it calls BrokeredConnection.setTransactionIsolation(), which looks as follows
	public final void setTransactionIsolation(int level) throws SQLException 
	{
		try {
			getRealConnection().setTransactionIsolation(level);
			stateIsolationLevel = level;
		} catch (SQLException sqle) {
			notifyException(sqle);
			throw sqle;
		}
	}
Just like in the case of sql processing, getRealConnection().setTransactionIsolation(level) ends up in GenericLanguageConnectionContext.setTransactionIsolation(). And so the behavior with JDBC api and SQL is the same so far. But with JDBC api, the additional step of setting stateIsolationLevel in BrokeredConnection to the new isolation level happens but this step is missing when using SQL. Later, when global transaction is started, in EmbedXAConnection.start(), the value of stateIsolationLevel gets used but this variable does not have updated value when coming through the SQL "SET CURRENT ISOLATION = newValue".

What I don't understand is why do we have to keep the isolation level and readonly information is BrokeredConnection.java. We need to keep autocommit and holdability information because they get changed by Derby inside the global transaction to false and CLOSE_CURSORS_ON_COMMIT and we need to restore the original values when the connection is out of the global transaction. But why do we have to keep the state information for isolation level and readonly. I think once I understand this, I will be able to make more progress on the bug.



> starting an XA transaction resets the isolation level set with SET CURRENT ISOLATION
> ------------------------------------------------------------------------------------
>
>          Key: DERBY-421
>          URL: http://issues.apache.org/jira/browse/DERBY-421
>      Project: Derby
>         Type: Sub-task
>     Reporter: Kathey Marsden

>
> When an XA Transaction is started the isolation level set with SET CURRENT ISOLATION gets reset to CS.
> Embedded setTransactionIsolation  does not have this problem but this problem is the root cause of DERBY-414 because client implements setTransactionIsolation by sending SET CURRENT ISOLATION
> $ java TestSetCurrentIsolation
> Database product: Apache Derby
> Database version: 10.2.0.0 alpha
> Driver name:      Apache Derby Embedded JDBC Driver
> Driver version:   10.2.0.0 alpha
> SET CURRENT ISOLATION = UR
> CURRENT ISOLATION: UR
> getTransactionIsolation:TRANSACTION_READ_UNCOMMITTED:1
> Isolation level after xa start
> CURRENT ISOLATION: CS
> getTransactionIsolation:TRANSACTION_READ_COMMITTED:2
> $
> import java.sql.*;
> import javax.sql.*;
> import javax.transaction.xa.*;
> public class TestSetCurrentIsolation
> {
>     public static void main(String[] args) throws Throwable
>     {
>         try
>         {
>              final org.apache.derby.jdbc.EmbeddedXADataSource ds =
>              new org.apache.derby.jdbc.EmbeddedXADataSource();
>              ds.setDatabaseName("C:\\drivers\\derby\\databases\\SCHEDDB");
>              ds.setUser("dbuser1");
>              ds.setPassword("******");
>             XAConnection xaConn = ds.getXAConnection();
>             Connection conn = xaConn.getConnection();
>             conn.setAutoCommit(true);
>             System.out.println("Database product: " + conn.getMetaData().getDatabaseProductName());
>             System.out.println("Database version: " + conn.getMetaData().getDatabaseProductVersion());
>             System.out.println("Driver name:      " + conn.getMetaData().getDriverName());
>             System.out.println("Driver version:   " + conn.getMetaData().getDriverVersion());
>             Statement stmt = conn.createStatement();
>             System.out.println("SET CURRENT ISOLATION = UR");
>             stmt.executeUpdate("SET CURRENT ISOLATION = UR");
>             showIsolationLevel(conn);
>             conn.setAutoCommit(false);
>             XAResource xaRes = xaConn.getXAResource();
>             Xid xid = new TestXid(1,(byte) 32, (byte) 32);
>             xaRes.start(xid, XAResource.TMNOFLAGS);
>             System.out.println("Isolation level after xa start");
>             showIsolationLevel(conn);
>             
>             xaRes.end(xid, XAResource.TMSUCCESS);
>             xaRes.rollback(xid);
>             conn.close();
>             xaConn.close();
>         }
>         catch (SQLException sqlX)
>         {
>             System.out.println("Error on thread 1.");
>             do sqlX.printStackTrace();
>             while ((sqlX = sqlX.getNextException()) != null);
>         }
>         catch (Throwable th)
>         {
>             System.out.println("Error on thread 1.");
>             do th.printStackTrace();
>             while ((th = th.getCause()) != null);
>         }
>     }
> 	/**
> 	 * @param conn
> 	 * @throws SQLException
> 	 */
> 	private static void showIsolationLevel(Connection conn) throws SQLException {
> 		PreparedStatement ps = conn.prepareStatement("VALUES CURRENT ISOLATION");
> 		ResultSet rs = ps.executeQuery();
> 		//ResultSet rs = conn.createStatement().executeQuery("VALUES CURRENT ISOLATION");
> 		rs.next();
> 		System.out.println("CURRENT ISOLATION: " +  rs.getString(1));
> 		System.out.println("getTransactionIsolation:" + 
> 					getIsoLevelName(conn.getTransactionIsolation()));						
> 	}
> 	
> 	public static String getIsoLevelName(int level)
> 	{
> 		switch (level) {
> 			case java.sql.Connection.TRANSACTION_REPEATABLE_READ:
> 				return "TRANSACTION_REAPEATABLE_READ:" + level;
> 					
> 			case java.sql.Connection.TRANSACTION_READ_COMMITTED:
> 				return "TRANSACTION_READ_COMMITTED:" + level;
> 			case java.sql.Connection.TRANSACTION_SERIALIZABLE:
> 				return "TRANSACTION_SERIALIZABLE:" + level;
> 			case java.sql.Connection.TRANSACTION_READ_UNCOMMITTED:
> 				return "TRANSACTION_READ_UNCOMMITTED:" + level;
> 		}
> 		return "UNEXPECTED_ISO_LEVEL";
> 	}
> }

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira