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
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
