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

Hi,

> Well, it depends on the DBMS implementation. If the DBMS allocates a
> significant amount of memory per connection, then yes. If it doesn't,
> no. But you have to also take into account the cost of switching
> transaction context in the DBMS as well. It might be expensive, again
> depending on the internal architecture of the DBMS.
>
> There isn't a generic answer, but I'd say that either way the overhead
> is probably negligible compared to all the other CPU work and memory
> used for shared buffers etc. What matters most is the total amount of
> CPU and I/O workload you submit to the server, not how you submit it.

I think the final statement about if transaction interleaving improves
performance or not is 'it depends'.


> 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.

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. 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.

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. 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. This has absolutely nothing to do
with XA nor interleaved transactions and you could reproduce the exact
same behavior with two different connections.


> > If the two transactions you run concurrently aren't put to sleep
> > because of locked data access, they'd both run in parallel without a
> > hitch.
> >
> > To convince yourself, edit your test and change
> > conn.createStatement().executeUpdate("UPDATE foo SET bar = 0000");
> > into
> > conn.createStatement().executeUpdate("INSERT INTO foo VALUES(0000)");
>
> No, that's not what my imaginary application does, it does UPDATEs.
> Fixing the problem by changing the application is cheating :).

In your imaginary application, it is the *TRANSACTION* that is blocked
because of *ISOLATION*. The connection is perfectly reusable for
running another transaction which won't be blocked if it does not work
on rows locked by another transaction.

This is what I wanted to show by changing the update into an insert.


> Sure. If you find anything like that in the PostgreSQL driver, please
> drop a note (or even better, a patch).

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.

Ludovic