Cancelling Long-Running Statements

When managing a GemFire XD deployment, it may become necessary to cancel statements that are taking too long to complete, or that are causing bottlenecks in your system. GemFire XD supports canceling queries using either a system procedure or the JDBC Statement.cancel() API.

Capabilities and Limitations

GemFire XD enables you to manually cancel prepared or unprepared SELECT, UPDATE, DELETE, or INSERT statements. DDL statements cannot be canceled. A running statement that is cancelled manually throws a SQLState error XCL56.S: The statement has been cancelled due to a user request.

Note: If the canceled statement was being executed within the context of a transaction, then GemFire XD rolls back the transaction.
Note: When you use JDBC Statement.cancel() API from a thin client connection, then you can cancel only prepared UPDATE, INSERT, or DELETE statements, and prepared or unprepared SELECT statements. To cancel unprepared UPDATE, INSERT, or DELETE statements, execute Statement.cancel() or use the SYS.CANCEL_STATEMENT() procedure.

In order to cancel a statement using the SYS.CANCEL_STATEMENT() procedure you must obtain the statement's CURRENT_STATEMENT_UUID from the SYS.SESSIONS table. Because SYS.SESSIONS lists only root DML and SELECT statements, any statements that are initiated from within a trigger, data-aware procedure, or a sub-distribution of a query are not eligible for cancelling. Data-aware procedures must implement the ProcedureExecutionContext.checkQueryCancelled() API in order to support statement cancellation. See Supporting Cancellation in Data-Aware Procedures.

SYS.SESSIONS also excludes peer-to-peer connections, so statements executed from a peer client are not eligible for cancellation using the procedure.

For any statement that is eligible for cancellation, GemFire XD supports cancellation only if the statement is performing one of the following actions:
  • Distributing the query to a query coordinator
  • Distributing sub-queries to GemFire XD members
  • Ordering results
  • Grouping results
  • Joining results
  • Scanning tables and indexes
  • Evaluating predicate expressions
GemFire XD does not support cancelling a statement when the statement is:
  • Checking constraints
  • Distributing sub-queries from the body of a trigger or data-aware procedure
  • Queueing operations to a WAN gateway
  • Synchronizing operations with a secondary bucket
  • Waiting for data to be read from disk
  • Waiting for locks
  • Holding results (as with SELECT FOR UPDATE statements)
  • Holding listener, loader, or writer callbacks
  • Installing or refreshing JAR files

During a rolling upgrade, GemFire XD does not support cancelling statements on members that run the older version of the GemFire XD software.

Whether you use Statement.cancel() or SYS.CANCEL_STATEMENT(), keep in mind that issuing a cancellation request does not ensure that a given statement is actually canceled. It is possible that the statement completed successfully before the cancellation request was received, or that GemFire XD was not be able to cancel the statement due to the current state of the operation. A successful return from Statement.cancel() or SYS.CANCEL_STATEMENT() simply means that GemFire XD received the cancellation request and will make a best-effort attempt to cancel the statement.

Cancelling Statements using SYS.CANCEL_STATEMENT()

Follow this procedure to use the SYS.CANCEL_STATEMENT() procedure to cancel a statement:
  1. Query the SYS.SESSIONS table to obtain the CURRENT_STATEMENT_UUID of the statement that you want to cancel. For example:
    gfxd> select id, session_id, current_statement_uuid, current_statement, current_statement_status from sys.sessions;
    ID                          |SESSION_ID |CURRENT_STATEMENT_UUID   |CURRENT_STATEMENT                                                                                                               |CURRENT_STATEMENT_STATUS
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    pnq-rdiyewar(7438)<v3>:27584|2          |12884901905-12884901918-1|SYSLH0001	select id, session_id, current_statement_uuid, current_statement, current_statement_status from sys.sessions          |EXECUTING STATEMENT     
    pnq-rdiyewar(7194)<v2>:37098|2          |8589934609-8589934687-1  |SYSLH0001	select eqp_id, cntxt_id from CONTEXT_HISTORY where eqp_id||cast(cntxt_id as char(100)) in (select eqp_id||c&           |EXECUTING STATEMENT     
    pnq-rdiyewar(6844):27404    |2          |17-19-1                  |SYSLH0001	call SYS.GET_ALLSERVERS_AND_PREFSERVER(?, ?, ?, ?)                                                                    |SENDING RESULTS         
    pnq-rdiyewar(6844):27404    |3          |20-22-1                  |SYSLH0001	call SYS.GET_ALLSERVERS_AND_PREFSERVER(?, ?, ?, ?)                                                                    |SENDING RESULTS         
    
    4 rows selected
    Note: A UUID has the components connectionID-statementID-executionID and a statement may have more than one executionID. If you choose a UUID with a executionID of 0, then GemFire XD instead cancels the statement that has a matching connectionID and statementID.
  2. Execute SYS.CANCEL_STATEMENT() with the UUID of the statement that you want to cancel:
    gfxd> call sys.cancel_statement('8589934609-8589934687-1');
    Statement executed.
    Upon cancellation, the session that is running the statement receives a SQLState error XCL56. For example, in the above case an interactive gfxd session would receive an exception similar to:
    gfxd> select eqp_id, cntxt_id from CONTEXT_HISTORY where eqp_id||cast(cntxt_id as char(100)) in (select eqp_id||cast(t.cntxt_id as char(100)) from RECEIVER_LOG t where 1=1 );
    ERROR XCL56: SQLSTATE=XCL56,SEVERITY=-1: (Server=pnq-rdiyewar[1529],Thread[DRDAConnThread_15,5,gemfirexd.daemons]) The statement has been cancelled due to a user request. : 
    java.sql.SQLException: SQLSTATE=XCL56,SEVERITY=-1: (Server=pnq-rdiyewar[1529],Thread[DRDAConnThread_15,5,gemfirexd.daemons]) The statement has been cancelled due to a user request. : 
    	at com.pivotal.gemfirexd.internal.client.am.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:103)
    	at com.pivotal.gemfirexd.internal.client.am.SqlException.getSQLException(SqlException.java:401)
    	at com.pivotal.gemfirexd.internal.client.am.Statement.execute(Statement.java:1009)
    	at com.pivotal.gemfirexd.internal.impl.tools.ij.ij.executeImmediate(ij.java:347)
    	at com.pivotal.gemfirexd.internal.impl.tools.ij.utilMain.doCatch(utilMain.java:697)
    	at com.pivotal.gemfirexd.internal.impl.tools.ij.utilMain.runScriptGuts(utilMain.java:425)
    	at com.pivotal.gemfirexd.internal.impl.tools.ij.utilMain.go(utilMain.java:263)
    	at com.pivotal.gemfirexd.internal.impl.tools.ij.Main.go(Main.java:309)
    	at com.pivotal.gemfirexd.internal.impl.tools.ij.Main.mainCore(Main.java:275)
    	at com.pivotal.gemfirexd.internal.impl.tools.ij.Main.main(Main.java:87)
    	at com.pivotal.gemfirexd.internal.tools.ij.main(ij.java:66)
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.lang.reflect.Method.invoke(Method.java:606)
    	at com.gemstone.gemfire.internal.GemFireUtilLauncher.invoke(GemFireUtilLauncher.java:167)
    	at com.pivotal.gemfirexd.tools.GfxdUtilLauncher.invoke(GfxdUtilLauncher.java:228)
    	at com.pivotal.gemfirexd.tools.GfxdUtilLauncher.main(GfxdUtilLauncher.java:145)
    Caused by: ERROR XCL56: SQLSTATE=XCL56,SEVERITY=-1: (Server=pnq-rdiyewar[1529],Thread[DRDAConnThread_15,5,gemfirexd.daemons]) The statement has been cancelled due to a user request. : 
    	at com.pivotal.gemfirexd.internal.client.am.Statement.completeSqlca(Statement.java:2034)
    	at com.pivotal.gemfirexd.internal.client.net.NetStatementReply.parseOpenQueryError(NetStatementReply.java:695)
    	at com.pivotal.gemfirexd.internal.client.net.NetStatementReply.parseOPNQRYreply(NetStatementReply.java:286)
    	at com.pivotal.gemfirexd.internal.client.net.NetStatementReply.readOpenQuery(NetStatementReply.java:83)
    	at com.pivotal.gemfirexd.internal.client.net.StatementReply.readOpenQuery(StatementReply.java:62)
    	at com.pivotal.gemfirexd.internal.client.net.NetStatement.readOpenQuery_(NetStatement.java:174)
    	at com.pivotal.gemfirexd.internal.client.am.Statement.readOpenQuery(Statement.java:1604)
    	at com.pivotal.gemfirexd.internal.client.am.Statement.flowExecute(Statement.java:2324)
    	at com.pivotal.gemfirexd.internal.client.am.Statement.executeX(Statement.java:1014)
    	at com.pivotal.gemfirexd.internal.client.am.Statement.execute(Statement.java:1000)
    	... 15 more

Cancelling Statements using the JDBC API

You can use the JDBC Statement.cancel() method to cancel a statement that is being executed by another thread. If you use Statement.cancel() API from a thin client connection, then you can cancel only prepared UPDATE, INSERT, or DELETE statements, and prepared or unprepared SELECT statements. If you attempt to cancel an unprepared UPDATE, INSERT, or DELETE statement, the statement throws an exception with SQLState 0A000 to indicate that the feature is not supported.

See the Javadocs for the Statement interface for more information.

Using Query Timeouts to Cancel Statements Automatically

In addition to supporting manual statement cancellation with Statement.cancel() and SYS.CANCEL_STATEMENT(), GemFire XD supports setting query timeouts in order to automatically cancel long-running statements.

JDBC applications can use the Statement.setQueryTimeout() method to configure the maximum number of seconds the JDBC driver waits for a query to complete. After this period of time, the statement receives an exception with SQLState XCL52.S: The statement has been cancelled due to query time out. GemFire XD also provides the gemfirexd.query-timeout system property, which sets a query timeout value at the GemFire XD member level.

Data-aware procedures must implement the ProcedureExecutionContext.checkQueryCancelled() API in order to support query timeouts. See Supporting Cancellation in Data-Aware Procedures. For nested statements in a data-aware procedure, if a statement uses Statement.setQueryTimeout() to set a timeout value greater than the timeout of a parent statement, the new setting is ignored in favor of the parent's setting.

Supporting Cancellation in Data-Aware Procedures

Data-aware procedures can execute statements using nested connections, which are not eligible for cancellation as they do not appear in the SYS.SESSIONS table. To support statement cancellation and query timeouts from within a procedure, you must periodically call the ProcedureExecutionContext.checkQueryCancelled() method while executing a nested statement in the body of your procedure. For example:
example.MyProcedure {
public static void myProc(String inParam1, int[] outParam2,
        ExampleObj[] example, int[] count, ResultSet[] resultSet1,
        ResultSet[] resultSet2,
        ProcedureExecutionContext context) throws SQLException { 
  ...
  int executionCount = 0;
  while (rs.next()) {
    ...
    ...

     // After every 100 iterations of the statement, check to see whether the query was cancelled.
     if (executionCount++ % 100 == 0) {
       context.checkQueryCancelled();
     }
  } 
} // end of procedure