On Wed, 11 Oct 2000, Joseph Shraibman wrote:
[snip]
> It isn't. I wish there were online mail archives. But anyway the
> reason it isn't is that if two statements use the same connection, when
> one of them calls enters a transaction the other one does too. So if
> you do:
> 1) BEGIN;
> 2) SELECT blah FROM tablea FOR UPDATE;
> 3) UPDATE tablea SET blah = newblah;
>
> since both statements are using the same connection they ARE NOT LOCKED
> FROM EACH OTHER, and when one calls and END; or COMIT; both of them exit
> their transactions. That is why I'm using a connection pool now when I
> have to do locking. And if Peter wants to use cursors behind the scenes
> it will be even worse, because the cursors themselves need to be in a
> BEGIN; END;, and what happens if they user thinks he is in a transaction
> but the cursor ended it for him a while ago?
We already have this problem with the two MetaData interfaces. Some of
those methods issue their own queries, and if they fail while within the
client's app's transaction all hell can break loose. This is one of the
reasons why I'm intending that the cursor based ResultSet only gets used
if the client has requested one.
> Named transactions would help with this, but the real answer would be
> to be able to have more then one connection to a backend (maybe
> tunnelled over on TCP/IP link).
Named transactions would help with both of these problems. Not knowing
enough of how the existing transaction mechanism works internally, I'd say
it's easer to do than tunnelling.
> Right now each new connection requires forking off another backend,
> which makes it impractical to connect whenever you have a new
> transaction to do.
Yes, that's the last thing we want, especially while we are trying to
optimise things...
Peter
--
Peter T Mount peter@retep.org.uk http://www.retep.org.uk
PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/
Java PDF Generator http://www.retep.org.uk/pdf/