MetaMatrix Explained

Transactions Support

This describes generic and MetaMatrix-specific support for transactions, including distributed transactions. The focus here is really on app server integration, although much of the information pertains to other non-J2EE apps as well.

Background

Levels of Support

1. None – don’t support transactions at all
2. Local transactions – support transactions against a single source
3. XA transactions – support multi-source two-phase commit transactions

The MetaMatrix Enterprise Server supports the XA transaction level. That allows the MetaMatrix JDBC Driver to act as an XAResource to an external transaction manager while managing our own (single- or multi-source) transaction within MetaMatrix.

Because a 'local' transaction against MetaMatrix may actually involve multiple sources internally, MetaMatrix uses an XA transaction manager to handle all transactions. The transaction manager is responsible for optimizing a single-source transaction execution to one phase commit.

JDBC and Transactions

JDBC API Functionality

It is useful to look at the JDBC API for the three levels of transactional support listed above to understand how things are and can be implemented. These levels map to these JDBC modes:

• None - auto-commit=true, implicit single-command transactions
• Local transactions – auto-commit=false, explicitly demarcated multi-command transactions
• XA transactions – auto-commit ignored, JTA UserTransaction interface used in conjunction with an XAConnection that exposes an XAResource

The JDBC Connection interface supports two methods:

• getAutoCommit() : boolean
• setAutoCommit(boolean autoCommit) : void

These methods are used to alternate between none (really implicit) transactions and local (or explicit) transactions as described above. When using implicit transactions, no transaction demarcations are necessary; instead every command execution automatically occurs in it’s own transaction (or none depending on the data source). When using explicit transactions, the user ends a transaction by calling commit() or rollback() and initiates a transaction by either calling setAutoCommit(false), or as a side effect of calling commit() or rollback().

When turning off auto commit on a JDBC driver, a JDBC client is assuming control of transaction boundaries and is responsible for transaction termination (commit or rollback). When auto commit is turned off in the MetaMatrix JDBC driver, the MetaMatrix server must start a distributed transaction because it has no way of knowing the data sources that will be involved or the commands that will be processed. The server only knows that the JDBC client wants to group the next N commands in a transaction.

In the case of XA, the programming model is different and depends to some extent on an environment that can provide UserTransaction objects. In the XA case, the thread that created the UserTransaction object can perform multiple command executions via JDBC against one or more data sources and the commands will all be applied when the user commits the UserTransaction.

Independent of how the transactions are demarcated, a particular JDBC database connection has a 'transaction isolation' level that states the level of isolation between and within a transaction against the database by that connection. In general, the different levels (such as READ_COMMITTED, SERIALIZABLE, etc) trade off isolation and performance depending on the use case. Generally, not all levels are available on all databases and the default level is not always the same.

J2EE Usage Models

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.

MetaMatrix Transaction Processing

MetaMatrix differs from other JDBC drivers because of its potential to front more than one data source. Because of this potential, statements that result in modification of more than one data source must be in the context of a distributed (XA) transaction.

When turning off auto commit on a JDBC driver, a JDBC client is assuming control of transaction boundaries and is responsible for transaction termination (commit or rollback). When auto commit is turned off in the MetaMatrix JDBC driver, the MetaMatrix Server must start a distributed transaction because it has no way of knowing the data sources that will be involved or the commands that will be processed. The server only knows that the JDBC client wants to group the next N commands in a transaction.

When autocommit=true, MetaMatrix supports four automatic transaction wrapping modes:

• OFF – Do not wrap commands in a transaction. If it is determined that a transaction is needed, an exception will be thrown.
• ON – Always wrap commands in a transaction.
• OPTIMISTIC – Assume that the user will explicitly demarcate transactions if they are needed (updates on multiple sources will occur). Transactions are never created (same as OFF) but a check is done to determine whether a transaction is necessary and an exception is thrown. This is the default mode.
• PESSIMISTIC – Assume that the user will not know when to explicitly demarcate transactions and make an extra server call to start them only when they are required. This mode is like using ON but will generally be faster because it will not start transactions for commands that do not require one.

MetaMatrix and Transaction Isolation

The MetaMatrix JDBC Driver does not (and probably cannot due to the disparate nature of the sources that can be integrated) attempt to satisfy the transaction isolation level set by a caller. Instead, MetaMatrix JDBC Connectors use the default transaction isolation level of the driver/database by default and allow an administrator to configure the transaction isolation level.

MetaMatrix Query Behavior

MetaMatrix Query does not currently embed an XA transaction manager, and therefore cannot support XA transactions. Because MetaMatrix Query implements local transactions as XA transactions, this also means that it cannot support explicit local transactions either. MetaMatrix Query can support single update statements with no explicit transaction demarcation as these are implicitly committed for each execution.

Because MetaMatrix Query does not support transactions, it is only expected to be used with autocommit=true (the default). If auto-commit is set to false, this will fail with a SQLException indicating this is not supported.