FIX: A JDBC connection becomes invalid when you try to cancel a query from a second thread through the SQL Server 2000 Driver for JDBC (894556)

MORE INFORMATION

Steps to reproduce the behavior

1. On the first thread, follow these steps:
   1. Create a connection and a statement.
   2. Start a transaction on the connection, and then lock a row. To do this, update the row by using the statement.
   3. Create a second connection and a second statement.
   4. Start a worker thread, and then pass the second statement to the worker thread as a parameter.
2. On the second thread, execute a stored procedure that tries to use the previously locked row. This stored procedure will be blocked forever because you do not try to unlock that row.
3. On the first thread, follow these steps:
   1. Cancel the second statement.
   2. Create a new statement on the second connection, and then execute an arbitrary query. For example, execute SELECT @@VERSION.

import java.util.*;
import java.sql.*;

public class Class1
{
	static String strURL = "jdbc:microsoft:sqlserver://<Server>:1433;SelectMethod=direct";
	static String strUid = "<UserID>";
	static String strPass = "<Password>";
	
	public static void main(String args[]) throws Exception
	{

		Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
		createBlock();
		
		//Make con 2.
		//This is the connection where the cancellation will occur.
		System.out.println("making second con");
		Connection con2 = DriverManager.getConnection(strURL, strUid, strPass);
		con2.setAutoCommit(false);
	
		//Display some random data at this point to prove that the connection is good.
		//Notice that this data is not at all related to the data that is actually used
		//to create the blocking scenario.
		displayAnyData(con2);
	
		// Spawn another thread to call proc and then stop responding.
		System.out.println("spawning second thread");
		Statement s2 = con2.createStatement();
		iThread thread = new iThread( s2 );
		thread.start();

		System.out.println("main thread sleep.");
		// Sleep long enough for other thread to stop responding.
		Thread.sleep(10000);
		System.out.println("main thread back from sleep. Going to cancel()");
		s2.cancel();
		Thread.sleep(10000);
		
		
		//start Rollback option
		//If you uncomment this code, you will see the "operation has been canceled"
		//message on the rollback call instead of the following displayAnyData call.
//		System.out.println("main thread Going to rollback()");
//		try
//		{
//			con2.rollback();
//		}
//		catch (Exception ee)
//		{
//			System.out.println("after st.cancel(), con2.rollback() gives " + ee );
//		}
		//end rollback option
		
		s2.close();
    
		try
		{
			System.out.println("main thread trying query with new stmt from con with canceled statement.");

			//The first time that you do anything on this connection, you receive a message 
			//about the query being canceled.
			displayAnyData(con2);
			
			//The second time that you try to do something on this connection, you receive
			//a NullPointerException exception. In this case, you just try to display the data again.
			displayAnyData(con2);

			System.out.println("done with after-cancel() query");
		}
		catch (Exception ee)
		{
			System.out.println("unexpected exception occurred " + ee );
			ee.printStackTrace();
		}
	}
  
	//The purpose of this function is just to create a new statement and then execute
	//a simple query (select @@version) to verify whether the connection
	//allows for any execution.
	public static void displayAnyData(Connection conn)
	{
		Statement s3 = null;
		ResultSet rr = null;
		try
		{
			s3 = conn.createStatement();
			System.out.println("\nStart trying to retreive data\n");
			rr = s3.executeQuery("select @@version");

			boolean OK = rr.next();
			if (OK)
				System.out.println("result set got first row " + rr.getString(1) );
			else
				System.out.println("result set NO ROWS!");
		}
		catch (Exception ex)
		{
			System.out.println("Caught error in displayAnyData:\n\t" + ex.getMessage());
		}
		
		try
		{
			if (rr != null) rr.close();
			if (s3 != null) s3.close();
			System.out.println("End trying to retreive data\n");
		}
		catch (Exception ee) 
		{
			System.out.println("Error closing rr or s3 in displayData: " + ee.getMessage());
		}
			
	}

	//The purpose of the createBlock function is just to set up a connection that will make
	//an update and then cause a second connection to block later on.
	//The second connection is set up to block. Then, we can cancel that query.
	//The cancellation of the query appears to be what really causes the
	//problem. Therefore, you must set up a situation to force that behavior to occur.
	public static void createBlock()
	{
		try
		{
			//Make con 1. This connection is just used to set up a blocking
			//scenario. Therefore, con2 can block and then be canceled.
			System.out.println("making first con");
			Connection con1 = DriverManager.getConnection(strURL, strUid, strPass);

			System.out.println("doing setup");
			// Make a table if it is required.
			Statement s1 = con1.createStatement();
			try {s1.executeUpdate("set nocount on");} 
			catch (Exception e){}
			try {s1.executeUpdate("drop table mytable");} 
			catch (Exception e){}
			try {s1.executeUpdate("create mytable joe (bar int)");} 
			catch (Exception e){}
			try {s1.executeUpdate("insert mytable values(1)");} 
			catch (Exception e){}

			// Make proc if it is required.
			String makeproc = "create proc myproc as begin select 'start tran' begin tran update mytable set bar = 2 commit tran select 'after tran' end";
			try {s1.executeUpdate("drop procedure myproc");} 
			catch (Exception e){}
			try {s1.executeUpdate(makeproc);} 
			catch (Exception e){}

			// Use con 1, start tx, and lock table.
			System.out.println("setup completed");
			con1.setAutoCommit(false);
			s1.executeUpdate("update mytable set bar = 3");
		}
		catch (Exception ex)
		{
			System.out.println("Exception in createBlock: " + ex.getMessage());
		}
	}
}

class iThread extends Thread
{
	private Statement m_s;

	public iThread (Statement s)
	{
		m_s = s;
	}

	public void run()
	{
		try
		{
			System.out.println("thread 2 going to exec");
			m_s.execute("exec myproc"); // This should stop responding (hang).
			System.out.println("thread 2 after exec, finished");
		}
		catch (Exception e)
		{
			System.out.println("second thread got " + e.getMessage());
		}
	}
}

• In this code, replace <Server>, <UserID>, and <Password> with the name of your computer that is running SQL Server, your user ID, and your password.
• You may not receive a "The operation was cancelled at the user's request" exception if the stored procedure generated a result set before the procedure was actually canceled. The error message that states that the operation was canceled appears after the result set. If you modify the code in this section to fetch the results from the stored procedure, you receive a "The operation was cancelled at the user's request" exception when the next result set is fetched. Also, if the stored procedure is modified not to return a result set before the procedure is canceled, the exception will be raised without fetching the data.
  
  Note The word "canceled" is spelled incorrectly in the exception error message.