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://docs.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.