<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head profile="http://internetalchemy.org/2003/02/profile">
<link rel="foaf" type="application/rdf+xml" title="FOAF" href="http://www.openlinksw.com/dataspace/uda/about.rdf" />
<link rel="schema.dc" href="http://purl.org/dc/elements/1.1/" />
<meta name="dc.title" content="9. SQL Procedure Language Guide" />
<meta name="dc.subject" content="9. SQL Procedure Language Guide" />
<meta name="dc.creator" content="OpenLink Software Documentation Team ; " />
<meta name="dc.copyright" content="OpenLink Software, 1999 - 2009" />
<link rel="top" href="index.html" title="OpenLink Virtuoso Universal Server: Documentation" />
<link rel="search" href="/doc/adv_search.vspx" title="Search OpenLink Virtuoso Universal Server: Documentation" />
<link rel="parent" href="sqlprocedures.html" title="Chapter Contents" />
<link rel="prev" href="PROCEDURES_TRANSACTIONS.html" title="Procedures and Transactions" />
<link rel="next" href="TRIGGERS.html" title="Triggers" />
<link rel="shortcut icon" href="../images/misc/favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="doc.css" />
<link rel="stylesheet" type="text/css" href="/doc/translation.css" />
<title>9. SQL Procedure Language Guide</title>
<meta http-equiv="Content-Type" content="text/xhtml; charset=UTF-8" />
<meta name="author" content="OpenLink Software Documentation Team ; " />
<meta name="copyright" content="OpenLink Software, 1999 - 2009" />
<meta name="keywords" content="" />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="twopcimplementation" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>9. SQL Procedure Language Guide</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="sqlprocedures.html">Chapter Contents</a> | <a class="link" href="PROCEDURES_TRANSACTIONS.html" title="Procedures and Transactions">Prev</a> | <a class="link" href="TRIGGERS.html" title="Triggers">Next</a>
</div>
</div>
<div id="currenttoc">
<form method="post" action="/doc/adv_search.vspx">
<div class="search">Keyword Search: <br />
<input type="text" name="q" /> <input type="submit" name="go" value="Go" />
</div>
</form>
<div>
<a href="http://www.openlinksw.com/">www.openlinksw.com</a>
</div>
<div>
<a href="http://docs.openlinksw.com/">docs.openlinksw.com</a>
</div>
<br />
<div>
<a href="index.html">Book Home</a>
</div>
<br />
<div>
<a href="contents.html">Contents</a>
</div>
<div>
<a href="preface.html">Preface</a>
</div>
<br />
<div class="selected">
<a href="sqlprocedures.html">SQL Procedure Language Guide</a>
</div>
<br />
<div>
<a href="GENERALPRINCIPLES.html">General Principles</a>
</div>
<div>
<a href="SCOPEOFDECLARATION.html">Scope of Declarations</a>
</div>
<div>
<a href="sqlplDATATYPES.html">Data Types</a>
</div>
<div>
<a href="RESULTSETS.html">Handling Result Sets</a>
</div>
<div>
<a href="ARRAYS.html">Result Sets and Array Parameters</a>
</div>
<div>
<a href="EXCEPTIONS.html">Exception Semantics</a>
</div>
<div>
<a href="PLREF.html">Virtuoso/PL Syntax</a>
</div>
<div>
<a href="EXECSQLPROCSELECT.html">Execute Stored Procedures via SELECT statement</a>
</div>
<div>
<a href="EXECSQLPROCFORK.html">Execute Stored Procedures In Background</a>
</div>
<div>
<a href="createassembly.html">CREATE ASSEMBLY Syntax - External Libraries</a>
</div>
<div>
<a href="createexthostproc.html">CREATE PROCEDURE Syntax - External hosted procedures</a>
</div>
<div>
<a href="ASYNCEXECMULTITHREAD.html">Asynchronous Execution and Multithreading in Virtuoso/PL</a>
</div>
<div>
<a href="PERFTIPS.html">Performance Tips</a>
</div>
<div>
<a href="PROCEDURES_TRANSACTIONS.html">Procedures and Transactions</a>
</div>
<div class="selected">
<a href="twopcimplementation.html">Distributed Transaction & Two Phase Commit</a>
<div>
<a href="#twopc_send" title="Initiating Distributed Transactions">Initiating Distributed Transactions</a>
<a href="#twopc_resp" title="Responding to Distributed Transactions">Responding to Distributed Transactions</a>
<a href="#twopc_recovery" title="2PC Log & Recovery">2PC Log & Recovery</a>
<a href="#twopc_errors" title="Error Codes">Error Codes</a>
</div>
</div>
<div>
<a href="TRIGGERS.html">Triggers</a>
</div>
<div>
<a href="charescaping.html">Character Escaping</a>
</div>
<div>
<a href="PLSCROLLCRSRS.html">Virtuoso/PL Scrollable Cursors</a>
</div>
<div>
<a href="plmodules.html">Virtuoso PL Modules</a>
</div>
<div>
<a href="handlingplcondit.html">Handling Conditions In Virtuoso/PL Procedures</a>
</div>
<div>
<a href="pldebugger.html">Procedure Language Debugger</a>
</div>
<div>
<a href="rowlevelsecurity.html">Row Level Security</a>
</div>
<br />
</div>
<div id="text">
<a name="twopcimplementation" />
<h2>9.15. Distributed Transaction & Two Phase Commit</h2>
<p>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.</p>
<p>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().</p>
<p>The 2PC protocol needs to have a third party Distributed Transaction
Coordinator (DTC). Virtuoso supports Microsoft Transaction Server
(or MS DTC).</p>
<p>There are two ways of using MTS-driven distributed transactions in
Virtuoso. Virtuoso either initiates the transaction, or it responds to a transaction.</p>
<a name="twopc_send" />
<h3>9.15.1. Initiating Distributed Transactions</h3>
<p>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:</p>
<div>
<pre class="programlisting">SET MTS_2PC=1;</pre>
</div>
<p>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.
</p>
<p>Example of money transfer from one attached table to another:</p>
<div>
<pre class="programlisting">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;
}</pre>
</div>
<p>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.</p>
<p>
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.</p>
<p>
Deadlocks are detected for local transactions using a wait graph. Deadlocks
are detected for distributed transactions based on timeouts. Use
<a href="fn_mts_set_timeout.html">mts_set_timeout()</a> for explicitly
setting a timeout. See MS DTC for a definition of timeouts.</p>
<br />
<a name="twopc_resp" />
<h3>9.15.2. Responding to Distributed Transactions</h3>
<p>In this situation a distributed 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.</p>
<p>c++ example:</p>
<div>
<pre class="programlisting">/* 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 */</pre>
</div>
<p>
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).
</p>
<p>For more information, see Microsoft's documentation for MTS and OLE DB.
</p>
<p>If you want Virtuoso to start connected to MTS, add the following
string in the [VDB] section of virtuoso.ini file:</p>
<div>
<pre class="programlisting">UseMTS = 1</pre>
</div>
<div class="tip">
<div class="tiptitle">See also:</div>
<p>
<a href="fn_mts_connect.html">mts_connect</a>,
<a href="fn_mts_status.html">mts_status</a>,
<a href="fn_mts_set_timeout.html">mts_set_timeout</a>,
<a href="fn_mts_get_timeout.html">mts_get_timeout</a>. </p>
</div>
<br />
<a name="twopc_recovery" />
<h3>9.15.3. 2PC Log & Recovery</h3>
<p>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.</p>
<p>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.</p>
<br />
<a name="twopc_errors" />
<h3>9.15.4. Error Codes</h3>
<table class="data">
<caption>Table: 9.15.4.1. 2PC & MS DTC error list</caption>
<tr>
<th class="data">Code</th>
<th class="data">Description</th>
<th class="data">Possible courses</th>
</tr>
<tr>
<td class="data">MX000</td>
<td class="data">connection to MS DTC is failed</td>
<td class="data">MS DTC service is not started (in case of NT4.0 MTS is not started).</td>
</tr>
<tr>
<td class="data">37100</td>
<td class="data">MTS support is not enabled</td>
<td class="data">Current or involved in distributed transaction database has not connected to MS DTC. </td>
</tr>
</table>
<br />
<br />
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="PROCEDURES_TRANSACTIONS.html" title="Procedures and Transactions">Previous</a>
<br />Procedures and Transactions</td>
<td align="center" width="34%">
<a href="sqlprocedures.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="TRIGGERS.html" title="Triggers">Next</a>
<br />Triggers</td>
</tr>
</table>
</div>
<div id="footer">
<div>Copyright© 1999 - 2009 OpenLink Software All rights reserved.</div>
<div id="validation">
<a href="http://validator.w3.org/check/referer">
<img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0!" height="31" width="88" />
</a>
<a href="http://jigsaw.w3.org/css-validator/">
<img src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" height="31" width="88" />
</a>
</div>
</div>
</body>
</html>
distrib
>
Fedora
>
14
>
x86_64
>
media
>
updates
>
by-pkgid
>
71d40963b505df4524269198e237b3e3
>
files
>
901
