The Linux release notes (https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-release-notes) indicate support for a SQL Server to SQL Server distributed transaction type.
The release notes state: Distributed transactions requiring the Microsoft Distributed Transaction Coordinator service are not supported on SQL Server running on Linux. SQL Server to SQL Server distributed transactions are supported.
What they should state: Distributed transactions requiring the Microsoft Distributed Transaction Coordinator service are not supported on SQL Server running on Linux. SQL Server to SQL Server Linked Servers are supported if a DTC transaction is not required.
SQL Server on Linux does not support DTC transactions. You should receive the following error when attempting to use a DTC transaction involving an instance of SQL Server for Linux.
'MSDTC on server <my linked server name> is unavailable'.
There are various types of transactions supported by SQL Server. A few of these are:
- Local SQL Transaction (most common)
- Cross Database Transactions (same SQL Server instance with 2 or more databases involved in the transaction) – DTC is not required.
- DTC (more than one database involved crossing a connection/machine boundary)
- SQL Server Stretch Database – Data archive connections – not DTC, DTC like
Many of the interfaces provided by SQL Server client APIs and Assemblies, as well as SQL Server itself, engage in the two-phase transaction logic only when necessary. For example, issuing a begin distributed transaction and updating the local database can be detected as a single-phase commit and whenever possible remains a local, SQL transaction.
The DTC decision involves a series of decision logic:
- If already in DTC transaction continue
- If in a local SQL transaction and crossing a connection/machine boundary – may upgrade to DTC
- If executing across an connection/machine boundary (even local loopback) an (update, insert, delete) upgrade to DTC transaction. A Select or EXEC may not require upgrade to DTC transaction unless it is select into or select into exec
- There are sp_configure options to alter DTC transactional behaviors
- SQL Server checks some isolation levels (Ex: Repeatable Read) which may trigger DTC upgrade behavior
Bob Dorr - Principal Software Engineer SQL Server