FIX: Thread Blocking Issues When Using JDBC-ODBC Bridge

MORE INFORMATION

Steps to Reproduce the Behavior

The following code sample demonstrates the global thread blocking behavior with the Microsoft SQL Server ODBC driver. Run the sample using jview so you can watch the output as the code runs. If you run the sample as-is (modifying the connection string to point to your SQL Server if you don't have a local server), you will notice that all 10 threads block on the Java side but SQL Server only reports that one spid is blocked (using the sp_who2 command from an ISQL window).

If you uncomment the setQueryTimeout line:

   // stmt.setQueryTimeout(5);
				

you will notice that the threads can continue after the timeout expires, but there is still quite a bit of blocking.

If you uncomment the synchronization code block:

   /*synchronized static*/ 
				

you will notice that the threads no longer appear to hang; you have effectively synchronized all thread's access to the locking code.

   // START CODE SAMPLE
   import java.sql.*;
   import java.util.*;

   public class TestJDBC
   {
     static int THREAD_COUNT = 10;
     static int LOOP_COUNT   = 50;
     public static void main(String args[])
     {
       JDBCThread jdbcThreads[];
       int i;

       // Load the JDBC-ODBC bridge driver.
       try
       {
         Class.forName( "com.ms.jdbc.odbc.JdbcOdbcDriver" );
       }
       catch (ClassNotFoundException cnfEX)
       {
         System.out.println( "ClassNotFoundException: " +
           cnfEX.toString() );
         return;
       }

       // Create THREAD_COUNT worker threads.
       jdbcThreads = new JDBCThread[THREAD_COUNT];
       for (i=0; i<THREAD_COUNT; i++ )
       {
         jdbcThreads[i] = new JDBCThread( i, LOOP_COUNT );
       }

       // Start all worker threads.
       System.out.println( "Starting worker threads." );
       for (i=0; i<THREAD_COUNT; i++ )
       {
         jdbcThreads[i].start();
       }
       System.out.println( "All worker threads started." );
     }
   }

   // 
   // JDBC worker thread.
   // 
   class JDBCThread extends Thread
   {
     private int tid = 0;
     private int loopcount = 0;

     // Modify following connect string to point to your SQL Server.
     private static String connectInfo =
       "JDBC:ODBC:DRIVER={SQL Server};" +
       "SERVER=(local);DATABASE=pubs;UID=sa;PWD=;";
     private static String sql =
       "select * from authors (tablockx)";
     private java.sql.Connection conn = null;
     private java.sql.Statement stmt  = null;

     JDBCThread( int threadid, int iterations )
     {
       tid = threadid;
       loopcount = iterations;
       try
       {
         // Open connection to database.
         conn = DriverManager.getConnection ( connectInfo, "", "" );

         // Set connection to manual transaction mode so tablockx works.
         conn.setAutoCommit( false );
       }
       catch( Exception e )
       {
         System.out.println( e.toString() );
         conn = null;
       }
     }

     public /*synchronized static*/ void
       doExecute( int tid, Connection conn,
     Statement stmt, String sql ) throws SQLException
     {
       java.sql.ResultSet rs = null;

       System.out.println( "JDBCThread[" + tid +
         "] entering doExecute()" );

       // Open resultset.  Setting query timeout is recommended.
       // stmt.setQueryTimeout(5);
       rs = stmt.executeQuery( sql );

       // Close resultset and statement.
       rs.close();
       stmt.close();
       stmt = null;

       // Clear transaction and flag success.
       conn.rollback();
       System.out.println( "JDBCThread[" + tid + "] exiting doExecute()" );
     }
     public void run()
     {
       int i;

       if ( null == conn )
       {
         System.out.println( "Connection not open, exiting." );
         return;
       }

       System.out.println( "JDBCThread[" + tid + "] starting." );
       for ( i=1; i<=loopcount; i++ )
       {
         try
         {
           // Create statement and do some statement work.
           stmt = conn.createStatement();
           doExecute( tid, conn, stmt, sql );
         }
         catch( SQLException sqlEX )
         {
           while ( null != sqlEX )
           {
             System.out.println( "Thread[" + tid + "] SQLException: " +
                                                      sqlEX.toString() );
             sqlEX = sqlEX.getNextException();
           }
         }
         catch( Exception e)
         {
           if ( null != stmt )
           {
             try { stmt.close(); } catch( Exception e1) {};
           }
           stmt = null;
         };
       }
       System.out.println( "JDBCThread[" + tid + "] exiting." );
     }
   }
   // END CODE SAMPLE