JBoss.orgCommunity Documentation
Teiid utilizes XA transactions for participating in global transactions and for demarcating its local and command scoped transactions. JBoss Transactions is used by Teiid as its 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 AutoCommitTxn 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. |
The default transaction isolation level for Teiid is READ_COMMITTED.
Since user level commands may execute multiple source commands, users can specify the AutoCommitTxn execution property to control the transactional behavior of a user command when not in a local or global transaction.
Table 6.2. AutoCommitTxn 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 introduce performance overhead. |
| DETECT | This is the default setting. 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, the transaction isolation level, and available metadata. A wrapping transaction is not needed if:
If a user command is fully pushed to the source.
If the user command is a SELECT (including XML) and the transaction isolation is not REPEATABLE_READ nor SERIALIABLE.
If the user command is a stored procedure and the transaction isolation is not REPEATABLE_READ nor SERIALIABLE 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.
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 |
|---|---|
| 0 | No updates are performed by this command. |
| 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. |
| * | Any number greater than 1 indicates that execution is transactionally unsafe and an XA transaction will be required. |
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,
org.teiid.jdbc.TeiidDataSource. 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.
JBoss AS allows creation of different types of data sources, based on their transactional capabilities. The type of data source you create for your VDB's sources also dictates if that data source will be participating the distributed transaction or not, irrespective of the transaction scope you selected from above. Here are different types of data sources
xa-datasource: Capable of participating in the distributed transaction using XA. This is recommended type be used with any Teiid sources.
local-datasource: Does not participate in XA, unless this is the only source that is local-datasource that is participating among other xa-datasources in the current distributed transaction. This technique is called last commit optimization. However, if you have more then one local-datasources participating in a transaction, then the transaction manager will end up with "Could not enlist in transaction on entering meta-aware object!;" exception.
no-tx-datasource: Does not participate in distributed transaction at all. In the scope of Teiid command over multiple sources, you can include this type of datasource in the same distributed transaction context, however this source will be it will not be subject to any transactional participation. Any changes done on this source as part of the transaction scope, can not be rolled back.
If you have three different sources A, B, C and they are being used in Teiid. Here are some variations on how they behave with different types of data sources. The suffixes "xa", "local", "no-tx" define different type of sources used.
A-xa B-xa, C-xa : Can participate in all transactional scopes. No restrictions.
A-xa, B-xa, c-local: Can participate in all transactional scopes. Note that there is only one single source is "local". It is assumed that in the Global scope, the third party datasource, other than Teiid Datasource is also XA.
A-xa, B-xa, C-no-tx : Can participate in all transactional scopes. Note "C" is not a really bound by any transactional contract. A and B are the only participents in XA transaction.
A-xa, B-local, C-no-tx : Can participate in all transactional scopes. Note "C" is not a really bound by any transactional contract, and there is only single "local" source.
If any two or more sources are "local" : They can only participate in Command mode with "autoCommitTxn=OFF". Otherwise will end with exception as "Could not enlist in transaction on entering meta-aware object!;" exception, as it is not possible to do a XA transaction with "local" datasources.
A-no-tx, B-no-tx, C-no-tx : Can participate in all transaction scopes, but none of the sources will be bound by transactional terms. This is equivalent to not using transactions or setting Command mode with "autoCommitTxn=OFF".
Teiid Designer creates "local" data source by default which is not optimal for the XA transactions. Teiid would like this to be creating a XA data sources, however with current limitations with DTP that feature is currently not available. To create XA data source, look in JBoss AS "doc" directory for example templates, or use the "admin-console" to create the XA data sources.
If your datasource is not XA, and not the only local source and can not use "no-tx", then you can look into extending the source to implement the compensating XA implementation. i.e. define your own resource manager for your source and manage the transaction the way you want it to behave. Note that this could be complicated if not impossible if your source natively does not support distributed XA protocol. In summay
Use XA datasource if possible
Use no-tx datasource if applicable
Use autoCommitTxn = OFF, and let go distributed transactions, though not recommended
Write a compensating XA based implementation.
Table 6.4. Teiid Transaction Participation
| Teiid-Tx-Scope | XA source | Local Source | No-Tx SOurce |
|---|---|---|---|
| Local | always | Only If Single Source | never |
| Global | always | Only If Single Source | never |
| Auto-commit=true, AutoCommitTxn=ON | always | Only If Single Source | never |
| Auto-commit=true, AutoCommitTxn=OFF | never | never | never |
| Auto-commit=true, AutoCommitTxn=DETECT | always | Only If Single Source | never |
The client setting of transaction isolation level is not propogated to the connectors. 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.
Temporary tables are not transactional. For example, a global temporary table will retain all inserts performed during a local transaction that was rolled back.