Introduction
Mimer SQL supports distributed transactions based on the XA interface as defined by Open Group and Microsoft’s Distributed Transaction Coordinator (DTC) protocol.
This means that Mimer SQL can participate with other XA and DTC enabled systems to process transactions over different platforms and between databases from different vendors.
Description
The use of Distributed Transactions allows access to Mimer SQL database data from a distributed system, such as an application server. Changes to several different Mimer SQL databases can be grouped together in distributed transactions. This allows data changes to be made in either none, or all of the systems accessed by a transaction. In addition, changes in Mimer SQL can be coordinated with changes made in database management systems from other vendors.
How are distributed transactions handled?
Distributed transactions are handled through the 2-phase commit protocol. A transaction coordinator controls the steps to successfully commit the transaction. The following steps are performed:
- The application starts the transaction.
- Operations are performed against one or several databases. The transaction coordinator is informed of each database accessed. The transaction is tagged with the appropriate transaction ID as decided by the transaction coordinator.
- The application commits the transaction. The transaction coordinator now applies the 2-phase commit protocol.
- Each database prepare its part of the transaction. This is done through the xa_prepare method. When the transaction is prepared it is validated and also written to disk. The databases must ensure that it is possible to commit the transaction after this point. This means that later transactions that conflict with operations in the prepared transaction must be aborted.
- If all database servers succeed the transaction coordinator writes a commit decision to disk. This constitutes the actual decision to commit the transaction. If operations fail before this point the transaction will be rolled back. If this point is passed, all the different parts of the transaction will be performed. This is possible even in the event of a system failure as each database server and the transaction coordinator have saved the transaction to disk.
- Next the transaction coordinator informs all database servers of its decision to commit. This is done through the xa_commit method.
- The application is now be informed of the commit.
Recovery is handled as mandated by XA or DTC. Recovery is usually initiated by the transaction coordinator, but in some cases the individual database servers may also initiate the recovery process.
XA also provides support for heuristic transaction rollback and commit. This is typically only used when nodes are partitioned over longer periods of time thus preventing normal recovery to take place after a failure.
An example of an XA based environment is the integration of Mimer SQL and BEA Tuxedo, where the programmer can write Tuxedo services using Mimer Embedded SQL to access the database.
Of course, J2EE certified application servers can be used with Mimer SQL using the JDBC/Java development environment. An example here is the use of Mimer SQL with IBMs WebSphere Application Server.
Mimer SQL is also suitable for use in Microsoft’s COM+ and MSDTC architectures for application development.
Techniques
Distributed Transactions in Mimer SQL are available from various database APIs such as JDBC, ODBC and Embedded SQL.
Benefits
By using the Distributed Transaction functionality in Mimer SQL, it is possible to build high performance, scalable, and robust applications with transactions over several Mimer SQL databases or transactions that span other database brands. This allows changes to be made in either all or none of the systems, thereby ensuring the consistency of the data across database servers.
