How to Minimize Side Effects When Writing to Two Databases at the Same Time

Normally, data is stored in a single database.
However, sometimes you may need to write to two physically separate DB servers at the same time.

The naive approach everyone starts with

The examples in this post are written in C# using EF Core.

await mDbContext0.SaveChangesAsync();
await mDbContext1.SaveChangesAsync();

Most people simply write this way.
But this will definitely break one day.

Right after the first commit completes,

• The second DB's SaveChangesAsync() might fail due to a validation error or constraint violation,
• The network could go down, or
• A power failure could occur.

In the end, only the first DB is updated, and the second one fails.
In other words, you get a partially committed state, resulting in data inconsistency.

How I solved it today

⚠️ This approach is not perfect.

private async Task crossCommitBestEffortAsync()
{
    await using (IDbContextTransaction tx0 = await mDbContext0.Database.BeginTransactionAsync())
    await using (IDbContextTransaction tx1 = await mDbContext1.Database.BeginTransactionAsync())
    {
        // best-effort attempt to make two independent DB commits look atomic
        // still unsafe if:
        //   1) tx0.CommitAsync() succeeds, and
        //   2) power failure happens before tx1.CommitAsync()
        try
        {
            await mDbContext0.SaveChangesAsync();
            await mDbContext1.SaveChangesAsync();

            await tx0.CommitAsync();
            await tx1.CommitAsync();
        }
        catch
        {
            await tx0.RollbackAsync();
            await tx1.RollbackAsync();
            throw;
        }
    }
}

This code opens a separate transaction for each DB
and only commits if both SaveChangesAsync() calls succeed.

What's different from before?

In the naive version (SaveChangesAsync() twice),
if the first commit succeeds and the second throws an exception,
there's no way to revert the already committed data.

In contrast, this code:

• Rolls back both transactions if either SaveChangesAsync() or CommitAsync() fails.
• Ensures that under normal execution flow, both DBs either commit or roll back together.

This is a “best effort” approach —
as long as the OS and process stay alive, both DBs will end in the same state.

Why it's still not perfect

The real problem is physical failure.
In other words, the code handles logic-level consistency, but not system-level reliability.

For example, the following sequence will break things 👇

1. tx0.CommitAsync() succeeds
2. A power outage or process crash occurs
3. tx1.CommitAsync() never gets called

Now, DB0 has committed while DB1 has not.
The two databases are out of sync.

There's no way to prevent this in code,
because the two DBs live on independent physical servers.

Not suitable for mission-critical systems

Although the time gap between commits is small,
“small” doesn't mean simultaneous.

If a power failure happens 0.001 seconds after the first commit, the data becomes inconsistent.

Therefore, this approach should never be used in mission-critical transactions
such as payments, settlements, or order processing.

Why I still used it

This pattern was used in an internal developer tool,
not in a public-facing service.

The chance of failure was extremely low,
and even if it did happen, the developer was actively using the tool
and could immediately detect and correct the issue.

In short, it was acceptable in this low-risk environment.

The proper way: use a message queue

To handle this safely, you should use a Message Queue (MQ).

However, the naive approach — committing to the DB first and then pushing a message — is still unsafe.
If the system crashes or loses power right after the DB commit, the message never gets queued,
and you lose the chance to reprocess it.

A more reliable approach is to push every update request to the queue first,
and then let the consumer update DB0 and DB1 in sequence.

This way, the producer only performs one action — sending a message —
and even if a failure occurs, the pending message can always be reprocessed later.

You'll still need mechanisms like deduplication and pre-validation,
but at least you won't end up with inconsistent data.

If the consumer can't update the second DB
because of a validation error or business rule violation,
then a compensating transaction must be applied to the first DB
to roll back the previous change.

So even with a queue-based design, it's not fully automatic —
you still need explicit rollback logic to keep the system consistent.

Keep in mind that adopting an MQ means
adding another program to run and another data store to maintain.
It introduces an extra operational layer,
and debugging becomes harder since you can't simply inspect it with SQL.

In short, reliability increases, but so does complexity.
If your system is small or failures can be handled manually,
you may not need an MQ.
But if stability is your top priority, this is the right direction to go.

When starting out, I recommend using a Rebus + SQL combo
instead of a complex distributed MQ system —
it's easy to configure and supports transactional consistency cleanly.

Note: MSDTC works only on-premises

If you're running Windows servers on-premises,
you can use MSDTC (Microsoft Distributed Transaction Coordinator)
to coordinate fully atomic distributed transactions across multiple databases.

using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
    await mDbContext0.SaveChangesAsync();
    await mDbContext1.SaveChangesAsync();
    scope.Complete();
}

This guarantees atomic commits.
However, Azure SQL Database does not support MSDTC.

So in cloud environments, you're limited to either
best-effort commits or queue-based compensating transactions.

Conclusion

1. Calling SaveChangesAsync() twice will eventually cause trouble.
2. crossCommitBestEffortAsync() keeps things consistent in normal scenarios,
   but it's still vulnerable to physical failures like power loss.
3. It's not suitable for mission-critical systems.
4. For safety, you need a queue-based design.
5. MSDTC works only on-premises.

In the end, “two commits” will betray you, but a queue will save your system. 😏