Re: problem with transactions

pginfo wrote:
> Hi,
>
> We are using pg 7.4.2 + jdbc to connect to jboss.
>
> It looks that pg have problems with transactions and jdbc.
>
> My example:
>
> Connection con = ... I get connection to my db.
>
> con.setAutoCommit(false); // begin transaction
>
> // do some inserts
>
> con.rollback(); // rollback
>
> After this code all is working very well.
> All inserts are rollbacked and so on.
>
> And now the second example:
>
> Connection con = ... I get connection to my db.
>
> con.setAutoCommit(false); // begin transaction
>
> // do some inserts
>
> con.rollback(); // rollback
> con.setAutoCommit(true); // set autocommit. We are using connection
> pool and will to have clear connection.
>
> For my supprice the last example do not rollback any inserts !
> I do not see nothink bad in this code, and also it is working perfect on
> oracle + jdbc and also MSSQL + jdbc.
>
> Pls., can some one check the situation and post the result.

All I can guess from the above is that there is a bug in your connection
pool implementation, in jboss itself, or in the way you're using
transactions within jboss. Does jboss want you to hook into its
transaction manager rather than manage your own transactions? Needing to
reset autocommit when handing the connection back to the pool seems
suspicious, shouldn't the pool deal with that itself?

One possibility is that if something (jboss? connection pool?) is
intercepting the rollback() call before it reaches the JDBC driver, then
the subsequent setAutoCommit() will cause the inserts to be committed
(setAutoCommit() does an implicit commit() -- see the JDBC javadoc).

Either way it seems unlikely that rollback() is broken in the current
JDBC driver -- it's working just fine here.

Can you provide a selfcontained testcase that demonstrates the problem?
You may want to turn on statement logging on the server (log_statement =
true) to see exactly what statements the JDBC driver is sending.

Also, exactly which version of the JDBC driver are you using -- the one
supplied with 7.4.2? Does using a more recent driver from
jdbc.postgresql.org change the behaviour?

-O