1 Reply Latest reply on Oct 11, 2017 10:15 AM by Steven Hawkins

    How can transactions be managed while using procedures

    gadeyne.bram Master

      Hi,

       

      I've created some virtual procedures that filter, copy and transform a lot of data (milions of rows) from different sources to a single database.

       

      The setup is like this:

       

      Procedure A starts a cursor that for each line calls Procedure B. On it's turn procedure B starts a cursor and calls procedure C. Procedure C eventually executes (multiple) inserts.

       

      A transaction is necessary on none of the procedure calls but now it seems like a single transaction exists that spans the whole execution of procedure A.

       

      I know that settings like "AutoCommitTxn OFF" exist but I'm not sure that I understand how they work completely.

       

      I'm used to working with javax.transaction where I can set REQUIRED or NEVER on a Java method. Can someone explain to me how this would translate in Teiid?

        • 1. Re: How can transactions be managed while using procedures
          Steven Hawkins Master

          > I know that settings like "AutoCommitTxn OFF" exist but I'm not sure that I understand how they work completely.

           

          From a connection, you can participate in an XA transaction, use the autoCommit flag or transaction statements to start a local transaction, or have autoCommit=true.  With autoCommit you may not always want to start a transaction, as that can greatly impact performance.  The settings for autoCommitTxn are ON, OFF, DETECT.  By choosing OFF you are telling Teiid to not start a transaction when autoCommit=true.

           

          It is possible to control transactions down to a block level using BEGIN ATOMIC.  Note that a limitation is that we don't support subtransactions, so BEGIN ATOMIC is considered satisfied by having a higher level transaction.