Re: JDBC squirrely transaction behavior??

On Wed, 31 May 2000, Mark Dzmura wrote:

> dumb JDBC/SQL question:
>
> I have just moved some code over from pg 6.5.2 to pg 7.0 and am starting to modify an existing code base
> to use transactions.
>
> I need to add some records to masterfile tables in a particular order (due to foreign key dependencies) and
> want to use a transaction to allow aborting the whole shebang in the event any of the inserts fails.
>
> Below is a simple sample of code which shows the problem.
>
> ---------------------------------------------
>
> create table foos (foo_id serial primary key, foo_name varchar(64) unique);
>
> create table bars(bar_id serial primary key, foo_id int4 references foos, bar_len int4, bar_width int4);
>
> ...
>
> connection.setAutoCommit(false);
>
> while (true)
> {
> try
> {
> try
> {
> insert into foos (foo_name) values ('foo test value #1');
> }
> catch (SQLException e)
> {
> system.out.println("attempt to insert duplicate foo ... not a problem.");
> }
> // get the foo_id for this foo;
> rs = select foo_id from foos where foo_name='foo test value #1');
> if (rs.next()
> {
> int foo_id = rs.getInt(1);
> insert into bars (foo_id, bar_len, bar_width) values (foo_id, 25, 99);
> connection.Commit();
> }
> else
> {
> connection.Rollback();
> System.out.println("error: unable to lookup foo_id");
> throw new Exception("internal error...");
> }
> }
> catch (SQLException e)
> {
> System.out.println("bad things a-happenin");
> e.printStackTrace();
> }
> }
>

> This code will run for a number of insertions, then fail because the
> select of the foo_id returns no records. This should be impossible,
> because either (1) the foo_id was just added to the foos table, or (2)
> it had previously been added. Either way, there should be a record
> with a matching namefield.

I can see a problem here. Your first insert inserts foo_id, and fails if
foo_id already exists. In your code, you ignore this which is fine outside
a Transaction. However, because you are using a transaction, the
transaction is now in a failed state, so everything else done in that
transaction will be ignored.

So, I'd add in the catch clause of that block a connection.rollback()
call. That should then start a new transaction, which will then work for
the rest of the code.

The alternative is to but a connection.commit() after the insert, which
may work in this case, but you have to think about the integrity of your
tables.

> I then commented out the transaction stuff, and verified that the code ran properly.
>
> My first thought was that statement #2 was unable to see changes effected by
> statement #1, but because they are within the scope of the same transaction,
> they should be able to....strangely, this works for some records, but not others.
>
> Is the pg or jdbc support for transactions broken??

No, this looks like normal behaviour. Because foo_id already exists, the
first insert fails, and marks that transaction as void. PG will ignore
everything until the transaction ends, rolls back, or autocommit is
enabled.

> Finally, the API does not seem to offer support for nested transactions, which
> sometime come in handy... But even the JavaSoft-approved JDBC book
> written by the authors of JDBC does a lame job of covering transactions
> (and many other things...)

This is because Postgres doesn't support nested transactions. There's a
lot of JDBC internals that could be implemented easier if we had nested
transactions.

> Any guesses as to when the postgresql jdbc driver will support the JDBC 2.x extensions
> for record insert/update/delete via the special record in a result set ??

Possibly 7.1. It depends on if I get time between now and then. Time may
be tight for the next couple of months, as I may be moving home shortly,
and obviously I'll need to give that some priority ;-)

Peter

--
Peter T Mount peter(at)retep(dot)org(dot)uk
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
Java PDF Generator: http://www.retep.org.uk/pdf