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.