¶

In this case the transactions are initiated by Virtuoso itself. This causes all remote connections of linked tables to be automatically enlisted in a distributed transaction controlled by MTS. To enable this, Virtuoso's transaction must be set to a special state with the 'SET' statement as follows:

SET MTS_2PC=1;

This statement turns distributed transaction support on. All transactions started on remote databases shall automatically be enlisted as branches of a distributed transaction managed by MS DTC. The effect of SET, in this case, lasts until the commit or rollback of the transaction. The SET statement should be at the beginning of the transaction, before any distributed operations are undertaken.

Example of money transfer from one attached table to another:

CREATE PROCEDURE TWOPC_TRANSFER_MONEY(IN person_id INTEGER)
{
  IF (MTS_STATUS('MTS') = 'disconnected') -- check connection to MS DTC
  {
    MTS_CONNECT(0); -- connect to MS DTC
  }
  SET MTS_2PC=1; --  transaction of this procedure is now in distributed
  MTS_SET_TIMEOUT (1000); -- 1sec timeout on distributed transactions
  UPDATE linked_account1 SET amount=amount+100 WHERE id=person_id;
  UPDATE linked_account2 SET amount=amount-100 WHERE id=person_id;
  commit work;
}

This money transfer is under 2PC control of MTS. If one of the two participating databases crashes (or rolls back due to deadlock or timeout), Virtuoso will roll back the whole distributed transaction.

Note that if a transaction modifies the local Virtuoso database, and not more than one remote database, 2 phase commit is not needed for guaranteeing integrity.

Deadlocks are detected for local transactions using a wait graph. Deadlocks are detected for distributed transactions based on timeouts. Use mts_set_timeout() for explicitly setting a timeout. See MS DTC for a definition of timeouts.