Question :
For this question lets assume there are two servers, ServerA and ServerB, and there is a linked server setup on ServerA to ServerB. Let’s also assume I have REMOTE_PROC_TRANSACTIONS set to ON for both servers.
According to Microsoft’s BOL for BEGIN TRANSACTION:
The local transaction started by the BEGIN TRANSACTION statement is escalated to a distributed transaction if the following actions are performed before the statement is committed or rolled back:
An INSERT, DELETE, or UPDATE statement that references a remote table on a linked server is executed. The INSERT, UPDATE, or DELETE
statement fails if the OLE DB provider used to access the linked
server does not support the ITransactionJoin interface.A call is made to a remote stored procedure when the REMOTE_PROC_TRANSACTIONS option is set to ON.
Given the above, are there any cases where it makes sense or is mandatory to explicitly use BEGIN DISTRIBUTED TRANSACTION?
Specifically, I’m planning to call a stored procedure (that executes DML queries) on ServerB via the linked server from ServerA with the same transaction wrapped around some DML statements executed on ServerA as well. My goal is to achieve transactional consistency for both sets of DML queries happening between the two servers.
Answer :
I think it depends on whether you want to rely on the linked server configuration option ‘Enable Promotion of Distributed Transaction’ or whether
you want to be sure that your transaction can run only if it’s distributed.
