db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kmars...@apache.org
Subject svn commit: r391369 - in /db/derby/code/trunk/java: client/org/apache/derby/client/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/ testing/org/apache/derbyTesting/functionTests...
Date Tue, 04 Apr 2006 17:17:36 GMT
Author: kmarsden
Date: Tue Apr  4 10:17:34 2006
New Revision: 391369

URL: http://svn.apache.org/viewcvs?rev=391369&view=rev
Log:
DERBY-1144 With client PooledConnection.getConnection() does not reset holdability and isolation
state properly


This patch addresses this issue by changing PooledConnection.getConnection() to reset the
connection to the datasource defaults.

Code changes:
Just a small code change in ClientPooledConnection to pass true to the reset method to reset
the defaults.

This change has the side effect of making DERBY- 1173 harder to reproduce. But I don't think
it fixes that bug. With the change, the checkDataSource test hangs only very intermittently.
The fixer of that bug will have to go back to a previous version to get a easily reproducable
case.


Tests -
Changed the checkDataSource30.java test to test holdability,isolation, and autocommit reset.



Modified:
    db/derby/code/trunk/java/client/org/apache/derby/client/ClientPooledConnection.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/checkDataSource30.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/checkDataSource30.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/checkDataSource30.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/copyfiles.ant

Modified: db/derby/code/trunk/java/client/org/apache/derby/client/ClientPooledConnection.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/client/org/apache/derby/client/ClientPooledConnection.java?rev=391369&r1=391368&r2=391369&view=diff
==============================================================================
--- db/derby/code/trunk/java/client/org/apache/derby/client/ClientPooledConnection.java (original)
+++ db/derby/code/trunk/java/client/org/apache/derby/client/ClientPooledConnection.java Tue
Apr  4 10:17:34 2006
@@ -172,11 +172,14 @@
             }
             createLogicalConnection();
 
+            
             if (!newPC_) {
-                physicalConnection_.reset(logWriter_, user_, password_, ds_, false); // false
means do not recompute
+            	// DERBY-1144 changed the last parameter of this method to true
+            	// to reset the connection state to the default on 
+            	// PooledConnection.getConnection() otherwise the 
+            	// isolation level and holdability was not correct and out of sync with the
server.
+                physicalConnection_.reset(logWriter_, user_, password_, ds_, true);
             }
-            // properties from the dataSource
-            // properties don't change
             else {
                 physicalConnection_.lightReset();    //poolfix
             }

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/checkDataSource30.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/checkDataSource30.out?rev=391369&r1=391368&r2=391369&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/checkDataSource30.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/checkDataSource30.out
Tue Apr  4 10:17:34 2006
@@ -38,4 +38,22 @@
 Expected SQLException Invalid operation: result set closed
 Set connection to hold 
 CONNECTION(held) HOLDABILITY true
+**Test holdability state for: PooledConnection **
+PASS: Holdability matches expected holdability:HOLD_CURSORS_OVER_COMMIT
+** Test autoCommit state for: PooledConnection**
+PASS: autoCommit reset on getConnection
+done creating  table
+*** Test isolation level reset on PooledConnection.getConnection()***
+setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED
+PASS: No lock timeout occurs for READ_UNCOMMITTED
+Get a new connection with PooledConnection.getConnection()
+Isolation level should be reset to READ_COMMITTED
+PASS: Expected lock timeout for READ_COMMITTED
+done creating  table
+*** Test isolation level reset on XAConnection.getConnection()***
+setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED
+PASS: No lock timeout occurs for READ_UNCOMMITTED
+Get a new connection with XAConnection.getConnection()
+Isolation level should be reset to READ_COMMITTED
+PASS: Expected lock timeout for READ_COMMITTED
 Completed checkDataSource30

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/checkDataSource30.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/checkDataSource30.out?rev=391369&r1=391368&r2=391369&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/checkDataSource30.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/checkDataSource30.out
Tue Apr  4 10:17:34 2006
@@ -874,6 +874,24 @@
 Expected SQLException (local CallableStatement hold) Cannot set holdability ResultSet.HOLD_CURSORS_OVER_COMMIT
for a global transaction.
 CONNECTION(held) HOLDABILITY true
 PASS XA HOLDABILITY TEST
+**Test holdability state for: PooledConnection **
+PASS: Holdability matches expected holdability:HOLD_CURSORS_OVER_COMMIT
+** Test autoCommit state for: PooledConnection**
+PASS: autoCommit reset on getConnection
+done creating  table
+*** Test isolation level reset on PooledConnection.getConnection()***
+setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED
+PASS: No lock timeout occurs for READ_UNCOMMITTED
+Get a new connection with PooledConnection.getConnection()
+Isolation level should be reset to READ_COMMITTED
+PASS: Expected lock timeout for READ_COMMITTED
+done creating  table
+*** Test isolation level reset on XAConnection.getConnection()***
+setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED
+PASS: No lock timeout occurs for READ_UNCOMMITTED
+Get a new connection with XAConnection.getConnection()
+Isolation level should be reset to READ_COMMITTED
+PASS: Expected lock timeout for READ_COMMITTED
 Checked class declared as: java.sql.CallableStatement
 Checked class declared as: java.sql.Connection
 Checked class declared as: java.sql.DatabaseMetaData

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/checkDataSource30.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/checkDataSource30.java?rev=391369&r1=391368&r2=391369&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/checkDataSource30.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/checkDataSource30.java
Tue Apr  4 10:17:34 2006
@@ -30,6 +30,8 @@
 import java.sql.Statement;
 import java.util.Properties;
 
+import javax.sql.ConnectionPoolDataSource;
+import javax.sql.PooledConnection;
 import javax.sql.XAConnection;
 import javax.sql.XADataSource;
 import javax.transaction.xa.XAException;
@@ -60,10 +62,12 @@
 			tester.runTest(args);
 		tester.checkXAHoldability();
 		
+		testDerby1144();
 		// Print a report on System.out of the issues
 		// found with the security checks.
 		SecurityCheck.report();
 		
+		
 		System.out.println("Completed checkDataSource30");
 
 	}
@@ -433,4 +437,283 @@
     {
         return "checkDataSource30.checkNesConn30";
     }
+    
+    
+    /**
+     * Tests for DERBY-1144
+     * 
+     * This test tests that holdability, autocomit, and transactionIsolation are
+     * reset  on getConnection for PooledConnections obtaind from connectionPoolDataSources

+     * 
+     * DERBY-1134 has been filed for more comprehensive testing of client connection state.

+     * 
+     * @throws SQLException
+     */
+    public static void testDerby1144() throws SQLException
+    {
+    	Connection conn = null;
+    	PooledConnection pc1 = null;
+		Properties p = new Properties();
+		
+		p.put("databaseName","sample");
+		p.put("connectionAttributes","create=true");
+		p.put("user","APP");
+		p.put("password","pw");
+		
+        // Test holdability   
+        ConnectionPoolDataSource ds = TestUtil.getConnectionPoolDataSource(p);
+        pc1 = ds.getPooledConnection();
+        testPooledConnHoldability("PooledConnection", pc1);
+        pc1.close();
+        
+        // Test autocommit
+        pc1 = ds.getPooledConnection();
+        testPooledConnAutoCommit("PooledConnection", pc1);
+        pc1.close();
+        
+        // Test pooled connection isolation
+		ds = TestUtil.getConnectionPoolDataSource(p);
+		pc1 = ds.getPooledConnection();
+		testPooledConnIso("PooledConnection" , pc1);   
+        pc1.close();
+       
+        // Test xa connection isolation
+		XADataSource xds = TestUtil.getXADataSource(p);
+    	XAConnection xpc1 = xds.getXAConnection();        
+        testPooledConnIso("XAConnection", xpc1);                 
+        xpc1.close();
+      }
+  
+    
+	/**
+	 * Make sure autocommit gets reset on PooledConnection.getConnection()
+	 * @param desc		description of connection
+	 * @param pc1		pooled connection to test
+	 * @throws SQLException
+	 */
+	private static void testPooledConnAutoCommit(String desc, PooledConnection pc1) throws SQLException
{
+		System.out.println("\n** Test autoCommit state for: " + desc + "**");
+		Connection conn  = pc1.getConnection();
+		conn.setAutoCommit(true);
+		// reset the connection and see if the autocommit 
+		conn = pc1.getConnection();
+		boolean autocommit  = conn.getAutoCommit();
+		if (autocommit != true)
+		{
+			new Exception("FAIL: autoCommit not reset on getConnection").printStackTrace(System.out);
		
+		}
+		else 
+		{
+			System.out.println("PASS: autoCommit reset on getConnection");
+		}
+		conn.close();
+	}
+
+
+	/**
+	 * Test Holdability gets reset on PooledConnection.getConnection()
+	 * @param desc
+	 * @param pc1
+	 * @throws SQLException
+	 */
+	private static void testPooledConnHoldability(String desc, PooledConnection pc1) 
+	throws SQLException { 
+		System.out.println("\n**Test holdability state for: " + desc + " **");
+		Connection conn  = pc1.getConnection();
+		conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
+		// reset the connection and see if the holdability gets reset
+		// to HOLD_CURSORS_OVER_COMMIT
+		conn = pc1.getConnection();
+		checkConnHoldability(conn, ResultSet.HOLD_CURSORS_OVER_COMMIT);
+		conn.close();
+	}
+	
+
+
+	/**
+	 * Verify connection holdablity is expected holdability
+	 * @param conn
+	 * @param expectedHoldability 
+	 * 	 * @throws SQLException
+	 */
+	private static void checkConnHoldability(Connection conn, 
+				int expectedHoldability) throws SQLException {
+		int holdability = conn.getHoldability();
+		if (holdability != expectedHoldability)
+		{
+			new Exception("FAIL: Holdability:" + translateHoldability(holdability) +
+					" does not match expected holdability:" +
+						translateHoldability(expectedHoldability)).printStackTrace(System.out);			
+		}
+		else 
+		{
+			System.out.println("PASS: Holdability matches expected holdability:" +
+					translateHoldability(expectedHoldability));
+		}
+	}
+
+
+	/**
+	 * Test that isolation is reset on PooledConnection.getConnection()
+	 * @param pooledConnType   Descripiton of the type of pooled connection
+	 * @param pc
+	 * @throws SQLException
+	 */
+	private static void testPooledConnIso(String pooledConnType, PooledConnection pc)
+	 throws SQLException {
+			 Connection conn = pc.getConnection();
+             
+			 setupDerby1144Table(conn);
+			 System.out.println("*** Test isolation level reset on " + pooledConnType+ ".getConnection()***");;
		 
+			 System.out.println("\nsetTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED");
+			 conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
+			 checkIsoLocks(conn,Connection.TRANSACTION_READ_UNCOMMITTED);
+			 
+			 conn.close();
+			 System.out.println("\nGet a new connection with " + pooledConnType+ ".getConnection()");
+			 System.out.println("Isolation level should be reset to READ_COMMITTED");
+			 Connection newconn = pc.getConnection();
+			 checkIsoLocks(newconn,Connection.TRANSACTION_READ_COMMITTED);
+			 
+      
+	}
+
+
+	/**
+	 * Make a simple table for DERBY-1144 tests
+	 * @param conn
+	 * @throws SQLException
+	 */
+	private static void  setupDerby1144Table(Connection conn) throws SQLException
+	{
+		Statement stmt = conn.createStatement();
+		try {
+			stmt.executeUpdate("DROP TABLE TAB1");
+		}
+		catch (SQLException e)
+		{
+			// ignore drop error
+		}
+		stmt.executeUpdate("CREATE TABLE TAB1(COL1 INT NOT NULL)");
+		stmt.executeUpdate("INSERT INTO TAB1 VALUES(1)");
+		stmt.executeUpdate("INSERT INTO TAB1 VALUES(2)");
+
+        System.out.println("done creating  table");
+             conn.commit ();
+    }
+
+
+	
+	
+	/* 
+	 * Checks locks for designated isolation level on the connection.
+	 * Currently only supports TRANSACTION_READ_COMMITTED and 
+	 * TRANSACTION_READ_UNCOMMITTED
+	 * @param conn   Connection to test
+	 * @param isoLevel expected isolation level
+	 *
+	 */
+	private static void checkIsoLocks(Connection conn, int expectedIsoLevel)
+	{
+		try {
+			int conniso = conn.getTransactionIsolation();
+			if (conniso !=  expectedIsoLevel)
+			{
+				new  Exception("FAIL: Connection isolation level " + 
+						translateIso(conniso) + 
+						" does not match expected level " +
+						translateIso(expectedIsoLevel));
+			}			
+
+			boolean selectTimedOut  = selectTimesoutDuringUpdate(conn);
+			switch (conniso) {
+				case Connection.TRANSACTION_READ_UNCOMMITTED:
+					if (selectTimedOut)
+						new Exception("FAIL: Unexpected lock timeout for READ_UNCOMMITTED").printStackTrace(System.out);
+					else
+						System.out.println("PASS: No lock timeout occurs for READ_UNCOMMITTED");
+					case Connection.TRANSACTION_READ_COMMITTED:
+					if (selectTimedOut)	
+						System.out.println("PASS: Expected lock timeout for READ_COMMITTED");
+					else
+						new Exception("FAIL: Did not get lock timeout for READ_COMMITTED");
+					default:
+						new Exception("No test support for isolation level" + 
+									translateIso(conniso));
+			}
+		} catch (SQLException se) {
+			se.printStackTrace();	
+		}
+	}
+	
+	/**
+	 * Determine if a select on this connection during update will timeout.
+	 * Used to establish isolation level.  If the connection isolation level
+	 * is <code> Connection.TRANSACTION_READ_UNCOMMITTED </code> it will not
+	 * timeout.  Otherwise it should.  
+	 * 
+	 * @param conn   Connection to test.
+	 * @return  true if the select got a lock timeout, false otherwise.
+	 */
+	private static boolean selectTimesoutDuringUpdate(Connection conn) 	{
+	 Connection updateConn = null;
+	
+		try {
+		
+		conn.setAutoCommit(false);
+		// create another connection and do an update but don't commit
+		updateConn = TestUtil.getConnection("sample","create=true");
+		updateConn.setAutoCommit(false);
+		
+		
+		// First update the rows on the update connection
+		Statement upStmt = updateConn.createStatement();
+		upStmt.executeUpdate("update tab1 set col1 = 3");
+
+		// now see if we can select them
+
+		Statement stmt = conn.createStatement();
+		ResultSet rs = stmt.executeQuery("Select * from tab1");
+		while (rs.next()){};
+		rs.close();
+
+		}
+		catch (SQLException e)
+		{
+			if (e.getSQLState().equals("40XL1"))
+			{
+				// If we got a lock timeout this is not read uncommitted
+				return true;
+			}	
+
+		}
+		finally {
+
+			try {
+				conn.rollback();
+				updateConn.rollback();
+			}catch (SQLException  se) {
+				se.printStackTrace();
+			}
+		}
+		return false;
+		
+	}
+	
+	/**
+	 * Translate holdability int readable format.
+	 * 
+	 * @param holdability   holdability to translate.
+	 * @return  "HOLD_CURSORS_OVER_COMMIT" or "CLOSE_CURSORS_AT_COMMIT"
+	 */
+	public static String translateHoldability(int holdability)
+	{
+		switch (holdability)
+		{
+			case ResultSet.HOLD_CURSORS_OVER_COMMIT : return  "HOLD_CURSORS_OVER_COMMIT";
+			case ResultSet.CLOSE_CURSORS_AT_COMMIT : return  "CLOSE_CURSORS_AT_COMMIT";
+		}
+		return "UNKNOWN_HOLDABILTY";
+	}    
+
 }

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/copyfiles.ant
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/copyfiles.ant?rev=391369&r1=391368&r2=391369&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/copyfiles.ant
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/copyfiles.ant
Tue Apr  4 10:17:34 2006
@@ -7,6 +7,7 @@
 bestrowidentifier.sql
 bestrowidentifier_app.properties
 checkDataSource30_app.properties
+checkDataSource30_derby.properties
 checkDriver_app.properties
 checkDriver_derby.properties
 dataSourceReference_app.properties



Mime
View raw message