tomcat 8.5.23 dbcp not honoring autocommit = false?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

tomcat 8.5.23 dbcp not honoring autocommit = false?

Chris Cheshire-2
Working on a migration from 7 to 8.5, and in it I am now using the
tomcat dbcp, instead of apache commons dbcp. I have found that with no
other changes to the db code (except the factory param for the
resource), it is working fine other than there is an implicit commit
happening when I close a connection, even with autocommit turned off
in mysql config, resource config AND in my code.

Resource config :

<Resource name="jdbc/mysql"
          auth="Container"
          type="javax.sql.DataSource"
          driverClassName="com.mysql.jdbc.Driver"
          url="jdbc:mysql://localhost:3306/mydb?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false"
          factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
          username=""
          password=""
          maxActive="150"
          maxIdle="25"
          maxWait="60000"
          removeAbandoned="true"
          removeAbandonedTimeout="300"
          logAbandoned="true"
          validationQuery="/* ping */"
          testOnBorrow="true"
          testWhileIdle="true"
          timeBetweenEvictionRunsMillis="600000"
          defaultAutoCommit="false" />

Only thing changed in that from 7.x to 8.5.x is the factory was
org.apache.commons.dbcp.BasicDataSourceFactory. I am using Connector/J
5.1.44 (latest version).


Getting a connection boils down to this in my code (pieces pulled out
of factories and other classes)

(Support class in web code)
public static DataSource getDataSource() {
    try {
        return (DataSource)new
InitialContext().lookup("java:comp/env/" +
ServletContextParameters.getDatabaseResourceName());
    }
    catch (NamingException ex) {
        throw new RuntimeException("unable to find datasource", ex);
    }
}


(DAO Factory implementation)
public MySQLDAOFactoryImpl(@NotNull DataSource dataSource) {
    this.dataSource = dataSource;

    try {
        this.dbConn = this.dataSource.getConnection();
        this.dbConn.setAutoCommit(false);
        this.dbConn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    }
    catch (SQLException ex) {
        throw new DAOException("unable to get database connection", ex);
    }
}

@Override
public void close() {
    try {
        if (this.dbConn != null) {
            this.dbConn.close();
        }
    }
    catch (SQLException ex) {
        throw new DAOException("error closing database connection", ex);
    }
}


If I do

daoFactory = new MySQLDAOFactoryImpl(getDataSource());

// update #1
daoFactory.commit()

// update #2
daoFactory.close();

then update #2 is being committed.

If I put in this in the close() method of my DAO Factory

if (!this.dbConn.getAutoCommit()) {
    this.dbConn.rollback();
}

before the close() call, then update #2 is correctly not getting committed.

I looked back through the recent tomcat changelogs, and found a
reference to https://bz.apache.org/bugzilla/show_bug.cgi?id=61425
under the 8.5.21 release which looks like it might be addressing this
problem. However, when I download the source for 8.5.23, there is no
org.apache.tomcat.jdbc directory so I can't dive in there.

Anyone else experienced this? Hopefully I am just missing something obvious.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: tomcat 8.5.23 dbcp not honoring autocommit = false?

Christopher Schultz-2
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256

Chris,

On 10/11/17 5:21 PM, Chris Cheshire wrote:
> Working on a migration from 7 to 8.5, and in it I am now using the
>  tomcat dbcp, instead of apache commons dbcp.> I have found that
> with no other changes to the db code (except the factory param for
> the resource), it is working fine other than there is an implicit
> commit happening when I close a connection, even with autocommit
> turned off in mysql config, resource config AND in my code.
Your complaint is very close to my heart, here. <3

Back in 2003 or so, I posted roughly this exact question to this
mailing list with a little less ... diplomacy, shall we say?

> try { this.dbConn = this.dataSource.getConnection();
> this.dbConn.setAutoCommit(false);
> this.dbConn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMIT
TED);
>
>
}
> catch (SQLException ex) { throw new DAOException("unable to get
> database connection", ex); }

I'll bet you've had this problem for a really long time, but just
didn't notice it until now.

The core problem is that you have autocommit=false in your
configuration and autocommit=true in your code. If an exception occurs
and you don't rollback the transaction, the connection pool will reset
all of the settings to your configured settings (including
autocommit=true). Setting autocommit=true when autocommit=false
commits the transaction, which is SUPER surprising to anyone who
hasn't read the Javadoc[1]

Technically, this happens whether you encounter an exception or not,
but it's fairly rare to have code that intentionally does this:

conn.setAutoCommit(false);
// UPDATE ...;
conn.close();

So, given that this is usually an "exceptional" situation, it's your
exceptions you need to carefully handle. In fact, you need to do more
than you are used to doing.

Have a look at this post I did years later when related questions kept
coming up on the list:
http://blog.christopherschultz.net/index.php/2009/03/16/properly-handlin
g-pooled-jdbc-connections/

Hope that helps,
- -chris
-----BEGIN PGP SIGNATURE-----
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIzBAEBCAAdFiEEMmKgYcQvxMe7tcJcHPApP6U8pFgFAlngMBoACgkQHPApP6U8
pFhdjBAAl2u3lxKhrjKdmtjUgtYp0/gOfTPq7jXjdgZBrkSNh9q6I8dJ6gQOsECV
TkCQ2LH8tAUpuUYWCt7QJRLQPVAuwrzplue1InlTFCS8I1b/WK7vQk93teB4I0Ia
HKaC4RGGtgKgS//PsCRxDdFgo0Hzr+hA+nte1qFytmla8ZRZjimbz5EnWJpNwYoU
XjEjhaF6wdVOHP8zKU9/CIc3XQKC1kfQb9Rodb9SZTpjFDTw12NsBjEXG5evY8XJ
A2PPxHPiRdyyHq2R1MDWGFWdSdCsY4pFq4nvNExEuIuHg1/C+GlGUENLH3MiJNPY
minxeaKykVfmUd2zJvWjxmhrdw8nHT3ThtAHA0BgU7thBCenANFbSBxx7+39Jxg/
eDEW4dEqOP3c/ZvifpMrGxjJ0zXBXDu7Jik4KFEzMWI8oaAl3hSSDwEe7FEJE41Y
lDv+LjcBgDwSHLfTbau+0xJx79wAKTAFY7v7uGujUgDZqWsRG1znyZx+OuZnbWxQ
JSbQSJ3pOMerybeJMuHx1a4y+HwA4t0GtLigeRMeyFvTqqtfCVasr3ONMh22XYIU
OORbLADRwjbqYnswvxRC06FfKvU8AhNwuybt/XzHrTfURuIZWx7GRycMlaEMaUdS
OdWlW6Zf3+fWfUxg05zIX3q2Ug4h2O1zW+ccPOrs5bswE9EEAZM=
=eyik
-----END PGP SIGNATURE-----

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: tomcat 8.5.23 dbcp not honoring autocommit = false?

Chris Cheshire-2
On Thu, Oct 12, 2017 at 11:16 PM, Christopher Schultz
<[hidden email]> wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA256
>
> Chris,
>
> On 10/11/17 5:21 PM, Chris Cheshire wrote:
>> Working on a migration from 7 to 8.5, and in it I am now using the
>>  tomcat dbcp, instead of apache commons dbcp.> I have found that
>> with no other changes to the db code (except the factory param for
>> the resource), it is working fine other than there is an implicit
>> commit happening when I close a connection, even with autocommit
>> turned off in mysql config, resource config AND in my code.
> Your complaint is very close to my heart, here. <3
>
> Back in 2003 or so, I posted roughly this exact question to this
> mailing list with a little less ... diplomacy, shall we say?
>
>> try { this.dbConn = this.dataSource.getConnection();
>> this.dbConn.setAutoCommit(false);
>> this.dbConn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMIT
> TED);
>>
>>
> }
>> catch (SQLException ex) { throw new DAOException("unable to get
>> database connection", ex); }
>
> I'll bet you've had this problem for a really long time, but just
> didn't notice it until now.
>

Nope, only since swapping from commons dbcp (tomcat 7.x) to tomcat
dbcp in development.
I started with 8.5.20 and upgraded yesterday to 8.5.23 and it still
exhibits this behaviour.

> The core problem is that you have autocommit=false in your
> configuration and autocommit=true in your code. If an exception occurs
> and you don't rollback the transaction, the connection pool will reset
> all of the settings to your configured settings (including
> autocommit=true). Setting autocommit=true when autocommit=false
> commits the transaction, which is SUPER surprising to anyone who
> hasn't read the Javadoc[1]
>

I *don't* have autocommit=true in code, unless

this.dbConn.setAutoCommit(false);

doesn't mean what I think it means. You even have it in your example!


> Technically, this happens whether you encounter an exception or not,
> but it's fairly rare to have code that intentionally does this:
>
> conn.setAutoCommit(false);
> // UPDATE ...;
> conn.close();
>
> So, given that this is usually an "exceptional" situation, it's your
> exceptions you need to carefully handle. In fact, you need to do more
> than you are used to doing.
>
> Have a look at this post I did years later when related questions kept
> coming up on the list:
> http://blog.christopherschultz.net/index.php/2009/03/16/properly-handlin
> g-pooled-jdbc-connections/
>

I have autocommit set to false in 3 ways :

1) /etc/my.conf : autocommit=0
2) context.xml  resource def : defaultAutoCommit=false
3) in code : dbConn.setAutoCommit(false)

When I query autocommit on the connection it returns false, yet
transactions are being committed when I issue a close() on the
connection after making changes and not explicitly committing.

Color me very, very confused.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: tomcat 8.5.23 dbcp not honoring autocommit = false?

Chris Cheshire-2
</snip>

As a further test I just took out my explicit rollback in my
DAOFactory close() method, and swapped back to commons dbcp. Added an
update that wasn't explicitly committed, and it correctly did not get
committed when the connection was closed. Swapped back to tomcat dbcp
and repeated, it got committed without an explicit commit statement.

I'm really puzzled as to why *I* have to explicitly rollback on close
if autocommit is not enabled, instead of tomcat dbcp handling that
when commons dbcp appears to do it.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: tomcat 8.5.23 dbcp not honoring autocommit = false?

Christopher Schultz-2
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256

Chris,

Sorry, I had the autocommit true/false values mixed up in my mind.

When you say you have "autocommit disabled in mysql config" what do
you mean?

On 10/13/17 10:17 AM, Chris Cheshire wrote:

> </snip>
>
> As a further test I just took out my explicit rollback in my
> DAOFactory close() method, and swapped back to commons dbcp. Added
> an update that wasn't explicitly committed, and it correctly did
> not get committed when the connection was closed. Swapped back to
> tomcat dbcp and repeated, it got committed without an explicit
> commit statement.
>
> I'm really puzzled as to why *I* have to explicitly rollback on
> close if autocommit is not enabled, instead of tomcat dbcp handling
> that when commons dbcp appears to do it.

No connection pool can read your mind. If you begin a transaction (or
never start one), you must either commit or rollback. Merely calling
close() does not explicitly cause either of those to be called.

> If I do
>
> daoFactory = new MySQLDAOFactoryImpl(getDataSource());
>
> // update #1 daoFactory.commit()
>
> // update #2 daoFactory.close();
>
> then update #2 is being committed.

I'm curious why you are doing "update #2" without either COMMIT or
ROLLBACK. That seems like ... a mistake.

- From the Connection.close() javadoc:

"
It is strongly recommended that an application explicitly commits or
rolls back an active transaction prior to calling the close method. If
the close method is called and there is an active transaction, the
results are implementation-defined.
"

There *is* an implicit COMMIT executed if the autocommit flag is
flipped for any reason, either true->false or false->true.

If you have autocommit=false in your <Resource> configuration (which
you do), then calling setAutoCommit(false) shouldn't do anything.

> If I put in this in the close() method of my DAO Factory
>
> if (!this.dbConn.getAutoCommit()) { this.dbConn.rollback(); }
>
> before the close() call, then update #2 is correctly not getting
> committed.

This is probably the wrong approach: your close() method doesn't know
whether it's better to call commit() or rollback(), so it should do
neither.

- -chris
-----BEGIN PGP SIGNATURE-----
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIzBAEBCAAdFiEEMmKgYcQvxMe7tcJcHPApP6U8pFgFAlnhKXEACgkQHPApP6U8
pFhZmw/9FN376aEpeGwzLRnpWy0jIo7EezNXeV4G5Jtj1slDeqbFMOYcm/3bdSEt
sa+9FwHbe9gKMyeLWp3TJbDZ8F0RI+e2CIYV37XAchrDvpb+f80M+SjzPyuovO6a
9wRtbekKd8mbIfpGTfokjW+pNIJBbAJvZfh0UGFJP+VRX5XoE6MLzw60OnEMlmLP
7IFAdvM0t816Nuh7yJIg63I3eHYB3P7cx01ETEhWyI1oK2LQ50cODfgLoxT3iC+j
f8HAyn8wubZSkC3PKJ/oY8TGaLczSt8M/ANAucZ0mw91j7m93OB+3KGwVczGRUiD
lodMS9RZBsGmNxxzKcCUCmxydat2PRYJSmP/hRR0nQL7xRPiClAi4KJTWFCdn0hc
SNZp8shT9Z5EVBNHJ9z2ippW7K5Xr+U58bYrN+kEmq8jN5UTUcQlDoahwnRbRWV5
CCBX+9P+fd44yWuK5IGkFcuKr68LmZYHarZlL5OTxfretKB7QX9B4o4x6J8PhhFj
vnfJPlQCkKkuRyuSGTneLqM+f+CrQq7nquVxUmgUPF2btqNe1d21/g8AN8+FSdKS
YgiZ/OstBdoDHVxnfxDJQcwY8IG9qg/didN7oaNAdelulcdIFx8Ob7PEdEmXZlrV
o3vs2ntI7dCjfr0vfbvkzqrwXGgEMIPvukggB+DTgRxpYKJfCRM=
=/fEQ
-----END PGP SIGNATURE-----

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: tomcat 8.5.23 dbcp not honoring autocommit = false?

Chris Cheshire-2
On Fri, Oct 13, 2017 at 5:00 PM, Christopher Schultz
<[hidden email]> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----

> When you say you have "autocommit disabled in mysql config" what do
> you mean?
>

/etc/my.cnf :
[mysqld]
autocommit=0

This turns off autocommit off as a default for all connections. I need
this at a minimum
for the mysql client, but in the absence of any other configuration it
should be the
default for a connection from any client.

> On 10/13/17 10:17 AM, Chris Cheshire wrote:
>> </snip>
>>
>> As a further test I just took out my explicit rollback in my
>> DAOFactory close() method, and swapped back to commons dbcp. Added
>> an update that wasn't explicitly committed, and it correctly did
>> not get committed when the connection was closed. Swapped back to
>> tomcat dbcp and repeated, it got committed without an explicit
>> commit statement.
>>
>> I'm really puzzled as to why *I* have to explicitly rollback on
>> close if autocommit is not enabled, instead of tomcat dbcp handling
>> that when commons dbcp appears to do it.
>
> No connection pool can read your mind. If you begin a transaction (or
> never start one), you must either commit or rollback. Merely calling
> close() does not explicitly cause either of those to be called.
>

And that's just it. If I don't explicitly commit, then why are changes being
committed when the connection is closed and returned back to the pool?

>> If I do
>>
>> daoFactory = new MySQLDAOFactoryImpl(getDataSource());
>>
>> // update #1 daoFactory.commit()
>>
>> // update #2 daoFactory.close();
>>
>> then update #2 is being committed.
>
> I'm curious why you are doing "update #2" without either COMMIT or
> ROLLBACK. That seems like ... a mistake.
>

Correct. This is an example to illustrate a mistake I found in my code. I
found a servlet that actually wasn't explicitly committing when it should
have been, yet everything it was doing was being committed to the database.

> - From the Connection.close() javadoc:
>
> "
> It is strongly recommended that an application explicitly commits or
> rolls back an active transaction prior to calling the close method. If
> the close method is called and there is an active transaction, the
> results are implementation-defined.
> "
>

If a commit is not being explicitly issued, then the commit behaviour
should honor that of the connection, yes?


> There *is* an implicit COMMIT executed if the autocommit flag is
> flipped for any reason, either true->false or false->true.
>
> If you have autocommit=false in your <Resource> configuration (which
> you do), then calling setAutoCommit(false) shouldn't do anything.
>
>> If I put in this in the close() method of my DAO Factory
>>
>> if (!this.dbConn.getAutoCommit()) { this.dbConn.rollback(); }
>>
>> before the close() call, then update #2 is correctly not getting
>> committed.
>
> This is probably the wrong approach: your close() method doesn't know
> whether it's better to call commit() or rollback(), so it should do
> neither.

I realise this too, however I have to have it in otherwise if an
exception is thrown,
then work is being committed regardless of the fact that I have autocommit
turned OFF in 3 levels, all  the way back to the mysqld configuration.

This behaviour does not happen with commons dbcp, only tomcat dbcp. There is a
difference in default behaviour between the two pools when a transaction is
not explicitly committed or rolled back when a connection is closed and
returned to the pool.

I use a try-with-resources/finally to open and close my database connections,
thus short of a JVM crash, any exceptions thrown will always close the
connections
and return them to the pool.


Thought: Perhaps the transaction marker is not being reset when a
pooled connection is being handed out to a subsequent request for a
connection, and that new servlet's work is committing the work from the first
servlet that should not have been committed.

Chris

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: tomcat 8.5.23 dbcp not honoring autocommit = false?

kfujino
In reply to this post by Chris Cheshire-2
Hi

You have set factory="org.apache.tomcat.jdbc.pool.DataSourceFactory".
In other words, you do not use (tomcat)DBCP, you are using Tomcat jdbc-pool.

In DBCP, the default of rollbackOnReturn attribute is true.
However, in Tomcat jdbc-pool, the default of rollbackOnReturn( and
commitOnReturn
) attribute are false.

see:
http://commons.apache.org/proper/commons-dbcp/configuration.html
http://tomcat.apache.org/tomcat-8.5-doc/jdbc-pool.html


2017-10-12 6:21 GMT+09:00 Chris Cheshire <[hidden email]>:

> Working on a migration from 7 to 8.5, and in it I am now using the
> tomcat dbcp, instead of apache commons dbcp. I have found that with no
> other changes to the db code (except the factory param for the
> resource), it is working fine other than there is an implicit commit
> happening when I close a connection, even with autocommit turned off
> in mysql config, resource config AND in my code.
>
> Resource config :
>
> <Resource name="jdbc/mysql"
>           auth="Container"
>           type="javax.sql.DataSource"
>           driverClassName="com.mysql.jdbc.Driver"
>           url="jdbc:mysql://localhost:3306/mydb?useUnicode=true&amp;
> characterEncoding=utf8&amp;useSSL=false"
>           factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
>           username=""
>           password=""
>           maxActive="150"
>           maxIdle="25"
>           maxWait="60000"
>           removeAbandoned="true"
>           removeAbandonedTimeout="300"
>           logAbandoned="true"
>           validationQuery="/* ping */"
>           testOnBorrow="true"
>           testWhileIdle="true"
>           timeBetweenEvictionRunsMillis="600000"
>           defaultAutoCommit="false" />
>
> Only thing changed in that from 7.x to 8.5.x is the factory was
> org.apache.commons.dbcp.BasicDataSourceFactory. I am using Connector/J
> 5.1.44 (latest version).
>
>
> Getting a connection boils down to this in my code (pieces pulled out
> of factories and other classes)
>
> (Support class in web code)
> public static DataSource getDataSource() {
>     try {
>         return (DataSource)new
> InitialContext().lookup("java:comp/env/" +
> ServletContextParameters.getDatabaseResourceName());
>     }
>     catch (NamingException ex) {
>         throw new RuntimeException("unable to find datasource", ex);
>     }
> }
>
>
> (DAO Factory implementation)
> public MySQLDAOFactoryImpl(@NotNull DataSource dataSource) {
>     this.dataSource = dataSource;
>
>     try {
>         this.dbConn = this.dataSource.getConnection();
>         this.dbConn.setAutoCommit(false);
>         this.dbConn.setTransactionIsolation(Connection.TRANSACTION_READ_
> COMMITTED);
>     }
>     catch (SQLException ex) {
>         throw new DAOException("unable to get database connection", ex);
>     }
> }
>
> @Override
> public void close() {
>     try {
>         if (this.dbConn != null) {
>             this.dbConn.close();
>         }
>     }
>     catch (SQLException ex) {
>         throw new DAOException("error closing database connection", ex);
>     }
> }
>
>
> If I do
>
> daoFactory = new MySQLDAOFactoryImpl(getDataSource());
>
> // update #1
> daoFactory.commit()
>
> // update #2
> daoFactory.close();
>
> then update #2 is being committed.
>
> If I put in this in the close() method of my DAO Factory
>
> if (!this.dbConn.getAutoCommit()) {
>     this.dbConn.rollback();
> }
>
> before the close() call, then update #2 is correctly not getting committed.
>
> I looked back through the recent tomcat changelogs, and found a
> reference to https://bz.apache.org/bugzilla/show_bug.cgi?id=61425
> under the 8.5.21 release which looks like it might be addressing this
> problem. However, when I download the source for 8.5.23, there is no
> org.apache.tomcat.jdbc directory so I can't dive in there.
>
> Anyone else experienced this? Hopefully I am just missing something
> obvious.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
> --
> Keiichi.Fujino
> <[hidden email]>

<[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: tomcat 8.5.23 dbcp not honoring autocommit = false?

Chris Cheshire-2
On Tue, Oct 17, 2017 at 3:44 AM, Keiichi Fujino <[hidden email]> wrote:
> Hi
>
> You have set factory="org.apache.tomcat.jdbc.pool.DataSourceFactory".
> In other words, you do not use (tomcat)DBCP, you are using Tomcat jdbc-pool.

That's what I meant sorry. Was comparing to commons-dbcp and went
dyslexic on the acronyms.

>
> In DBCP, the default of rollbackOnReturn attribute is true.
> However, in Tomcat jdbc-pool, the default of rollbackOnReturn( and
> commitOnReturn
> ) attribute are false.
>
> see:
> http://commons.apache.org/proper/commons-dbcp/configuration.html
> http://tomcat.apache.org/tomcat-8.5-doc/jdbc-pool.html
>
>

Now that explains it entirely. Thank you so much!

Part of this is me failing to RTFM entirely, and then part is the nature of
configuration references - if you don't know what you are looking for
it is easy to miss
important details. It would be helpful if that page explained some
of the fundamental differences from a usage perspective (not just implementation
improvements) but that's another story.

Cheers,

Chris

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: tomcat 8.5.23 dbcp not honoring autocommit = false?

Christopher Schultz-2
In reply to this post by Chris Cheshire-2
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256

Chris,

On 10/16/17 9:43 AM, Chris Cheshire wrote:

> On Fri, Oct 13, 2017 at 5:00 PM, Christopher Schultz
> <[hidden email]> wrote:
>> -----BEGIN PGP SIGNED MESSAGE-----
>
>> When you say you have "autocommit disabled in mysql config" what
>> do you mean?
>>
>
> /etc/my.cnf : [mysqld] autocommit=0
>
> This turns off autocommit off as a default for all connections.

It only affects connections from MySQL's "mysql" command-line client.
It does not affect e.g. Java-based clients.

> I need this at a minimum for the mysql client, but in the absence
> of any other configuration it should be the default for a
> connection from any client.

The JDBC spec says that all connections are auto-commit unless
otherwise specified. So if you are creating your own connections or
using e.g. a connection-pool then you'll have to make sure that you
configure them to be NOT auto-commit. This is not a setting that you
can control from the server.

More below.

>> On 10/13/17 10:17 AM, Chris Cheshire wrote:
>>> </snip>
>>>
>>> As a further test I just took out my explicit rollback in my
>>> DAOFactory close() method, and swapped back to commons dbcp.
>>> Added an update that wasn't explicitly committed, and it
>>> correctly did not get committed when the connection was closed.
>>> Swapped back to tomcat dbcp and repeated, it got committed
>>> without an explicit commit statement.
>>>
>>> I'm really puzzled as to why *I* have to explicitly rollback
>>> on close if autocommit is not enabled, instead of tomcat dbcp
>>> handling that when commons dbcp appears to do it.
>>
>> No connection pool can read your mind. If you begin a transaction
>> (or never start one), you must either commit or rollback. Merely
>> calling close() does not explicitly cause either of those to be
>> called.
>>
>
> And that's just it. If I don't explicitly commit, then why are
> changes being committed when the connection is closed and returned
> back to the pool?
>
>>> If I do
>>>
>>> daoFactory = new MySQLDAOFactoryImpl(getDataSource());
>>>
>>> // update #1 daoFactory.commit()
>>>
>>> // update #2 daoFactory.close();
>>>
>>> then update #2 is being committed.
>>
>> I'm curious why you are doing "update #2" without either COMMIT
>> or ROLLBACK. That seems like ... a mistake.
>>
>
> Correct. This is an example to illustrate a mistake I found in my
> code. I found a servlet that actually wasn't explicitly committing
> when it should have been, yet everything it was doing was being
> committed to the database.
>
>> - From the Connection.close() javadoc:
>>
>> " It is strongly recommended that an application explicitly
>> commits or rolls back an active transaction prior to calling the
>> close method. If the close method is called and there is an
>> active transaction, the results are implementation-defined. "
>>
>
> If a commit is not being explicitly issued, then the commit
> behaviour should honor that of the connection, yes?

Yes, but it's more complicated than that. Any change to the
connection's settings (which happen ALL THE TIME when the connection
is being returned to a connection pool) will cause an implicit COMMIT.
That's why it's super important for you to either COMMIT or ROLLBACK
yourself.

Note that "autocommit = false" doesn't mean "autorollback=true".
Best-case scenario for you there is that the transaction gets
committed *later* when another piece of your code grabs a connection
from the pool, does its work (successfully) and issues a COMMIT.

It's just NOT the pool's job nor the driver's job to clean-up after
any messes created by your code.

>> There *is* an implicit COMMIT executed if the autocommit flag is
>> flipped for any reason, either true->false or false->true.
>>
>> If you have autocommit=false in your <Resource> configuration
>> (which you do), then calling setAutoCommit(false) shouldn't do
>> anything.
>>
>>> If I put in this in the close() method of my DAO Factory
>>>
>>> if (!this.dbConn.getAutoCommit()) { this.dbConn.rollback(); }
>>>
>>> before the close() call, then update #2 is correctly not
>>> getting committed.
>>
>> This is probably the wrong approach: your close() method doesn't
>> know whether it's better to call commit() or rollback(), so it
>> should do neither.
>
> I realise this too, however I have to have it in otherwise if an
> exception is thrown, then work is being committed regardless of the
> fact that I have autocommit turned OFF in 3 levels, all  the way
> back to the mysqld configuration.

The mysqld configuration is not relevant, here. Are you able to run
your code through a debugger to see when the COMMIT is happening?

> This behaviour does not happen with commons dbcp, only tomcat dbcp.
> There is a difference in default behaviour between the two pools
> when a transaction is not explicitly committed or rolled back when
> a connection is closed and returned to the pool.

Can you create a SSCCE test-case which demonstrates this problem where
it behaves one way with tomcat-pool versus commons-dbcp? Something
simple like "java RunTest" where it prints out "expected behavior" or
"unexpected behavior" would be great. Remember that JavaDB (aka Derby)
is built-into JVMs before 9.0 so you don't need any complicated
database setup.

> I use a try-with-resources/finally to open and close my database
> connections, thus short of a JVM crash, any exceptions thrown will
> always close the connections and return them to the pool.

Good. Will those exceptions cause a ROLLBACK to occur? If not, your
code is broken.

> Thought: Perhaps the transaction marker is not being reset when a
> pooled connection is being handed out to a subsequent request for
> a connection, and that new servlet's work is committing the work
> from the first servlet that should not have been committed.

That absolutely could be the case.

Like I said: it's neither the driver's not the pool's job to clean up
after the messes made by your code.

- -chris
-----BEGIN PGP SIGNATURE-----
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQJRBAEBCAA7FiEEMmKgYcQvxMe7tcJcHPApP6U8pFgFAlnyMNgdHGNocmlzQGNo
cmlzdG9waGVyc2NodWx0ei5uZXQACgkQHPApP6U8pFhiUw/+Jj8vW+DU76rBUvbq
nUuM69wZWuD9oA5vjJdOgxo+5Zp51oUXZHlFXQe6ZhTlR020f6o/Y4iQ7HlljE2g
k1S0tThPUxDoL66hcKb1ctOvg3Rj1ncVfPqq/MC8mizkUMQstN7T4Qj3NmAIpqeY
ntLlHu4cOf8Bb11X9ayZWYSFPBqoQEBlAOQqVi9r98ASzLDQNdQ0KGhmZ5hPPB1F
7VrneucaOFBL6KOKIirf5XyP6RPwUXBRrVAzICUrL1XC80Z5W1ScvOTWI/l1YhA6
j2P5H2+WvIb9KROak6LcWVnJ7a1cC05E7KQJ3H+bvr0MWsrZD5CYGuiyjL78R3Am
+OnHg8AKxfKKvIHlfUDae6M0rQCS/XE+QtPS1CVV8hLqHvuAQu+rUgtkssb+K1BE
lRWPiTDCKL4nN6wc6G7UnATuoAan0J4I5SKkgdEy+LTiSey01JBxwOIXpscEuJ7g
ksPhdcji309wQB3ATvcZRBlOifbSoDUpxXj2KUta3wE0pxi4n+I8sn1e4EmCcD2a
jPo6IiKl36AhllzLDXM7Gr1mBRoMnSK1Lg/uy4R+DWxcIAmgifSdObVMVLRJqWE3
AuWWWw5qXyH4sxvZly7yobfq79Vr69z+/5Rmh0Ye7yOLhY0odDxAuHQoFzuIP6qG
s5MqJ6vEAUQ4FeuJKrR3P8mqdz4=
=eAGQ
-----END PGP SIGNATURE-----

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: tomcat 8.5.23 dbcp not honoring autocommit = false?

Christopher Schultz-2
In reply to this post by Chris Cheshire-2
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256

Chris and Keiichi,

On 10/17/17 5:31 PM, Chris Cheshire wrote:

> On Tue, Oct 17, 2017 at 3:44 AM, Keiichi Fujino
> <[hidden email]> wrote:
>> Hi
>>
>> You have set
>> factory="org.apache.tomcat.jdbc.pool.DataSourceFactory". In other
>> words, you do not use (tomcat)DBCP, you are using Tomcat
>> jdbc-pool.
>
> That's what I meant sorry. Was comparing to commons-dbcp and went
> dyslexic on the acronyms.
>
>>
>> In DBCP, the default of rollbackOnReturn attribute is true.
>> However, in Tomcat jdbc-pool, the default of rollbackOnReturn(
>> and commitOnReturn ) attribute are false.
>>
>> see:
>> http://commons.apache.org/proper/commons-dbcp/configuration.html 
>> http://tomcat.apache.org/tomcat-8.5-doc/jdbc-pool.html
>>
>>
>
> Now that explains it entirely. Thank you so much!
>
> Part of this is me failing to RTFM entirely, and then part is the
> nature of configuration references - if you don't know what you are
> looking for it is easy to miss important details. It would be
> helpful if that page explained some of the fundamental differences
> from a usage perspective (not just implementation improvements) but
> that's another story.

I didn't realize that these two libraries had different default
semantics. IMO, Tomcat's jdbc-pool behaves _correctly_ in that it
doesn't issue an unnecessary ROLLBACK on every connection returned to
the pool.

- -chris
-----BEGIN PGP SIGNATURE-----
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQJRBAEBCAA7FiEEMmKgYcQvxMe7tcJcHPApP6U8pFgFAlnyMUcdHGNocmlzQGNo
cmlzdG9waGVyc2NodWx0ei5uZXQACgkQHPApP6U8pFij4w//Q5tGBgwdcXsT9k/V
Raop4RalDLSeJNhTDY2FIMpDNHnvcq45BrOqkO3GhukyuNxpn/aJrUsE6ucbXN/3
jeb32JDEJ5f8FK5A6t9ZCeEG84p5BNDEfQUJEJQA5G67tBrhYzGfbW0t4X4qMyuM
l/xrVBLVP9JCLdjWUg/VnTyYhNKZGkIHYXzX5QtmZtjTRH+OBzCTuTT4B54WhkWM
MIFn5WYXxOnP6/+nR4f4rcV8VPKiJOoYuTv+dxt/Ih4IiGPNh+xUIzNS+lCTiagK
rsWDq51pqkH05nHdJTZ2+aZJEs/mFWXaCyjU3BOvIEPaUKaBJgjxjGBrIL1Tb/a9
jVEU3WrRJ3jNSNGYeN9kBfaL2dtB4W/H1btk1WAu39IuAdD/ZEcr8VgyIbteP6ar
StHXm2g5xt88X8ndgIo/2jdZisZ8LBNFksZ5yMXRuJckw3FN/YeLzHbe3gbPFXtv
K4xMFlI8OZFqr89EuNzzQjsris0gy6k3EFfQGz5tJDK7yiTNIEBfQbjGj9eFyYoA
7V2f7HvUIVmiNnRpCdg0CihdOypzIrXAZePOYTJGzkER0pUlGqt4ue4+6o5i8GSf
lap1sScK7Q6FiOGAvtQ3ZE+WMXvUFqGrs//O0pIi67KwXXHu+WsDV7av1BupZ0/F
hFkomQ3hHYAJoDoEJJHkn7jRXpY=
=zG27
-----END PGP SIGNATURE-----

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: tomcat 8.5.23 dbcp not honoring autocommit = false?

Chris Cheshire-2
In reply to this post by Christopher Schultz-2
On Thu, Oct 26, 2017 at 3:00 PM, Christopher Schultz
<[hidden email]> wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA256
>
> Chris,
>
> On 10/16/17 9:43 AM, Chris Cheshire wrote:
>> On Fri, Oct 13, 2017 at 5:00 PM, Christopher Schultz
>> <[hidden email]> wrote:
>>> -----BEGIN PGP SIGNED MESSAGE-----
>>
>>> When you say you have "autocommit disabled in mysql config" what
>>> do you mean?
>>>
>>
>> /etc/my.cnf : [mysqld] autocommit=0
>>
>> This turns off autocommit off as a default for all connections.
>
> It only affects connections from MySQL's "mysql" command-line client.
> It does not affect e.g. Java-based clients.
>

It's in the [mysqld] section of my.cnf so it is supposed to be at a
server level.

I do have stuff in the [mysql] section that affects the command line client
behaviour only.

>> I need this at a minimum for the mysql client, but in the absence
>> of any other configuration it should be the default for a
>> connection from any client.
>
> The JDBC spec says that all connections are auto-commit unless
> otherwise specified. So if you are creating your own connections or
> using e.g. a connection-pool then you'll have to make sure that you
> configure them to be NOT auto-commit. This is not a setting that you
> can control from the server.
>
> More below.
>

I was doing that anyway in both the pool configuration and when
the connection is grabbed from the pool. I've since changed the code to not
touch the autocommit setting and leave that up to the datasource
(resource config in context.xml)


>>> On 10/13/17 10:17 AM, Chris Cheshire wrote:
>>>> </snip>
>>>>
>>>> As a further test I just took out my explicit rollback in my
>>>> DAOFactory close() method, and swapped back to commons dbcp.
>>>> Added an update that wasn't explicitly committed, and it
>>>> correctly did not get committed when the connection was closed.
>>>> Swapped back to tomcat dbcp and repeated, it got committed
>>>> without an explicit commit statement.
>>>>
>>>> I'm really puzzled as to why *I* have to explicitly rollback
>>>> on close if autocommit is not enabled, instead of tomcat dbcp
>>>> handling that when commons dbcp appears to do it.
>>>
>>> No connection pool can read your mind. If you begin a transaction
>>> (or never start one), you must either commit or rollback. Merely
>>> calling close() does not explicitly cause either of those to be
>>> called.
>>>
>>
>> And that's just it. If I don't explicitly commit, then why are
>> changes being committed when the connection is closed and returned
>> back to the pool?
>>
>>>> If I do
>>>>
>>>> daoFactory = new MySQLDAOFactoryImpl(getDataSource());
>>>>
>>>> // update #1 daoFactory.commit()
>>>>
>>>> // update #2 daoFactory.close();
>>>>
>>>> then update #2 is being committed.
>>>
>>> I'm curious why you are doing "update #2" without either COMMIT
>>> or ROLLBACK. That seems like ... a mistake.
>>>
>>
>> Correct. This is an example to illustrate a mistake I found in my
>> code. I found a servlet that actually wasn't explicitly committing
>> when it should have been, yet everything it was doing was being
>> committed to the database.
>>
>>> - From the Connection.close() javadoc:
>>>
>>> " It is strongly recommended that an application explicitly
>>> commits or rolls back an active transaction prior to calling the
>>> close method. If the close method is called and there is an
>>> active transaction, the results are implementation-defined. "
>>>
>>
>> If a commit is not being explicitly issued, then the commit
>> behaviour should honor that of the connection, yes?
>
> Yes, but it's more complicated than that. Any change to the
> connection's settings (which happen ALL THE TIME when the connection
> is being returned to a connection pool) will cause an implicit COMMIT.
> That's why it's super important for you to either COMMIT or ROLLBACK
> yourself.
>
> Note that "autocommit = false" doesn't mean "autorollback=true".
> Best-case scenario for you there is that the transaction gets
> committed *later* when another piece of your code grabs a connection
> from the pool, does its work (successfully) and issues a COMMIT.
>
> It's just NOT the pool's job nor the driver's job to clean-up after
> any messes created by your code.


Agreed, however since it was acting differently than the commons
pool I mistakenly attributed the behaviour to tomcat jdbc instead.


>
>>> There *is* an implicit COMMIT executed if the autocommit flag is
>>> flipped for any reason, either true->false or false->true.
>>>
>>> If you have autocommit=false in your <Resource> configuration
>>> (which you do), then calling setAutoCommit(false) shouldn't do
>>> anything.
>>>
>>>> If I put in this in the close() method of my DAO Factory
>>>>
>>>> if (!this.dbConn.getAutoCommit()) { this.dbConn.rollback(); }
>>>>
>>>> before the close() call, then update #2 is correctly not
>>>> getting committed.
>>>
>>> This is probably the wrong approach: your close() method doesn't
>>> know whether it's better to call commit() or rollback(), so it
>>> should do neither.
>>
>> I realise this too, however I have to have it in otherwise if an
>> exception is thrown, then work is being committed regardless of the
>> fact that I have autocommit turned OFF in 3 levels, all  the way
>> back to the mysqld configuration.
>
> The mysqld configuration is not relevant, here. Are you able to run
> your code through a debugger to see when the COMMIT is happening?
>
>> This behaviour does not happen with commons dbcp, only tomcat dbcp.
>> There is a difference in default behaviour between the two pools
>> when a transaction is not explicitly committed or rolled back when
>> a connection is closed and returned to the pool.
>
> Can you create a SSCCE test-case which demonstrates this problem where
> it behaves one way with tomcat-pool versus commons-dbcp? Something
> simple like "java RunTest" where it prints out "expected behavior" or
> "unexpected behavior" would be great. Remember that JavaDB (aka Derby)
> is built-into JVMs before 9.0 so you don't need any complicated
> database setup.


I don't have time to make an SSCCE for a while, but since Keiichi pointed out
the tomcat jdbc pool is doing nothing in the absence of any instruction to do
otherwise, I figured out that it was a subsequent explicit commit when
the connection
was reused. This was giving me the impression that a commit was happening
when the connection was being returned.


>
>> I use a try-with-resources/finally to open and close my database
>> connections, thus short of a JVM crash, any exceptions thrown will
>> always close the connections and return them to the pool.
>
> Good. Will those exceptions cause a ROLLBACK to occur? If not, your
> code is broken.
>

No, I am using the rollbackOnReturn parameter now to handle that. I explicity
commit, but any unchecked exceptions that are thrown will trigger a rollback
via this. Previously the commons pool appeared to be doing that for me and I
didn't think anything of it.

I could change this to have every single servlet action
catch a Throwable, issue a rollback and then rethrow the Throwable, but
it is easier right now to make use of the configuration available in tomcat jdbc
to issue a rollback on return.

I understand this is not the correct approach and if I find the time,
I will fix it.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]