Auto-commit UPDATE Transaction On Linked Server

Question

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.

Answer 1

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.