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.
Best Answer
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 theEnlist= {true | false}
connection string keyword). I am guessing that the Linked Server property ofis_remote_proc_transaction_promotion_enabled
/ "Enable Promotion of Distributed Transactions for RPC" is equivalent to theEnlist
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: