How can transactions be managed while using procedures

[gadeyne.bram]

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?

[shawkins]

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