Name: mc57594 Date: 02/11/97


We have a multithreaded JDK 1.1-beta3 application that uses JDBC to
access a MS SQL Server 6.5 database. Each thread opens its own
connection to the db, and most things work as expected. However, we
encounter problems when we try to have java.sql.Statement objects (one
per connection) try to executeQuery("select x from y (tablockx)") to
get an exclusive table lock when using the JDK 1.1-beta3 JDBC-ODBC
bridge driver.

When we use the JDBC-ODBC bridge driver (with Microsoft's ODBC 3.0
driver manager and driver), all the threads hang as if deadlocked.
When we use Connect Software's all-Java to native (Microsoft) DBMS
protocol (type 4) driver (FastForward 2.5) instead of the JDBC-ODBC
bridge, things work without hanging. While we very much like the
all-Java driver concept, we have plans to access other RDBMSs for
which ODBC drivers are available but no type 4 all-Java drivers will
be available for a long time. Therefore, we need to be able to make
our application work correctly with the JDBC-ODBC bridge.

company - OCLC , email - ###@###.###
======================================================================

IBM uses JDBC-ODBC bridge to access a DB2 datebase. The testcase and thread dump are in attachment. The testcase is pretty small and nearly always deadlocks on the first iteration although it sometimes makes it through to the second or third iteration.

The deadlock is reproduced for Sybase SQL Anywhere.

natalie.wang@eng 1999-02-23
========================================================================

More details of the problem as seen and reported by IBM:

================================================================================

I ran the test case to the point of deadlock (< 1 second) and then did
Ctrl-break and cut+paste the screens contents.

P:\defects\8451>java test1.test1.SimpleInc2
create thread 1
create thread 2
exit main()
#### Iteration 1
#### Iteration 1

Full thread dump:
    "Thread-2" (TID:0xf8b4c0, sys_thread_t:0x8937a0, Win32ID:0xa5, state:R)
prio=5
        sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:1168)
        sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:208)
        sun.jd
bc.odbc.JdbcOdbcStatement.executeUpdate(JdbcOdbcStatement.java:165)
        test1.test1.TestThread2.updateDataBase(Compiled Code)
        test1.test1.TestThread2.run(TestThread2.java:209)
    "Thread-1" (TID:0xf8b530, sys_thread_t:0x8935e0, Win32ID:0xe0, state:MW)
prio=5
        sun.jdbc.odbc.JdbcOdbc.SQLNumResultCols(JdbcOdbc.java:2636)

sun.jdbc.odbc.JdbcOdbcStatement.getColumnCount(JdbcOdbcStatement.java:666)

sun.jdbc.odbc.JdbcOdbcStatement.getUpdateCount(JdbcOdbcStatement.java:302)

sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(JdbcOdbcStatement.java:166)
        test1.test1.TestThread2.updateDataBase(Compiled Code)
        test1.test1.TestThread2.run(TestThread2.java:209)
    "Finalizer thread" (TID:0xf70088, sys_thread_t:0x87b490, Win32ID:0xfc,
state:CW) prio=2
    "main" (TID:0xf700b0, sys_thread_t:0x877230, Win32ID:0x78, state:CW) prio=5
Monitor Cache Dump:
    sun.jdbc.odbc.JdbcOdbcStatement@F8B800/103FCE0: owner "Thread-2" (0x8937a0,
1 entry)
    sun.jdbc.odbc.JdbcOdbc@F74530/FC7328: owner "Thread-2" (0x8937a0, 1 entry)
Registered Monitor Dump:
    SymcJIT Method Monitor: <unowned>
    SymcJIT Method Monitor: <unowned>
    SymcJIT Method List Monitor: <unowned>
    SymcJIT Fixups Allocation: <unowned>
    SymcJIT Code Allocation: <unowned>
    SymcJIT Data Allocation: <unowned>
    Thread queue lock: <unowned>
        Waiters: 1
    Name and type hash table lock: <unowned>
    String intern lock: <unowned>
    JNI pinning lock: <unowned>
    JNI global reference lock: <unowned>
    BinClass lock: <unowned>
    Class loading lock: <unowned>
    Java stack lock: <unowned>
    Code rewrite lock: <unowned>
    Heap lock: <unowned>
    Has finalization queue lock: <unowned>
    Finalize me queue lock: <unowned>
        Waiters: 1
    Monitor registry: <unowned>

================================================================================
Analysis of problem by IBM engineer :-

This problem is caused by the fact that the odbc bridge has
been designed to avoid re-entrant calls on the ODBC engine.
This is because ODBC pre version 3 and some ODBC drivers
cannot handle reentrant calls. This is done by the JDBC-ODBC
bridge maintaining a single JdbcOdbc class acting as the API.
This object is defined in JdbcOdbcDriver.java as a static and
passed to all connections. All the API object calls are
synchronized. The problem occurs when one thread enters a
synchronized API which then blocks on the database such as due
to a locked record. The JdbcOdbc objects monitor is owned by
this thread so any other thread which calls a JdbcOdbc API
call will block on entry. This is what the thread that needs
to unlock the record will do, causing deadlock.

In the test case the locking is caused by auto commit being
off and so a read or write locks the record until commit is
called. The second thread locks on an equivalent call inside
SQLExecDirect locking the JdbcOdbc Api. The first thread
hence blocks on the commit call or on getColumnCount in
JdbcOdbcStatement.java - execute(..) depending on timing.

The deadlock occurs with both threads simultaneously trying
to update the same record or one updating and the other
reading.

The deadlock occurs on JDK116, JDK117 and JDK1.2.

The deadlock does NOT occur with the jdbc db2 driver.

patrick.ong@Eng 1999-03-23