FIX: JDBC Driver Does Not Duplicate the Transaction Isolation Level in Cloned Connection (821192)

Steps to Reproduce the Problem

1. To create a table with large number of rows, compile and run the following code.
   
   Note You must replace the server name, the user id, and the password in the connection string as appropriate for your environment.

   public class CreateTable
   {
       
       
       public static void main(String[] args)
       {
       	try
   	{
               
               Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
   
   	    Connection conn = DriverManager.getConnection("jdbc:microsoft:sqlserver://myServer:1433;DatabaseName=myDatabase;User=UserID;Password=password");
   
               Statement statement = conn.createStatement();
   	
   	    try
   	    {
               	statement.executeUpdate("DROP TABLE SampleTable");
               }
   	    catch (SQLException e)
   	    {
              	 //do nothing, table probably does not exist
   	    }
   
   	    statement.executeUpdate("create table SampleTable (myId integer, myValue varchar(25))");
   
   	    for (int i=1; i<=100000; i++)
   	    {
   
               	String sql = "insert into SampleTable values (" + i + ", 'text" + i + "')";
       	        statement.executeUpdate(sql);
               }
               
   	    statement.close();
               
   	    conn.close();
   
           }
   	catch (Exception e)
           {
               e.printStackTrace();
           }
   
       }
   
   
   }     

2. Start SQL Profiler to trace the calls to the SQL Server that the application connects to. Make sure that the trace properties are set to include the Security Audit and the TSQL SQL:BatchStarting events together with the TextData and SPID data columns. Start the trace.
3. Compile and run the following code. The execution stops mid-way for user input. You see the cloned connection and the properties of the cloned connection that are traced by the profiler.
   
   Note You must replace the server name, the user id, and the password in the connection string as appropriate for your environment.

   import java.sql.*;
   import java.io.*;
   
   public class ClonedIsolation
   {
    
       public static void main(String[] args)
       {
        
   	   try
   	   {
             
   	     
   
               Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
   	
   	    //App opens a connection. -- This is the original connection. Opened with the default Direct mode
   	    Connection conn = DriverManager.getConnection("jdbc:microsoft:sqlserver://myServer:1433;DatabaseName=myDatabase;User=UserID;Password=password");
   
   	    //Workaround #1: Comment this line and uncomment the following line to use server cursors
               //Connection conn = DriverManager.getConnection("jdbc:microsoft:sqlserver://myServer:1433;DatabaseName=myDatabase;SelectMethod=Cursor;User=UserID;Password=password");
   
   	    //Set transaction isolation level to Read Uncommitted on this connection
               conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
   
   	    //Execute a query on this connection  
               PreparedStatement ps1 = conn.prepareStatement("SELECT * FROM SysFiles");
   
   	    ResultSet rs1 = ps1.executeQuery();
   
               rs1.next();
   
               rs1.close();
   
   	    //Prepare a second query 
               PreparedStatement ps2 = conn.prepareStatement("SELECT * FROM SampleTable");
   	    	    	    
   	    //Workaround #2. Comment this line and uncomment the following line to work around in this case, using lock hint
   	    //PreparedStatement ps2 = conn.prepareStatement("SELECT * FROM SampleTable WITH (NOLOCK)");
   
   	    //Execute the second query -- this causes the driver to open a cloned connection	
   	    ResultSet rs2 = ps2.executeQuery();
   
   	    //program pauses on the line following, check profiler trace to see the cloned connection
               System.out.println("Press Enter to continue");
       	    try
               {
   		System.in.read();
   	    }
   
               catch( IOException ioe)
   	    {}
               
   	    rs2.next();
   
               rs2.close();
   
   	    ps2.close();
   
               conn.close();
   
           } 
           catch (Exception e)
           {
               e.printStackTrace();
           }
   
       }
   
   
   }

4. When the execution stops for user input, press the TAB key to move to the profiler trace. You see two Audit Login events. The first one is for the original connection, and the second one is for the cloned connection. Note their Server Process IDs (SPIDs). Note that the second connection does not contain the following call:

   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

5. At this point, if you run the sp_lock query from SQL Query Analyzer, you see that the cloned connection holds the following three locks:
   • PAGE lock with IS mode
   • DB lock with S mode
   • TABLE lock with IS mode
   However, if you use the lock hint workaround that is indicated in the code in this article, you see that the second connection does not hold a PAGE lock, and only holds the DB and TABLE locks.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED