11.15.Distributed Transaction & Two Phase Commit

2PC is an acronym for 2 Phase Commit. This is a protocol by which data being committed to a database is committed in two phases. In the first phase, the transaction processor checks that all parts of the transaction can be committed. In the second phase, all parts of the transaction are committed. If any part of the transaction indicates in the first phase that it cannot be committed, the second phase does not occur. ODBC does not support two-phase commits.

Transactions in SQL databases are expected to have "ACID" features: Atomicity, Consistency, Isolation, Durability. A two-phase commit (2PC) protocol is needed for guaranteeing ACID properties of transactions which involve changing data in more than one database. This can be the case in a transaction involving tables attached from other databases or explicit access to remote databases with rexecute().

The 2PC protocol needs to have a third party Distributed Transaction Coordinator (DTC). Virtuoso supports Microsoft Transaction Server (or MS DTC).

There are two ways of using MTS-driven distributed transactions in Virtuoso. Virtuoso either initiates the transaction, or it responds to a transaction.