Distributed Transactions

SQL for Reality enables ODBC compliant applications to read and write data on a Reality database. If the ODBC compliant application accessing the database via the SQL/ODBC interface is running in a Microsoft COM+ environment, it may be using distributed transactions. Reality's support for COM+ transactions uses both ODBC and XA interfaces, see the diagram below.

A distributed transaction, like a local transaction, is a set of related updates logically grouped by transaction boundary commands. However, the related updates can be to different databases on different systems on the network. If one of the updates is applied, all of the other systems must be updated to maintain consistency. In this scenario, updates applied to each database are classified as belonging to a transaction branch, each branch being uniquely identified by the Transaction Manager (TM). The responsibility for managing distributed transactions rests not with the local Reality TM, but with a remote TM: Microsoft's Distributed Transaction Co-ordinator (MDTC).

Where distributed transactions are being used, the Reality database must be configured for Transaction Handling and for Transaction Logging. Refer to Configuring Transaction Handling and Configuring Transaction Logging.

A local transaction is committed when the final update in that logical group is applied to the database. Up to that point, the transaction can be rolled back if remaining updates fail to complete because of process or system failure, or because the transaction is deliberately aborted. The transaction is also rolled back if remaining updates fail to complete before the specified timeout period.

A distributed transaction has a two phase commit. In the first phase, each participating database is requested to prepare their transactions for commitment; then, providing they all return a successful outcome to phase-one, they enter phase-two whereby they are requested to commit their transactions. If one or more databases fail to prepare their transaction branches, the TM requests all databases to roll back their updates, effectively aborting the transaction. Once a transaction branch is prepared, the database effectively guarantees that it is capable of committing the transaction, even after a database crash. If there is a crash, the database may be requested to commit the transaction twice - once during normal operation and once after system recovery.

Until a transaction has been prepared, it can be rolled back, for example, if it exceeds the local transaction timeout value. The Reality SQL server informs the MDTC of the rollback and the MDTC rolls back all other participating transaction branch updates. Once a transaction is prepared, it cannot be rolled back by the local TM. In the case of a failure, the MDTC runs a recovery process, examining each prepared transaction branch in turn and requesting either a commit or a rollback (see Process Recovery).