Skip to content

Latest commit

 

History

History
73 lines (48 loc) · 3.86 KB

performing-transactions-distributed-transactions.md

File metadata and controls

73 lines (48 loc) · 3.86 KB
Raw
title description author ms.author ms.date ms.service ms.subservice ms.topic helpviewer_keywords
Create a distributed transactions
Applications can use MSDTC to extend or distribute a transaction across several instances of SQL Server. A .NET class can also distribute a transaction.
markingmyname
maghan
05/13/2019
sql
native-client
reference
MS DTC, performing distributed transactions
SQL Server Native Client ODBC driver, transactions
distributed transactions [ODBC]
transactions [ODBC]
ODBC, transactions

Create a distributed transaction

[!INCLUDE SQL Server]

Important

[!INCLUDEsnac-removed-oledb-and-odbc]

A distributed transaction can be created for different Microsoft SQL systems in different ways.

ODBC driver calls the MSDTC for SQL Server on-premises

The Microsoft Distributed Transaction Coordinator (MSDTC) allows applications to extend or distribute a transaction across two or more instances of [!INCLUDEssNoVersion]. The distributed transaction works even when the two instances are hosted on separate computers.

MSDTC is installed for Microsoft SQL Server on-premises, but isn't available for Microsoft's Azure SQL Database cloud service.

MSDTC is called by the SQL Server Native Client driver for Open Database Connectivity (ODBC), when your C++ program manages a distributed transaction. The Native Client ODBC driver has a transaction manager that is compliant with the Open Group Distributed Transaction Processing (DTP) XA standard. This compliance is required by MSDTC. Typically, all transaction management commands are sent through this Native Client ODBC driver. The sequence is as follows:

  1. Your C++ Native Client ODBC application starts a transaction by calling SQLSetConnectAttr, with the autocommit mode turned off.

  2. The application updates some data on SQL Server X on computer A.

  3. The application updates some data on SQL Server Y on computer B.

    • If an update on SQL Server Y fails, all the uncommitted updates on both SQL Server instances are rolled back.

  4. Finally, the application ends the transaction by calling SQLEndTran (1), with either the SQL_COMMIT or SQL_ROLLBACK option.

(1) MSDTC can be invoked without ODBC. In such a case, MSDTC becomes the transaction manager, and the application no longer uses SQLEndTran.

Only one distributed transaction

Suppose that your C++ Native Client ODBC application is enlisted in a distributed transaction. Next the application enlists in a second distributed transaction. In this case, the [!INCLUDEssNoVersion] Native Client ODBC driver leaves the original distributed transaction, and enlists in the new distributed transaction.

For more information, see DTC Programmer's Reference.

C# alternative for SQL Database in the cloud

MSDTC isn't supported for either Azure SQL Database or Azure Synapse Analytics.

However, a distributed transaction can be created for SQL Database by having your C# program use the .NET class System.Transactions.TransactionScope.

Other programming languages

The following other programming languages might not provide any support for distributed transactions with the SQL Database service:

  • Native C++ that use ODBC drivers
  • Linked server using Transact-SQL
  • JDBC drivers

See also

Performing Transactions (ODBC)