You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@commons.apache.org by Robert Paschek <Ro...@comm-unity.at> on 2020/06/29 19:17:36 UTC
[DBCP] poolPreparedStatements
Hello,
DBCP has a feature to pool PreparedStatements for the lifetime of a connection.
This results in cursors being open and locks in the database for a long time, which could cause problems with administrative tasks in the database. That why I would prefer this pool to be more short-living, that means that the pool is filled while the application is using the connection und cleared when the application is returning the connection to the ConnectionPool. This way the application can still benefit from the Statement-cache in mass operations, without creating headaches for database admins.
I would suggest an additional setting like limitPreparedStatementPoolToConnectionUse or something similar.
What do you think?
Regards,
Robert
Re: [DBCP] poolPreparedStatements
Posted by Phil Steitz <ph...@gmail.com>.
> On Jun 30, 2020, at 2:36 PM, Gary Gregory <ga...@gmail.com> wrote:
>
> How about clearStatementPoolOnReturn.
+1
Phil
>
> You do not need to say "Prepared" in the name IMO since we only pool
> prepared statements. The above name actual says what happens when.
>
> The full version of that name if you want to be extra specific would be
> clearPreparedStatementPoolOnConnectionReturn.
>
> Gary
>
>> On Tue, Jun 30, 2020, 16:59 Robert Paschek <Ro...@comm-unity.at>
>> wrote:
>>
>> Thanks for the implementation hints. I'm willing to look into this.
>>
>> Can you think of a better propertyname than
>> limitPreparedStatementPoolToConnectionUse? While the meaning is clear (at
>> least to me), it's also quite long.
>>
>> Robert
>>
>>
>> From: Phil Steitz <ph...@gmail.com>
>> Sent: Dienstag, 30. Juni 2020 21:07
>> To: dev@commons.apache.org
>> Subject: Re: [DBCP] poolPreparedStatements
>>
>>
>>> On 6/29/20 12:17 PM, Robert Paschek wrote:
>>> Hello,
>>>
>>> DBCP has a feature to pool PreparedStatements for the lifetime of a
>> connection.
>>> This results in cursors being open and locks in the database for a long
>> time, which could cause problems with administrative tasks in the database.
>> That why I would prefer this pool to be more short-living, that means that
>> the pool is filled while the application is using the connection und
>> cleared when the application is returning the connection to the
>> ConnectionPool. This way the application can still benefit from the
>> Statement-cache in mass operations, without creating headaches for database
>> admins.
>>>
>>> I would suggest an additional setting like
>> limitPreparedStatementPoolToConnectionUse or something similar.
>>>
>>> What do you think?
>>>
>>> Regards,
>>> Robert
>>
>> One way to workaround the need to periodically clean up would be to
>> either set maximum lifetimes on connections or periodically invalidate
>> them. That would force the underlying PoolingConnections to be closed,
>> which would in turn cause the statement pools to be closed.
>>
>> The feature request sounds reasonable to me. For anyone interested in
>> creating a patch to implement this, here is a way that might work to
>> implement it:
>>
>> Connections that pool statements are PoolingConnections. Add a property
>> to this class to determine whether or not to clear the statement pool
>> when a connection is returned to the pool. Override
>> DelegatingConnnection's passivate method to first call super() but then
>> examine the property and if so configured, clear (not close) the
>> statement pool. Modify PoolableConnectionFactory to set the property
>> and BasicDataSource to pass it in.
>>
>> Probably best to open a JIRA for this. I don't have time to work on it
>> now, but I would be willing to review PRs.
>>
>> Phil
>>
>>>
>>
>>
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@commons.apache.org
For additional commands, e-mail: dev-help@commons.apache.org
Re: [DBCP] poolPreparedStatements
Posted by Gary Gregory <ga...@gmail.com>.
How about clearStatementPoolOnReturn.
You do not need to say "Prepared" in the name IMO since we only pool
prepared statements. The above name actual says what happens when.
The full version of that name if you want to be extra specific would be
clearPreparedStatementPoolOnConnectionReturn.
Gary
On Tue, Jun 30, 2020, 16:59 Robert Paschek <Ro...@comm-unity.at>
wrote:
> Thanks for the implementation hints. I'm willing to look into this.
>
> Can you think of a better propertyname than
> limitPreparedStatementPoolToConnectionUse? While the meaning is clear (at
> least to me), it's also quite long.
>
> Robert
>
>
> From: Phil Steitz <ph...@gmail.com>
> Sent: Dienstag, 30. Juni 2020 21:07
> To: dev@commons.apache.org
> Subject: Re: [DBCP] poolPreparedStatements
>
>
> On 6/29/20 12:17 PM, Robert Paschek wrote:
> > Hello,
> >
> > DBCP has a feature to pool PreparedStatements for the lifetime of a
> connection.
> > This results in cursors being open and locks in the database for a long
> time, which could cause problems with administrative tasks in the database.
> That why I would prefer this pool to be more short-living, that means that
> the pool is filled while the application is using the connection und
> cleared when the application is returning the connection to the
> ConnectionPool. This way the application can still benefit from the
> Statement-cache in mass operations, without creating headaches for database
> admins.
> >
> > I would suggest an additional setting like
> limitPreparedStatementPoolToConnectionUse or something similar.
> >
> > What do you think?
> >
> > Regards,
> > Robert
>
> One way to workaround the need to periodically clean up would be to
> either set maximum lifetimes on connections or periodically invalidate
> them. That would force the underlying PoolingConnections to be closed,
> which would in turn cause the statement pools to be closed.
>
> The feature request sounds reasonable to me. For anyone interested in
> creating a patch to implement this, here is a way that might work to
> implement it:
>
> Connections that pool statements are PoolingConnections. Add a property
> to this class to determine whether or not to clear the statement pool
> when a connection is returned to the pool. Override
> DelegatingConnnection's passivate method to first call super() but then
> examine the property and if so configured, clear (not close) the
> statement pool. Modify PoolableConnectionFactory to set the property
> and BasicDataSource to pass it in.
>
> Probably best to open a JIRA for this. I don't have time to work on it
> now, but I would be willing to review PRs.
>
> Phil
>
> >
>
>
Re: [DBCP] poolPreparedStatements
Posted by Robert Paschek <Ro...@comm-unity.at>.
Thanks for the implementation hints. I'm willing to look into this.
Can you think of a better propertyname than limitPreparedStatementPoolToConnectionUse? While the meaning is clear (at least to me), it's also quite long.
Robert
From: Phil Steitz <ph...@gmail.com>
Sent: Dienstag, 30. Juni 2020 21:07
To: dev@commons.apache.org
Subject: Re: [DBCP] poolPreparedStatements
On 6/29/20 12:17 PM, Robert Paschek wrote:
> Hello,
>
> DBCP has a feature to pool PreparedStatements for the lifetime of a connection.
> This results in cursors being open and locks in the database for a long time, which could cause problems with administrative tasks in the database. That why I would prefer this pool to be more short-living, that means that the pool is filled while the application is using the connection und cleared when the application is returning the connection to the ConnectionPool. This way the application can still benefit from the Statement-cache in mass operations, without creating headaches for database admins.
>
> I would suggest an additional setting like limitPreparedStatementPoolToConnectionUse or something similar.
>
> What do you think?
>
> Regards,
> Robert
One way to workaround the need to periodically clean up would be to
either set maximum lifetimes on connections or periodically invalidate
them. That would force the underlying PoolingConnections to be closed,
which would in turn cause the statement pools to be closed.
The feature request sounds reasonable to me. For anyone interested in
creating a patch to implement this, here is a way that might work to
implement it:
Connections that pool statements are PoolingConnections. Add a property
to this class to determine whether or not to clear the statement pool
when a connection is returned to the pool. Override
DelegatingConnnection's passivate method to first call super() but then
examine the property and if so configured, clear (not close) the
statement pool. Modify PoolableConnectionFactory to set the property
and BasicDataSource to pass it in.
Probably best to open a JIRA for this. I don't have time to work on it
now, but I would be willing to review PRs.
Phil
>
Re: [DBCP] poolPreparedStatements
Posted by Phil Steitz <ph...@gmail.com>.
On 6/29/20 12:17 PM, Robert Paschek wrote:
> Hello,
>
> DBCP has a feature to pool PreparedStatements for the lifetime of a connection.
> This results in cursors being open and locks in the database for a long time, which could cause problems with administrative tasks in the database. That why I would prefer this pool to be more short-living, that means that the pool is filled while the application is using the connection und cleared when the application is returning the connection to the ConnectionPool. This way the application can still benefit from the Statement-cache in mass operations, without creating headaches for database admins.
>
> I would suggest an additional setting like limitPreparedStatementPoolToConnectionUse or something similar.
>
> What do you think?
>
> Regards,
> Robert
One way to workaround the need to periodically clean up would be to
either set maximum lifetimes on connections or periodically invalidate
them. That would force the underlying PoolingConnections to be closed,
which would in turn cause the statement pools to be closed.
The feature request sounds reasonable to me. For anyone interested in
creating a patch to implement this, here is a way that might work to
implement it:
Connections that pool statements are PoolingConnections. Add a property
to this class to determine whether or not to clear the statement pool
when a connection is returned to the pool. Override
DelegatingConnnection's passivate method to first call super() but then
examine the property and if so configured, clear (not close) the
statement pool. Modify PoolableConnectionFactory to set the property
and BasicDataSource to pass it in.
Probably best to open a JIRA for this. I don't have time to work on it
now, but I would be willing to review PRs.
Phil
>
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@commons.apache.org
For additional commands, e-mail: dev-help@commons.apache.org
Re: [DBCP] poolPreparedStatements
Posted by Robert Paschek <Ro...@comm-unity.at>.
That would be quite difficult to handle on application side.
Two connection pools means two datasources, which need to be selectively injected into the business logic. When the same business logic is handling 100 or 100000 rows, it's difficult to find a place to decide which datasource to use.
Robert
From: Gary Gregory <ga...@gmail.com>
Sent: Montag, 29. Juni 2020 23:37
To: Commons Developers List
Subject: Re: [DBCP] poolPreparedStatements
You can do this today by using two connection pools, one configured with
statement pooling, and the other not (which is the default).
Gary
On Mon, Jun 29, 2020, 16:36 Robert Paschek <Ro...@comm-unity.at>
wrote:
> Hello,
>
> DBCP has a feature to pool PreparedStatements for the lifetime of a
> connection.
> This results in cursors being open and locks in the database for a long
> time, which could cause problems with administrative tasks in the database.
> That why I would prefer this pool to be more short-living, that means that
> the pool is filled while the application is using the connection und
> cleared when the application is returning the connection to the
> ConnectionPool. This way the application can still benefit from the
> Statement-cache in mass operations, without creating headaches for database
> admins.
>
> I would suggest an additional setting like
> limitPreparedStatementPoolToConnectionUse or something similar.
>
> What do you think?
>
> Regards,
> Robert
>
Re: [DBCP] poolPreparedStatements
Posted by Gary Gregory <ga...@gmail.com>.
You can do this today by using two connection pools, one configured with
statement pooling, and the other not (which is the default).
Gary
On Mon, Jun 29, 2020, 16:36 Robert Paschek <Ro...@comm-unity.at>
wrote:
> Hello,
>
> DBCP has a feature to pool PreparedStatements for the lifetime of a
> connection.
> This results in cursors being open and locks in the database for a long
> time, which could cause problems with administrative tasks in the database.
> That why I would prefer this pool to be more short-living, that means that
> the pool is filled while the application is using the connection und
> cleared when the application is returning the connection to the
> ConnectionPool. This way the application can still benefit from the
> Statement-cache in mass operations, without creating headaches for database
> admins.
>
> I would suggest an additional setting like
> limitPreparedStatementPoolToConnectionUse or something similar.
>
> What do you think?
>
> Regards,
> Robert
>