Oracle JDBC Issues

From: Bryce (spamtrap_at_berzerker-soft.com)
Date: 03/23/05 

Date: Wed, 23 Mar 2005 17:47:48 -0500

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

Relevant Pages

  • Re: Unifying Temp table behavior across oracle, mssql
    ... CREATE GLOBAL TEMPORARY TABLE foo ON COMMIT PRESERVE ROWS; ... Dropping the table is simple - only on oracle does the truncation seem ... Because we have to support SQL Server, we have to be careful about ...
    (comp.databases.oracle.server)
  • RE: Slow connection to Oracle 9i
    ... A commit() should be issues only when necessary - the cost in the database of a commit is large and doing so in this random fashion is an invitation to other performance problems. ... Slow connection to Oracle 9i ... do not get a transaction too long error (can't remember exactly what its ...
    (perl.dbi.users)
  • Re: up front designs always useless
    ... >> so if I don't know up front whether I am going to use XML or an Oracle ... You can commit to risky decisions with little impact of changing ... decisions until as late as possible is an up front design decision. ...
    (comp.object)
  • RE: Slow connection to Oracle 9i
    ... we commit every 1000 or every 10000 rows. ... Slow connection to Oracle 9i ... transaction, and then committing after every row. ...
    (perl.dbi.users)
  • RE: Slow connection to Oracle 9i
    ... Slow connection to Oracle 9i ... Commit is the cheapest thing you can do in Oracle. ...
    (perl.dbi.users)