Keeping ACID Across More Than One Server

A distributed transaction coordinates one unit of work across multiple participating resources. The difficult part is preserving the agreed outcome when a participant or the network stops responding.

Separate Atomicity From the Other Guarantees

Atomicity means the transaction's work commits together or does not commit. Consistency concerns the rules the operation must preserve, while isolation controls interference from concurrent work. Durability concerns committed work surviving the failures covered by the system.

Crossing a server boundary makes coordination more complicated, but the words keep their meanings. Two-phase commit primarily coordinates the atomic outcome. It does not automatically choose a serializable isolation level or validate every business rule.

Begin by asking whether both resources truly need one immediate atomic decision. Some operations do, while others can tolerate a documented intermediate state. That is a business requirement, not simply a preference for fewer configuration steps.

Understand Prepare and Decision

In two-phase commit, a coordinator asks participants to prepare. A participant that votes to commit must be able to honor the later decision. The coordinator then communicates the commit or abort outcome.

Preparation can retain resources while the final decision is pending. Network delays and unavailable participants therefore affect more than application response time. Other work can remain blocked behind the unresolved transaction.

SELECT transaction_id, name, transaction_begin_time,
       transaction_type, transaction_state, transaction_uow
FROM sys.dm_tran_active_transactions
WHERE transaction_type = 4;

This SQL Server view identifies active distributed transactions visible to the querying account. It is a snapshot rather than a complete incident history. Interpret states using the documentation for the installed version.

Know the SQL Server Coordinator

On SQL Server, Microsoft Distributed Transaction Coordinator supports distributed transaction coordination. Participating services, authentication, networking, and product support must be configured appropriately. A linked server definition alone does not prove that a distributed transaction will work.

SELECT name, product, provider, data_source,
       is_data_access_enabled, is_rpc_out_enabled,
       is_remote_proc_transaction_promotion_enabled
FROM sys.servers
WHERE is_linked = 1;

Local transactions can be promoted in supported remote-access scenarios. The exact behavior depends on the operation, provider, and settings. Review those conditions instead of assuming every remote SELECT or procedure call behaves identically.

Availability groups and hosted SQL offerings have their own support details. Check the exact version and topology before designing the transaction. An old restriction or a different service's behavior is not a reliable guide.

Treat Silence as Uncertainty

A timed-out client does not necessarily know whether commit succeeded. The server may have committed before the acknowledgment was lost. Repeating the business operation can therefore duplicate its effect.

SELECT aborted, committed, in_doubt, [open],
       forced_abort, forced_commit
FROM sys.dm_tran_distributed_transaction_stats;

This statistics view is available in SQL Server 2022 and later and requires the documented performance-state permission. Its in-doubt count is a signal to investigate, not permission to force an outcome. Follow coordinator recovery and the supported operational procedure.

Manually forcing opposite decisions at different participants can violate the intended consistency. Preserve transaction identifiers and relevant logs during investigation. Recovery needs a coherent decision, not merely two servers that stop waiting.

Consider Local Transactions Plus Reliable Messages

An alternative is committing local data and an outgoing message record together. A separate worker delivers that record, with retries and duplicate detection. This outbox approach changes the guarantee from one immediate cross-system commit to coordinated eventual processing.

DECLARE @OperationId uniqueidentifier = NEWID();
DECLARE @Outbox table
(
    OperationId uniqueidentifier PRIMARY KEY,
    Payload nvarchar(max) NOT NULL,
    Delivered bit NOT NULL
);
INSERT @Outbox VALUES (@OperationId, N'{"event":"ExampleCreated"}', 0);
SELECT OperationId, Payload FROM @Outbox WHERE Delivered = 0;

This temporary example illustrates message identity only. A production outbox must be durable and commit with the actual business write. The receiver also needs durable duplicate handling for that identity.

A compensating action is another business operation, not a time machine that erases every consequence. Design what happens if compensation also fails. Eventual consistency requires explicit intermediate states and monitoring.

Test the Guarantee You Chose

Test connection loss before prepare, during coordination, and after commit acknowledgment becomes uncertain. Verify both participants and the application's retry behavior. A happy-path transfer does not validate the failure protocol.

Choose distributed atomicity when the requirement and supported infrastructure justify it. Choose a message-based workflow when its delayed consistency is acceptable and fully designed. Neither option becomes reliable merely because it has a familiar name.

Cross-server consistency is not a free extension of BEGIN TRAN, it is a recovery protocol.

This post was rewritten from scratch in September 2026. The original, published on 2013-08-08, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.