Re: [Re] Re: PREPARE and transactions

Jeroen T. Vermeulen wrote:

> There was also the middleware argument--some intermediate software layer
> may be in control of bracketing. But in such cases, can you even rely
> on two independent transactions executing in the same session? You'd
> need to assume that to make the current semantics work in that situation.
> What if the middleware does connection pooling, or restores a broken
> connection between two transactions? The latter might happen because of
> a timed-out firewall, for instance, when there is a long pause between
> two unrelated transactions.

The current JDBC driver uses PREPARE/EXECUTE to prepare arbitrary
queries, requested either directly from the application or via a
middleware layer. For queries where use of PREPARE/EXECUTE is requested,
the driver sends a PREPARE/EXECUTE pair on query execution. If the
PREPARE succeeds, subsequent query executions for the same query send
only the EXECUTE.

This might all happen either inside or outside a transaction -- the
mechanics of transaction demarcation are done by the driver, but the
transaction model used is up to the application and is effectively
invisible to the driver.

The set of PREPAREd queries is per-connection state, so whatever
connection pooling etc. logic runs on top of the driver isn't an issue.
The driver currently stores that state in the statement objects
allocated by the application -- which are also tied to a particular
connection -- but there's no reason why the driver couldn't, for
example, maintain a cache of prepared statements per connection and
match that against newly requested queries.

If PREPAREd statements did DEALLOCATE on transaction rollback, the
driver would have to track the set of statements that were first
PREPAREd in the current transaction so it can fix the state on the
driver side if the transaction rolls back. This is a lot of extra
complexity for no benefit I can see. And it'd get pretty nasty if nested
transactions were involved..

It's all somewhat moot for the JDBC driver as it's moving to using
protocol-level Parse/Bind messages instead of PREPARE/EXECUTE statements
anyway. That said, I would be very unhappy if Parse suddenly became
transactional to match the behaviour of PREPARE.

-O