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