-----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
> <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.
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: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
