0

Can someone please explain to me how this works? what would happen if upon commiting, one of the participants of the distributed transaction loses it's connection? How would the other participants know that they would need to rollback? I can't see how a distributed transaction would be ACID, somebody care to explain?

here's a sample

SET XACT_ABORT ON
BEGIN DISTRIBUTED TRANSACTION

INSERT INTO [linkedserver1].[Play].[dbo].[tb_Test] VALUES ('Test');
INSERT INTO [linkedserver2].[Play].[dbo].[tb_Test] VALUES ('Test');
INSERT INTO [linkedserver3].[Play].[dbo].[tb_Test] VALUES ('Test');

COMMIT
SET XACT_ABORT OFF
Improve this question
2
  • Distributed transactions are not 100% foolproof, but probably 99.9999% (or more).
    – The Impaler
    Commented Mar 13, 2019 at 15:20
  • 3
    Distributed transactions use a two-phase commit protocol with some slight modifications. It's far from trivial (and distributed transactions are relatively expensive), but it does take such scenarios into account. Basically, a transaction is not committed until all participants agree that it is -- if at any point a failure occurs, this agreement cannot happen and a rollback will be initiated sooner or later (either by notification, by timeout or by a local action).
    – Jeroen Mostert
    Commented Mar 13, 2019 at 15:48
Related questions
14
How do distributed transactions work (eg. MSDTC)?
0
distributed transaction between two MS SQL Server 2005
5
Security risk of enabling MSDTC
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.