Mailing-List: contact users-help@tomcat.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Tomcat Users List" <users@tomcat.apache.org>
MIME-Version: 1.0
In-Reply-To: <87d4a07b-3c47-a0de-6311-0b236babbaab@christopherschultz.net>
References: <CACw2CjQ9aEGXQtdaOSvMW79-jJSeAoPEF06rQCse-Vc3WgOiVQ@mail.gmail.com>
 <baea48b6-a4c1-6ff3-3b22-feca8fdcd0a2@christopherschultz.net>
 <CACw2CjSy8FjAi-GBVbUFvV2CEg0bEPfCDcZgiZa36aLAxYZmsA@mail.gmail.com>
 <CACw2CjQy7f33OcADcdm+PF8J2-X1_WcQGMhr-AgHP82YwG+Wqw@mail.gmail.com> <87d4a07b-3c47-a0de-6311-0b236babbaab@christopherschultz.net>
From: Chris Cheshire <yahoonomas@gmail.com>
Date: Mon, 16 Oct 2017 09:43:05 -0400
Message-ID: <CACw2CjRi0Yiyjvqy5PbK2xJAau90QZMAOmirVYwxopM85Vr3Rg@mail.gmail.com>
Subject: Re: tomcat 8.5.23 dbcp not honoring autocommit = false?
To: Tomcat Users List <users@tomcat.apache.org>
Content-Type: text/plain; charset="UTF-8"
archived-at: Mon, 16 Oct 2017 13:43:14 -0000

On Fri, Oct 13, 2017 at 5:00 PM, Christopher Schultz
<chris@christopherschultz.net> 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: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org