I'm having a couple of issues with using Oracle and JDBC.

Environment:
Windows XP Pro
j2sdk1.4.2_07
Oracle JDBC Driver version - 10.1.0.2.0
Server: Oracle 9i Enterprise Ed Release 0.2.0.1.0 - Production

Now that I have that out of the way, here are my problems:

I have broken the code into small parts, the entire code can be viewed
here:

http://rafb.net/paste/results/hvsrN259.html

Note, this is just a simple program I wrote to help demonstrate the
issues I have. It is not production code, just trying to test some
stuff.

Ok, I've successfully retrieved my connection, and now I'm setting
auto commit to false, and I don't want to read data unless its been
committed...

     connection.setAutoCommit(false);

connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

     try {

Create a statement for update.

        Statement statement =    
    connection.createStatement(
        ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_UPDATABLE);

Execute a statement to get the next ID. Now, given that I've set the
connection to TRANSACTION_READ_COMMITTED, I am expecting this to not
allow me to read this if an uncommitted transaction has updated this
row.

        statement.execute(
    "SELECT MAX(TEST.ID) + 1 AS NEWID FROM TEST");
        ResultSet idResultSet = statement.getResultSet();
        int id = 1;
        if (idResultSet.first()) {
           id = idResultSet.getInt(1);
        }

Here, I select and get the updatable Rowset, and insert the row.
       
        statement.execute("SELECT TEST.* FROM TEST");
        ResultSet resultSet = statement.getResultSet();

        resultSet.moveToInsertRow();
        resultSet.updateInt(1, id);
        resultSet.updateString(2, "TEST" + id);

        resultSet.insertRow();

And here I committ.
       
        connection.commit();
     } catch (SQLException e) {
        connection.rollback();
       
        e.printStackTrace();
     }

Ok, in another application (Toad Free), I update the TEST table, but
don't commit.

I run the above application, and it blocks on Line 26
(resultSet.insertRow()). This isn't what I was expecting. I thought it
would block on Line 12 (the first statement.execute()). The
consequence of this is that suppose I inserted a row (5, 'test') in
Toad Free, and didn't commit, then ran the application, and the next
ID is 5, when Toad commits, my application will get a unique
constraint violation.

Second issue is this. I put a break point on line 26
(resultSet.insertRow()). While sitting on this row, I simulate a
network outage by disabling my network. I resume my program, and it
errors out as expected. This issue is that now Oracle has a lock on
the table, and doesn't seem to want to get rid of it. The ONLY way I
could release it was to kill the session in Oracle.

Any ideas what concepts I'm missing? Doing wrong?

Thanks.

--
now with more cowbell