Microsoft KB Archive/821192

= FIX: JDBC Driver Does Not Duplicate the Transaction Isolation Level in Cloned Connection =

Article ID: 821192

Article Last Modified on 10/25/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Driver for JDBC

-





SYMPTOMS
When the Microsoft SQL Server 2000 Driver for JDBC internally opens a cloned connection, it does not copy the transaction isolation level from the original connection to the cloned connection. This can cause unintended locks on the Database objects. This behavior may cause other SQL Server applications to block.



CAUSE
When the JDBC Driver opens a connection, the SelectMethod property is set to Direct (the default setting), and by design, no server cursors are used. Therefore, to manage multiple statements, the driver has to internally open extra connections that are named cloned connections. These cloned connections should have the same properties that are set on the original connection that is opened by the application. However, any transaction isolation level that is set on the original connection is not set on the cloned connection because of a bug in the driver. This causes the cloned connections to use the default isolation level setting (READ COMMITTED).



RESOLUTION
A supported hotfix is now available from Microsoft, but it is only intended to correct the problem that is described in this article. Only apply it to systems that are experiencing this specific problem. This hotfix may receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next service pack that contains this hotfix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information about support costs, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

Note In special cases, charges that are ordinarily incurred for support calls may be canceled if a Microsoft Support Professional determines that a specific update will resolve your problem. The usual support costs will apply to additional support questions and issues that do not qualify for the specific update in question. The English version of this fix has the file attributes (or later) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel. The Windows version of this fix has the following properties:

Date        Time   Version            Size    File name --  08-May-2003  20:44  2.2.0035          286,790  Msbase.jar 08-May-2003 20:44  2.2.0035           67,159  Mssqlserver.jar 08-May-2003 20:44  2.2.0035           58,903  Msutil.jar

The Unix-based version of this fix has the following properties:

Date        Time   Version            Size    File name --  07-May-2003  18:30  2.2.0035          286,790  Msbase.jar 07-May-2003 18:30  2.2.0035           67,159  Mssqlserver.jar 07-May-2003 18:30  2.2.0035           58,903  Msutil.jar Note Although the file timestamp may be different for each operating system that is listed, internally the files are exactly the same. The best way to determine the version of the driver that you are using is to use the getDriverVersion method of the DatabaseMetaData interface.



WORKAROUND
Because the driver opens cloned connections only when the SelectMethod property is set to Direct, use server cursors instead by setting the SelectMethod property to Cursor.

If you cannot use the Cursor method for some reason, you might be able to work around this bug by using locking hints in queries. For example, if you must use the READ UNCOMMITTED ioslation level, append the locking hint &quot;WITH (NOLOCK)&quot; to your queries. This causes the cloned connection to behave as if the isolation level were set to READ UNCOMMITTED. Because you cannot use this workaround for all the isolation levels, Microsoft reccommends that you use the first workaround (set the SelectMethod property to Cursor).



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



MORE INFORMATION


Steps to Reproduce the Problem
  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(&quot;com.microsoft.jdbc.sqlserver.SQLServerDriver&quot;);

Connection conn = DriverManager.getConnection(&quot;jdbc:microsoft:sqlserver://myServer:1433;DatabaseName=myDatabase;User=UserID;Password=password&quot;);

Statement statement = conn.createStatement; try {               statement.executeUpdate(&quot;DROP TABLE SampleTable&quot;); }       catch (SQLException e)        { //do nothing, table probably does not exist }

statement.executeUpdate(&quot;create table SampleTable (myId integer, myValue varchar(25))&quot;);

for (int i=1; i<=100000; i++) {

String sql = &quot;insert into SampleTable values (&quot; + i + &quot;, 'text&quot; + i + &quot;')&quot;; statement.executeUpdate(sql); }       statement.close; conn.close;

}   catch (Exception e)        { e.printStackTrace; }

}

}     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.  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(&quot;com.microsoft.jdbc.sqlserver.SQLServerDriver&quot;); //App opens a connection. -- This is the original connection. Opened with the default Direct mode Connection conn = DriverManager.getConnection(&quot;jdbc:microsoft:sqlserver://myServer:1433;DatabaseName=myDatabase;User=UserID;Password=password&quot;);

//Workaround #1: Comment this line and uncomment the following line to use server cursors //Connection conn = DriverManager.getConnection(&quot;jdbc:microsoft:sqlserver://myServer:1433;DatabaseName=myDatabase;SelectMethod=Cursor;User=UserID;Password=password&quot;);

//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(&quot;SELECT * FROM SysFiles&quot;);

ResultSet rs1 = ps1.executeQuery;

rs1.next;

rs1.close;

//Prepare a second query PreparedStatement ps2 = conn.prepareStatement(&quot;SELECT * FROM SampleTable&quot;); //Workaround #2. Comment this line and uncomment the following line to work around in this case, using lock hint //PreparedStatement ps2 = conn.prepareStatement(&quot;SELECT * FROM SampleTable WITH (NOLOCK)&quot;);

//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(&quot;Press Enter to continue&quot;); try {       System.in.read; }

catch( IOException ioe) {}       rs2.next;

rs2.close;

ps2.close;

conn.close;

}        catch (Exception e)        { e.printStackTrace; }

}

}   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 </li> 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: <ul> PAGE lock with IS mode</li> DB lock with S mode</li> TABLE lock with IS mode</li></ul>

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.</li></ol>

After you apply the hotfix, you see the following call on the second connection: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED and you see only the DB and TABLE locks for that connection.

Additional query words: Hang Java Type4 Performance

Keywords: kbqfe kbhotfixserver KB821192

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.