Re: confused about transactions and connection pools

On Mon, 30 Oct 2006, David Goodenough wrote:

> On Monday 30 October 2006 18:40, Kris Jurka wrote:
>> For each connection the statements BEGIN, ROLLBACK, and COMMIT are only
>> prepared once and after that they are executed again and again.  This can
>> be very confusing when looking at the logs which only show the preparation
>> step, but not execution.  So even though you can't see them, they're being
>> executed.  The 8.2 release will be the first release that can accurately
>> log this usage pattern.
>>
> is it only the BEGIN, ROLLBACK and COMMIT that are only shown when being
> prepared?  I ask because all the statement are PreparedStatements and they
> get prepared each time we get a new connection, which I understand means
> that they prepared for real first time they are used, but are then cached
> and therefore that one should always use substitutable ? paramaters so that
> the statement is always the same.  If only the prepares are being logged
> then the pool is obviously not working as the insert into the logs table
> get logged each time through.
>

The driver handles BEGIN, ROLLBACK, and COMMIT slightly differently than
regular PreparedStatement objects.  For user PreparedStatements, the
driver only avoids reparsing it on the fifth execution.  This is
configurable via the prepareThreshold URL parameter.  So after you use
it five times you won't see it being logged any more.  For these
transaction handling commands the driver knows it will be reusing them
often, so it switches over right away, not on the fifth exectuion.  The
execution count is kept in the PreparedStatement object so if you do
something like:

get connection from pool
create PreparedStatement
execute PreparedStatement
close PreparedStatement
return connection to pool

Then no matter how many times you do the above the it will never switch
to avoiding the reparse because the count is kept in the
PreparedStatement which is continually created and destroyed,
reinitializing the counter.  The driver/connection manages the reference
to the transaction handling commands so it can keep them around across
connections the pool hands out.

Kris Jurka