Does Tomcat JDBC Connection Pool reset autocommit on closed connections?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Does Tomcat JDBC Connection Pool reset autocommit on closed connections?

My Subs
Hello,

I'm using Tomcat 10.0.0.  Suppose I call setAutoCommit(false) on a connection obtained from a Tomcat JDBC Connection Pool.  Then I do some stuff with the connection, call commit() or rollback() and finally call close() on it without ever calling setAutocommit(true).

What will the autocommit state of that connection be, the next time the pool gives it to my code?  Will it be in the same state I left it, that is, with autocommit set to false, or will it be reverted back to the default state (autocommit set to true)?

Thank you!

Alex

Sent using Zoho Mail




---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Does Tomcat JDBC Connection Pool reset autocommit on closed connections?

markt
On 12/03/2021 03:57, My Subs wrote:
> Hello,
>
> I'm using Tomcat 10.0.0.  Suppose I call setAutoCommit(false) on a connection obtained from a Tomcat JDBC Connection Pool.  Then I do some stuff with the connection, call commit() or rollback() and finally call close() on it without ever calling setAutocommit(true).
>
> What will the autocommit state of that connection be, the next time the pool gives it to my code?  Will it be in the same state I left it, that is, with autocommit set to false, or will it be reverted back to the default state (autocommit set to true)?

The simplest way to be sure is to run a test with a pool size of 1.

A quick look at the code (I might have missed something) suggests that
if defaultAutoCommit is configured then it will be reset to that default
value. Otherwise it will be unchanged from when the connection was
returned to the pool.

Mark

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Does Tomcat JDBC Connection Pool reset autocommit on closed connections?

My Subs
---- On Fri, 12 Mar 2021 02:35:27 -0500 Mark Thomas <[hidden email]> wrote ----

 > On 12/03/2021 03:57, My Subs wrote:
 > > Hello,
 > >
 > > I'm using Tomcat 10.0.0.  Suppose I call setAutoCommit(false) on a connection obtained from a Tomcat JDBC Connection Pool.  Then I do some stuff with the connection, call commit() or rollback() and finally call close() on it without ever calling setAutocommit(true).
 > >
 > > What will the autocommit state of that connection be, the next time the pool gives it to my code?  Will it be in the same state I left it, that is, with autocommit set to false, or will it be reverted back to the default state (autocommit set to true)?
 >  
 > The simplest way to be sure is to run a test with a pool size of 1.
 >  
 > A quick look at the code (I might have missed something) suggests that
 > if defaultAutoCommit is configured then it will be reset to that default
 > value. Otherwise it will be unchanged from when the connection was
 > returned to the pool.
 >  
 > Mark
 >  
 > ---------------------------------------------------------------------
 > To unsubscribe, e-mail: [hidden email]
 > For additional commands, e-mail: [hidden email]

Hi Mark,

Thanks for your comment.  I found this on https://tomcat.apache.org/tomcat-10.0-doc/jdbc-pool.html:

"The only state the pool itself inserts are defaultAutoCommit, defaultReadOnly, defaultTransactionIsolation, defaultCatalog if these are set. These 4 properties are only set upon connection creation. Should these properties be modified during the usage of the connection, the pool itself will not reset them."

It seems to say that connections will not be reverted back to the default auto-commit state even if defaultAutoCommit is set.  Given your reading of the source code, might it be that the docs are wrong?

Thank you,

Alex


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Does Tomcat JDBC Connection Pool reset autocommit on closed connections?

Christopher Schultz-2
Alex,

On 3/12/21 16:32, My Subs wrote:

> ---- On Fri, 12 Mar 2021 02:35:27 -0500 Mark Thomas <[hidden email]> wrote ----
>
>   > On 12/03/2021 03:57, My Subs wrote:
>   > > Hello,
>   > >
>   > > I'm using Tomcat 10.0.0.  Suppose I call setAutoCommit(false) on a connection obtained from a Tomcat JDBC Connection Pool.  Then I do some stuff with the connection, call commit() or rollback() and finally call close() on it without ever calling setAutocommit(true).
>   > >
>   > > What will the autocommit state of that connection be, the next time the pool gives it to my code?  Will it be in the same state I left it, that is, with autocommit set to false, or will it be reverted back to the default state (autocommit set to true)?
>   >
>   > The simplest way to be sure is to run a test with a pool size of 1.
>   >
>   > A quick look at the code (I might have missed something) suggests that
>   > if defaultAutoCommit is configured then it will be reset to that default
>   > value. Otherwise it will be unchanged from when the connection was
>   > returned to the pool.
>   >
>   > Mark
>   >
>   > ---------------------------------------------------------------------
>   > To unsubscribe, e-mail: [hidden email]
>   > For additional commands, e-mail: [hidden email]
>
> Hi Mark,
>
> Thanks for your comment.  I found this on https://tomcat.apache.org/tomcat-10.0-doc/jdbc-pool.html:
>
> "The only state the pool itself inserts are defaultAutoCommit, defaultReadOnly, defaultTransactionIsolation, defaultCatalog if these are set. These 4 properties are only set upon connection creation. Should these properties be modified during the usage of the connection, the pool itself will not reset them."
>
> It seems to say that connections will not be reverted back to the default auto-commit state even if defaultAutoCommit is set.  Given your reading of the source code, might it be that the docs are wrong?

I think the docs can easily be misunderstood.

I don't use the tomcat-pool but I do use the *other* pool provided by
Tomcat and they are generally expected to be (mostly) interchangeable.

A connection pool which doesn't reset the auto-commit state on
connection-return operation would IMO be considered very badly broken.

tomcat-pool allows you to NOT specify a default in which case
Connection.setAutoCommit() will never be called *at all* by the pool.
That seems like reasonable behavior (don't mess with my stuff!), but it
means that the application will *always* have to reset the auto-commit
state to a known value *every single time* a connection is borrowed.

Since the whole point of the pool is to manage this kind of thing, I
would argue that having defaultAutoCommit NOT set to anything would be
considered very bad practice.

If you set defaultAutoCommit="true", you should expect that, when your
connections are returned to the pool, that setAutoCommit(true) will be
called every single time the connection is returned to the pool --
usually by the application calling Connection.close().

Are you worried about a particular use-case or are you just being
extra-vigilant? Or are you observing some unexpected behavior?

-chris

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Does Tomcat JDBC Connection Pool reset autocommit on closed connections?

My Subs


Sent using Zoho Mail




---- On Fri, 12 Mar 2021 20:24:48 -0500 Christopher Schultz <[hidden email]> wrote ----

 >
 > Alex,
 >  
 > On 3/12/21 16:32, My Subs wrote:
 > > ---- On Fri, 12 Mar 2021 02:35:27 -0500 Mark Thomas <[hidden email]> wrote ----
 > >
 > >   > On 12/03/2021 03:57, My Subs wrote:
 > >   > > Hello,
 > >   > >
 > >   > > I'm using Tomcat 10.0.0.  Suppose I call setAutoCommit(false) on a connection obtained from a Tomcat JDBC Connection Pool.  Then I do some stuff with the connection, call commit() or rollback() and finally call close() on it without ever calling setAutocommit(true).
 > >   > >
 > >   > > What will the autocommit state of that connection be, the next time the pool gives it to my code?  Will it be in the same state I left it, that is, with autocommit set to false, or will it be reverted back to the default state (autocommit set to true)?
 > >   >
 > >   > The simplest way to be sure is to run a test with a pool size of 1.
 > >   >
 > >   > A quick look at the code (I might have missed something) suggests that
 > >   > if defaultAutoCommit is configured then it will be reset to that default
 > >   > value. Otherwise it will be unchanged from when the connection was
 > >   > returned to the pool.
 > >   >
 > >   > Mark
 > >   >
 > >   > ---------------------------------------------------------------------
 > >   > To unsubscribe, e-mail: [hidden email]
 > >   > For additional commands, e-mail: [hidden email]
 > >
 > > Hi Mark,
 > >
 > > Thanks for your comment.  I found this on https://tomcat.apache.org/tomcat-10.0-doc/jdbc-pool.html: 
 > >
 > > "The only state the pool itself inserts are defaultAutoCommit, defaultReadOnly, defaultTransactionIsolation, defaultCatalog if these are set. These 4 properties are only set upon connection creation. Should these properties be modified during the usage of the connection, the pool itself will not reset them."
 > >
 > > It seems to say that connections will not be reverted back to the default auto-commit state even if defaultAutoCommit is set.  Given your reading of the source code, might it be that the docs are wrong?
 >  
 > I think the docs can easily be misunderstood.
 >  
 > I don't use the tomcat-pool but I do use the *other* pool provided by
 > Tomcat and they are generally expected to be (mostly) interchangeable.
 >  
 > A connection pool which doesn't reset the auto-commit state on
 > connection-return operation would IMO be considered very badly broken.
 >  
 > tomcat-pool allows you to NOT specify a default in which case
 > Connection.setAutoCommit() will never be called *at all* by the pool.
 > That seems like reasonable behavior (don't mess with my stuff!), but it
 > means that the application will *always* have to reset the auto-commit
 > state to a known value *every single time* a connection is borrowed.
 >  
 > Since the whole point of the pool is to manage this kind of thing, I
 > would argue that having defaultAutoCommit NOT set to anything would be
 > considered very bad practice.
 >  
 > If you set defaultAutoCommit="true", you should expect that, when your
 > connections are returned to the pool, that setAutoCommit(true) will be
 > called every single time the connection is returned to the pool --
 > usually by the application calling Connection.close().
 >  
 > Are you worried about a particular use-case or are you just being
 > extra-vigilant? Or are you observing some unexpected behavior?
 >  
 > -chris
 >  
 > ---------------------------------------------------------------------
 > To unsubscribe, e-mail: [hidden email]
 > For additional commands, e-mail: [hidden email]
 >  
 >

Hi Chris,

I’m trying to wrap my head around on whether I can end transaction blocks in a try clause with a simple call to commit().  That would be the case if the pool resets the auto-commit state to the value of defaultAutoCommit.  Try blocks would end with commit(), catch blocks with rollback(), and the pool takes care of setting auto-commit back to true.

On the other hand, if as the docs say, “the pool itself will not reset” the state of connection attributes like auto-commit, then I’d need to tack to every try/catch a finally clause having setAutoCommit(true).   But this would be unnecessary boilerplate if, as Mark says —and you believe should be the case—, the pool does effect the reset.

I’d rather not bloat the code, hence my question.  I guess that puts me in the extra-vigilant camp.

Cheers,

Alex

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Does Tomcat JDBC Connection Pool reset autocommit on closed connections?

Christopher Schultz-2
Alex,

On 3/13/21 10:20, My Subs wrote:

> ---- On Fri, 12 Mar 2021 20:24:48 -0500 Christopher Schultz <[hidden email]> wrote ----
>
>   >
>   > Alex,
>   >
>   > On 3/12/21 16:32, My Subs wrote:
>   > > ---- On Fri, 12 Mar 2021 02:35:27 -0500 Mark Thomas <[hidden email]> wrote ----
>   > >
>   > >   > On 12/03/2021 03:57, My Subs wrote:
>   > >   > > Hello,
>   > >   > >
>   > >   > > I'm using Tomcat 10.0.0.  Suppose I call setAutoCommit(false) on a connection obtained from a Tomcat JDBC Connection Pool.  Then I do some stuff with the connection, call commit() or rollback() and finally call close() on it without ever calling setAutocommit(true).
>   > >   > >
>   > >   > > What will the autocommit state of that connection be, the next time the pool gives it to my code?  Will it be in the same state I left it, that is, with autocommit set to false, or will it be reverted back to the default state (autocommit set to true)?
>   > >   >
>   > >   > The simplest way to be sure is to run a test with a pool size of 1.
>   > >   >
>   > >   > A quick look at the code (I might have missed something) suggests that
>   > >   > if defaultAutoCommit is configured then it will be reset to that default
>   > >   > value. Otherwise it will be unchanged from when the connection was
>   > >   > returned to the pool.
>   > >   >
>   > >   > Mark
>   > >   >
>   > >   > ---------------------------------------------------------------------
>   > >   > To unsubscribe, e-mail: [hidden email]
>   > >   > For additional commands, e-mail: [hidden email]
>   > >
>   > > Hi Mark,
>   > >
>   > > Thanks for your comment.  I found this on https://tomcat.apache.org/tomcat-10.0-doc/jdbc-pool.html:
>   > >
>   > > "The only state the pool itself inserts are defaultAutoCommit, defaultReadOnly, defaultTransactionIsolation, defaultCatalog if these are set. These 4 properties are only set upon connection creation. Should these properties be modified during the usage of the connection, the pool itself will not reset them."
>   > >
>   > > It seems to say that connections will not be reverted back to the default auto-commit state even if defaultAutoCommit is set.  Given your reading of the source code, might it be that the docs are wrong?
>   >
>   > I think the docs can easily be misunderstood.
>   >
>   > I don't use the tomcat-pool but I do use the *other* pool provided by
>   > Tomcat and they are generally expected to be (mostly) interchangeable.
>   >
>   > A connection pool which doesn't reset the auto-commit state on
>   > connection-return operation would IMO be considered very badly broken.
>   >
>   > tomcat-pool allows you to NOT specify a default in which case
>   > Connection.setAutoCommit() will never be called *at all* by the pool.
>   > That seems like reasonable behavior (don't mess with my stuff!), but it
>   > means that the application will *always* have to reset the auto-commit
>   > state to a known value *every single time* a connection is borrowed.
>   >
>   > Since the whole point of the pool is to manage this kind of thing, I
>   > would argue that having defaultAutoCommit NOT set to anything would be
>   > considered very bad practice.
>   >
>   > If you set defaultAutoCommit="true", you should expect that, when your
>   > connections are returned to the pool, that setAutoCommit(true) will be
>   > called every single time the connection is returned to the pool --
>   > usually by the application calling Connection.close().
>   >
>   > Are you worried about a particular use-case or are you just being
>   > extra-vigilant? Or are you observing some unexpected behavior?
>   >
>   > -chris
>   >
>   > ---------------------------------------------------------------------
>   > To unsubscribe, e-mail: [hidden email]
>   > For additional commands, e-mail: [hidden email]
>   >
>   >
>
> Hi Chris,
>
> I’m trying to wrap my head around on whether I can end transaction blocks in a try clause with a simple call to commit().  That would be the case if the pool resets the auto-commit state to the value of defaultAutoCommit.  Try blocks would end with commit(), catch blocks with rollback(), and the pool takes care of setting auto-commit back to true.
>
> On the other hand, if as the docs say, “the pool itself will not reset” the state of connection attributes like auto-commit, then I’d need to tack to every try/catch a finally clause having setAutoCommit(true).   But this would be unnecessary boilerplate if, as Mark says —and you believe should be the case—, the pool does effect the reset.
>
> I’d rather not bloat the code, hence my question.  I guess that puts me in the extra-vigilant camp.

It should work like any other connection pool you have used in the past.

Just be sure to set defaultAutoCommit="true" in your configuration. Then
it will *always* set autocommit=true when you return your connections to
the pool.

Just some more food for thought:
https://blog.christopherschultz.net/2009/03/16/properly-handling-pooled-jdbc-connections/

-chris

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Does Tomcat JDBC Connection Pool reset autocommit on closed connections?

My Subs
---- On Mon, 15 Mar 2021 11:25:17 -0500 Christopher Schultz <[hidden email]> wrote ----

 > Alex,
 >  
 > On 3/13/21 10:20, My Subs wrote:
 > > ---- On Fri, 12 Mar 2021 20:24:48 -0500 Christopher Schultz <[hidden email]> wrote ----
 > >
 > >   >
 > >   > Alex,
 > >   >
 > >   > On 3/12/21 16:32, My Subs wrote:
 > >   > > ---- On Fri, 12 Mar 2021 02:35:27 -0500 Mark Thomas <[hidden email]> wrote ----
 > >   > >
 > >   > >   > On 12/03/2021 03:57, My Subs wrote:
 > >   > >   > > Hello,
 > >   > >   > >
 > >   > >   > > I'm using Tomcat 10.0.0.  Suppose I call setAutoCommit(false) on a connection obtained from a Tomcat JDBC Connection Pool.  Then I do some stuff with the connection, call commit() or rollback() and finally call close() on it without ever calling setAutocommit(true).
 > >   > >   > >
 > >   > >   > > What will the autocommit state of that connection be, the next time the pool gives it to my code?  Will it be in the same state I left it, that is, with autocommit set to false, or will it be reverted back to the default state (autocommit set to true)?
 > >   > >   >
 > >   > >   > The simplest way to be sure is to run a test with a pool size of 1.
 > >   > >   >
 > >   > >   > A quick look at the code (I might have missed something) suggests that
 > >   > >   > if defaultAutoCommit is configured then it will be reset to that default
 > >   > >   > value. Otherwise it will be unchanged from when the connection was
 > >   > >   > returned to the pool.
 > >   > >   >
 > >   > >   > Mark
 > >   > >   >
 > >   > >   > ---------------------------------------------------------------------
 > >   > >   > To unsubscribe, e-mail: [hidden email]
 > >   > >   > For additional commands, e-mail: [hidden email]
 > >   > >
 > >   > > Hi Mark,
 > >   > >
 > >   > > Thanks for your comment.  I found this on https://tomcat.apache.org/tomcat-10.0-doc/jdbc-pool.html: 
 > >   > >
 > >   > > "The only state the pool itself inserts are defaultAutoCommit, defaultReadOnly, defaultTransactionIsolation, defaultCatalog if these are set. These 4 properties are only set upon connection creation. Should these properties be modified during the usage of the connection, the pool itself will not reset them."
 > >   > >
 > >   > > It seems to say that connections will not be reverted back to the default auto-commit state even if defaultAutoCommit is set.  Given your reading of the source code, might it be that the docs are wrong?
 > >   >
 > >   > I think the docs can easily be misunderstood.
 > >   >
 > >   > I don't use the tomcat-pool but I do use the *other* pool provided by
 > >   > Tomcat and they are generally expected to be (mostly) interchangeable.
 > >   >
 > >   > A connection pool which doesn't reset the auto-commit state on
 > >   > connection-return operation would IMO be considered very badly broken.
 > >   >
 > >   > tomcat-pool allows you to NOT specify a default in which case
 > >   > Connection.setAutoCommit() will never be called *at all* by the pool.
 > >   > That seems like reasonable behavior (don't mess with my stuff!), but it
 > >   > means that the application will *always* have to reset the auto-commit
 > >   > state to a known value *every single time* a connection is borrowed.
 > >   >
 > >   > Since the whole point of the pool is to manage this kind of thing, I
 > >   > would argue that having defaultAutoCommit NOT set to anything would be
 > >   > considered very bad practice.
 > >   >
 > >   > If you set defaultAutoCommit="true", you should expect that, when your
 > >   > connections are returned to the pool, that setAutoCommit(true) will be
 > >   > called every single time the connection is returned to the pool --
 > >   > usually by the application calling Connection.close().
 > >   >
 > >   > Are you worried about a particular use-case or are you just being
 > >   > extra-vigilant? Or are you observing some unexpected behavior?
 > >   >
 > >   > -chris
 > >   >
 > >   > ---------------------------------------------------------------------
 > >   > To unsubscribe, e-mail: [hidden email]
 > >   > For additional commands, e-mail: [hidden email]
 > >   >
 > >   >
 > >
 > > Hi Chris,
 > >
 > > I’m trying to wrap my head around on whether I can end transaction blocks in a try clause with a simple call to commit().  That would be the case if the pool resets the auto-commit state to the value of defaultAutoCommit.  Try blocks would end with commit(), catch blocks with rollback(), and the pool takes care of setting auto-commit back to true.
 > >
 > > On the other hand, if as the docs say, “the pool itself will not reset” the state of connection attributes like auto-commit, then I’d need to tack to every try/catch a finally clause having setAutoCommit(true).   But this would be unnecessary boilerplate if, as Mark says —and you believe should be the case—, the pool does effect the reset.
 > >
 > > I’d rather not bloat the code, hence my question.  I guess that puts me in the extra-vigilant camp.
 >  
 > It should work like any other connection pool you have used in the past.
 >  
 > Just be sure to set defaultAutoCommit="true" in your configuration. Then
 > it will *always* set autocommit=true when you return your connections to
 > the pool.
 >  
 > Just some more food for thought:
 > https://blog.christopherschultz.net/2009/03/16/properly-handling-pooled-jdbc-connections/ 
 >  
 > -chris
 >  
 > ---------------------------------------------------------------------
 > To unsubscribe, e-mail: [hidden email]
 > For additional commands, e-mail: [hidden email]
 >  
 >

Hi Chris,

Thank you for the advice and the great article.  The example it gives on how to prevent partial commits using multiple catch clauses that roll back on every possible exception scenario has me thinking whether all that is necessary when the pool attribute rollbackOnReturn is set to true.

I understand that in such case any exception thrown from within a TWR's try block will make TWR to call close() on the connection declared on the TWR header.  And given that rollbackOnReturn is set to true, this will make the pool to call rollback() on the connection before returning it to the pool.  By the time control reaches any catch clause, the transaction has already been rolled back.

Am I missing something and rolling back on the catch clauses is nevertheless necessary?

Best,

Alex






---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Does Tomcat JDBC Connection Pool reset autocommit on closed connections?

Christopher Schultz-2
Alex,

On 3/15/21 23:48, My Subs wrote:

> ---- On Mon, 15 Mar 2021 11:25:17 -0500 Christopher Schultz <[hidden email]> wrote ----
>
>   > Alex,
>   >
>   > On 3/13/21 10:20, My Subs wrote:
>   > > ---- On Fri, 12 Mar 2021 20:24:48 -0500 Christopher Schultz <[hidden email]> wrote ----
>   > >
>   > >   >
>   > >   > Alex,
>   > >   >
>   > >   > On 3/12/21 16:32, My Subs wrote:
>   > >   > > ---- On Fri, 12 Mar 2021 02:35:27 -0500 Mark Thomas <[hidden email]> wrote ----
>   > >   > >
>   > >   > >   > On 12/03/2021 03:57, My Subs wrote:
>   > >   > >   > > Hello,
>   > >   > >   > >
>   > >   > >   > > I'm using Tomcat 10.0.0.  Suppose I call setAutoCommit(false) on a connection obtained from a Tomcat JDBC Connection Pool.  Then I do some stuff with the connection, call commit() or rollback() and finally call close() on it without ever calling setAutocommit(true).
>   > >   > >   > >
>   > >   > >   > > What will the autocommit state of that connection be, the next time the pool gives it to my code?  Will it be in the same state I left it, that is, with autocommit set to false, or will it be reverted back to the default state (autocommit set to true)?
>   > >   > >   >
>   > >   > >   > The simplest way to be sure is to run a test with a pool size of 1.
>   > >   > >   >
>   > >   > >   > A quick look at the code (I might have missed something) suggests that
>   > >   > >   > if defaultAutoCommit is configured then it will be reset to that default
>   > >   > >   > value. Otherwise it will be unchanged from when the connection was
>   > >   > >   > returned to the pool.
>   > >   > >   >
>   > >   > >   > Mark
>   > >   > >   >
>   > >   > >   > ---------------------------------------------------------------------
>   > >   > >   > To unsubscribe, e-mail: [hidden email]
>   > >   > >   > For additional commands, e-mail: [hidden email]
>   > >   > >
>   > >   > > Hi Mark,
>   > >   > >
>   > >   > > Thanks for your comment.  I found this on https://tomcat.apache.org/tomcat-10.0-doc/jdbc-pool.html:
>   > >   > >
>   > >   > > "The only state the pool itself inserts are defaultAutoCommit, defaultReadOnly, defaultTransactionIsolation, defaultCatalog if these are set. These 4 properties are only set upon connection creation. Should these properties be modified during the usage of the connection, the pool itself will not reset them."
>   > >   > >
>   > >   > > It seems to say that connections will not be reverted back to the default auto-commit state even if defaultAutoCommit is set.  Given your reading of the source code, might it be that the docs are wrong?
>   > >   >
>   > >   > I think the docs can easily be misunderstood.
>   > >   >
>   > >   > I don't use the tomcat-pool but I do use the *other* pool provided by
>   > >   > Tomcat and they are generally expected to be (mostly) interchangeable.
>   > >   >
>   > >   > A connection pool which doesn't reset the auto-commit state on
>   > >   > connection-return operation would IMO be considered very badly broken.
>   > >   >
>   > >   > tomcat-pool allows you to NOT specify a default in which case
>   > >   > Connection.setAutoCommit() will never be called *at all* by the pool.
>   > >   > That seems like reasonable behavior (don't mess with my stuff!), but it
>   > >   > means that the application will *always* have to reset the auto-commit
>   > >   > state to a known value *every single time* a connection is borrowed.
>   > >   >
>   > >   > Since the whole point of the pool is to manage this kind of thing, I
>   > >   > would argue that having defaultAutoCommit NOT set to anything would be
>   > >   > considered very bad practice.
>   > >   >
>   > >   > If you set defaultAutoCommit="true", you should expect that, when your
>   > >   > connections are returned to the pool, that setAutoCommit(true) will be
>   > >   > called every single time the connection is returned to the pool --
>   > >   > usually by the application calling Connection.close().
>   > >   >
>   > >   > Are you worried about a particular use-case or are you just being
>   > >   > extra-vigilant? Or are you observing some unexpected behavior?
>   > >   >
>   > >   > -chris
>   > >   >
>   > >   > ---------------------------------------------------------------------
>   > >   > To unsubscribe, e-mail: [hidden email]
>   > >   > For additional commands, e-mail: [hidden email]
>   > >   >
>   > >   >
>   > >
>   > > Hi Chris,
>   > >
>   > > I’m trying to wrap my head around on whether I can end transaction blocks in a try clause with a simple call to commit().  That would be the case if the pool resets the auto-commit state to the value of defaultAutoCommit.  Try blocks would end with commit(), catch blocks with rollback(), and the pool takes care of setting auto-commit back to true.
>   > >
>   > > On the other hand, if as the docs say, “the pool itself will not reset” the state of connection attributes like auto-commit, then I’d need to tack to every try/catch a finally clause having setAutoCommit(true).   But this would be unnecessary boilerplate if, as Mark says —and you believe should be the case—, the pool does effect the reset.
>   > >
>   > > I’d rather not bloat the code, hence my question.  I guess that puts me in the extra-vigilant camp.
>   >
>   > It should work like any other connection pool you have used in the past.
>   >
>   > Just be sure to set defaultAutoCommit="true" in your configuration. Then
>   > it will *always* set autocommit=true when you return your connections to
>   > the pool.
>   >
>   > Just some more food for thought:
>   > https://blog.christopherschultz.net/2009/03/16/properly-handling-pooled-jdbc-connections/
>   >
>   > -chris
>   >
>   > ---------------------------------------------------------------------
>   > To unsubscribe, e-mail: [hidden email]
>   > For additional commands, e-mail: [hidden email]
>   >
>   >
>
> Hi Chris,
>
> Thank you for the advice and the great article.  The example it gives
> on how to prevent partial commits using multiple catch clauses that
> roll back on every possible exception scenario has me thinking
> whether all that is necessary when the pool attribute
> rollbackOnReturn is set to true.
That post was written before rollbackOnReturn was a thing, and before
try-with-resources was a thing. Probably before multi-catch was a thing,
too. I also like giving complete code examples and sometimes brevity
must be sacrificed in favor of clarity.

Also, rollbackOnReturn will always issue a rollback() whether it's
necessary or not. Most drivers will implement that as a real ROLLBACK
sent to the server which could represent a (small) reduction in performance.

> I understand that in such case any exception thrown from within a
> TWR's try block will make TWR to call close() on the connection
> declared on the TWR header.  And given that rollbackOnReturn is set
> to true, this will make the pool to call rollback() on the connection
> before returning it to the pool.  By the time control reaches any
> catch clause, the transaction has already been rolled back.

I'd have to read the code, but I'd assume that return-object-handling
looks something like this:

if(autoRollbackOnReturn) {
     conn.rollback(); // with try/catch obviously
}
if(null != defaultAutoCommit) {
   conn.setAutoCommit(defaultAutoCommit);
}

If that's correct, then you can probably get away with
try-with-resources. You will end up calling rollback after every
transaction, though, which might be a (bit of a) waste.

> Am I missing something and rolling back on the catch clauses is
> nevertheless necessary?

I'm not sure it is necessary, unless I've missed something.

As I said before, I tend to favor clarity in my code for improved
maintainability, and in the case of try-with-resources plus an
elsewhere-configured-and-documented rollback behavior, I would
personally stay away from it. One change to that configuration and
literally all of your code breaks.

-chris

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]