# 9. SQL Procedure Language Guide

Chapter Contents | Prev | Next
Keyword Search:
www.openlinksw.com
docs.openlinksw.com
Book Home
Contents
Preface
SQL Procedure Language Guide
General Principles
Scope of Declarations
Data Types
Handling Result Sets
Result Sets and Array Parameters
Exception Semantics
Virtuoso/PL Syntax
CREATE ASSEMBLY Syntax - External Libraries
CREATE PROCEDURE Syntax - External hosted procedures
Asynchronous Execution and Multithreading in Virtuoso/PL
Performance Tips
Procedures and Transactions
Distributed Transaction & Two Phase Commit
Initiating Distributed Transactions Responding to Distributed Transactions 2PC Log & Recovery Error Codes
Triggers
Character Escaping
Virtuoso/PL Scrollable Cursors
Virtuoso PL Modules
Handling Conditions In Virtuoso/PL Procedures
Procedure Language Debugger
Row Level Security

## 9.13. 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.

### 9.13.1. Initiating Distributed Transactions

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.

### 9.13.2. Responding to Distributed Transactions

In this situation a distrtibuted transaction is initiated by an ODBC client of Virtuoso. The application enlists one or more Virtuoso hdbcs in an OLE/DB distributed transaction, and then works with that hdbcs and commits or rolls back the distributed transaction.

c++ example:

    /* begin of example */
      ITransaction* transaction;
      ITransactionDispenser* disp;
      HRESULT hr =
          DtcGetTransactionManager (0, 0, &IID_ITransactionDispenser, 0, 0, 0,
          &disp);
      hr = disp->BeginTransaction (0, ISOLATIONLEVEL_ISOLATED,
          0, 0, &transaction); /* initialize transaction */
      SQLSetConnectOption (hdbc1, SQL_COPT_SS_ENLIST_IN_DTC,
          (DWORD) transaction); /* enlist 1st hdbc in transaction */
      SQLSetConnectOption (hdbc2, SQL_COPT_SS_ENLIST_IN_DTC,
          (DWORD) transaction); /* enlist 2nd hdbc in transaction */

      ..... /* some work with ODBC connections */

      transaction->Commit (0, 0, 0); /* commit the transaction */
    /* end of example */

If a Virtuoso connection is enlisted into a distributed transaction managed by MS DTC, and a Virtuoso statement executed in this transaction accesses attached tables, or otherwise uses other databases, then Virtuoso automatically enlists these remote databases into the original distributed transaction. If the remote database does not support MS DTC, then it signals the special error (see error list below).

For more information, see Microsoft's documentation for MTS and OLE DB.

If you want Virtuoso to start connected to MTS, add the following string in the [VDB] section of virtuoso.ini file:

    UseMTS = 1

See also:

mts_connect, mts_status, mts_set_timeout, mts_get_timeout.

### 9.13.3. 2PC Log & Recovery

If one branch of a distributed transaction crashes during the second phase of a commit, the recovery cycle will be performed during the next start up of the server. Information about a distributed transaction is stored in the transaction log file.

When Virtuoso connects to MS DTC, it creates a guid.bin file in the working directory. This file contains a unique ID of the server and is require for the recovery cycle.

### 9.13.4. Error Codes

Table: 9.13.4.1. 2PC & MS DTC error list
Code | Description | Possible courses
--- | --- | ---
MX000 | connection to MS DTC is failed | MS DTC service is not started (in case of NT4.0 MTS is not started).
37100 | MTS support is not enabled | Current or involved in distributed transaction database has not connected to MS DTC.

Previous Procedures and Transactions | Chapter Contents | Next Triggers