Jay Rajput wrote:
> I would also like to
> limit the number of grab and release in my thread to one.
Why? Grabbing and releasing are cheap operations. A released
connection stays active until the aging logic says it needs to be shut
down lest the DB server do it for us.
The user manual recommends using half the DB server's timeout, so that
would be 4 hours by default. Surely we're not talking about a thread
wanting to hold onto a connection, idle, for such long spans of time?
If a thread (call it T1) has a connection, releases it, then another
thread (T2) comes along and grabs it before T1 does another grab, T1's
second grab call will be expensive. But, there's no getting around the
fact that if two threads need a connection at the same time and there's
only one active in the pool, either T1 or T2 will have to bring up a new
connection. It's only a race to see which thread gets inconvenienced.
> I have to grab the
> connection four times and release it four times.
You don't *have* to release it between queries. If you have four
back-to-back queries, it's perfectly legal and reasonable to hang onto
the connection through the query sequence.
The user manual recommends early release only to discourage people from
holding onto a connection for long periods of time, preventing other
threads from using it while the first one doesn't need it. Also,
holding onto a connection too long means you give up ConnectionPool's
automatic connection aging: it can expire those that haven't been used
recently so your code doesn't have to worry about DB server timeouts due
to attempting to use an old connection.