Name: ddT132432 Date: 10/26/2001


java version "1.3.1"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.3.1-b24)
Java HotSpot(TM) Client VM (build 1.3.1-b24, mixed mode)

  Commit problem with IBM DB2 jdbc / odbc drivers

I'm not sure if this is a problem with the JRE code, or with IBM's drivers.
However, it happens with IBM's DB2 v7.2 driver certified as j2ee compliant.

When accessing a db2 database from Java, Connection.commit fails to release locks.
This is causing us serious grief, and contrary to the supposed Connection.commit
() behaviour (from jdk1.3.1 API for Connection interface): commit()
          Makes all changes made since the previous commit/rollback permanent
and releases any database locks currently held by the Connection


I reproduced this on these platforms:
db2 v5.2 Workgroup + fixpak 16 + NT v4 sp5a + jre1.3.1 and also jdk1.2 from
ibm's VisualAge 3.5
and also
db2 v7.2 developer + fixpak 4 + Win 2000 sp1 + jre1.3.1
And with both IBM jdbc driver COM.ibm.db2.jdbc.app.DB2Driver
and Sun's jdbc/odbc bridge driver (sun.jdbc.odbc.JdbcOdbcDriver)
So possibly the same symptom with both Ibm's jdbc and odbc drivers.

I've read and re-read Sun's jdbc tutorial and I'm pretty sure I'm doing what
you're supposed to. My test program below is just hacked from
TransactionPairs.java from the tutorial.
I cannot see anything wrong with our DB2 configuration, I have eg checked lock
and patch values.


REPRODUCTION STEPS:
To reproduce I created a simple java test harness LockTest.java:

Sequence of events
In java program:
load a driver (same with either COM.ibm.db2.jdbc.app.DB2Driver with
classpath ..\sqllib\java\db2java.zip
and sun.jdbc.odbc.JdbcOdbcDriver )
establish a connection to the db2 database
create a statement object
set autocommit off on the connection
run a couple of updates
commit with Connection.commit()
At this point, this connection should NOT hold any locks


Now start another copy of the LockTest program.
It hangs. The other copy still has locks on the table. That is quite wrong.

(Alternatively you can start a db2 command window,
connect to the database and issue a select on the table.
It too will hang.)

Switch back to the first java program and press any key to let it continue.
It now completes. Interestingly, the select from the 2nd connection in that
program (running in the same thread) runs OK.

Once the first program completes, the 2nd copy unblocks.


I have got a very nasty workround:
After performing your Connection.commit(),
set autocommit true and run some junk SQL,
discarding the SQLException that you get. Since autocommit is on, even though
the SQL didn't actually do anything but fail this does commit *and* clear the
locks.
You can see this workround commented out in my test program below.


Here is my java test program LockTest.java:

import java.sql.*;
     
public class LockTest {

    public static void main(String args[]) {
          
        String url = "jdbc:db2:COPS"; // change to your db
        Connection con1 = null;
        Connection con2 = null;
        Statement stmt1;
        Statement stmt2;
        String updateString;
        String queryString;
        int count;
    
        try {
            // need ...\sqllib\java\db2java.zip in the classpath
            // also works exactly the same with sun.jdbc.odbc.JdbcOdbcDriver,
            // and url jdbc:odbc:<dbalias>
            Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");

        } catch(java.lang.ClassNotFoundException e) {
            System.err.print("ClassNotFoundException: ");
            System.err.println(e.getMessage());
        }

        try {
    
            con1 = DriverManager.getConnection(url, "sopas90", "cops"); //change userid/pwd for your db
            con2 = DriverManager.getConnection(url, "sopas90", "cops");
    
            con1.setAutoCommit(false);
            stmt1 = con1.createStatement();
            stmt2 = con2.createStatement();

            updateString = "update ocvt set itin_no = 1 "
                + "where ticket_no = '1111111111' and itin_no = 99" ; //change to equivalent in your db
            System.out.println ("con1: " + updateString + "...");
            count = stmt1.executeUpdate( updateString );
            System.out.println ("con1: updated " + count + " rows");

            updateString = "update ocvt set itin_no = 99 "
                + "where ticket_no = '1111111111' and itin_no = 1" ; //change to equivalent in your db
            System.out.println ("con1: " + updateString + "...");
            count = stmt1.executeUpdate( updateString );
            System.out.println ("con1: updated " + count + " rows");

            System.out.println ("con1: commit..." );
            con1.commit();
            System.out.println ("con1: OK" );

            // nasty workround: put into auto-commit mode and
            // issue a junk update, discarding the SQL error.
            // Auto-commit mode does not exhibit the same problem of
            // hanging onto locks, instead it commits and DOES release all locks.
            //con1.setAutoCommit(true);
            //try {
            // stmt1.executeUpdate("XXX");
            //} catch (SQLException e) {
            // System.out.println("Workround threw exception as expected: "+ e.toString() );
            //}
            //con1.setAutoCommit(false); // back into transaction mode for any further work

            System.out.println( "Now start another copy and see if you can run.") ;
            System.out.println( "Press any key to continue here" );
            // The other copy will hang, because this process still has
            // a lock on the table, even though it just committed.
            try {
            int b = System.in.read();
            } catch (java.io.IOException e ) {}
            System.out.println( "" );

            // now try and access that table from con2
            // Interestingly, this does not hang. Perhaps these
            // are not really separate connections.
            queryString = "select count(*) AS TOT from ocvt"; //change to equivalent in your db

            System.out.println ("con2: " + queryString + "..." );
            ResultSet rs = stmt2.executeQuery(queryString);
            while (rs.next()) {
                int c = rs.getInt("TOT");
                System.out.println(c );
            }
            System.out.println ("con2: OK" );
    
            stmt1.close();
            con1.close();
            stmt2.close();
            con2.close();
            stmt1 = null;
            con1 = null;
            stmt2 = null;
            con2 = null;
    
        } catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }
    }
}
(Review ID: 134478)
======================================================================