2 Replies Latest reply on May 21, 2002 5:55 PM by James Cook

    XA Trouble

    James Cook Newbie

      I am trying to use XA transactions in Sybase ASE 12.5. We are using jBoss 2.6 to perform our tests and the Feb 2002 jConnect drivers. Isolation level is set to READ_COMMITED.

      To test an XA scenario, I have created a table (MYTABLE) and inserted a row.

      I have set up an XA datasource in jBoss and deployed a Stateless Session Bean that performs these operations:

      *. XA Begin Tran (implied, performed by app server)
      1. Get a connection (C1) from the jBoss XA pool.
      2. Get another connection (C2) from the jBoss XA pool. (C1 <> C2)
      3. Create a statement using C1 and issue an update call against the row in MYTABLE.
      4. Create a statement using C2 and attempt to update the same row.
      *. XA Commit Tran (implied, performed by app server)

      Problem
      ======
      The XA transaction blocks when statement 4 is executed. It is my understanding that step 4 above should execute normally under XA transactional support. In a non-XA scenario, it would be expected that step 4 will block.

      Is my expectation correct? Should multiple connections under the same XA transaction share locks?

        • 1. Re: XA Trouble
          David Jencks Master

          I expect that the locks should be shared for all connections in a tx from one machine at least. Otherwise you might as well tie the transaction to a connection and kill scalability. However, this is not expressed very clearly in the xa docs I have found (such as the jta spec). (i.e. they don't say a word about it). If the connections are from different machines, there is an explicit concept of "loosely coupled" which means that locks won't be shared, and work done from one machine won't be visible to the other.

          In any case, I wonder if there is some db or driver setting that will make this work?

          Also check that the 2 connections are getting the same xid with the same branchid.

          • 2. Re: XA Trouble
            James Cook Newbie

            > I expect that the locks should be shared for all
            > connections in a tx from one machine at least.

            It turns out that this is not the case. I have a conference call with the Sybase XA engineer tomorrow, but the current support engineer states that an exclusive lock shared by one connection is not shared with another connection even if both connections are controlled by the same XA transaction.

            > In any case, I wonder if there is some db or driver
            > setting that will make this work?

            I can read from the 2nd connection as long as I use isolation level 0, however I can't get that to work in jBoss 2.4.6. It seems that the XAConnectionFactory.prepare method requires my connection to be wrapped in XAConnectionImpl in order for that to work. Not sure how that figures into the configuration options. My SybaseXADataSourceLoader simply exposes a SybXADataSource object. I had to rewrite this because it does not have a setProperties() method that XADataSourceLoader requires.

            > Also check that the 2 connections are getting the
            > same xid with the same branchid.

            The Sybase guys claim that XA simply does not support the sharing of shared locks. Puts a real damper on cascade deletes and validation routines that must validate the state of the database prior to a commit.