FIX: Thread Blocking Issues When Using JDBC-ODBC Bridge

• Microsoft SDK for Java, versions 1.0, 1.5, 1.51, 2.0, 2.01, 2.02, 3.0

SYMPTOMS

When using the Microsoft supplied JDBC-ODBC Bridge component for JDBC (Msjdbc10.dll and associated Java class files) with multiple threads in a Java application or applet, the threads using JDBC appear to hang. Running a JDBC statement on more than one thread results in thread blocking, even when using a thread safe non-blocking ODBC driver such as the Microsoft SQL Server ODBC driver.

CAUSE

The Microsoft JDBC-ODBC Bridge globally synchronizes all method calls. This means that a call to any JDBC method that does not immediately return will block all other JDBC calls from other threads. If your JDBC code calls executeQuery for example, and the query takes five seconds to execute, then all other JDBC using threads in your application will hang for five seconds.

This can easily cause a deadlock when one JDBC using thread has locked a table and another JDBC using thread attempts to lock the same table. Because the second thread is blocked by the first threads table lock, the second thread waits inside the JDBC call to lock the table--thus preventing the first thread from making the necessary JDBC calls to unlock the table.

RESOLUTION

Update your virtual machine to the latest version.

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article. This bug has been corrected in the Virtual Machine that ships with SDK for Java 3.1.

MORE INFORMATION

Steps to Reproduce Behavior

   // stmt.setQueryTimeout(5); 

   /*synchronized static*/  

   // 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 

REFERENCES

For the latest Knowledge Base articles and other support information on Visual J++ and the SDK for Java, see the following pages on the Microsoft Technical Support site:

http://support.microsoft.com/support/visualj/

http://support.microsoft.com/support/java/

http://www.microsoft.com/java/

Additional query words:

Keywords : kbJava
Version : WINDOWS:1.0,1.5,1.51,2.0,2.01,2.02,3.0
Platform : WINDOWS
Issue type : kbbug kbprb