Re: Connection Idle in transaction

Barry Lind wrote:

> > try to connect to your DB, do a "begin" and leave that transaction
> > sitting there for days...
>
> The problem here is that your application would allow a connection to
> the database to sit around for days. Even with your suggested changes
> to the driver, if a jdbc connection did a select (which would start a
> transaction) and then left that connection sit arround for days you
> would still have the same problem. Your suggested change to the driver
> just delays the point where a connection starts, but if you allow long
> lived connections to the database I contend that you will still see your
> problem since some code somewhere will simply issue a select and then
> hold an open idle connection for long periods of time.

If you're pooling connections, while it makes sense for the pool to
commit/rollback any transaction in progress when the connection is
released by a client, it's not obvious that the pool should immediately
reset the autocommit state of the connection. But the current driver
requires setting autocommit on idle connections to avoid keeping a
transaction open.

So you can have:

get connection from pool
conn.setAutoCommit(true);
conn.setAutoCommit(false); -> begin
stmt.executeUpdate("foo"); -> foo
commit() -> commit;begin
return connection to pool
rollback() -> rollback;begin

connection is idle in the pool for the next week.

get connection from pool
conn.setAutoCommit(true); -> commit
// client uses connection

This seems to be the main case where the change in behaviour is useful.

-O