On Thu, Oct 12, 2017 at 11:16 PM, Christopher Schultz
<ch...@christopherschultz.net> 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: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
