<?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" [
<!ENTITY % CustomDTD SYSTEM "../../../../../../docbook/custom.dtd">
%CustomDTD;
]>
<chapter id="jdbc_transactions">
    <title>Transactions with JDBC</title>
    <para>The Teiid JDBC API supports three types of transactions from a 
    client perspective – global, local, and request level.  All are implemented 
    by the Teiid Server as XA transactions.  
    See the <ulink url="http://java.sun.com/javaee/technologies/jta/index.jsp">JTA specification</ulink> for more on XA Transactions.</para>
    
    <section>
        <title>Local Transactions</title>
        <para>The Connection class uses the "autoCommit" flag to explicitly control local transactions.  By default, autoCommit 
        is set to "true", which indicates request level or implicit transaction control.  example of 
        how to use local transactions by setting the autoCommit flag to false.</para>
        <example>
        	<title>Local transaction control using autoCommit</title>
	     <programlisting><![CDATA[// Set auto commit to false and start a transaction
connection.setAutoCommit(false);

try {
    // Execute multiple updates
    Statement statement = connection.createStatement();
    statement.executeUpdate(“INSERT INTO Accounts (ID, Name) VALUES (10, ‘Mike’)”);
    statement.executeUpdate(“INSERT INTO Accounts (ID, Name) VALUES (15, ‘John’)”);
    statement.close();

    // Commit the transaction
    connection.commit();

} catch(SQLException e) {
    // If an error occurs, rollback the transaction
    connection.rollback();
}]]></programlisting>         
               </example>
       <para>This example demonstrates several things:</para>
       <orderedlist numeration="arabic">
        <listitem> <para>Setting autoCommit flag to false.  This will start a transaction bound to the connection.</para> </listitem>
        <listitem> <para>Executing multiple updates within the context of the transaction.</para> </listitem>
        <listitem> <para>When the statements are complete, the transaction is committed by calling commit().</para> </listitem>
        <listitem> <para>If an error occurs, the transaction is rolled back using the rollback() method.</para> </listitem>               
       </orderedlist>
       <para>Any of the following operations will end a local transaction:</para>
       <orderedlist>
        <listitem> <para>Connection.setAutoCommit(true) – if previously set to false</para> </listitem>
        <listitem> <para>Connection.commit()</para> </listitem>
        <listitem> <para>Connection.rollback()</para> </listitem>
        <listitem> <para>A transaction will be rolled back automatically if it times out.</para> </listitem>
       </orderedlist>
       <section>
            <title>Turning Off Local Transactions</title>
            <para>In some cases, tools or frameworks above Teiid will call setAutoCommit(false), commit() and rollback() 
            even when all access is read-only and no transactions are necessary.  
            In the scope of a local transaction Teiid will start and attempt to commit an XA transaction, 
            possibly complicating configuration or causing performance degradation.</para>
            
            <para>In these cases, you can override the default JDBC behavior to indicate that these methods 
            should perform no action regardless of the commands being executed. To turn off the use of local transactions, 
            add this property to the JDBC connection URL</para>

           <programlisting><![CDATA[disableLocalTxn=true]]></programlisting>
                       
            <warning><para>Turning off local transactions can be dangerous and can result in inconsistent 
            results (if reading data) or inconsistent data in data stores (if writing data).  
            For safety, this mode should be used only if you are certain that 
            the calling application does not need local transactions.</para></warning>
       </section>
    </section>
    
    <section>
        <title>Request Level Transactions</title>
        <para>Request level transactions are used when the request is not in the 
        scope of a global or local transaction, which implies "autoCommit" is "true".  In a request 
        level transaction, your application does not need to explicitly call commit or rollback, 
        rather every command is assumed to be its own transaction that will automatically 
        be committed or rolled back by the server.</para>
        
        <para>The Teiid Server can perform updates through virtual tables.  These updates might 
        result in an update against multiple physical systems, even though the application issues 
        the update command against a single virtual table.  Often, a user might not know whether 
        the queried tables actually update multiple sources and require a transaction.</para>
        
        <para>For that reason, the Teiid Server allows your application to automatically wrap commands 
        in transactions when necessary.  Because this wrapping incurs a performance penalty for your queries, 
        you can choose from a number of available wrapping modes to suit your environment.  You need to choose 
        between the highest degree of integrity and performance your application needs.  
        For example, if your data sources are not transaction-compliant, you might turn the transaction 
        wrapping off (completely) to maximize performance.
        </para>
        
        <orderedlist>
        	<para>You can set your transaction wrapping to one of the following modes:</para>
            <listitem> <para><emphasis>ON</emphasis>: This mode always wraps every command in a transaction 
            without checking whether it is required.  This is the safest mode.</para></listitem>
            <listitem> <para><emphasis>OFF</emphasis>: This mode never automatically wraps a command in a 
            transaction or check whether it needs to wrap a command.  This mode can be dangerous as it will 
            allow multiple source updates outside of a transaction without an error.  This mode has best 
            performance for applications that do not use updates or transactions.</para></listitem>
            <listitem> <para> <emphasis>DETECT</emphasis>: This mode assumes that the user does not know to execute 
            multiple source updates in a transaction.  The Teiid Server checks every command to see whether it 
            is a multiple source update and wraps it in a transaction. If it is single source then uses
            the source level command transaction.</para></listitem>
        </orderedlist>
        
        <para>You can set the transaction mode as a property when you establish the Connection or on a 
        per-query basis using the execution properties.  For more information on execution 
        properties, see the section <link linkend="execution_properties">“Execution Properties”</link></para>
        
        <section>
        <title>Multiple Insert Batches</title>
        <para>When issuing an INSERT with a query expression (or the deprecated SELECT INTO), multiple insert batches handled by separate source INSERTS may be processed by the Teiid server.
         Care should be taken to ensure that targeted sources support XA or that compensating actions are taken in the event of a failure.</para>    
        </section>
        
    </section>
    
    <section>
        <title>Using Global Transactions</title>
        <para>Global or client XA transactions allow the Teiid JDBC API to participate in transactions that 
        are beyond the scope of a single client resource.  For this use the Teiid DataSource Class for establishing connections.</para>
        
        <para>When the DataSource is used in the context of a UserTransaction in an application server, 
        such as JBoss, WebSphere, or Weblogic, the resulting connection will already be 
        associated with the current XA transaction.  No additional client JDBC code is 
        necessary to interact with the XA transaction.</para>
        <example><title>>Manual Usage of XA transactions</title>
        <programlisting><![CDATA[XAConnection xaConn = null;
XAResource xaRes = null;
Connection conn = null;
Statement stmt = null;

try {
  xaConn = <XADataSource instance>.getXAConnection();
  xaRes = xaConn.getXAResource();
  Xid xid = <new Xid instance>;
  conn = xaConn.getConnection();
  stmt = conn.createStatement();

  xaRes.start(xid, XAResource.TMNOFLAGS);
  stmt.executeUpdate("insert into …");
  <other statements on this connection or other resources enlisted in this transaction>
  xaRes.end(xid, XAResource.TMSUCCESS);
	
  if (xaRes.prepare(xid) == XAResource.XA_OK) {
    xaRes.commit(xid, false);
  }
}
catch (XAException e) {
  xaRes.rollback(xid);
} 
finally {
  <clean up>
}]]></programlisting>  
        </example>
        
        <para>With the use of global transactions multiple Teiid XAConnections may participate in the 
        same transaction.  It is important to note that the Teiid JDBC XAResource "isSameRM" method only returns 
        "true", if connections are made to the same server instance in a cluster.  If the Teiid connections are to different 
        server instances then transactional behavior may not be the same as if they were to the same cluster member.  
        For example, if the client transaction manager uses the same XID for each connection, duplicate XID exceptions 
        may arise from the same physical source accessed through different cluster members.  
        If the client transaction manager uses a different branch identifier for each connection, issues may arise 
        with sources that lock or isolate changes based upon branch identifiers.</para>      
    </section>
    
    <section>
        <title>Restrictions</title>
        <section>
            <title>Application Restrictions</title>
            <para>The use of global, local, and request level transactions are all mutually exclusive.  
            Request level transactions only apply when not in a global or local transaction.  Any attempt to 
            mix global and local transactions concurrently will result in an exception.</para>
        </section>
        
        <section>
            <title>Enterprise Information System Support</title>
            <para>The underlying resource adaptors that represent the EIS system and the EIS system itself must support XA transactions
             if they want to participate in distributed XA transaction thru Teiid. If
            source system do not support the XA, then it can not particilate in the distributed transaction. However, the source is still
            eligible to participate in data integration with out the XA support</para> 
            
            <para>The participation in the XA 
            transaction is automatically determined based on the resource adaptors XA capability. It is user's repsonsiblity to make sure
            that they configure a XA resource when they require them to participate in distributed transaction.
            </para>
        </section>
    </section>
</chapter>