SQL Server 2019 CTP2
SQL Server 2019 adds broad support for distributed transactions (both MSDTC/OLE-TX and XA) to SQL Server on Linux. SQL Server 2019 on Linux enables the MSDTC service in SQLPAL providing distributed transaction capabilities on Linux alongside our SQL Server on Windows counterpart, supporting MSDTC/OLE-TX and XA transaction types.
You can establish and use transactions between Linux and Linux or Linux and Windows instances of SQL Server or your application. In fact, you can use the Microsoft SQL Server JDBC provider directly on Linux to engage in XA transactions. Try out this example
Current limitation: Engaging in a distributed transaction with SQL Server 2017 on Linux. SQL Server 2017 on Linux does not have distributed transaction support. However, SQL Server 2017 on Windows provides distributed transaction support allowing interactions with SQL Server 2019 on Linux.
Configuring MSDTC for SQL Server on Linux: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-msdtc?view=sql-server-ver15
Compatibility: (SQL JDBC and XA Transactions) – SQL Server 2019 CTP2 automatically includes the XA procedures used by the SQL JDBC driver. You no longer need to install the sqljdbc_xa.dll as SQL Server 2019 has included the procedures.
SQL Server 2017 (Historical Information)
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
Linux, SQL Server 2017 SQL Server on Linux
