AutoCommitTxn Execution Property
You are looking at an older version of the documentation. The latest version is found here.
Since user-level commands may execute multiple source commands, you can specify the AutoCommitTxn
execution property to control the transactional behaviour of a command when not in a local or global transaction.
The property has the following settings:
Setting | Description |
---|---|
| Default setting for SQL running inside a job. Do not wrap each command in a transaction. Individual source commands may commit or rollback regardless of the success or failure of the overall command |
| Wrap each command in a transaction. This mode is the safest, but may introduce performance overhead |
| This is the default setting outside of jobs. Will automatically wrap commands in a transaction, but only if the command seems transactionally unsafe |
The concept of command safety with respect to a transaction is determined by the Data Virtuality Server, depending on the command type, the transaction isolation level, and available metadata. A wrapping transaction is not needed in the following cases:
If a user command is fully pushed to the source;
If the user command is a
SELECT
(including XML) and the transaction isolation is notREPEATABLE_READ
orSERIALIABLE
;If the user command is a stored procedure and the transaction isolation is not
REPEATABLE_READ
orSERIALIABLE
and the updating model count is zero. The update count may be set on all procedures as part of the procedure metadata in the model.