JBoss.orgCommunity Documentation
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 JTA specification for more on XA Transactions.
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.
Example 4.1. Local transaction control using autoCommit
// 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();
}This example demonstrates several things:
Setting autoCommit flag to false. This will start a transaction bound to the connection.
Executing multiple updates within the context of the transaction.
When the statements are complete, the transaction is committed by calling commit().
If an error occurs, the transaction is rolled back using the rollback() method.
Any of the following operations will end a local transaction:
Connection.setAutoCommit(true) – if previously set to false
Connection.commit()
Connection.rollback()
A transaction will be rolled back automatically if it times out.
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.
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
disableLocalTxn=true
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.
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.
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.
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.
You can set your transaction wrapping to one of the following modes:
ON: This mode always wraps every command in a transaction without checking whether it is required. This is the safest mode.
OFF: 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.
DETECT: 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.
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 “Execution Properties”
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.
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.
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.
Example 4.2. >Manual Usage of XA transactions
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>
}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.
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.
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
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.