Re: Jdbc3PoolingDataSource default auto commit is false

See my comments below.

Cheers,
Csaba.

On Thu, 2003-04-03 at 19:21, Ryan Christianson wrote:
> Actually, I thought of setting the autocommit flag per connection and oh
> man is that bad. First it sent the "set autocommit = on" query, then it
> did my query, then did a "set autocommit = off;" query, then I have no
> idea why it did a "SELECT 1;" query.
The autocommit is set again to the default value (which is off) by the
pooled connection implementation after you return the connection  tho
the pool.
The "SELECT 1" is placed there by the JDBC driver to avoid warnings
(errors? don't remember) on a subsecvent commit/rollback when you don't
execute any query and return the connection to the pool.
>
> I agree that the default should not be false, if you look at the
> javadocs for "java.sql.Connection" it says "By default, new connections
> are in auto-commit mode."
>
> Here is a sample of the logs that I got when I was setting the
> connections to autocommit, using  Connection.setAutoCommit.||
>
> 2003-04-01 11:03:49 [8048]   LOG:  query: commit; set autocommit = on;
> 2003-04-01 11:03:49 [8048]   LOG:  duration: 0.000592 sec
> 2003-04-01 11:03:49 [8048]   LOG:  query: -- MY FRIST QUERY --
> 2003-04-01 11:03:49 [8048]   LOG:  duration: 0.142125 sec
> 2003-04-01 11:03:49 [8048]   LOG:  query: set autocommit = off;
> 2003-04-01 11:03:49 [8048]   LOG:  duration: 0.000421 sec
> 2003-04-01 11:03:49 [8048]   LOG:  query: select 1;
> 2003-04-01 11:03:49 [8048]   LOG:  duration: 0.000190 sec
> 2003-04-01 11:03:49 [8048]   LOG:  query: commit; set autocommit = on;
> 2003-04-01 11:03:49 [8048]   LOG:  duration: 0.000592 sec
> 2003-04-01 11:03:49 [8048]   LOG:  query: -- MY SECOND QUERY --
> 2003-04-01 11:03:49 [8048]   LOG:  duration: 0.001855 sec
> 2003-04-01 11:03:49 [8048]   LOG:  query: set autocommit = off;
> 2003-04-01 11:03:49 [8048]   LOG:  duration: 0.000341 sec
> 2003-04-01 11:03:49 [8048]   LOG:  query: select 1;
> 2003-04-01 11:03:49 [8048]   LOG:  duration: 0.000132 sec
>
>
>
> Csaba Nagy wrote:
>
> >You still could have set the autocommit flag on a per connection basis.
> >The default autocommit is the autocommit value a newly checked out
> >connection will have, but you can set it to on after checking out the
> >connection from the pool.
> >And yes, when autocommit is off, you need to commit/rollback after your
> >queries.
> >BTW, I find the autocommit being off by default as rather useless and
> >confusing, as people will know how to explicitly open a transaction when
> >they want to have one wrapping more queries, and queries not explicitly
> >wrapped in transactions are usually to be committed immediately. It is
> >confusing, as your case clearly demonstrates.
> >
> >HTH,
> >Csaba.
> >
> >On Wed, 2003-04-02 at 19:55, Ryan Christianson wrote:
> >
> >
> >>Hi.
> >>
> >>I am useing Jdbc3PoolingDataSource to do database pooling. When I first
> >>started using it some of my updates where not taking effect. So I
> >>started enabled postgres logging and watched my queries, and I found out
> >>why. The Jdbc3PoolingDataSource by default sets setDefaultAutoCommit to
> >>false, and because of that, after every query it was calling "rollback".
> >>
> >>To fix it, I extended Jdbc3PoolingDataSource, over-wrote the
> >>createConnectionPool like so:
> >>    protected ConnectionPool createConnectionPool() {
> >>        ConnectionPool pool = super.createConnectionPool();
> >>        pool.setDefaultAutoCommit(true);
> >>        return pool;
> >>    }
> >>
> >>So my question is, how should I have worked with defaultAutoCommit set
> >>to true? Would I need to wrap all of my queries in begin; and commit; ?
> >>
> >>Thanks
> >>
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 4: Don't 'kill -9' the postmaster
> >>
> >>
> >>
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 3: if posting/reading through Usenet, please send an appropriate
> >subscribe-nomail command to majordomo@postgresql.org so that your
> >message can get through to the mailing list cleanly
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>