Jdbc3PoolingDataSource default auto commit is false

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

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
>

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.

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(at)postgresql(dot)org so that your
>message can get through to the mailing list cleanly
>
>

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(at)postgresql(dot)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(at)postgresql(dot)org
>