Re: XA end then join fix for WebLogic

Heikki,

You wondered why Weblogic calls XAResource.start(TMJOIN) in this post:
http://archives.postgresql.org/pgsql-jdbc/2006-10/msg00011.php

I'm going to try to explain you why that's happening in this email.
Please note that this explanation is solely based on my personal
observations of multiple transaction managers/app servers. I'm writing
this from the top of my head so I may have done a few mistakes.

*** deep breathe, this is long and complex ***

The transaction manager is not the masterpiece of this puzzle, the
XADataSource wrapper (that usually provides pooling and exposes a
(non-XA) DataSource interface) is. The way resources are
enlisted/delisted depends on a chosen enlistment policy. Unfortunately
the JTA spec leaves much to desire on this subject: there is not a
single line describing how enlistment should be performed nor any kind
of recommendation.

Implementors are left on their own to choose an implementation and you
basically have two major enlistment policies: on statement creation
and on connection acquirement. Websphere and Atomikos
TransactionsEssentials implement the second policy. Weblogic and
Bitronix TM implement the first one.

The biggest advantage of the 'on connection acquirement' one is that
it's much (much, much, much !) easier to implement and works plain
fine 9 times out of 10. This one calls XAResource.start(TMNOFLAGS)
when XADataSource.getConnection() is called and
XAResource.end(TMSUCCESS) when TransactionManager.commit() is called.

The 'on statement creation' one, while more complex to write is more
flexible (end-user wise) and is generally more performant when used
with databases fully supporting transaction interleaving. This one
calls XAResource.start(TMNOFLAGS) when Connection.createStatement() or
prepareStatement() or prepareCall() is called and
XAResource.end(TMSUCCESS) when Connection.close() is called.

For a good example of the increased flexibility you get with the 'on
statement creation' policy has been discussed by Dmitri Maximovich:
http://www.jroller.com/page/maximdim?entry=is_xa_transaction_support_broken
Basically only implementations using 'on statement creation'
enlistment policy can support this special case.

Now what would be the difference between the two in terms of
XAResource calls ? Let's assume we have this small example:

1 public void transactionalMethod() {
2 tm.begin();
3
4 executeSQL();
5 executeSQL();
6
7 tm.commit();
8 }
9
10 private void executeSQL() {
11 Connection c = getXADataSource().getConnection();
12 c.createStatement().executeUpdate("some SQL");
13 c.close();
14 }

With 'on connection acquirement':

line 11 (called by line 4): get a physical connection out of the
connection pool and call XAResource.start(TMNOFLAGS).
line 13 (called by line 4): mark the connection as 'not accessible'
and keep it out of the connection pool.
line 11 (called by line 5): get back the connection previously maked
as 'not accessible'.
line 13 (called by line 5): mark again the connection as 'not
accessible' but don't do anything else.
line 7: call XAResource.end(TMSUCCESS) on the connection's XAResource.
Requeue the connection into the pool.

How can line 11 (called by 5) get back the same connection that was
previously acquired ? Simply by looking at all the connections marked
as 'not accessible' and picking the one that has called
XAResource.start() with a XID that has the same GTRID as the current
global transaction's GTRID. If it can't find any this just means a new
connection should be acquired from the pool but in this example that
will never happen.

With 'on statement creation':

line 11 (called by line 4): nothing happens enlistment/delistment wise.
line 12 (called by line 4): XAResource.start(TMNOFLAGS) is called.
line 13 (called by line 4): XAResource.end(TMSUCCESS) is called and
the connection is requeued.
line 11 (called by line 5): nothing happens enlistment/delistment wise.
line 12 (called by line 5): XAResource.start(TMJOIN) is called.
line 13 (called by line 5): XAResource.end(TMSUCCESS) is called and
the connection is requeued.
line 7: nothing happens enlistment/delistment wise.

How can line 12 (called by 5) knows it should call XAResource.start()
with TMJOIN instead of TMNOFLAGS ? The TM must keep a list of
resources enlisted in the current transaction (-> XAConnections which
XAResource.start() has been called) and call
XAResource.isSameRm(currentXAResource) on all of them with
currentXAResource being the XAResource of the Connection acquired on
line 11 (called by 5). If isSameRm() returns true,
XAResource.start(TMJOIN) can be called but if it returns false
XAResource.start(TMNOFLAGS) should be called instead. TMJOIN is much
more performant as the database does not have to create a new
transaction branch and isolate work done on both connections: it just
works like if you called both executeUpdate() in the same local
transaction while with TMNOFLAGS the database has to create a new
branch and apply ACID semantics between the two executeUpdate() calls
since it has to treat the two branches as two unrelated local
transactions.

Of course, subtle variations can happen depending on a lot of
implementation-specific choices and potential configuration parameters
but this gives you the basic idea of what's happening under the cover.

Cheers,
Ludovic

ludovic orban wrote:
> Heikki,
>
> You wondered why Weblogic calls XAResource.start(TMJOIN) in this post:
> http://archives.postgresql.org/pgsql-jdbc/2006-10/msg00011.php
>
> I'm going to try to explain you why that's happening in this email.
> Please note that this explanation is solely based on my personal
> observations of multiple transaction managers/app servers. I'm writing
> this from the top of my head so I may have done a few mistakes.

Thanks for the explanation.

> The 'on statement creation' one, while more complex to write is more
> flexible (end-user wise) and is generally more performant when used
> with databases fully supporting transaction interleaving. This one
> calls XAResource.start(TMNOFLAGS) when Connection.createStatement() or
> prepareStatement() or prepareCall() is called and
> XAResource.end(TMSUCCESS) when Connection.close() is called.

Can you elaborate how 'on statement creation' is generally more
performant? It seems to me that it leads to more start/end calls. At
least in a straightforward implementation of start(TMJOIN), that means
more round-trips to the database. Have you seen or done any performance
tests on this?

You might be able to get away with a slightly smaller connection pool,
but that's with the cost of more "context switches" in the DBMS as a
connection is repeatedly associated and disassociated with a transaction.

That also makes me a bit worried about possible deadlocks. For example,
imagine this simple example:

1 public void transactionalMethod() {
2 tm.begin();
3
4 Connection c = getXADataSource().getConnection();
5 c.createStatement().executeUpdate("UPDATE ...");
6 c.close();
7
8 tm.commit();
9 }

Now consider running that with a connection pool of just 1 connection:

Thread A: runs begin + getConnection + update + close, down to line 7.
The single Connection is now released back to the pool
Thread B: runs lines 1-5, but the UPDATE blocks because of a lock held
by transaction A.
Thread A: Tries to commit, but blocks there's no connections available
in the pool.

-> deadlock.

This is an extreme example, but the same scenario is possible with a
larger connection pool, just harder to trigger.

> For a good example of the increased flexibility you get with the 'on
> statement creation' policy has been discussed by Dmitri Maximovich:
> http://www.jroller.com/page/maximdim?entry=is_xa_transaction_support_broken
> Basically only implementations using 'on statement creation'
> enlistment policy can support this special case.

Passing a Connection-object in a bean invocation just isn't sane, even
if it was allowed by the spec. What if you pass a ResultSet-object,
should it work in BeanB? What about updates to an updatable ResultSet?

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

Hi again,

> Can you elaborate how 'on statement creation' is generally more
> performant? It seems to me that it leads to more start/end calls. At
> least in a straightforward implementation of start(TMJOIN), that means
> more round-trips to the database. Have you seen or done any performance
> tests on this?

It is more performant because it allows greater concurrency for a
fixed amount of connections in the pool. This of course requires that
the DB fully supports transaction interleaving or else the wrapper has
to use a mix of both techniques and you loose that advantage. I
measured an average 25% speed improvement with FirebirdSQL on a highly
concurrent test (250+ XA transactions per second).

The reason why you get this speed improvement is because in the first
technique you have to keep the connection out of the pool until after
the full prepare/commit cycle executed. This can take a relatively
long amount of time (0.25s is not uncommon for a transaction with lots
of enlisted resources) during which your connections are not usable.

> You might be able to get away with a slightly smaller connection pool,
> but that's with the cost of more "context switches" in the DBMS as a
> connection is repeatedly associated and disassociated with a transaction.
>
> That also makes me a bit worried about possible deadlocks. For example,
> imagine this simple example:
>
> 1 public void transactionalMethod() {
> 2 tm.begin();
> 3
> 4 Connection c = getXADataSource().getConnection();
> 5 c.createStatement().executeUpdate("UPDATE ...");
> 6 c.close();
> 7
> 8 tm.commit();
> 9 }
>
> Now consider running that with a connection pool of just 1 connection:
>
> Thread A: runs begin + getConnection + update + close, down to line 7.
> The single Connection is now released back to the pool
> Thread B: runs lines 1-5, but the UPDATE blocks because of a lock held
> by transaction A.
> Thread A: Tries to commit, but blocks there's no connections available
> in the pool.
>
> -> deadlock.
>
> This is an extreme example, but the same scenario is possible with a
> larger connection pool, just harder to trigger.

Thread A won't block since commit() does not require a connection, it
just needs to use the reference it kept on the enlisted XAResource to
be able to execute the 2PC protocol. Refer to the JTA spec chapter
3.4.6, Resource Sharing for details.

> Passing a Connection-object in a bean invocation just isn't sane, even
> if it was allowed by the spec. What if you pass a ResultSet-object,
> should it work in BeanB? What about updates to an updatable ResultSet?

I agree this isn't proper design but as you can read from the comments
that's not the point. There are other ways to get the same kind of
unexpected bahavior when playing with suspend/resume. Consider this
example:

tm.begin();

c = getXADataSource().getConnection();
c.createStatement().executeUpdate("UPDATE A");

tx = tm.suspend();

tm.begin();
c.createStatement().executeUpdate("UPDATE B");
tm.rollback();

tm.resume(tx);

c.close();

tm.commit();

What do you think should happen to UPDATE A and UPDATE B ? What do you
think will happen with 'on statement creation' enlistment and with 'on
connection acquirement' ? In my opinion, update A should be committed
and update B rolled back. What will actually happen depends on both
the resource and the enlistment policy implementations.

With 'on statement creation' what I expect will happen: update A
committed and update B rolled back while with 'on connection
acquirement' both will get committed because update B is executed in a
local transaction context since it has not been enlisted in the
sub-transaction's context.

While I totally agree that those examples are corner-case and not good
examples of how things should be written, those are nevertheless traps
that application programmers can easily fall on during the
implementation process. I guess you can imagine how insane it is when
you have to troubleshoot some code where you ask the TM to rollback
and you actually see the update committed to the DB.

Cheers,
Ludovic

Hi Ludovic,

I finally put aside some time to test this..

ludovic orban wrote:
> Hi again,
>
>> Can you elaborate how 'on statement creation' is generally more
>> performant? It seems to me that it leads to more start/end calls. At
>> least in a straightforward implementation of start(TMJOIN), that means
>> more round-trips to the database. Have you seen or done any performance
>> tests on this?
>
> It is more performant because it allows greater concurrency for a
> fixed amount of connections in the pool. This of course requires that
> the DB fully supports transaction interleaving or else the wrapper has
> to use a mix of both techniques and you loose that advantage. I
> measured an average 25% speed improvement with FirebirdSQL on a highly
> concurrent test (250+ XA transactions per second).

Well, if the server has the resources to do more concurrent work than
the pool size allows, just increase the pool. If it doesn't, then it
doesn't no matter what you do.

Did you try increasing the pool size instead of interleaving normal work
and commits?

>> This is an extreme example, but the same scenario is possible with a
>> larger connection pool, just harder to trigger.
>
> Thread A won't block since commit() does not require a connection, it
> just needs to use the reference it kept on the enlisted XAResource to
> be able to execute the 2PC protocol. Refer to the JTA spec chapter
> 3.4.6, Resource Sharing for details.

I guess it depends on the RM, but let's assume a typical client/server
scenario where one XAConnection has one TCP connection to the server.
The commit() will certainly have to submit the commit message to the
server somehow, and unless it uses carrier pigeons or some other
out-of-band channel, it has to use the TCP connection it has. If that
connection is busy, waiting for another query to finish, the commit will
have to wait.

In fact, because the commit() call will have to wait for the current
statement to finish, that might lead to a longer response time.
Especially if you have a mix of long and short transactions.

I just tried this with Firebird (1.5.3) and JayBird (2.1.0) that you did
your performance test with. It *does* seem to suffer from the scenario I
described. See attached test case.

>> Passing a Connection-object in a bean invocation just isn't sane, even
>> if it was allowed by the spec. What if you pass a ResultSet-object,
>> should it work in BeanB? What about updates to an updatable ResultSet?
>
> I agree this isn't proper design but as you can read from the comments
> that's not the point. There are other ways to get the same kind of
> unexpected bahavior when playing with suspend/resume. Consider this
> example:
>
> tm.begin();
>
> c = getXADataSource().getConnection();
> c.createStatement().executeUpdate("UPDATE A");
>
> tx = tm.suspend();
>
> tm.begin();
> c.createStatement().executeUpdate("UPDATE B");
> tm.rollback();
>
> tm.resume(tx);
>
> c.close();
>
> tm.commit();
>
> What do you think should happen to UPDATE A and UPDATE B ? What do you
> think will happen with 'on statement creation' enlistment and with 'on
> connection acquirement' ? In my opinion, update A should be committed
> and update B rolled back. What will actually happen depends on both
> the resource and the enlistment policy implementations.

Agreed, A should be committed and B rolled back.

> With 'on statement creation' what I expect will happen: update A
> committed and update B rolled back while with 'on connection
> acquirement' both will get committed because update B is executed in a
> local transaction context since it has not been enlisted in the
> sub-transaction's context.

Umm, sub-transaction?

> While I totally agree that those examples are corner-case and not good
> examples of how things should be written, those are nevertheless traps
> that application programmers can easily fall on during the
> implementation process. I guess you can imagine how insane it is when
> you have to troubleshoot some code where you ask the TM to rollback
> and you actually see the update committed to the DB.

I can feel your pain :). Some years ago, we found out the hard way that
a FixPack to WebSphere Application Server 3.5.X (can't remember exactly)
introduced a bug that made it ignore setRollbackOnly-calls if an EJB
throwed an exception. That lead to corruption in our production
database, and the customer was not happy.

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

Hi Heikki,

2006/11/7, Heikki Linnakangas <heikki(at)enterprisedb(dot)com>:
> Well, if the server has the resources to do more concurrent work than
> the pool size allows, just increase the pool. If it doesn't, then it
> doesn't no matter what you do.
>
> Did you try increasing the pool size instead of interleaving normal work
> and commits?

No, I didn't try with different pool size. I just checked that with 10
connections you could do 25% more work with interleaved transactions.

I guess I could achieve the same performance by increasing the pool
size but wouldn't that add some overhead in memory utilization on the
DB engine ? I mean you can do less with the same amount of resources
when using XA compared to local transactions.

> I guess it depends on the RM, but let's assume a typical client/server
> scenario where one XAConnection has one TCP connection to the server.
> The commit() will certainly have to submit the commit message to the
> server somehow, and unless it uses carrier pigeons or some other
> out-of-band channel, it has to use the TCP connection it has. If that
> connection is busy, waiting for another query to finish, the commit will
> have to wait.
>
> In fact, because the commit() call will have to wait for the current
> statement to finish, that might lead to a longer response time.
> Especially if you have a mix of long and short transactions.
>
> I just tried this with Firebird (1.5.3) and JayBird (2.1.0) that you did
> your performance test with. It *does* seem to suffer from the scenario I
> described. See attached test case.

I disagree. The reason why commit blocks in your use case is because
of transaction isolation (ie: update locks) in the DB engine and
nothing else. You could reproduce this exact scenario with two local
transactions.

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)");

Then add the missing
xares.end(blockxid, XAResource.TMSUCCESS);
after
System.out.println("Second update committed");

and finally change this incorrect call
conn.commit();
into
xares.commit(blockxid, true);

> > With 'on statement creation' what I expect will happen: update A
> > committed and update B rolled back while with 'on connection
> > acquirement' both will get committed because update B is executed in a
> > local transaction context since it has not been enlisted in the
> > sub-transaction's context.
>
> Umm, sub-transaction?

A badly chosen term to indicate the transaction that runs between
tm.suspend() and tm.resume() in the example.

With 'on connection acquirement' UPDATE B is committed because it runs
on a resource that has not been enlisted in the current transaction.
This means a default auto-committed local transaction is silently used
instead.

> I can feel your pain :). Some years ago, we found out the hard way that
> a FixPack to WebSphere Application Server 3.5.X (can't remember exactly)
> introduced a bug that made it ignore setRollbackOnly-calls if an EJB
> throwed an exception. That lead to corruption in our production
> database, and the customer was not happy.

This is exactly my point: I can accept that a database, JMS server or
transaction manager has some limitations but *NOT* inconsistencies.
Especially *NOT* when it comes to distributed transactions where the
whole point is to secure data coherence, even at a high cost. I could
accept an exception being raised when the TM or the RM detects an
unsupported call but silently messing the consistency is not
acceptable.

Ludovic

ludovic orban wrote:
> 2006/11/7, Heikki Linnakangas <heikki(at)enterprisedb(dot)com>:
>> Well, if the server has the resources to do more concurrent work than
>> the pool size allows, just increase the pool. If it doesn't, then it
>> doesn't no matter what you do.
>>
>> Did you try increasing the pool size instead of interleaving normal work
>> and commits?
>
> No, I didn't try with different pool size. I just checked that with 10
> connections you could do 25% more work with interleaved transactions.
>
> I guess I could achieve the same performance by increasing the pool
> size but wouldn't that add some overhead in memory utilization on the
> DB engine ? I mean you can do less with the same amount of resources
> when using XA compared to local transactions.

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 guess it depends on the RM, but let's assume a typical client/server
>> scenario where one XAConnection has one TCP connection to the server.
>> The commit() will certainly have to submit the commit message to the
>> server somehow, and unless it uses carrier pigeons or some other
>> out-of-band channel, it has to use the TCP connection it has. If that
>> connection is busy, waiting for another query to finish, the commit will
>> have to wait.
>>
>> In fact, because the commit() call will have to wait for the current
>> statement to finish, that might lead to a longer response time.
>> Especially if you have a mix of long and short transactions.
>>
>> I just tried this with Firebird (1.5.3) and JayBird (2.1.0) that you did
>> your performance test with. It *does* seem to suffer from the scenario I
>> described. See attached test case.
>
> I disagree. The reason why commit blocks in your use case is because
> of transaction isolation (ie: update locks) in the DB engine and
> nothing else. You could reproduce this exact scenario with two local
> transactions.

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.

In the XATest.java, transaction A == neverfinishxid and transaction B ==
blockxid. TM trying to issue the commit of A is the Committer thread.

This scenario wouldn't happen without interleaving of commits and other
work. Transaction A wouldn't release the connection to the pool until it
commits, and therefore transaction B would block until it commits, and
there would be no deadlock.

Of course, as the number of connections in the pool increases, the
chances of this happening decreases, but is not fully eliminated. Some
usage patterns would make it quite likely. For example, if you have a
fairly long-running transaction that's executed periodically and takes a
full exclusive table lock, you might have 20 other transactions blocking
on that transaction, exhausting the pool and causing a deadlock because
the long-running transaction could never be committed.

There is ways to fix that in the TM. For example, the TM could reserve a
dedicated connection to do commits with. But that seems bad for
concurrency. Or a separate pool of connections. Or it could not return
the connection to the pool until commit.

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

> Then add the missing
> xares.end(blockxid, XAResource.TMSUCCESS);
> after
> System.out.println("Second update committed");
>
> and finally change this incorrect call
> conn.commit();
> into
> xares.commit(blockxid, true);

Good catch. I missed that because the test case never runs that far.

>> > With 'on statement creation' what I expect will happen: update A
>> > committed and update B rolled back while with 'on connection
>> > acquirement' both will get committed because update B is executed in a
>> > local transaction context since it has not been enlisted in the
>> > sub-transaction's context.
>>
>> Umm, sub-transaction?
>
> A badly chosen term to indicate the transaction that runs between
> tm.suspend() and tm.resume() in the example.
>
> With 'on connection acquirement' UPDATE B is committed because it runs
> on a resource that has not been enlisted in the current transaction.
> This means a default auto-committed local transaction is silently used
> instead.

Well I guess you could detect that in the TM by keeping track of
connections and their associations with threads if you cared. But this
is getting off-topic.

> This is exactly my point: I can accept that a database, JMS server or
> transaction manager has some limitations but *NOT* inconsistencies.
> Especially *NOT* when it comes to distributed transactions where the
> whole point is to secure data coherence, even at a high cost. I could
> accept an exception being raised when the TM or the RM detects an
> unsupported call but silently messing the consistency is not
> acceptable.

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

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

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

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

Heikki,

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

I totally agree with you but I think the ones supporting this feature
have a serious advantage over the ones that don't.

On the other hand, you've had good argument indicating that this
advantage might not exist if increasing the connection pool just gives
the same performance.

I'll dig the subject deeper next time I run a benchmark.

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

Done. See attached test case.

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

I agree with you: heuristics are not mandated by any of these two
specs but I'd personally would not go on production with a system not
supporting them.

I'll consider writing that patch if time permits.

Ludovic

ludovic orban wrote:
>> > 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. ;)
>
> Done. See attached test case.

Ahh, I see what you mean now.

Technically, with your modified test case, what you get is not a
*deadlock*, it's a serialization error. Which is different: the deadlock
in my original test case doesn't get resolved until a timeout kicks in,
whereas in your modified test case the serialization error is detected
as soon as A commits.

Actually, I'm surprised that Firebird notices the deadlock in my
original test case at all.

You can "fix" the test case by changing the commit call in the Committer
thread to a rollback. What should then happen is that the second update
continues as soon as A rolls back. There's no conflict because A
rolled back, right?. With that modification, your test case runs OK, and
mine throws the same exception as before.

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