tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Chris Cheshire <>
Subject Re: tomcat 8.5.23 dbcp not honoring autocommit = false?
Date Mon, 16 Oct 2017 13:43:05 GMT
On Fri, Oct 13, 2017 at 5:00 PM, Christopher Schultz
<> wrote:

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

/etc/my.cnf :

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
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.


To unsubscribe, e-mail:
For additional commands, e-mail:

View raw message