Re: XA end then join fix for WebLogic - Mailing list pgsql-jdbc

ludovic orban wrote:
>> You missed the point of the test case. It mimics the call pattern a RM
>> would see in an environment consisting of:
>>
>> 1. An application server
>> 2. A TM that does 'on statement acquirement'
>> 3. A connection pool of 1 connection
>> 4. A transaction that executes: UPDATE foo SET bar = ?
>>
>> If two of these transactions. let's call them A and B, arrive to the
>> application server roughly at the same time, you can get a deadlock with
>> this call pattern:
>>
>> A: gets connection from pool
>> A: executes UPDATE
>> A: returns connection to pool, but doesn't commit yet
>> B: gets connection from pool
>> B: executes UPDATE. UPDATE blocks because it has to wait for transaction
>> A to finish.
>>
>> Now when TM tries to get a connection from the pool to commit A, it
>> can't because the single connection in the pool is already in use. Or,
>> if it doesn't try to acquire a connection but just issues
>> XAResource.commit of the single connection, the commit-call will block
>> because the underlaying physical connection to the server is busy
>> waiting for UPDATE B statement to finish.
>
> I didn't miss your point, I just tough some things were clear but it
> seems they aren't.
>
> This is where you're wrong: the commit call won't block because the
> connections is busy but because the row update to commit conflicts
> with another concurrent update.

Well, the update B blocks in the database because of the open
transaction A. And the commit call blocks because UPDATE b statement blocks.

> Let me reiterate: the 'A to finish' step does NOT need to acquire a
> connection from the pool.  The enlisted XAResource will be reused to
> issue the prepare/commit calls.

That's legal according to JTA spec, agreed.

> Potentially these calls could happen
> in parallel with other XAResource calls and eventually while the
> related XAConnection has been taken out of the connection pool by some
> other thread that executes statements concurrently in the context of
> another transaction. Calling XAResource methods concurently is
> perfectly legal according to the JTA spec.

Agreed, potentially that's what could and does happen. And agreed,
that's legal.

> It might be that some DB would suffer from the problem you describe
> (client blocks because the connection is busy) and maybe this is what
> would happen with Postgres (if it supported tx interleaving) but some
> other DB might well be able to run everything asynchronously.

You're right that if the DBMS is capable of receiving and processing
messages incoming asynchronously, there's no problem. However, I think
such systems are an exception, not a norm.

And no, I don't think it's sensible to expect a DBMS to redesign it's
client/server protocol just to support this behavior. There's nothing
that can't be done without it.

> Once
> again it depends on the implementation but in the FB case, the
> connection sends the order to the DB and the DB itself deadlocks
> because of transaction isolation.

I think this is the part that you haven't fully understood. Update B
blocks because A is keeping the row locked in the DB. But note: it's not
a deadlock yet! A is ready to commit, and as soon as A commits, B can
continue.

It only becomes a deadlock when the TM calls commit A using the
connection that's busy handling the UPDATE B statement, which in turn is
waiting for A to commit. Had the TM used another connection to issue the
commit, there would be no deadlock.

> This has absolutely nothing to do
> with XA nor interleaved transactions and you could reproduce the exact
> same behavior with two different connections.

I challenge you to do so. ;)

> The only point where we seem to disagree is the importance of
> heuristics. Apart from that I think the postgresql driver support the
> minimal amount of features to make it usable. I'll run my test suite
> against it as soon as I have some time.

Actually I tend to agree that heuristics as you define them would be
useful. What I disagree about is that it's a required and essential part
of the JTA/XA spec. Doing it would be a small patch to both the server
and driver. I don't think I care enough to write it myself, but feel
free to submit a patch!

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com