1 2 Previous Next 23 Replies Latest reply on May 30, 2002 6:46 AM by Chris Harris

    When do i need an XADataSource?

    Oliver Henlich Newbie

      I've been following the discussions to do with XADataSource / Distributed transactions for some time and have also tried to read through the various specs
      to do with this.

      However, i still feel i'm missing a vital bit of information.

      Quick problem background and description
      o We have gone live with a system developed with
      - One JBoss2.4.3 server
      - One SQLServer2000
      - Opta2000_413 driver
      o We are using Optas XADataSource but it does not have access to the master db.
      o We are experiencing serious database locks on a daily basis!
      o Most often these stop everything but sometimes SQLServer detects a deadlock and chooses a victim.
      o Some methods in our EJBs are required to run in a container managed transaction.
      A few of these methods need access to more than one connection
      (obtained from the JBoss pool) within the same transaction. Example
      below:
      [pre]
      class EJB
      {
      // run in a container managed transaction
      public methodA()
      {
      try
      {
      doSomething1()
      doSomething2()
      }
      catch ()
      {
      // decide if i want to rollback
      }
      }

      private doSomething1() throws Exception
      {
      // jndi lookup of datasource
      // get connection from datasource
      // do some work
      // close connection
      }
      private doSomething2() throws Exception
      {
      // jndi lookup of datasource
      // get connection from datasource
      // do some work
      // close connection
      }
      }
      [/pre]
      Q1: From what i have described, should we be using XADataSource?
      Q2: How do you know if you need to use an XADataSource/distributed
      transactions?

      I hope i have been clear.
      Thanks already
      Oliver Henlich
      www.pogo-tech.com

        • 1. Re: When do i need an XADataSource?
          Thomas Poeschmann Newbie

          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

          • 2. Re: When do i need an XADataSource?
            Oliver Henlich Newbie

            Hi thomas.
            Thanks for your response. So, just to verify (cement it). Are we saying that if any SLSB, SFSB, BMP Bean has the following repeated more than once on a particular method invocation running in a transaction...

            [pre]
            // this occurs more than once in a single tx
            datasource.getConnection()
            connection.createStatement()
            statement.close()
            connection.close()
            // end
            [/pre]

            ...we do NOT need to use an XADataSource?

            Q1 will everything be transactionally sound?
            Q2 all connections will be commited at the end of the transaction?
            Q3 there is no danger of these connections locking each other out?
            Q4 are there any dependencies on the driver

            pls can someone from the dev team verify/deny/clarify my original questions w.r.t JBoss? Or is my question not that simply answered?

            thanks guys

            • 3. Re: When do i need an XADataSource?
              Oliver Henlich Newbie

              Hi again.
              In the hope that this will be useful to others I'll post up findings. I put the same question as my original one above to the guys at i-net software (the guys who make the opta driver). Here is their response:


              In general you need only a XADataSource if you have transaction over more as
              one connection.

              I am not know how JBOSS is handling if you request a connection, close it
              and request a new connection from the same DataSource. It should be handled
              it.

              In general it is faster the connection to request only once and to pass it
              to the methods doSomething1() and doSomething2(). In this case you need only
              a simple transaction.


              I have to say, I am still confused!

              Q If i use (open & close) more than one connection within the context of a single transaction, do i need to make sure i am using distributed transactions/XADataSource

              feeling braindead
              someone explain pls

              • 4. Re: When do i need an XADataSource?
                David Jencks Master

                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.

                • 5. Re: When do i need an XADataSource?
                  Oliver Henlich Newbie

                  Hi David.
                  Thank you very much for the detailed an informative response. I will respond to it in a seperate post.

                  Here is more from the inet guys...is there a conflict between what you and they are saying?
                  I'm kindof hoping to open up a dialog between JBoss and inet (they are very helpful, i sent them the link to this thread)


                  I can not verify in your sample if you use the same datasource or different
                  datasources in your sample.

                  If I understand you then you use the same connection [I think they mean datasource here]. In this case it is
                  faster to use only one connection. The advatages are
                  - one lookup
                  - one getConnection
                  - one close
                  - you can use a simple DataSource.

                  It doesn't make any difference how JBoss work because the XDataSource is
                  only a emulation of XA. The driver need 2 or more physical connections to
                  the database in this case.


                  If you use the DTCDataSource then you need only one connection with the SQL
                  Server 2000 but you have the overhead of the MSDTC.

                  If you want use 2 same connections with the XDataSource and you work with
                  the same database objects (tables) then you need access to the master db.

                  • 6. Re: When do i need an XADataSource?
                    Oliver Henlich Newbie

                    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 Not using a DataSource that implements
                    javax.sql.XADataSource but a DataSource that only implements javax.sql.DataSource.
                    - you also explained (in point 1) that we get this behaviour if i use the JBoss DataSource
                    wrapper org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl.

                    Q2. I am using inets com.inet.tds.XDataSource. How is a 3rd party XADataSource
                    implementation expected to work? Is it expected to exhibit the same behavior as
                    the JBoss DataSource wrapper? I think you described this in your point (2), but
                    am not sure that they are doing it this way?

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

                    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 connection for the duration of a transaction.
                    -(i) Use JBoss wrapper.
                    [pre]

                    org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl
                    ...

                    [/pre]
                    -(ii) Use inet XADataSource
                    [pre]

                    com.inet.tds.XDataSource
                    ...

                    [/pre]
                    -(iii) Use inet normal DataSource
                    [pre]

                    com.inet.tds.TdsDataSource
                    ...

                    [/pre]

                    Much appreciate any input here.
                    Learnt lots so far...might be a useful FAQ/HOWTO.
                    cheers
                    oliver

                    • 7. Re: When do i need an XADataSource?
                      Oliver Henlich Newbie

                      inet respone after reading this thread
                      they are pooling physical connections in their XADataSource? How does this tie in with what you said david about only handing out 'Handles' to connections?


                      I am understand your confusion. But you can only answer this question final.
                      All what was write over JBoss and pool is right if you use a read
                      XADataSource like DTCDataSource. But the XDataSource is only a emulation. In
                      your sample it can be that the XDataSource create a internal pool of 4
                      physical connections. If you have a pool of 5 connections in JBoss then you
                      have already 20 physical connections. The XDataSource is working internal
                      equals JBoss with JDBC 1 datasources but with more features because it has
                      directly database access.

                      The questions are:
                      Q1: Do you use the same datasource in both methods? [Yes]
                      Q2: Do you use a single transaction over all (No required new) ? [Yes]
                      Q3: Do you call this methods only internal? It is not part of an interface ? [Yes]

                      Your code look then like if you use only one connection:
                      [pre]
                      public methodA()
                      {
                      try
                      {
                      // jndi lookup of datasource
                      // get connection from datasource
                      doSomething1(connection)
                      doSomething2(connection)
                      }
                      catch ()
                      {
                      // decide if i want to rollback
                      }
                      finally
                      {
                      // close connection
                      }
                      }

                      private doSomething1(connection) throws Exception
                      {
                      // do some work
                      }
                      private doSomething2(connection) throws Exception
                      {
                      // do some work
                      }
                      }
                      [/pre]

                      You can also use a XDataSource with this code. Because you have only 1
                      connection from this DB in your code you not receive a deadlock.

                      You need to decide. You have 3 solutions:
                      - access to the master db
                      - install the Ressource Manager Proxy and use the DTCDataSource
                      - Modify your code and use only one connection. You can use this with any
                      DataSource.

                      • 8. Re: When do i need an XADataSource?
                        David Jencks Master

                        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.

                        • 9. Re: When do i need an XADataSource?
                          Oliver Henlich Newbie

                          Hi David.
                          I'm sorry it looks like i'm simply dumping all this here...i guess i am confused by the different responses from jboss and inet.

                          I was wondering if you could have a look at my Mar 5, 2002 5:09 AM posting and give me a 'nod' or 'shake head' or 'comment' on my four questions/comments.

                          Thanks for your help again.

                          • 10. Re: When do i need an XADataSource?
                            David Jencks Master

                            > 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

                            • 11. Re: When do i need an XADataSource?
                              Thomas Poeschmann Newbie

                              > 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

                              • 12. Re: When do i need an XADataSource?
                                Thomas Poeschmann Newbie

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

                                • 13. Re: When do i need an XADataSource?
                                  David Jencks Master

                                  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.

                                  • 14. Re: When do i need an XADataSource?
                                    Chris Harris Newbie

                                    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.

                                    1 2 Previous Next