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