<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.5//EN" "http://www.oasis-open.org/docbook/xml/4.5/docbookx.dtd">
<chapter id="transaction_support">
	<title>Transaction Support</title>
	<para>
		Teiid utilizes XA transactions for participating in global
		transactions and for demarcating its local and command scoped
		transactions.
		<ulink url="http://www.jboss.org/jbosstm/">JBoss Transactions</ulink>
		is used by Teiid as its transaction manager. See
		<ulink url="http://www.jboss.org/jbosstm/docs/index.html"> this documentation</ulink>
		for the advanced features provided by JBoss Transactions.
	</para>
	<table>
		<title>Teiid Transaction Scopes</title>
		<tgroup cols="2">
			<thead>
				<row>
					<entry>Scope</entry>
					<entry>Description</entry>
				</row>
			</thead>
			<tbody>
				<row>
					<entry>Command</entry>
					<entry>
						Treats the user command as if all source commands are executed
						within the scope of the same transaction. The
						<link linkend="autowrap">AutoCommitTxn</link>
						execution property controls the behavior of command level
						transactions.
					</entry>
				</row>
				<row>
					<entry>Local</entry>
					<entry>The transaction boundary is local defined by a single
						client session.</entry>
				</row>
				<row>
					<entry>Global</entry>
					<entry>Teiid participates in a global transaction as an
						XA Resource.</entry>
				</row>
			</tbody>
		</tgroup>
	</table>
	<para>The default transaction isolation level for Teiid is READ_COMMITTED.</para> 
	<section id="autowrap">
		<title>AutoCommitTxn Execution Property</title>
		<para>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.</para>
		<table>
			<title>AutoCommitTxn Settings</title>
			<tgroup cols="2">
				<colspec colwidth="1*" />
				<colspec colwidth="3*" />
				<thead>
					<row>
						<entry>Setting</entry>
						<entry>Description</entry>
					</row>
				</thead>
				<tbody>
					<row>
						<entry>OFF</entry>
						<entry>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.</entry>
					</row>
					<row>
						<entry>ON</entry>
						<entry>Wrap each command in a transaction. This mode is the
							safest, but may introduce performance overhead.</entry>
					</row>
					<row>
						<entry>DETECT</entry>
						<entry>
						    This is the default setting. Will automatically wrap commands in a transaction, but only if
							the command seems to be transactionally unsafe.
						</entry>
					</row>
				</tbody>
			</tgroup>
		</table>
		<para>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:
			<itemizedlist>
				<listitem>
					<para>If a user command is fully pushed to the source.
					</para>
				</listitem>
				<listitem>
					<para>If the user command is a SELECT (including XML) and the transaction isolation is not REPEATABLE_READ nor SERIALIABLE.
					</para>
				</listitem>
				<listitem>
					<para>If the user command is a stored procedure and the transaction isolation is not REPEATABLE_READ nor SERIALIABLE and the 
					<link linkend="updating_model_count">updating model count</link> is zero.
					</para>
				</listitem>
			</itemizedlist>
	    </para>
		<para>The update count may be set on all procedures as part of 
			the procedure metadata in the model.</para>
	</section>
	<section id="updating_model_count">
		<title>Updating Model Count</title>
		<para>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.</para>
		<table>
			<title>Updating Model Count Settings</title>
			<tgroup cols="2">
				<colspec colwidth=".5*" />
				<colspec colwidth="2*" />
				<colspec colwidth="1.5*" />
				<thead>
					<row>
						<entry>Count</entry>
						<entry>Description</entry>
					</row>
				</thead>
				<tbody>
					<row>
						<entry>0</entry>
						<entry>No updates are performed by this command.</entry>
					</row>
					<row>
						<entry>1</entry>
						<entry>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.</entry>
					</row>
					<row>
						<entry>*</entry>
						<entry>Any number greater than 1 indicates that execution is
							transactionally unsafe and an XA transaction will be required.
						</entry>
					</row>
				</tbody>
			</tgroup>
		</table>
	</section>
	<section>
		<title>JDBC and Transactions</title>
		<section>
			<title>JDBC API Functionality</title>
			<itemizedlist>
				<para>The transaction scopes above map to these JDBC modes:
				</para>
				<listitem>
					<para>Command - Connection autoCommit property set to true.
					</para>
				</listitem>
				<listitem>
					<para>
						Local - Connection autoCommit property set to false. The
						transaction is committed by setting autoCommit to true or calling
						<function>java.sql.Connection.commit
						</function>
						. The transaction can be rolled back by a call to
						<function>java.sql.Connection.rollback
						</function>
					</para>
				</listitem>
				<listitem>
					<para>
						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,
						<classname>org.teiid.jdbc.TeiidDataSource</classname>. JEE containers or data access APIs typically control XA
						transactions on behalf of application code.
					</para>
				</listitem>
			</itemizedlist>
		</section>
		<section>
			<title>J2EE Usage Models</title>
			<itemizedlist>
				<para>J2EE provides three ways to manage transactions for beans:
				</para>
				<listitem>
					<para>Client-controlled – the client of a bean
						begins and ends a transaction explicitly.</para>
				</listitem>
				<listitem>
					<para>Bean-managed – the bean itself begins and ends a
						transaction explicitly.</para>
				</listitem>
				<listitem>
					<para>Container-managed – the app server container
						begins and ends a transaction automatically.</para>
				</listitem>
			</itemizedlist>
			<para>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.</para>
		</section>
	</section>
    <section>
        <title>Transactional Behavior with JBoss Data Source Types</title>
        <para>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</para>
        <itemizedlist>
            <listitem>
                <para>xa-datasource: Capable of participating in the distributed transaction using XA. This is recommended 
                type be used with any Teiid sources.</para>
            </listitem>
            <listitem>
                <para>local-datasource: Does not participate in XA, unless this is the <emphasis>only</emphasis> 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 <emphasis>"Could not enlist in transaction on entering meta-aware object!;"</emphasis>
                exception.</para>
            </listitem>
            <listitem>
                <para>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.</para>
            </listitem>            
        </itemizedlist>
        <para>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.</para>
        <itemizedlist>
            <listitem>
                <para>A-xa B-xa, C-xa : Can participate in all transactional scopes. No restrictions.</para>
            </listitem>
            <listitem>
                <para>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.</para>
            </listitem>
            <listitem>
                <para>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.</para>
            </listitem>
            <listitem>
                <para>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.</para>
            </listitem>
            <listitem>
                <para>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.</para>
            </listitem>
            <listitem>
                <para>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".</para>
            </listitem>            
        </itemizedlist>
        <note><para>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.</para></note>
        
        <para>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</para>
        <itemizedlist>
            <listitem>
                     <para>Use XA datasource if possible</para>
            </listitem>
            <listitem>
                     <para>Use no-tx datasource if applicable</para>
            </listitem>
            <listitem>
                     <para>Use autoCommitTxn = OFF, and let go distributed transactions, though not recommended</para>
            </listitem>
            <listitem>
                     <para>Write a compensating XA based implementation.</para>
            </listitem>            
        </itemizedlist>        
        
        <table>
            <title>Teiid Transaction Participation</title>
            <tgroup cols="4">
                <thead>
                    <row>
                        <entry>Teiid-Tx-Scope</entry>
                        <entry>XA source</entry>
                        <entry>Local Source</entry>
                        <entry>No-Tx SOurce</entry>
                    </row>
                </thead>
                <tbody>
                    <row>
                        <entry>Local</entry>
                        <entry>always</entry>
                        <entry>Only If Single Source</entry>
                        <entry>never</entry>
                    </row>
                    <row>
                        <entry>Global</entry>
                        <entry>always</entry>
                        <entry>Only If Single Source</entry>
                        <entry>never</entry>
                    </row>
                    <row>
                        <entry>Auto-commit=true, AutoCommitTxn=ON</entry>
                        <entry>always</entry>
                        <entry>Only If Single Source</entry>
                        <entry>never</entry>
                    </row>
                    <row>
                        <entry>Auto-commit=true, AutoCommitTxn=OFF</entry>
                        <entry>never</entry>
                        <entry>never</entry>
                        <entry>never</entry>
                    </row>
                    <row>
                        <entry>Auto-commit=true, AutoCommitTxn=DETECT</entry>
                        <entry>always</entry>
                        <entry>Only If Single Source</entry>
                        <entry>never</entry>
                    </row>                    
                </tbody>
            </tgroup>
        </table>        
        
    </section>
	<section>
		<title>Limitations and Workarounds</title>
		<itemizedlist>
			<listitem>
				<para>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.</para>
			</listitem>
			<!-- <listitem>
				<para>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.</para>
			</listitem>-->
		</itemizedlist>
	</section>
</chapter>