How It Works: SQL Server DTC (MSDTC and XA Transactions)

Articolo
01/28/2018

I have found the term DTC is used in many ways so for the purposes of the discussion here are a few terms. When talking DTC, I am talking about the feature set, MSDTC as the OLE-TX implementation and XA for the X/Open XA implementation.

DTC	Distributed Transaction Coordination – Generic term
MSDTC	Microsoft specific implementation for DTC using (OLE-TX)
XA	X/Open Standard for TM to RM communications and behavior
TM	Transaction Monitor/Manager – Coordinator
RM	Resource Manager – Database, File System, with ACID capabilities
OLE-TX	Microsoft MSDTC protocol.
UUID	Transaction GUID used by MSDTC, often referred to as a UOW (Unit-of-Work)
XID	Transaction identifier used by XA with proper OID data section.
OID	Standardized object/organizational identifier. For DTC it contains connection information such as host and UOW.

Specification Links

OLE-TX Specification | TDS Specification | XA Specification | MSDTC Interfaces | JTA API

Let’s begin the discussion with a pure, MSDTC based transaction scenario (OLE-TX protocol.)

The client application establishes a connection with an MSDTC service by invoking olehlp!DtcGetTransactionManager and requesting an interface. This is commonly the ITransactionDispenser.

Note: SQL Server can be a client using a linked server, allowing SQL Server to become an APP and/or an RM)

There are 3 connection variations used by the DtcGetTransactionManager(Host, …).

Local Host Name	The olehlp!DtcGetTransactionManager looks in the local registry to determine the connection and proxy information.
Remote Host Name	The olehlp!DtcGetTransactionManager contacts the host, performing a remote registry access to determine the connection and proxy information.
‘null’ Host Name	The olehlp!DtcGetTransactionManager consults the default entry in the local registry to determine the connection and proxy information.

HKLM\Software\Classes\ CID and HKLM\Software\Classes\SVCID

Once the registry information is obtained the specified proxy DLL is loaded (this is msdtcprx.dll for MSDTC.) The proxy DLL provides the interfaces and RPC communication capabilities with the MSDTC service. In the diagram this is MSDTC (A.)

Using the acquired dispenser, the APP invokes the BeginTransaction method. The MSDTC proxy communicates with the MSDTC (A) service to perform a begin transaction and the method returns an ITransaction pointer.

The application then uses the SQL Server client (SQL Server ODBC for this example) to establish a TDS based connection to the target SQL Server (local or remote.)

Once the connection to SQL Server is established the APP executes the odbc!SQLSetConnAttr function passing the SQL_COPT_SS_ENLIST_IN_DTC option and the ITransaction pointer.

The SQL Server ODBC driver then performs a series of operations, on behalf of the application enlisting the connection in the MSDTC transaction.

The SQL Server ODBC driver requests a TM_GET_ADDRESS TDS response. The TM_GET_ADDRESS is a request of the SQL Server to return information about the MSDTC (B) service SQL Server is connected to. SQL Server uses the ITransactionImportWhereabouts to obtain a binary array object representing the whereabouts of MSTDC (B) and returns the array to the SQL Server ODBC driver, in the TDS response.

The SQL Server ODBC driver obtains an ITransactionExport interface from MSDTC (A) and executes the Export method to obtain a transaction cookie. The transaction cookie is a binary array object representing the transaction and whereabouts information.

Once the transaction cookie is obtained the SQL Server ODBC driver sends the TM_PROPAGATE request to the SQL Server, including the cookie obtained during export.

SQL Server uses the cookie received and the ITransactionImport interface, talking with MSDTC (B), to import the transaction. The import process establishes communication between MSTDC (B) and MSDTC (A), creating the proper superior to subordinate relationship, adding DTC voting endpoints and returning an ITransaction to SQL Server.

The client and SQL Server can not perform actions, under the same UOW and commit or abort with DTC capabilities.

Before continuing further let’s zoom in on SQL Server as a RM (Resource Manager.) SQL Server can be an APP (linked server to other DTC enabled resources) or an RM.

SQL Server uses background tasks to support MSDTC RM requests. SQL Server establishes a connection with the MSDTC (B) service using olehlp!DtcGetTransactionManager, asking for the IResourceManager interface.

When SQL Server is asked to engage in a DTC transaction, SQL Server calls the Enlist method. Enlist occurs the first time SQL Server is asked to engage in the transaction. Subsequent requests propagate into the transaction.

Note: A DTC transaction can be active without a SQL Server session allowing the client to join (SQL_COPT_SS_ENLIST_IN_DTC, ITransaction) and leave the transaction (SQL_COPT_SS_ENLIST_IN_DTC, NULL) one or more times.

The IResourceManager::Enlist establishes the proper callback/sink for the MSDTC TM. When the transaction is aborted or committed the MSDTC TM invokes the provided callback/sink. SQL Server receives the prepare, abort. commit, … request and assigns the request to a background task to carry out.

Building on the first example the APP calls ITransaction->Commit or ITransactionAbort. The MSDTC proxy for the application engages MSDTC (A), which in turn carries out the request. For example, a commit request involves the prepare and commit operations.

The MSDTC (A) sends prepare requests to all the enlisted RMs. In this example this occurs via MSDTC (B) to the SQL Server. Once all voters have returned success from the prepare request the final commit request is broadcast by MSDTC (A) to all voters and the transaction is complete.

SQL Server also supports XA based transactions but has no code in the SQL Server engine to engage in XA transactions. No, it is not magic just some implementation specific details.

MSDTC can engage in MSDTC, XA, LU and TIP transactions. SQL Server is built to directly use MSDTC (OLE-TX) based interfaces. However, MSDTC provides XA mapper capabilities which can wraps the XA transaction activities within an ITransaction. SQL Server does not have to implement XA communications and semantics directly, and defferring to the MSDTC implementation.

The diagram is adjusted slightly from the original. Notice that xa_start replaces BeginTransaction and SQL_COPT_SS_ENLIST_IN_XA replaces …_ENLIST_IN_DTC.

The xa_open and xa_start are the xaswitch exposed entries for contacting and staring an XA transaction. MSDTC exposes the xaswitch in the SDK’s xaswitch.lib. Other vendors provide their own xaswitch implementations. This diagram highlights a 3^rd party XA TM. MSDTC can also be used as the XA TM if so desired (see example code at end of this post.)

Once the XID is acquired SQLSetConnAttrib is invoked by the application. This engages slightly different behavior in the SQL Server ODBC driver. The SQL Server ODBC driver leverages the IXATransactionLookup interface. The lookup interface takes the XID and communicates with the MSDTC (A) service.

The MSDTC service uses the OID information stored in the XID to contact the XA TM and establish appropriate voting, superior and subordinate relationships. Then MSDTC returns the ITransaction to the SQL Server ODBC driver.

The SQL Server ODBC driver then follows the same TM_GET_ADDRESS, ITransactionExport, TM_PROPAGATE, ITransactionImport exchange with the SQL Server as previously described.

Once the enlistment and propagation is complete, MSDTC (A), XA TM and MSDTC (B) are involved in the same transaction along with the APP and SQL Server (RM.) The SQL Server service doesn’t do anything with XA directly. The ITransaction wrapper technique used by SQL Server ODBC driver allows SQL Server to participate in an XA transaction, via the MSDTC implementation.

SQLJDBC uses a slightly different design but again the SQL Server service does not change. The SQL Server ODBC driver is built for Windows and linked with the MSDTC libraries but the SQL Server JDBC implementation is not Windows specific and does not have the msdtcprx.dll or service available on the client.

The SQLJDBC implementation contains an extended stored procedure (XPROC) that is installed at the SQL Server. The XPROC exposes a ‘remote’ xa_switch interface that any TDS client can invoke (the client does not require the msdtcprx.dll or sqlncli.dll.)

The SQLXAResource provides JTA capabilities for the SQLXADataSource and other SQLJDBC objects requiring XA capabilities.

The Java application starts an XA transaction with the XA enabled TM.

SQLJDBC calls xp_sqljdbc_xa_start passing the XID is passed over the TDS connection. The extended procedure is built with MSDTC’s xaswitch.lib allowing communication with MSDTC (B.)

You can probably see where this is going by now. The XPROC uses the xaswitch for MSDTC to engage with MSDTC (B) and leverages the XID to engage with the XA TM. Once the MSDTC (B) is engaged with the XA transaction the XPROC engages the IXATransactionLookup to obtain the ITransaction.

Using the returned ITransaction the XPROC obtains the whereabouts for MSDTC (B) and uses the whereabouts with ITransactionExport to obtain the transaction cookie. The binary array object (cookie) is passed back to SQLJDBC.

The transaction cookie is then sent back to SQL Server using the TM_PROPAGATE TDS request to enlist the desired connection or connections in the transaction.

Again, the SQL Server engine ONLY understands the MSDTC based interfaces. Leveraging TDS SQL Server remotes the xaswitch to any client (Windows, Linux, …) without client binaries for MSDTC. Only the SQL Server must load the XPROC and associated MSDTC components.

What about SQL Server on Linux?

A common question is how to enable DTC (MSDTC and/or XA) with SQL Server on Linux. The answer today is you can’t. The SQL Server ODBC driver for Linux does not support SQL_COPT_SS_ENLIST_IN_DTC or SQL_COPT_SS_ENLIST_IN_DTC and SQL Server for Linux prevents the install of extended stored procedures. This means there are no DTC components available for SQL Server 2017 on Linux.

There is a technique that can be used with SQL Server (Windows, Azure or Linux) to support some DTC capabilities. You don’t enlist the SQL Server connection in the DTC transaction. Instead you use a local SQL Server transaction.

The APP begins an XA or MSDTC transaction and enlists all the other end-points it requires in the transaction (File System, Oracle, PostreSQL, DB2, …) These end-points all become part of the distributed transaction and are controlled under the 2-phase protocol.

DTC specifications require a commit to always succeed if the RM reported a successful prepare. Using this knowledge we can take advantage of the prepare / commit sequencing.

Do work with SQL Server using a local SQL Server transaction, not distributed.

Then the application issues xa_prepare and if the prepare for all RMs is successful; the SQL Server transaction can be committed. If the prepare fails, the SQL Server transaction is aborted. If the SQL Server commit attempt fails, the xa_abort is invoked by the application instead of xa_commit.

Note: SQL Server Azure does not support DTC. However, this technique can be used with SQL Server Azure as well.

Helpful References

https://blogs.msdn.microsoft.com/psssql/2016/05/12/sql-2016-leverages-on-demand-msdtc-startup/
https://docs.microsoft.com/en-us/sql/relational-databases/native-client-odbc-how-to/use-microsoft-distributed-transaction-coordinator-odbc
https://docs.microsoft.com/en-us/sql/connect/jdbc/understanding-xa-transactions
https://blogs.msdn.microsoft.com/psssql/2010/04/20/how-it-works-orphan-dtc-transaction-sessionspid-2/
https://blogs.msdn.microsoft.com/psssql/2015/01/26/do-i-really-need-to-use-dtc-transactions/
https://blogs.msdn.microsoft.com/psssql/2012/01/24/how-it-works-sys-dm_tran_session_transactions/
https://support.microsoft.com/en-us/help/3005016/orphaned-xa-transactions-when-you-connect-to-sql-server-by-using-jdbc
https://msdn.microsoft.com/en-us/library/mt748186.aspx

Using MSDTC as XA TM Example

The following uses the xaswitch.lib provided in the SDK, leveraging MSDTC as an XA transaction manager and enlisting SQL Server.

#include "stdafx.h"
#include "windows.h"
#include "xa.h"
#include "sql.h"
#include "sqlext.h"
#include "sqltypes.h"
#include "odbcss.h"

// Include MSDTC xa switch
//
#pragma comment(lib, "xaswitch.lib")
#pragma comment(lib, "rpcrt4.lib")

extern "C"
{
    extern struct xa_switch_t msqlsrvxa1;
}

int main()
{
    // Connect to XA TM
    //
    LPSTR pxa_info = (LPSTR) "TM=MyTmHostName, RmRecoveryGuid=b3a7647d-FFFF-0000-9872-217f01b92071, BrIso=Tight";
    int myRmId = 100;
    XID myXid;

    int xaerr = msqlsrvxa1.xa_open_entry(pxa_info, myRmId, TMNOFLAGS);
    printf("xa_open_entry returned: %d\n", xaerr);

    myXid.formatID = 0;
    myXid.gtrid_length = 64;
myXid.bqual_length = 64;

    UuidCreate((UUID*)&myXid.data[0]);
UuidCreate((UUID*)&myXid.data[64]);

    xaerr = msqlsrvxa1.xa_start_entry(&myXid, myRmId, TMNOFLAGS);
printf("xa_start_entry returned: %d\n", xaerr);

//
// At this point we should have active transaction in MSDTC.
// You can see with Component Services | MSDTC | Transaction Statistics
//
// Use SQL ODBC provider to enlist XA which will do the MSDTCPRX
// ITransactionLookup and TM_PROPAGATE activities for us.
//
HENV hEnv;
HDBC hDbc;
HSTMT hStmt;
SQLRETURN rc;

    rc = SQLAllocHandle(SQL_HANDLE_ENV, nullptr, &hEnv);
printf("SQLAllocHandle returned: %d\n", rc);

    rc = SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
printf("SQLSetEnvAttr returned: %d\n", rc);

    rc = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);
printf("SQLAllocHandle returned: %d\n", rc);

    rc = SQLDriverConnect(hDbc, nullptr, (SQLWCHAR*)L"Driver={SQL Server Native Client 11.0};Server=.\\SQL2017;Trusted_Connection=yes;", SQL_NTS, nullptr, 0, nullptr, SQL_DRIVER_NOPROMPT);
printf("SQLDriverConnect returned: %d\n", rc);

    rc = SQLSetConnectAttr(hDbc, SQL_COPT_SS_ENLIST_IN_XA, (SQLPOINTER)1, 0);
printf("SQL_COPT_SS_ENLIST_IN_XA returned: %d\n", rc);

    rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt);
printf("SQLAllocHandle returned: %d\n", rc);

    rc = SQLExecDirect(hStmt, (SQLWCHAR*) L"create table #test (iID int); if((select count(*) from sys.dm_tran_session_transactions st inner join sys.dm_tran_active_transactions at on at.transaction_id = st.transaction_id and session_id = @@SPID and transaction_uow is not null) = 0) begin raiserror('NO UOW FOUND', 16, 1) end", SQL_NTS);
    printf("SQLExecDirect returned: %d\n", rc);

    // Get connection out of transaction. Transaction is still active at MSDTC and SQL Server.
    //
    rc = SQLSetConnectAttr(hDbc, SQL_COPT_SS_ENLIST_IN_XA, (SQLPOINTER)0, 0);
printf("SQL_COPT_SS_ENLIST_IN_XA returned: %d\n", rc);

    // Done with our branch
    //
    xaerr = msqlsrvxa1.xa_end_entry(&myXid, myRmId, TMNOFLAGS);
printf("xa_end_entry returned: %d\n", xaerr);

    if (SQL_SUCCESS != rc)
    {
        xaerr = msqlsrvxa1.xa_rollback_entry(&myXid, myRmId, TMNOFLAGS);
        printf("xa_rollback_entry returned: %d\n", xaerr);
    }
    else
    {
        xaerr = msqlsrvxa1.xa_prepare_entry(&myXid, myRmId, TMNOFLAGS);
        printf("xa_prepare_entry returned: %d\n", xaerr);
        xaerr = msqlsrvxa1.xa_commit_entry(&myXid, myRmId, TMNOFLAGS);
        printf("xa_commit_entry returned: %d\n", xaerr);
}

    return 0;
}

Export A Transaction Snippet

hr = m_pIDispenser->BeginTransaction(nullptr, lIsoLevel, ISOFLAG_RETAIN_DONTCARE, pOptions, &pNode->m_pTransaction);
if(SUCCEEDED(hr))
{
    hr = pNode->m_pTransaction->QueryInterface(IID_IGetDispenser, (LPVOID*)&pGetDispenser);
    if(SUCCEEDED(hr))
{
        hr = pGetDispenser->GetDispenser(IID_ITransactionExportFactory, (LPVOID *) &pExportFactory);
        if(SUCCEEDED(hr))
        {
            hr = m_pIDispenser->QueryInterface(IID_ITransactionImportWhereabouts, (LPVOID*) &pWhereAbouts);
            if(SUCCEEDED(hr))
            {
                unsigned long lWhereAbouts = 0;
                hr = pWhereAbouts->GetWhereaboutsSize(&lWhereAbouts);
                if(SUCCEEDED(hr))
                {
                    pWhereAboutsAddress = (unsigned char *) calloc(1, lWhereAbouts);
                    if(pWhereAboutsAddress)
                    {
                        pWhereAbouts->GetWhereabouts(lWhereAbouts, pWhereAboutsAddress, &lWhereAbouts);
                        if(SUCCEEDED(hr))
                        {
                            hr = pExportFactory->Create(lWhereAbouts, pWhereAboutsAddress, &pExport);

Import A Transaction Snippet

HRESULT hr = m_pIDispenser->QueryInterface(IID_ITransactionImport, (LPVOID*)&pTransImport);
if(SUCCEEDED(hr))
{
hr = pTransImport->Import(lCookieLen, gbCookie, (GUID *) &IID_ITransaction, (LPVOID*)&pITransaction);

Bob Dorr - Principal Software Engineer SQL Server

Comments

Anonymous
January 30, 2018
Hi,Great article.Regards.
Anonymous
May 29, 2019
Hello,Thanks for your article.Are there some C# wrapper to integrate in .NET WebApplication ? Have you some snippets in C# that use MSDTC ?I would interested to use MSDTC to manage transactions between differents microservices.Thanks.

Condividi tramite

How It Works: SQL Server DTC (MSDTC and XA Transactions)

Comments

Risorse aggiuntive