Q2: How do you know if you need to use an XADataSource/distributed
transactions?

A2: Whenever you must ensure that statements are not fired during the running of the transaction, but at the end (during Transaction.commit). That includes object/relational mappers (we get a beforeCompletion-event at the end of the TA and fire the statements out) and CMP. Within BMP, you say

datasource.getConnection()
connection.createStatement()
statement.close()
connection.close()

That returns the connection back to the pool, indeed you should be given the same connection in doSomething2 - check it out by setting the pool size to 1.

Q1: From what i have described, should we be using XADataSource?

A1: According to above, no, if you just use BMP/Session Bean database access.

hope it helps you...

Thomas

I don't understand Thomas' reply and don't think it is very accurate, but since I don't understand it I can't say for sure.

In the scenario you outline you should not need xa transactions. However...look at (3).

1. JBoss pooling works like this: calling close() on a Connection(handle) you got from a pool just means you are done with it. If you are using xa transactions, that means put it back in the pool so someone else can use it. If you are using local (jdbc 1 tx), the pool has to keep it on standby and hand out (a handle to) the same (underlying physical) connection whenever some component wants to do more work in the same tx. When the tx completes (commit/rollback) then it can go back in the pool. If you are using "XADataSourceImpl" you are using jdbc 1 1 pahse tx, and all calls to getConnection() within the same transaction will return handles to the same (physical) connection. I think this is what the inet guys were hoping jboss does.

2. You definitely need xa transactions if you are accessing 2 separate resource managers (database instances) in the same transaction. (I'm assuming that the database isn't handling the 2pc for you, Interbase/Firebird can do that but I think nowadays the usual plan is to have an external transaction manager). Since you have only one db, you do not need xa transactions for support of 2pc and transactional integrity. However, xa transactions can also support a much higher degree of concurrency and reuse. With xa tx, you can do work in any tx on any connection. So when you close a connection handle, the pool can put it back in the "available" pool right away, and give it out to anyone who asks. With jdbc 1 tx, the pool can't give it out until the tx completes, so connections can be tied up for long times waiting for their tx to complete.

3. As a consequence, it is possible to run out of connections with jdbc 1 tx. All you need is some tx required methods calling some tx requires new. Simultaneously call enough instances of the "requires" ejbs to use up all the connections, then none of them will be able to get the other connection they need for the "requires new" call. With xa, as long as you close your connection before calling out of the current method, the same connection you just used can be used for the "requires new" tx, and you won't run out.

I haven't actually experienced (3), but it is a pretty obvious possibility. It is conceivable this is causing your deadlocks.


More comments on the inet guys comments...

The connections you get from jboss are "handles" not what you get from say Driver.getConnection(...). You can get many that use the same underlying connection. So their comment "you need xa with more than one connection" applies to the underlying connection, not the handle you get from jboss.

If all the methods are in the same ejb, it makes little difference if you pass a connection to doSomething1 and doSomething2 or get it anew each call, the lookup of the underlying connection and handing out a handle is very fast.

In answer to your final question... you only NEED xa transactions if you are including more than one database in one transaction.

I'm having severe problems understanding about 90% of the inet responses. Remember that I have never used the db or the driver and don't know what classes the driver offers nor what capabilities the db has.

You might consider trying the jca wrappers, I at least find them considerably easier to understand than the "XADataSourceLoader" mess.

A DataSource implementation provided by a driver vendor that is not part of a jca spi implementation is very unlikely to be useable in a managed environment. I would not try without a really good understanding of what it is in front of.

Whether you are using a jdbc 1 driver, an xa driver, the XADataSourceLoader, or the jca-jdbc wrappers around one of these, the DataSource bound into JNDI that your app accesses is not part of the jdbc driver: it is supplied either by jboss (with XADataSourceLoader) or the jca-jdbc wrapper, and hides all the connection pooling, tx management, and security management jboss is doing for you.

> Hi David.
> Here is a response to your (much
> appreciated/clarifying) comments from Mar 1, 2002
> 1:45 PM.
> I would be very grateful for
> agreements/disagreements/comments...
>
> Q1. In your point (1) when you say "If you are using
> local (jdbc 1 tx)"
> - you are describing the scenario where i am
> m Not using a DataSource that implements
> javax.sql.XADataSource but a DataSource
> ce that only implements javax.sql.DataSource.
> - you also explained (in point 1) that we get this
> behaviour if i use the JBoss DataSource
> wrapper
> er
> org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl
>
You can't use a javax.sql.DataSource provided by a driver vendor in jboss, unless its part of a jca-jdbc driver implementation. (AFAIK I wrote the only one of these, for Firebird). The XADataSourceLoader requires you to use a XADataSource implementation. Since many people don't have one for their favorite db, and many of the others seem to take spec interpretation into their own hands, JBoss provides a fake XADataSource implementation wrapping a Driver implementation. This implements the methods of XADataSource, but doesn't implement xa semantics. This XADataSourceImpl wraps a jdbc 1 driver, and of course only uses the local 1 phase transactions supported by such drivers. So, it has to keep track of which tx goes with which connection, and only release connections back to the pool after the tx ends. This can result in running out of connections if you have RequiresNew calls from inside Requires calls. I must say I have been unsuccessful in understanding how this code works. I much prefer the separation of concerns in the jca model, where all this is handled rather simply.

>
> Q2. I am using inets com.inet.tds.XDataSource.
> How is a 3rd party XADataSource
> implementation expected to work? Is it expected
> ected to exhibit the same behavior as
> the JBoss DataSource wrapper? I think you
> k you described this in your point (2), but
> am not sure that they are doing it this way?
>
A XADataSource implementation is expected to implement the interface and contracts of an XADataSource, especially those involving the 2pc process. The jboss XADataSourceImpl is basically a jboss specific hack to allow you to use jdbc 1 drivers. I am hampered by not knowing anything about inet's XDataSource implementation.

> Q3. About your point (3). I am definately
> experiencing this behavior (I think). Run some
> stress tests and depending on the load JBoss
> JBoss 'hangs'. I can check that it is hung because
> of what you describe in (3) by doing a thread
> hread dump (ctrl srcl-lock) and observing the
> following in all the threads. This does not
> s not cause database locks however which are visible
> in the database manager tools. Here we are just
> just running out of resources.
> [pre]
> "RMI TCP Connection(60)-192.168.10.106"
> 10.106" daemon prio=5 tid=0x838ad0 nid=0x27b waiting
> on monitor [0xe8fe000..0xe8ffdc8]
> at java.lang.Object.wait(Native
> ect.wait(Native Method)
> at
> at
> at java.lang.Object.wait(Object.java:420)
> at
> at
> at
> at org.jboss.pool.ObjectPool.getObject(Unknown
> nknown Source)
> at
> at
> at
> at org.jboss.pool.ObjectPool.getObject(Unknown
> nknown Source)
> at
> at
> at
> at
> at
> at
> org.jboss.pool.jdbc.xa.XAPoolDataSource.getConnection
> Unknown Source)
> at
> at
> at
> at
> at
> at
> pogo.twang.sql.ServiceLocator.getConnection(ServiceLo
> ator.java:89)
> [/pre]
>
This looks to me as if it is consistent with the running out of connection problem I outlined.

> Q4. About my configuration (jboss.jcml), it seems
> like i have three options. From the discussion so far
> i believe it
> is ok to use (iii) knowing that i tie up a
> up a connection for the duration of a transaction.
> -(i) Use JBoss wrapper.
> [pre]
> <mbean
&gt; <mbean code="org.jboss.jdbc.XADataSourceLoader"
&gt; name="DefaultDomain:service=XADataSource,name=TWANG_P
&gt; OL">
> <attribute
&gt; <attribute
&gt; name="DataSourceClass">org.jboss.pool.jdbc.xa.wrapper
> XADataSourceImpl
> ...
>
> [/pre]

You can do this with the danger of running out of connections.
> -(ii) Use inet XADataSource
> [pre]
> <mbean
&gt; <mbean code="org.jboss.jdbc.XADataSourceLoader"
&gt; name="DefaultDomain:service=XADataSource,name=TWANG_P
&gt; OL">
> <attribute
&gt; <attribute
&gt; name="DataSourceClass">com.inet.tds.XDataSource</attr
&gt; bute>
> ...
>
> [/pre]

if this really implements XADataSource properly, including "you can do work on any connection in any transaction at any time" this is your best bet and will avoid the running out of connections problem.
> -(iii) Use inet normal DataSource
> [pre]
> <mbean
&gt; <mbean code="org.jboss.jdbc.XADataSourceLoader"
&gt; name="DefaultDomain:service=XADataSource,name=TWANG_P
&gt; OL">
> <attribute
&gt; <attribute
&gt; name="DataSourceClass">com.inet.tds.TdsDataSource</at
&gt; ribute>
> ...
>
> [/pre]
>

Assuming TdsDataSource implements DataSource you cannot do this. DataSource does not extend XADataSource.
> Much appreciate any input here.
> Learnt lots so far...might be a useful FAQ/HOWTO.

better to switch to jboss 3 and the jca wrappers, IMNSHO.
> cheers
> oliver

> I don't understand Thomas' reply and don't think it is
> very accurate, but since I don't understand it I can't
> say for sure.

David,

first: shame on me for my bad english ;) Quite ugly, you're right.

What I wanted to say is: IMHO, whenever you use BMP or databases directly using JDBC, the chance to run into a conflict that can only be solved using 2PC is extremly small. That would mean: "using BMP and direct JDBC access, there is no need to use 2PC" - no matter if you are using the same connection again or two connections to two different databases. I haven't seen a database that fails during "commit" - maybe there is a chance that some optimistic ones could do.

If you do the following fron the client:

utx.begin();
useBean#1();
useBean#2();
utx.commit();

Inside the Beans you say:

public void doSth()
{
ds = ctx.lookup();
con = ds.getConnection();
ps = con.prepareStatement(update...);
ps.executeUpdate();
con.close();
}

As you can see, if you don't commit the changes (delegate that to the utx, which enlists the connections), there is no need for 2pc here, as long as you can guarantee that the con.commit() during the utx.commit() does not fail.

Sorry for responding that late, I wasn't in the office.

Please let me know your opinion...

Thomas

> However, xa transactions can also support a much higher
> degree of concurrency and reuse. With xa tx, you can do
> work in any tx on any connection. So when you close a
> connection handle, the pool can put it back in
> the "available" pool right away, and give it out to
> anyone who asks.

David,

me again. That't the advantage, you're right.

1. With some databases with some constraints it is possible to defer checking until commit. Therefore commit can fail even if no communication failures occur.

2. The major use of 2pc is perhaps not when everything is going well but to provide guarantees in case something fails during commit. If, during a tx spanning more than one db, something fails during a dml operation, it is easy enough to rollback work in each db separately without 2pc... as long as no failures happen during the rollback process. The canonical 2pc example however is:

start tx
remove money from account in db1
deposit money to account in db2
start commit process
db1 fails (someone unplugs computer from ups) before commit is complete, whereas db2 succeeds. On restart of db1, you have a problem.

2pc solves this by:
1. tm writes in persistent log that tx involves db1 and db2. (jboss tm does not do this currently, you have to use Tyrex)
2. tm says "prepare" to both dbs. If one fails before prepare is complete, recovery can look at db1 and db2 (it knows to look at these dbs due to logging in step 1), see that only one has prepared, and roll back the other.
3. If failure occurs to the app server after (2) but before (4), recovery could either commit or rollback. The usual choice I believe is to rollback.
4. tm, having heard back from all participants that prepare was fine, says "commit" to both dbs. If one fails before commit is complete, recovery can look at db1 and db2, see that one has committed, and commit the other.

So, even with bmp, if you have more than one db in your tx, you need 2pc.

Just to provide a little clarification, the inet driver opta2000 *does* provide a proper XADataSource in com.inet.tds.XDataSource. I use this extensively to talk to MS Sql Server 2000 and use it to do proper coordinated transactions involving db access and JMS messages within a single transaction. It all works well if you use Tyrex - if you use the default tm it doesn't work at all (complains about Xid implementation iirc - I'm at home so can't clarify now).

That said, we also have methods (or, more accurately, inter-bean method chains using a single tx) which use a whole bunch of database connections and that works fine also. (The only locking issues we ever get are with SFSB and that's nothing to do with the database.)

So, it *is* possible and we haven't seen any problems. We're using version 4.14 of the driver but 4.13 worked OK also.