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