db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-421) starting an XA transaction resets the isolation level set with SET CURRENT ISOLATION
Date Tue, 30 Aug 2005 08:22:07 GMT
    [ http://issues.apache.org/jira/browse/DERBY-421?page=comments#action_12320544 ] 

Mamta A. Satoor commented on DERBY-421:

Kathey, thanks for your explanation of prepareIsolationLevel in GenericLanguageConnectionContext.java.

As for your question, following set of steps hopefully will help understand why the flag needs
to be reset.

Say, the user application has following code.
XAConnection xac = xaDataSource.getXAConnection();
At this point, xac will be EmbedXAConnection object with real database connection EmbedConnection
in it.

The user application can then request a connection handle (BrokeredConnection) from EmbedXAConnection.
Derby will set the state of this connection handle to that of the real database connection.
Connection con = xac.getConnection();

Let's say the local transaction then sets the state using JDBC api
This call will change the isolation level of the real database connection to the new value
and the state of the connection handle also gets set correctly inside BrokeredConnection.setTransactionIsolation

But if the local transaction sets the state using SQL
BrokeredStatement.executeUpdate("set current isolation = RR");
real database connection's isolation level gets set to new value but the state information
in BrokeredConnection doesn't.

Now if the user application says
XAResource.start(xid, XAResource.TMNOFLAGS);
Before starting a global transaction, EmbedXAConnection's start method checks if there is
already a real database connection and a handle to it. If yes, it calls connection handle's
setState(true). (The current Derby code without my changes) sets the state of the real database
connection object to the handle's state. But handle's state is out of sync at this point because
of earlier SQL set current isolation. And hence the state setting onto real connection will
be incorrect. (this is the cause of Derby-421) 

To fix this, I have a call to connection handle's (BrokeredConnection's) getIsolationUptoDate()
method prior to setting the real connection's state to connection handle's state. If the isolation
was changed via JDBC/SQL, the connection handle's state will be brought to the correct value,
and the flag used to keep track of isolation level change will be set to false.

Similar thing needs to happen when the global transaction is ended because isolation level
could have been set using SQL inside the global transaction as shown below.
BrokeredStatement.executeUpdate("set current isolation = UR");
Again, this will update the real database connection's isolation to UR, but BrokeredConnection's
state does not get changed.

To correct the state information of BrokeredConnection, when the global transation is ended
as follows
XAResource.end(xid, XAResource.TMSUCCESS);
EmbedXAConnection's end method checks if the BrokeredConnection is not null. If not null,
it brings the isolation level upto date by calling BrokeredConnection.getIsolationUptoDate
and then resets the flag.

If say, the flag is not reset after fixing the BrokeredConnection's state information during
the global transaction start and end times, then once isolation level is changed in a global
transaction, local transaction will always take the isolation level of last global transaction
that just ended. This is incorrect. Local transaction should take the isolation level of last
global transaction only if the isolation level was changed in that transaction. Following
is the incorrect behavior with no flag resetting:

Start a local transaction. 
	isolation level default CS
Start global transaction 1
	isolation level default CS
	Change isolation level in global transaction 1 to UR(the flag gets set to true at this point)
Exit global transaction 1. Isolation level is local transaction
	isolation level UR
	Change isolation level in local transaction to RS
Start global transaction 2
	isolation level RS
End global transaction 2 and rejoin global transaction 1
	isolation level UR
Exit global transaction 1. The isolation level in local transaction at this point should have
been RS but it got set to UR which is the last global transaction's isolation level. This
happened because the flag was not reset to false at the end of first global transaction when
the isolation was set using JDBC/SQL.

Kathey, I hope this is not too much information and helps answer your question. If I have
missed a scenario in which we could run into problem, let me know.

Also, Dan, I am making couple changes to the code and putting some more comments and will
send a new review package soon(after running the tests) with explanation as to why we need
so many methods.

> 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
>   Components: JDBC
>     Reporter: Kathey Marsden
>     Assignee: Mamta A. Satoor
>  Attachments: derby421XAIsolation082205.txt
> 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
> $ java TestSetCurrentIsolation
> Database product: Apache Derby
> Database version: alpha
> Driver name:      Apache Derby Embedded JDBC Driver
> Driver version: alpha
> getTransactionIsolation:TRANSACTION_READ_UNCOMMITTED:1
> Isolation level after xa start
> 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;
> 		}
> 	}
> }

This message is automatically generated by JIRA.
If you think it was sent incorrectly contact one of the administrators:
For more information on JIRA, see:

View raw message