2

I would like to know more about what happens behind the scenes with an auto-commit transaction when performing a cross-server query on a linked server.

I naïvely think that when executing an auto-commit transaction the compiler/SQL Server/something else just prepends all statements with a BEGIN TRANSACTION and appends all statements with a COMMIT TRANSACTION since everything is technically enclosed in a transaction (Is it a bad practice to always create a transaction?). I'm sure this is incorrect and the source of my confusion as to why performing a cross-server UPDATE without explicitly stating BEGIN TRANSACTION works but explicitly stating one does not. According to Microsoft (https://learn.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql?view=sql-server-2017), an explicit BEGIN TRANSACTION on an UPDATE query that references a table on a linked server gets escalated to a distributed transaction; and since distributed transactions aren't configured on the linked server, I receive an error. How does the auto-commit setting avoid this? How does it not get escalated to a distributed transaction? Does the auto-commit setting send the data to the linked server but doesn't "listen" for a response from the linked server via Microsoft Distributed Transaction Coordinator (MS DTC); thus if an error occurs, it "silently" fails?

Auto-commit doesn't escalate to a distributed transaction:

UPDATE l
SET l.RecordKey = s.RecordKey
FROM LinkedServer.ExampleDatabase.dbo.ExampleTable AS l
INNER JOIN ServerWithActiveConnection.ExampleDatabase.dbo.ExampleTable AS s
    ON l.Value1 = s.Value1;

Explicit transaction does escalate to a distributed transaction (and errors in my case):

BEGIN TRANSACTION
UPDATE l
SET l.RecordKey = s.RecordKey
FROM LinkedServer.ExampleDatabase.dbo.ExampleTable AS l
INNER JOIN ServerWithActiveConnection.ExampleDatabase.dbo.ExampleTable AS s
    ON l.Value1 = s.Value1;
COMMIT TRANSACTION;

I should add that the statements were run in SQL Server Management Studio.

philomathic_life
  • 472
  • 3
  • 14

2 Answers2

2

Given that a Linked Server is an external / separate connection, I would expect that it can be handled / managed separately, just like making an external / regular connection to any SQL Server (even back to the calling instance -- i.e. loop back) via SqlClient in SQLCLR. Being a separate connection, it is optional whether or not to attempt joining the current transaction (this is handled via the Enlist= {true | false} connection string keyword). I am guessing that the Linked Server property of is_remote_proc_transaction_promotion_enabled / "Enable Promotion of Distributed Transactions for RPC" is equivalent to the Enlist connection string keyword.

That being said, there is something being overlooked (including being overlooked in my original answer): the name of that option is "Enable Promotion of Distributed Transactions for RPC" (emphasis mine). The highlighted part is rather important here. This option affects stored procedure calls over the linked server, not regular ad hoc query batches. So, that option should not be relevant to the behavior you are describing in the question.

My (educated) guess is that for ad hoc queries, the option to "Enlist" in the current transaction is defaulted to "false" for auto-commit transactions. But, for explicit transactions, you are telling SQL Server that all statements within the BEGIN TRAN ... COMMIT TRAN should be considered an atomic operation, so the remote connection then tries to enlist in the current Transaction.

FYI: this behavior should have nothing to do with executing the statements in SSMS. That is just a client that submits the statements to SQL Server for execution. It should be the same behavior even if executing via SQLCMD.EXE or SqlCient in a .NET app, etc.

UPDATE

With regards to testing this, I suggested to the O.P. using SQL Server Profiler or Extended Events to capture the transaction enlistment / promotion to distributed transaction. The O.P. replied with:

I followed your suggestion and used Profiler, and there is indeed a row that has its EventClass value with "DTCTransaction" and EventSubClass value as "Enlisting in a DTC transaction" when I use an explicit transaction. No such rows exist when I rely on the auto-commit setting.

Solomon Rutzky
  • 70,048
  • 8
  • 160
  • 306
1

This depends on the level of transaction support in the linked server OLEDB provider:

  1. The ability to manage local transactions at the remote server (ITransactionLocal)
  2. The ability to join a transaction started by the caller (ITransactionJoin)

Auto-commit mode

It is enough for SQL Server to be able to ask the remote server to start a local transaction there, which SQL Server can ask it to commit or rollback depending on the outcome of executing the local auto-commit statement. This only requires ITransactionLocal support by the remote provider.

Explicit or implicit transaction mode

  • Read-only operations:

    Permitted if the provider is unable to enlist in the transaction already active on the local server (no ITransactionJoin support).

    It doesn't matter if the provider supports transactions at all for read-only operations.

  • Update operations:

    • If SET XACT_ABORT is ON:

      Permitted if the provider supports ITransactionJoin.
      The local transaction becomes a distributed transaction managed by e.g. DTC.

    • If SET XACT_ABORT is OFF:

      Requires provider support for nested transactions.
      Almost no one supports nested transactions.

You probably ran your tests with SET XACT_ABORT set to OFF, the OLEDB provider you are using doesn't support ITransactionJoin, or a distributed transaction coordinator was unavailable.

The setting of Enable Promotion of Distributed Transactions for RPC on the linked server is also important because the individual remote row changes are performed using an RPC call.

The documentation: Distributed Queries and Distributed Transactions.

Paul White
  • 94,921
  • 30
  • 437
  • 687