JBoss.orgCommunity Documentation
Teiid utilizes XA transactions for both participating in global transactions and for demarcating its own local and command scoped transactions. JBoss Transactions is used by Teiid as its internal transaction manager. See this documentation for the advanced features provided by JBoss Transactions.
Table 6.1. Teiid Transaction Scopes
| Scope | Description |
|---|---|
| Command | Treats the user command as if all source commands are executed within the scope of the same transaction. The AutoWrap execution property controls the behavior of command level transactions. |
| Local | The transaction boundary is local defined by a single client session. |
| Global | Teiid participates in a global transaction as an XA Resource. |
Since user level commands may execute multiple source commands, users can specify the AutoWrap execution property to control the transactional behavior of a user command when not in a local or global transaction.
Table 6.2. AutoWrap Settings
| Setting | Description |
|---|---|
| OFF | 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. |
| ON | Wrap each command in a transaction. This mode is the safest, but may be burdonsome on performance. |
| OPTIMISTIC | This is the default setting. Will not automatically wrap a command in a transaction, instead throw an exception if the command executed is transactionally unsafe. to execute outside of a transaction. |
| PESSIMITIC | Will automatically wrap commands in a transaction, but only if the command seems to be transactionally unsafe. |
The concept of command safety with respect to a transaction is determined by Teiid based upon command type and available metadata. Whenever any INSERT, UPDATE, DELETE, or EXECUTE (with update count greater than 0) command is detected and the success or failure of that command is not the same as the user level command, then the command is deemed unsafe without a transaction.
The update count may be set on dynamic SQL as part of the command and on all other procedures as part of the procedure metadata in the model.
The term "updating model count" refers to the number of times any model is updated during the execution of a command. It is used to determine whether a transaction, of any scope, is required to safely execute the command.
Table 6.3. Updating Model Count Settings
| Count | Description | Default For |
|---|---|---|
| 0 | No updates are performed by this command. | Dynamic SQL instructions |
| 1 | Indicates that only one model is updated by this command (and its subcommands). Also the success or failure of that update corresponds to the success of failure of the command. It should not be possible for the update to succeed while the command fails. Execution is not considered transactionally unsafe. | Physical procedures, inserts, updates, and deletes |
| * | Any number greater than 1 indicates that exection is transactionally unsafe and an XA transaction will be required. | XQuery commands |
By default Teiid will calculate the updating model count for a user query (which may be composed of many subcommands) given the above table. Thus a command with a single update as a subcommand may still require a transaction if the update is not tied to the success of the command.
Explicit values for updating model count may be set using Teiid Designer and directly in the dynamic SQL command .
The transaction scopes above map to these JDBC modes:
Command - Connection autoCommit property set to true.
Local - Connection autoCommit property set to false. The
transaction is committed by setting autoCommit to true or calling
java.sql.Connection.commit
. The transaction can be rolled back by a call to
java.sql.Connection.rollback
Global - the XAResource interface provided by an XAConnection is
used to control the transaction. Note that XAConnections are
available only if Teiid is consumed through its XADataSource,
com.metamatrix.jdbc.MMDataSource
. JEE containers or data access APIs typically control XA
transactions on behalf of application code.
J2EE provides three ways to manage transactions for beans:
Client-controlled – the client of a bean begins and ends a transaction explicitly.
Bean-managed – the bean itself begins and ends a transaction explicitly.
Container-managed – the app server container begins and ends a transaction automatically.
In any of these cases, transactions may be either local or XA transactions, depending on how the code and descriptors are written. Some kinds of beans (stateful session beans and entity beans) are not required by the spec to support non-transactional sources, although the spec does allow an app server to optionally support this with the caution that this is not portable or predictable. Generally speaking, to support most typical EJB activities in a portable fashion requires some kind of transaction support.
The client setting of transaction isolation level is not used. The transaction isolation level can be set on each XA connector, however this isolation level is fixed and cannot be changed at runtime for specific connections/commands.
Since the client transaction isolation level is not used, Teiid internally assumes a level of READ_COMMITTED. This implies that explicit transactions are not required for user level commands performing multiple reads.
Temporary tables are not transactional. For example, a global temporary table will retain all inserts performed during a local transaction that was rolled back.
Connectors may be set to immutable to prevent their participation in transactions. This is useful in situations where update commands are being issued against a source that lacks XA transaction capabilities.