Steve Wampler wrote:
>
> Peter Mount wrote:
> ...
> > Yes, there is a problem with multiple statements and transactions on the
> > same connection when it comes to thread safety.
> >
> > The problem as I see it is two fold.
> >
> > 1: How to deal with two statements within one transaction. Related to this
> > is the metadata methods that issue queries, how to deal with them while
> > within a transaction.
> >
> > 2: Currently the JDBC Specs only have transactions supported at the
> > Connection level, so I can't see how they thought that Statements could
> > possibly run within their own transactions, unless they thought that a
> > workaround of this is the use of batches.
>
> Ah, that probably explains why I've seen "tuple arrived before metadata"
> messages when I've got several apps talking through CORBA to a java app
> that connects to postgres. Do I need to synchronize both inserts and
> queries at the java app level to prevent this? (I was hoping that
> the BEGIN/END block in a transaction would be sufficient, but this makes
> it sound as though it isn't.)
>
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? 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).
Right now each new connection requires forking off another backend,
which makes it impractical to connect whenever you have a new
transaction to do.