You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by kr...@apache.org on 2010/10/29 14:22:23 UTC
svn commit: r1028716 - in /db/derby/code/trunk/java:
engine/org/apache/derby/iapi/sql/conn/ engine/org/apache/derby/impl/sql/conn/
engine/org/apache/derby/impl/sql/execute/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: kristwaa
Date: Fri Oct 29 12:22:23 2010
New Revision: 1028716
URL: http://svn.apache.org/viewvc?rev=1028716&view=rev
Log:
DERBY-4849: Re-compilation may cause duplicate entries in the XPLAIN table
Avoid writing XPLAIN data if result set is being closed due to invalidation.
Patch file: derby-4849-2c-broad_fix_with_test.diff
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/conn/StatementContext.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/conn/GenericStatementContext.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/NoPutResultSetImpl.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/NoRowsResultSetImpl.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XplainStatisticsTest.java
Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/conn/StatementContext.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/conn/StatementContext.java?rev=1028716&r1=1028715&r2=1028716&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/conn/StatementContext.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/conn/StatementContext.java Fri Oct 29 12:22:23 2010
@@ -28,16 +28,11 @@ import org.apache.derby.iapi.error.Stand
import org.apache.derby.iapi.sql.execute.NoPutResultSet;
import org.apache.derby.iapi.sql.Activation;
-import org.apache.derby.iapi.sql.PreparedStatement;
import org.apache.derby.iapi.sql.ResultSet;
import org.apache.derby.iapi.sql.ParameterValueSet;
import org.apache.derby.iapi.sql.depend.Dependency;
-import org.apache.derby.iapi.types.DataValueFactory;
-import org.apache.derby.iapi.sql.LanguageFactory;
-import org.apache.derby.iapi.sql.conn.SQLSessionContext;
-
/**
* StatementContext keeps the context for a statement.
*/
@@ -277,4 +272,10 @@ public interface StatementContext extend
*/
public void setSQLSessionContext(SQLSessionContext ctx);
+ /**
+ * Tells if this statement has been invalidated.
+ *
+ * @return {@code true} if the statement was invalidated.
+ */
+ public boolean getStatementWasInvalidated();
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/conn/GenericStatementContext.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/conn/GenericStatementContext.java?rev=1028716&r1=1028715&r2=1028716&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/conn/GenericStatementContext.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/conn/GenericStatementContext.java Fri Oct 29 12:22:23 2010
@@ -44,8 +44,6 @@ import org.apache.derby.iapi.sql.Activat
import org.apache.derby.iapi.sql.ResultSet;
import org.apache.derby.iapi.sql.ParameterValueSet;
-import org.apache.derby.iapi.store.access.TransactionController;
-
import org.apache.derby.iapi.services.context.ContextImpl;
import org.apache.derby.iapi.error.ExceptionSeverity;
@@ -54,7 +52,6 @@ import java.util.ArrayList;
import java.util.Iterator;
import java.util.Timer;
import java.util.TimerTask;
-import java.sql.SQLException;
/**
* GenericStatementContext is pushed/popped around a statement prepare and execute
@@ -90,6 +87,7 @@ final class GenericStatementContext
private boolean isAtomic;
private boolean isSystemCode;
private boolean rollbackParentContext;
+ private boolean statementWasInvalidated;
private String stmtText;
private ParameterValueSet pvs;
@@ -232,6 +230,7 @@ final class GenericStatementContext
sqlAllowed = -1;
isSystemCode = false;
rollbackParentContext = false;
+ statementWasInvalidated = false;
if (cancelTask != null) {
cancelTask.forgetContext();
@@ -511,9 +510,19 @@ final class GenericStatementContext
** protocol violation errors, we treat java errors here
** to be of session severity.
*/
- int severity = (error instanceof StandardException) ?
- ((StandardException) error).getSeverity() :
- ExceptionSeverity.SESSION_SEVERITY;
+ int severity = ExceptionSeverity.SESSION_SEVERITY;
+ if (error instanceof StandardException) {
+ StandardException se = (StandardException)error;
+ // Update the severity.
+ severity = se.getSeverity();
+ // DERBY-4849: Remember that the plan was invalidated, such that
+ // we can avoid performing certain actions more than once
+ // (for correctness, not optimization).
+ if (SQLState.LANG_STATEMENT_NEEDS_RECOMPILE.equals(
+ se.getMessageId())) {
+ statementWasInvalidated = true;
+ }
+ }
/**
@@ -784,4 +793,8 @@ final class GenericStatementContext
public void setSQLSessionContext(SQLSessionContext ctx) {
sqlSessionContext = ctx;
}
+
+ public boolean getStatementWasInvalidated() {
+ return statementWasInvalidated;
+ }
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/NoPutResultSetImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/NoPutResultSetImpl.java?rev=1028716&r1=1028715&r2=1028716&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/NoPutResultSetImpl.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/NoPutResultSetImpl.java Fri Oct 29 12:22:23 2010
@@ -158,7 +158,8 @@ extends BasicNoPutResultSetImpl
LanguageConnectionContext lcc = getLanguageConnectionContext();
// only if statistics is switched on, collect & derive them
- if (lcc.getRunTimeStatisticsMode())
+ if (lcc.getRunTimeStatisticsMode() &&
+ !lcc.getStatementContext().getStatementWasInvalidated())
{
endExecutionTime = getCurrentTimeMillis();
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/NoRowsResultSetImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/NoRowsResultSetImpl.java?rev=1028716&r1=1028715&r2=1028716&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/NoRowsResultSetImpl.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/NoRowsResultSetImpl.java Fri Oct 29 12:22:23 2010
@@ -369,7 +369,8 @@ abstract class NoRowsResultSetImpl imple
** to skip printing it.
*/
if (lcc.getRunTimeStatisticsMode() &&
- !doesCommit() && !activation.isClosed())
+ !doesCommit() && !activation.isClosed() &&
+ !lcc.getStatementContext().getStatementWasInvalidated())
{
endExecutionTime = getCurrentTimeMillis();
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XplainStatisticsTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XplainStatisticsTest.java?rev=1028716&r1=1028715&r2=1028716&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XplainStatisticsTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XplainStatisticsTest.java Fri Oct 29 12:22:23 2010
@@ -27,11 +27,15 @@ import java.io.InputStream;
import java.net.MalformedURLException;
import java.security.AccessController;
import java.security.PrivilegedActionException;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
+import java.sql.Types;
import java.util.Date;
+import java.util.Random;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
@@ -466,17 +470,18 @@ public class XplainStatisticsTest extend
"delete from t where x = 3");
}
- private boolean hasTable(String schemaName, String tableName)
+ private static boolean hasTable(Statement s,
+ String schemaName, String tableName)
throws SQLException
{
- ResultSet rs = getConnection().getMetaData().getTables((String)null,
+ ResultSet rs = s.getConnection().getMetaData().getTables((String)null,
schemaName, tableName, new String[] {"TABLE"});
boolean tableFound = rs.next();
rs.close();
return tableFound;
}
- private String []tableNames = {
+ private static String []tableNames = {
"SYSXPLAIN_STATEMENTS",
"SYSXPLAIN_STATEMENT_TIMINGS",
"SYSXPLAIN_RESULTSETS",
@@ -485,19 +490,19 @@ public class XplainStatisticsTest extend
"SYSXPLAIN_SCAN_PROPS",
};
- private void enableXplainStyle(Statement s)
+ private static void enableXplainStyle(Statement s)
throws SQLException
{
verifyXplainUnset(s);
for (int i = 0; i < tableNames.length; i++)
- if (hasTable("XPLTEST", tableNames[i]))
+ if (hasTable(s, "XPLTEST", tableNames[i]))
s.execute("delete from XPLTEST." + tableNames[i]);
s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
s.execute("call syscs_util.syscs_set_xplain_schema('XPLTEST')");
s.execute("call syscs_util.syscs_set_xplain_mode(0)");
}
- private void enableXplainStyleWithTiming(Statement s)
+ private static void enableXplainStyleWithTiming(Statement s)
throws SQLException
{
enableXplainStyle(s);
@@ -509,7 +514,7 @@ public class XplainStatisticsTest extend
* @param s
* @throws Exception
*/
- private void disableXplainStyle(Statement s)
+ private static void disableXplainStyle(Statement s)
throws Exception
{
s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)");
@@ -528,7 +533,7 @@ public class XplainStatisticsTest extend
{
stmt_id = rs.getString(1);
access =
- new AccessDatabase(getConnection(), "XPLTEST", stmt_id);
+ new AccessDatabase(s.getConnection(), "XPLTEST", stmt_id);
if(access.initializeDataArray()){
access.createXMLFragment();
access.markTheDepth();
@@ -545,7 +550,7 @@ public class XplainStatisticsTest extend
}
}
- private void verifyXplainUnset(Statement s)
+ private static void verifyXplainUnset(Statement s)
throws SQLException
{
JDBC.assertFullResultSet(
@@ -747,7 +752,57 @@ public class XplainStatisticsTest extend
fail(e.getMessage());
}
}
-
+
+ /**
+ * Tests that invalidation of a statement after compile-time doesn't result
+ * in duplicate entries in the XPLAIN-table(s).
+ * <p>
+ * This test is timing-dependent, and may not trigger the bug under all
+ * circumstances.
+ * <p>
+ * See DERBY-4849.
+ *
+ * @throws Exception if something goes wrong
+ */
+ public void testSimpleQueryMultiWithInvalidation()
+ throws Exception {
+ // Start two threads; one selecting from COUNTRIES with XPLAIN on, and
+ // one generating statistics for the same table.
+ // The latter thread should cause the statement executed in the former
+ // thread to be invalidated.
+ long runTime = 10*1000; // Run for 10 seconds
+ AbstractMTThread select = new MTSimpleSelect(
+ openDefaultConnection(), runTime);
+ AbstractMTThread invalidate = new MTSimpleInvalidate(
+ openDefaultConnection(), runTime);
+ Thread tInv = new Thread(invalidate);
+ Thread tSel = new Thread(select);
+ tInv.start();
+ tSel.start();
+
+ // Wait until the threads have finished.
+ tInv.join();
+ tSel.join();
+
+ // Check if any errors were raised in the worker-threads.
+ int selects = select.getActionCount();
+ int invalidations = invalidate.getActionCount();
+ println("selects=" + selects + ", invalidations=" + invalidations);
+ if (select.failed()) {
+ fail("select-thread failed", select.getError());
+ }
+ if (invalidate.failed()) {
+ fail("invalidate-thread failed", invalidate.getError());
+ }
+
+ // There should be as many entries in the XPLAIN-table as the number
+ // of times we have executed the select-statement.
+ Statement s = createStatement();
+ JDBC.assertSingleValueResultSet(s.executeQuery(
+ "select count(*) from xpltest.sysxplain_statements"),
+ Integer.toString(selects));
+ }
+
/**
* Verify that XPLAIN style captures basic statistics and timings.
*
@@ -2345,7 +2400,7 @@ public class XplainStatisticsTest extend
{
Statement s = createStatement();
for (int i = 0; i < tableNames.length; i++)
- if (hasTable("XPLTEST", tableNames[i]))
+ if (hasTable(s, "XPLTEST", tableNames[i]))
s.executeUpdate("drop table xpltest."+tableNames[i]);
s.executeUpdate("create table xpltest.sysxplain_resultsets(a int)");
try
@@ -2366,4 +2421,132 @@ public class XplainStatisticsTest extend
}
}
+ /**
+ * Abstract class for a thread executing a database action (i.e. a query).
+ */
+ private static abstract class AbstractMTThread
+ implements Runnable {
+
+ protected final Connection con;
+ /** Duration of the run. */
+ protected final long runTime;
+ /** Tells how many times the action has been performed. */
+ protected int count;
+ /** If an error is raised when performing the action. */
+ protected Throwable error;
+
+ protected AbstractMTThread(Connection con, long runTime) {
+ this.con = con;
+ this.runTime = runTime;
+ }
+
+ public int getActionCount() {
+ return count;
+ }
+
+ public boolean failed() {
+ return error != null;
+ }
+
+ public Throwable getError() {
+ return error;
+ }
+ }
+
+ /**
+ * Thread for selecting from the COUNTRIES table in a loop, with the XPLAIN-
+ * functionality enabled.
+ */
+ private static class MTSimpleSelect
+ extends AbstractMTThread {
+
+ public MTSimpleSelect(Connection con, long runTime) {
+ super(con, runTime);
+ }
+
+ /**
+ * Selects from the COUNTRIES table in a loop.
+ */
+ public void run() {
+ Random rand = new Random();
+ final long start = System.currentTimeMillis();
+ try {
+ Statement s = con.createStatement();
+ enableXplainStyleWithTiming(s);
+ PreparedStatement ps = con.prepareStatement(
+ "SELECT country from countries WHERE " +
+ "region = 'Central America'");
+ while (System.currentTimeMillis() - start < runTime) {
+ JDBC.assertUnorderedResultSet(ps.executeQuery(),
+ new String[][] {
+ {"Belize"}, {"Costa Rica"}, {"El Salvador"},
+ {"Guatemala"}, {"Honduras"}, {"Nicaragua"} } );
+ count++;
+ try {
+ Thread.sleep(rand.nextInt(50));
+ } catch (InterruptedException ie) {
+ // Ignore
+ }
+ }
+ // Don't disable XPLAIN here, as it takes a long time due to the
+ // high number of recorded plans - these are currently being
+ // exported to disk as XML in the disable-method.
+ // This connection is going away anyway.
+ //disableXplainStyle(s);
+ s.close();
+ ps.close();
+ } catch (Throwable t) {
+ // Signal failure
+ error = t;
+ } finally {
+ try {
+ con.rollback();
+ con.close();
+ } catch (SQLException sqle) {
+ // Ignore
+ }
+ }
+ }
+ }
+
+ /**
+ * Thread for invalidating the COUNTRIES table in a loop.
+ */
+ private static class MTSimpleInvalidate
+ extends AbstractMTThread {
+
+ public MTSimpleInvalidate(Connection con, long runTime) {
+ super(con, runTime);
+ }
+
+ /**
+ * Invalidates the COUNTRIES table continuously by updating the
+ * associated index statistics. Loops until the run-time is up.
+ */
+ public void run() {
+ final long start = System.currentTimeMillis();
+ try {
+ PreparedStatement ps = con.prepareStatement(
+ "call SYSCS_UTIL.SYSCS_UPDATE_STATISTICS(?, ?, ?)");
+ ps.setString(1, "APP");
+ ps.setString(2, "COUNTRIES");
+ ps.setNull(3, Types.VARCHAR);
+ while (System.currentTimeMillis() - start < runTime) {
+ ps.execute();
+ count++;
+ }
+ ps.close();
+ } catch (Throwable t) {
+ // Signal failure
+ error = t;
+ } finally {
+ try {
+ con.rollback();
+ con.close();
+ } catch (SQLException sqle) {
+ // Ignore
+ }
+ }
+ }
+ }
}