Distributed Transactions

Mimer SQL supports distributed transactions based on the XA interface as defined by the Open Group and Microsoft's Distributed Transaction Coordinator (DTC) protocol.

This means that Mimer SQL can be used in application environments that support distributed transactions.

Support for distributed transactions is enabled by a special key in the Mimer license file. The evaluation key that is included in the distribution enables distributed transactions. In order to use distributed transactions with other licenses obtained from Mimer, make sure that the distributed transaction option is included.

Terms and Abbreviations

In the field of distributed transactions, a number of terms and abbreviations are used. Here are the most common ones:

How Does it Work?

Normally, an application that uses distributed transactions is a component in an application server. The application server environment takes care of all transaction processing. The application component just accesses one or several database servers (Resource Managers, RM’s) using normal programming interfaces.

Note that the application server or Transaction Manager (TM) are not part of the Mimer database server. These components are obtained from other sources.

When the application server environment starts a new transaction, it contacts the Transaction Manager, which assigns a new transaction id (XID) to the transaction. All operations done by the application components are automatically assigned to this XID.

When the transaction is ready to commit, the TM executes the commit operations according to the two-phase commit protocol.

In phase 1, each participating RM is asked if it is ready to prepare for commit. By replying yes, the RM promises that it is able to commit the transaction and remember everything about the transaction. Although it can not commit the transaction yet, it must secure all information on disk to make sure that no information can be lost. If the RM determines that it can not commit the transaction at a later stage, it may answer no to the TM. In this case, the TM aborts its preparation phase. It contacts all RMs again and tells them to abort the transaction.

If the TM got a yes from all RMs in the first phase, phase 2 begins. Each RM is asked to commit the transactions.

Handling failures

The Transaction Manager is responsible for performing the two-phase commit protocol. It must maintain the current state of this protocol for every transaction it manages. It should also be able to deal with failures in any component, including itself.

A problematic situation occurs if the contact between the TM and the RM is lost when the protocol is between phase 1 and 2. In this case, the RM has promised to be able to commit a certain update, but it does not yet know whether it should actually do so. This is determined in phase 2. Because of this uncertainty, the RM does not know what value to return to other transactions that asks for the information that was updated. Should the old or the new value be returned? A Mimer database server will typically abort transactions that request data which was updated by a transaction that is in doubt.

The situation is automatically resolved when the contact between the TM and the RM is reestablished. Since both TM and RM save all information on disk, they may both crash between phase 1 and 2, and still be able to carry through with the two phase commit protocol.

However, if the TM somehow fails to reconnect to a Mimer database server that has prepared transactions in doubt, there is another option. The operator may perform a heuristic commit or a heuristic rollback. By doing this, the operator does the role that the TM normally does and resolves the state of the transaction that is in doubt. This can be done by using the TRANSACTIONS command, described in Mimer SQL User's Manual, TRANSACTIONS.

Note that if the TM has already instructed some RMs to (for example) commit, while the operator does a heuristic rollback on another RM, a transactional inconsistency has been introduced. This must be resolved manually. Because of this risk, heuristic operations should be used with due care.

Mimer SQL Support For Microsoft DTC on Windows

Mimer SQL supports the complete distributed transaction model according to the Microsoft Distributed Transaction Coordinator (MSDTC). This allows transactions to span several Mimer SQL databases. The database servers can be located on any type of hardware where Mimer SQL 9.1 or later is supported. To use MSDTC, the client must be on a Windows platform.

It is also possible to have transactions over heterogeneous database systems. For example, a single transaction can be performed which updates data in both a Mimer SQL database and a Microsoft SQL Server database.

The support allows code written for Microsoft Transaction Server and COM+ to be used with Mimer SQL. The transaction can be managed automatically by the COM+ server. I.e. transactional components are fully supported and transactional attributes can be ‘Supported’, ‘Required’, or ‘Requires New’. Please see your COM+ documentation for a thorough discussion of these options.

There is no distributed transaction support for Mimer SQL servers older than 9.1.

Mimer SQL Support for Java Enterprise Edition

Mimer SQL has full support for the distributed transactions in Java Enterprise Edition (Java EE) through the XA support in the Mimer JDBC Driver. With Java EE any XA compliant data source can take part in a distributed transaction. The distributed transactions are handled by the Java EE Application Server and specified by the application developer in a declarative manner. This way the application developer does not have to include any transaction handling logic in the application. Distributed transactions can be used in several different parts of the Java EE framework, for example in Enterprise Java Beans (EJB).

To be able to use Mimer SQL in a distributed transaction an XA data source must be created in the application server. How this is done differ between different application servers, consult your manual to see how it is done. When defining an XA data source for Mimer SQL, the com.mimer.jdbc.MimerXADataSource Java class should be used.

For more information about the Mimer JDBC Driver, see the Mimer JDBC Driver Guide.