Back to: ADO.NET Core Tutorial For Beginners and Professionals
ADO.NET Core Distributed Transactions
In this article, I will discuss how to implement ADO.NET Core Distributed Transactions involving multiple databases with examples. Please read our previous article discussing ADO.NET Core Implicit vs Explicit Transactions. When working with multiple databases in a single business operation, maintaining data consistency becomes critical. Distributed transactions in ADO.NET Core allow you to coordinate operations across multiple resource managers (e.g., databases) as a single atomic unit. If any operation fails, the entire transaction is rolled back, preventing partial updates and preserving data integrity.
What Are Distributed Transactions in ADO.NET Core?
A Distributed Transaction in ADO.NET Core spans multiple databases (e.g., two different SQL Server databases) or other resource managers (e.g., SQL Server + a message queue). All operations in the transaction either commit or roll back together, ensuring data consistency and integrity. You might need a distributed transaction when:
- Business Logic Spans Multiple Databases: A single operation needs to interact with multiple database servers.
- Strong Consistency Guarantees: You require that all updates succeed (commit) or all fail (rollback).
Different ways to Implement Saga Pattern in .NET Core Application:
Distributed transactions commonly rely on the Microsoft Distributed Transaction Coordinator (MSDTC) on Windows to coordinate two-phase commit operations across multiple resources. On non-Windows platforms (Linux, macOS), MSDTC is unavailable. In those environments, or where MSDTC is not desired, you often implement eventual consistency using the Saga Pattern. ADO.NET Core coordinates distributed transactions primarily through:
- Microsoft Distributed Transaction Coordinator (MSDTC): Automatically coordinates transactions on Windows platforms.
- Saga Pattern: Used especially on non-Windows platforms or microservices environments.
Example to Understand Distributed Transaction
Imagine a scenario in which you have an order processing system where one database records customer orders while another keeps track of inventory. If the order is saved but the inventory update fails, you could end up with an inconsistent state (a sold order with no inventory reduction). Using a distributed transaction, both operations are coordinated: either the entire transaction is committed or rolled back, preserving data integrity.
Database Setup in SQL Server
Please execute the following SQL scripts in SQL Server Management Studio (SSMS) (or your preferred SQL tool) to create the OrdersDB and InventoryDB databases.
Create OrdersDB Database
This database will store customer orders.
-- Create OrdersDB
CREATE DATABASE OrdersDB;
GO
-- Use OrdersDB and create an Orders table
USE OrdersDB;
GO
-- Create the Orders table
CREATE TABLE Orders (
OrderID INT IDENTITY(1,1) PRIMARY KEY,
CustomerName NVARCHAR(100) NOT NULL,
OrderDate DATETIME NOT NULL DEFAULT GETDATE()
);
GO
Create InventoryDB Database
This database will manage the inventory.
-- Create InventoryDB
CREATE DATABASE InventoryDB;
GO
-- Use InventoryDB and create an Inventory table
USE InventoryDB;
GO
-- Create the Inventory table
CREATE TABLE Inventory (
ProductID INT IDENTITY(1,1) PRIMARY KEY,
ProductName NVARCHAR(100) NOT NULL,
Quantity INT NOT NULL
);
GO
-- Insert a sample product record to update later.
INSERT INTO Inventory (ProductName, Quantity)
VALUES ('Sample Product', 10);
GO
How Do We Enable Implicit Distributed Transactions with MSDTC?
ADO.NET Core provides a way to automatically promote a transaction to a distributed transaction when multiple connections are opened within the same TransactionScope. For Windows environments, MSDTC is used to coordinate these transactions. To allow this automatic promotion, you need to include the following setting at the beginning of your application:
TransactionManager.ImplicitDistributedTransactions = true;
Notes:
- This setting should be applied at the very start of your application (often in the Main method).
- It explicitly allows the system to use implicit distributed transactions via MSDTC.
- This feature is specific to environments where MSDTC is available (typically Windows).
How Do We Implement Distributed Transactions in ADO.NET Core?
When multiple database connections (such as one to the OrdersDB and another to the InventoryDB) are opened in the same transaction scope, ADO.NET Core automatically promotes the transaction to a distributed one. This is achieved using the TransactionScope class from the System.Transactions namespace. We need to follow the below steps:
- Create a Transaction Scope: Encapsulate all database operations within a using (TransactionScope scope = new TransactionScope()) block.
- Open Multiple Connections: Open connections to each database resource (e.g., OrdersDB and InventoryDB).
- Execute Database Operations: Within the transaction scope, execute all required operations (INSERT, UPDATE, etc.). If all operations succeed, call scope.Complete() to commit the transaction.
- Automatic Rollback on Failure: If an exception occurs and scope.Complete() is not reached, all changes will be rolled back automatically.
MSDTC Involvement: When more than one connection (e.g., connecting to both OrdersDB and InventoryDB) is opened inside the same transaction scope, the system automatically uses the Microsoft Distributed Transaction Coordinator (MSDTC) to coordinate the distributed transaction across these resources.
Implementing Distributed Transaction using MSDTC
The following example uses a distributed transaction. The example simulates an order processing operation where an order is inserted into the OrdersDB, and the inventory is updated in the InventoryDB. If any operation fails, both changes will be rolled back to ensure data consistency.
using Microsoft.Data.SqlClient;
using System.Transactions;
namespace DistributedTransactionExample
{
public class Program
{
static void Main(string[] args)
{
// Enable implicit distributed transactions so that when multiple connections are used,
// the transaction is automatically promoted to a distributed one via MSDTC.
#pragma warning disable CA1416 // Validate platform compatibility
TransactionManager.ImplicitDistributedTransactions = true;
#pragma warning restore CA1416 // Validate platform compatibility
Console.WriteLine("Starting distributed transaction for order processing...");
//Connection String Pointing to OrdersDB
string connectionStringOrdersDB = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=OrdersDB;Trusted_Connection=True;TrustServerCertificate=True;";
//Connection String Pointing to InventoryDB
string connectionStringInventoryDB = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=InventoryDB;Trusted_Connection=True;TrustServerCertificate=True;";
// For Distributaed Transaction, we need to create an instance of TransactionScope
// Here, we are using the using block which will dispose the transactionScope object automatically
// Enclose the entire operation in a try-catch block to handle any exceptions.
try
{
// Create a transaction scope that automatically manages distributed transactions.
using (TransactionScope scope = new TransactionScope())
{
// First: Insert a new order into OrdersDB.
using (SqlConnection orderConnection = new SqlConnection(connectionStringOrdersDB))
{
// Open the connection to OrdersDB.
orderConnection.Open();
// Define the SQL query to insert a record into the Orders table.
string insertOrderQuery = "INSERT INTO Orders (CustomerName, OrderDate) VALUES (@CustomerName, @OrderDate)";
using (SqlCommand orderCommand = new SqlCommand(insertOrderQuery, orderConnection))
{
// Add parameters for the query to secure against SQL injection.
orderCommand.Parameters.AddWithValue("@CustomerName", "John Doe");
orderCommand.Parameters.AddWithValue("@OrderDate", DateTime.Now);
// Execute the command.
orderCommand.ExecuteNonQuery();
Console.WriteLine("Order inserted successfully into OrdersDB.");
}
}
// Next: Update inventory in InventoryDB to reduce the stock count.
using (SqlConnection inventoryConnection = new SqlConnection(connectionStringInventoryDB))
{
// Open the connection to InventoryDB.
inventoryConnection.Open();
// Define the SQL query to update the inventory by reducing the quantity.
string updateInventoryQuery = "UPDATE Inventory SET Quantity = Quantity - 1 WHERE ProductID = @ProductID";
using (SqlCommand inventoryCommand = new SqlCommand(updateInventoryQuery, inventoryConnection))
{
// Specify the product to update. Here, we're using ProductID 1 as an example.
inventoryCommand.Parameters.AddWithValue("@ProductID", 1);
// Execute the update command.
int affectedRows = inventoryCommand.ExecuteNonQuery();
// Validate that the inventory was updated.
if (affectedRows > 0)
{
Console.WriteLine("Inventory updated successfully in InventoryDB.");
}
else
{
// If no rows are affected, throw an exception to trigger the transaction rollback.
throw new Exception("Inventory update failed for ProductID 1.");
}
}
}
// If both operations are successful, mark the transaction as complete.
scope.Complete();
Console.WriteLine("Distributed transaction committed successfully.");
}
}
catch (Exception ex)
{
// If an exception occurs, the transaction is not completed and will be rolled back.
Console.WriteLine("An error occurred during the distributed transaction: " + ex.Message);
}
Console.ReadKey();
}
}
}
Code Explanations:
- Implicit Distributed Transactions: The line TransactionManager.ImplicitDistributedTransactions = true; ensures that if more than one resource (e.g., two separate database connections) is used inside the same transaction scope, the transaction is automatically promoted to a distributed transaction using MSDTC.
- TransactionScope Usage: The TransactionScope wraps the entire process. Two connections are opened, one to each database, so that all operations are part of the same transaction.
- Order Insertion: A new order is inserted into the OrdersDB. A parameterized query is used to prevent SQL injection.
- Inventory Update: The inventory is updated to decrement the product quantity. If the update does not affect any rows, an exception is thrown to trigger a rollback.
- Completion and Rollback: Calling scope.Complete() signals that all operations are successful and commits the transaction. If an exception occurs before this call, the transaction automatically rolls back.
Output:
Now, you can verify the database tables in both databases, and you will see the data as expected. If any of the DML operations fail in the transaction scope, the Complete method will not be invoked, and the transaction will roll back. This is how we can implement Distributed Transactions using ADO.NET Core.
How Do We Implement Distributed Transactions in Linux, Mac, or Other OS?
MSDTC (Microsoft Distributed Transaction Coordinator) is a Windows-specific technology, which means it isn’t available on Linux, macOS, or other non-Windows platforms. On non-Windows systems, there isn’t a built-in system equivalent that automatically coordinates distributed transactions as MSDTC does. On platforms like Linux or macOS, you have to rely on the Saga Pattern or Manual Two-Phase Commit:
- Saga Pattern: A design pattern for managing long-running business transactions where each step is a local transaction. If any step fails, compensating transactions (undo operations) are executed.
- Manual Two-Phase Commit: Implement a custom protocol where the commit is split into two phases: first, prepare and lock resources, then commit or roll back based on overall success.
What Is the Saga Pattern?
The Saga Pattern is a design approach for handling long‑running business transactions spanning across multiple services or databases. Instead of relying on a single distributed transaction coordinator, a saga divides the work into multiple local transactions. Each local transaction commits its change immediately. However, if one step fails later in the saga, a compensating transaction (or rollback action) is executed to undo the effects of the previously committed steps, ensuring data consistency and integrity.
Components of the Saga Pattern:
The following are the Components of the Saga Pattern:
- Local Transactions: Each participating service or database performs its transaction independently.
- Compensating Transactions: These are operations designed to revert the changes made by a previous transaction. They are executed if subsequent steps fail.
- Saga Orchestrator: The component responsible for managing the sequence of transactions and coordinating compensations when needed.
Example Implementing Saga Pattern in .NET Core Application:
The following example demonstrates the Saga Pattern for an order processing scenario. The process involves creating an order in the OrdersDB, updating inventory in the InventoryDB, and executing a compensating transaction (canceling the order) if any step fails. The following example code is self-explained; please read the comment lines for a better understanding.
using Microsoft.Data.SqlClient;
namespace SagaPatternExample
{
// The main program that starts the saga.
public class Program
{
static void Main(string[] args)
{
Console.WriteLine("Starting Saga Pattern order processing...");
// Create an instance of the saga orchestrator and execute the saga.
var saga = new OrderProcessingSaga();
saga.Run();
Console.ReadKey();
}
}
// This class represents the Saga orchestrator for order processing.
// It includes all the steps as local transactions and compensates if any step fails.
public class OrderProcessingSaga
{
//Connection String Pointing to OrdersDB
string connectionStringOrdersDB = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=OrdersDB;Trusted_Connection=True;TrustServerCertificate=True;";
//Connection String Pointing to InventoryDB
string connectionStringInventoryDB = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=InventoryDB;Trusted_Connection=True;TrustServerCertificate=True;";
// Runs the overall saga.
public void Run()
{
int orderId = 0;
try
{
// Step 1: Execute local transaction - Create Order.
orderId = CreateOrder("John Doe");
Console.WriteLine($"Order created successfully with OrderID: {orderId}");
// Step 2: Execute local transaction - Update Inventory.
bool inventoryUpdated = UpdateInventory(1);
if (!inventoryUpdated)
{
// If the inventory update fails, throw an exception.
throw new Exception("Inventory update failed.");
}
Console.WriteLine("Inventory updated successfully.");
// If all steps succeed, the saga ends in success.
Console.WriteLine("Saga completed successfully.");
}
catch (Exception ex)
{
Console.WriteLine($"Error encountered in saga: {ex.Message}");
// Compensation: Roll back the order if created.
if (orderId > 0)
{
CancelOrder(orderId);
}
Console.WriteLine("Saga compensated and rolled back.");
}
}
// Creates an order in OrdersDB and returns the generated OrderID.
private int CreateOrder(string customerName)
{
int createdOrderId = 0;
// Open a connection to OrdersDB.
using (SqlConnection conn = new SqlConnection(connectionStringOrdersDB))
{
conn.Open();
// Use OUTPUT INSERTED to capture the generated OrderID.
string query = "INSERT INTO Orders (CustomerName, OrderDate) OUTPUT INSERTED.OrderID VALUES (@CustomerName, GETDATE())";
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.AddWithValue("@CustomerName", customerName);
createdOrderId = (int)cmd.ExecuteScalar();
}
}
return createdOrderId;
}
// Updates the inventory by decrementing the quantity for a given product.
private bool UpdateInventory(int productId)
{
int affectedRows = 0;
// Open a connection to InventoryDB.
using (SqlConnection conn = new SqlConnection(connectionStringInventoryDB))
{
conn.Open();
// Define the query to decrement inventory.
string query = "UPDATE Inventory SET Quantity = Quantity - 1 WHERE ProductID = @ProductID";
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.AddWithValue("@ProductID", productId);
affectedRows = cmd.ExecuteNonQuery();
}
}
return affectedRows > 0;
}
// Compensating transaction that cancels an order.
// Cancels an order by deleting it.
// This is executed if a subsequent step in the saga fails.
private void CancelOrder(int orderId)
{
// Open a connection to OrdersDB to delete the order.
using (SqlConnection conn = new SqlConnection(connectionStringOrdersDB))
{
conn.Open();
// For demonstration, we simply delete the order.
// In a real-world scenario, you might update the order status to 'Cancelled'.
string query = "DELETE FROM Orders WHERE OrderID = @OrderID";
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.AddWithValue("@OrderID", orderId);
int affectedRows = cmd.ExecuteNonQuery();
if (affectedRows > 0)
Console.WriteLine($"Cancelling transaction: Order {orderId} has been cancelled successfully.");
else
Console.WriteLine($"Cancelling transaction failed: Order {orderId} could not be cancelled.");
}
}
}
}
}
Code Explanation:
- Saga Orchestrator: The OrderProcessingSaga class acts as the orchestrator. It coordinates the steps (order creation and inventory update) and handles any exceptions that occur.
- Local Transactions: Each database operation (creating an order and updating the inventory) is a separate local transaction. These transactions are not coordinated by MSDTC but are instead executed one after the other.
- Compensating Transaction: If updating the inventory fails after creating an order, the CancelOrder method is invoked to delete the order (or mark it as cancelled), thereby rolling back the saga.
Output:
When to Use ADO.NET Core Distributed Transactions Versus the Saga Pattern?
Use Distributed Transactions When:
- Multi-Database Operations: A single business operation updates multiple databases in an atomic fashion.
- Multiple Resource Managers: Consistency must be maintained across different types of resource managers (e.g., SQL Server and messaging systems).
- Strict Data Integrity Requirements: Critical operations (such as financial transactions or order processing) need to ensure that all parts of the operation succeed together.
- Avoiding Partial Updates: When any partial commit could lead to data anomalies or corruption, distributed transactions help enforce overall consistency.
Use the Saga Pattern When:
- Cross-Platform Needs: On Linux, macOS, or other non-Windows systems where MSDTC is not available.
- Long-Running Transactions: When operations are long-running and a strict atomic commit is impractical.
- Compensation Capability: When you can define clear compensating actions for each transaction step to recover from partial failures.
- Scalability: When the overhead and complexity of distributed transactions via MSDTC are not justified.
Distributed transactions in ADO.NET Core using TransactionScope (and optionally enabling implicit distributed transactions via MSDTC) provide a robust mechanism for ensuring data consistency across multiple databases. However, given the overhead and platform limitations (i.e., MSDTC being Windows-only), the Saga Pattern emerges as a popular alternative for managing distributed operations in microservices and cross-platform systems.
Both approaches, coordinated distributed transactions and the saga pattern, offer solutions for complex transactional scenarios. The choice between them depends on specific system requirements, operational constraints, and the need for immediate versus eventual consistency.
In the next article, I will discuss ADO.NET Core SqlBulkCopy with Examples. In this article, I explain ADO.NET Core Distributed Transaction with Examples. I would like your feedback. Please post your feedback, questions, or comments about this article.
About the Author: Pranaya Rout
Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.