Any situations where a ROLLBACK won't rollback changes against a linked server

Question

Any situations where a ROLLBACK won't rollback changes against a linked server

Andrew P

SSCarpal Tunnel

Points: 4649
More actions
January 11, 2017 at 4:13 pm

#325184

Hi all,
A while back I ran a query similar to the below.
SET XACT_ABORT ON
BEGIN TRANSACTION
UPDATE RemoteAccounts
SET BalanceAmt = LocalAccounts.BalanceAmt
FROM [RemoteSVR].[RemoteFinances].[dbo].[Accounts] AS RemoteAccounts
INNER JOIN LocalAccounts
ON RemoteAccounts.Accounts.Account_ID = LocalAccounts.Account_ID;
ROLLBACK TRANSACTION
The contents of the remote table seemed have been updated by the transaction, however browsing online regarding Distributed Transaction Coordinator, it looks like the situation I've described is not possible. This TechNet page for distributed transactions states that DTC will be used when a local transaction executes a distributed query against a linked server, which would have rolled the transaction back.
There were no triggers or nested transactions involved, and I did not receive any errors, however, I may have canceled query execution.
Are there any situations where changes made in the above transaction wouldn't be rolled back?
Regards,
Andrew

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

whenriksen SSCarpal Tunnel Points: 4752 More actions · Answer 1

Andrew P - Wednesday, January 11, 2017 4:13 PM
however, I may have canceled query execution

Do you still have the transaction open?
SELECT @@TRANCOUNT
If you stopped the transaction before the rollback, even if you run the statement again, you'll still need to rollback the original transaction. Every begin that gets run must have a commit or rollback executed as well.

Updating a remote table is never going to perform well. Doing it within an explicit transaction is just asking for problems. Is there any way for you to run the update on the remote server?

Wes
(A solid design is always preferable to a creative workaround)

Andrew P SSCarpal Tunnel Points: 4649 More actions · Answer 2

whenriksen - Thursday, January 12, 2017 1:12 PM
Andrew P - Wednesday, January 11, 2017 4:13 PM
however, I may have canceled query execution
Do you still have the transaction open?
SELECT @@TRANCOUNT
If you stopped the transaction before the rollback, even if you run the statement again, you'll still need to rollback the original transaction. Every begin that gets run must have a commit or rollback executed as well.
Updating a remote table is never going to perform well. Doing it within an explicit transaction is just asking for problems. Is there any way for you to run the update on the remote server?

I had tried to avoid setting up the linked server on the remote server, but eventually ran the update on the remote server, which worked as expected. Thanks for the tip regarding remote inserts, after some Google searching on the topic I ran a basic profiler on INSERT INTO <remote table> and wow! That causes row-by-row inserts, thanks! Here's a post I found with more info on that.

Andrew