3 Replies Latest reply on Apr 5, 2013 7:51 AM by shawkins

    Teiid JDBC transaction rollback fail (rollback without any effect)

    royshum
    royshum Apr 3, 2013 5:44 AM

      Hello,


      I am using Teiid 8.4 with AS/400 DB2 (JDBC driver JTOpen 7.9) as the backend database.

       

       

      I've tested that the AS/400 transaction support with JDBC worked well without any problem; however, when executing any INSERT / UPDATE statements with Teiid and then rollback immediately without committing, the changes are still committed to the database. There are no exceptions nor errors during the execution and rollback, the rollback call executed normally but just without any effects.

       

       

      The Teiid data model was created with Teiid Designer 8.0.0.Final with Eclipse Juno Service Release 1 (Build ID: 20120920-0800); by importing the JDBC Database to the Source Model, and the Transactor is set to “db2”. All the other settings are by default.

       

       

      I had checked the following items carefully but still without any clues:

      -AS/400 DB2 Transactions are functioning well.

      -Connection.setAutoCommit(false)

      -Tried with Connection.setSavepoint(), but Teiid JDBC client do not support it and simply throw exception.

      -JDBC driver property “autoCommitTxn”, tried default (blank), or specified value “OFF”, without any effect.

      -JDBC driver property “disableLocalTxn”, tried default (blank), or specified value “false”, without any effect.

      -Tried both INSERT / UPDATE statements rollback, without any effects.

       

      Sample source file is attached for references.

       

       

      Any hints would be grateful.

       

       

      Thanks.

       

      Roy

        • 1. Re: Teiid JDBC transaction rollback fail (rollback without any effect)
          shawkins
          shawkins Apr 3, 2013 7:02 AM (in response to royshum)

          > -AS/400 DB2 Transactions are functioning well.

           

          How is your source configured?  Is it an XADataSource?

           

          > -Connection.setAutoCommit(false)

           

          That will start a Teiid local transaction.  On the server side that means that we're coordinating an XA transaction.  If the source is not an XADataSource it will not fully participate in the transaction.  I believe it is up to the JBoss Transactions configuration whether it will use a source local transaction for a source that just supports local transations.

           

          > -Tried with Connection.setSavepoint(), but Teiid JDBC client do not support it and simply throw exception.

           

          That is expected.  Savepoints are not implemented.

           

          > -JDBC driver property “autoCommitTxn”, tried default (blank), or specified value “OFF”, without any effect.

           

          Off disables Teiid's automatic creation of a wrapping transaction when you are in autoCommit = true and execute sql that appears to need coordinated across multiple sources.

           

          > -JDBC driver property “disableLocalTxn”, tried default (blank), or specified value “false”, without any effect.

           

          False effectively disables the affect of calling setAutoCommit(false), commit(), rollback().  In some scenarios tooling will use local transactions, but for performance or other reasons people may want to disable them.

           

          Hope this helps,

           

          Steve


            • Actions
          • 2. Re: Teiid JDBC transaction rollback fail (rollback without any effect)
            royshum
            royshum Apr 4, 2013 11:49 PM (in response to shawkins)

            Hi Steve,

             

             

            Thanks for your reply!

            The backend data source between Teiid and AS400 is configured with “local-tx-datasource” instead of “xa-datasource”. Once I’ve reconfigure it to a “xa-datasource”, the problem gone and the transaction rollback worked well. Thanks for your hints again.

             

             

            Just wanna know, is it the data source between Teiid and the backend database must be using xa-datasource instead of local-tx-datasource?

             

             

            Thanks.

             

            Roy

              • Actions
            • 3. Re: Teiid JDBC transaction rollback fail (rollback without any effect)
              shawkins
              shawkins Apr 5, 2013 7:51 AM (in response to royshum)

              Currently yes, your source data sources must be XA to properly participate in Teiid transactions.  Only if the transaction manager is configured to workaround by using local-tx-datasources as 1 phase commit resources could a local-tx-datasource work.  Otherwise Teiid will only use a local transactions in very specific scenarios, such as a push down of batched updates.

               

              Steve

                • Actions