Transactions in .NET: From Basics to Best Practices

What is a Transaction in .NET?

A transaction is a sequence of operations performed as a single logical unit of work. Transactions are used to ensure the consistency and integrity of data in database systems.

They follow the ACID properties: Atomicity, Consistency, Isolation, and Durability. In the context of .NET, transactions can be managed through various classes and interfaces provided by the .NET Framework.

Importance of Transactions in Database Management

Transactions are crucial for maintaining data integrity and consistency in database systems.

They ensure that multiple operations occur atomically, either all succeeding or all failing, preventing partial updates that could leave the database in an inconsistent state.

Transactions also help to isolate concurrent operations, ensuring that each transaction’s changes are isolated from others until they’re committed.

Types of Transactions in .NET

Local Transactions

Local transactions are transactions that involve a single resource, such as a single database or a single message queue.

They are simpler and faster compared to distributed transactions, as they involve only one resource manager. In .NET, local transactions can be implemented using the TransactionScope class or the SqlTransaction class.

Distributed Transactions

Distributed transactions involve multiple resources, such as multiple databases or a combination of databases and message queues.

They are more complex and slower than local transactions, as they require coordination between multiple resource managers. In .NET, distributed transactions can be implemented using the TransactionScope class in conjunction with the System.Transactions namespace.

Working with Transactions in .NET

TransactionScope Class

Creating a TransactionScope

The TransactionScope class, available in the System.Transactions namespace, allows you to define a block of code that participates in a transaction.

To create a new transaction scope, you simply create a new instance of the TransactionScope class, like so:

using (TransactionScope scope = new TransactionScope())
{
    // Perform transactional operations here
}

Committing and Rolling Back Transactions

By default, a transaction will be committed when the TransactionScope is disposed. To commit the transaction, you can call the Complete method:

using (TransactionScope scope = new TransactionScope())
{
    // Perform transactional operations here

    scope.Complete();
}

If an exception occurs within the TransactionScope, the transaction will be rolled back automatically:

using (TransactionScope scope = new TransactionScope())
{
    try
    {
        // Perform transactional operations here

        scope.Complete();
    }
    catch (Exception ex)
    {
        // Handle the exception and let the transaction roll back
    }
}

SqlTransaction Class

Establishing a Connection

The SqlTransaction class, available in the System.Data.SqlClient namespace, allows you to manage transactions directly on a SQL Server database.

To use SqlTransaction, you first need to establish a connection to the database using the SqlConnection class:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    // Perform transactional operations here
}

Implementing SqlTransaction

Once you have an open connection, you can create a new instance of the SqlTransaction class by calling the BeginTransaction method on the SqlConnection object

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    using (SqlTransaction transaction = connection.BeginTransaction())
    {
        try
        {
            // Perform transactional operations here

            transaction.Commit();
        }
        catch (Exception ex)
        {
            // Handle the exception and roll back the transaction
            transaction.Rollback();
        }
    }
}

Isolation Levels in .NET Transactions

Read Uncommitted

This isolation level allows transactions to read uncommitted changes made by other transactions.

It is the lowest level of isolation and can lead to issues such as dirty reads, non-repeatable reads, and phantom reads.

Read Committed

Ensures that a transaction can only read committed changes made by other transactions.

It prevents dirty reads but can still result in non-repeatable reads and phantom reads.

Repeatable Read

Repeatable read level prevents dirty reads and non-repeatable reads by locking the data being read by a transaction.

However, it can still result in phantom reads.

Serializable

This is the highest level of isolation, which prevents dirty reads, non-repeatable reads, and phantom reads by locking the entire range of data being accessed by a transaction.

This level of isolation can lead to reduced concurrency and potential deadlocks.

Snapshot

This isolation level provides a snapshot of the data at the start of a transaction, allowing for consistent reads without acquiring locks.

It prevents dirty reads, non-repeatable reads, and phantom reads, while still allowing for high concurrency.

Best Practices for Implementing Transactions in .NET

1. Choose the appropriate transaction type: Use local transactions when working with a single resource and distributed transactions for multiple resources.
2. Use the correct isolation level: Select the isolation level that provides the necessary consistency guarantees without sacrificing performance.
3. Keep transactions short: Minimize the duration of transactions to reduce the potential for contention and deadlocks.
4. Handle exceptions properly: Ensure that transactions are rolled back in the case of an error or exception.
5. Close connections and dispose of resources: Always close database connections and dispose of transaction objects to prevent resource leaks.

Conclusion

Transactions are an essential part of maintaining data integrity and consistency in database systems. In .NET, you can work with transactions using classes such as TransactionScope and SqlTransaction.

By understanding the different types of transactions, isolation levels, and best practices, you can implement robust and efficient transactions in your .NET applications.

Comments

[Anthony Fung]

Interesting - thanks for this!

I understand the concept of a transaction, and can see why a SqlTransaction would be useful.

When/how might we create/use a TransactionScope instance? Would this be for NoSQL databases?

[nausaf]

Ordinarily, TransactionScope shouldn't ever need to be used if your code is running in the cloud. It belongs to an era before cloud and Kubernetes/PaaS-enabled microservices.

Single operations on transactional resources are normally ACID anyway (so a single update query on SQL Azure would run in its own transaction, in Cosmos which is a NoSQL DB from Microsoft, an update/insert etc. would be transactional and if you want to span multiple rows, the only transactional choice is to create a server-side stored proc which can only operate on a single logical particion). Behaviour should be broadly similar in Mongo also but I'm not sure.

But in cloud, multiple operations should never be wrapped in a transaction due to the fact that locks are held opened too long and this affects throughput and therefore scalability, even when the opreations are to the same cloud resource.

This would probably be worse when multiple resources providers (e.g. a two SQL Azure instances or a SQL Azure and a message) are involved because then the transaction is promoted by TransactionScope to a heavier-weight distribued transaction I think.

Updates (C, U and D operations) should be short and sweet in the cloud. If you have a batch processor, you can always use BEGIN TRAN and END TRAN in your SQL batches (not sure if a sproc is wrapped in a transaction by default in Azure SQL?) so again, TransactionScope shouldn't be needed.

ORMs like Entity Framework also batch the update (using the Unit of Work design pattern from Martin Fowler's PEAA book) internally; EF Core does it by wrapping the SQL batch it generates in BEGIN TRAN and END TRAN I am think)

If you do find yourself needing distributed transactions, which is really what TransactionScope is for, the code probably needs to be refactored into multiple microservices that communicate with each other asynchronously using event sourcing. This would indeed entail some head scratching but would be totally the way to go.

[Anthony Fung]

I've worked on a few code bases now, and this explains why I don't ever remember seeing code involving TransactionScope instances.

Thanks for the insights!

[ByteHide]

Thank you for your comment!

TransactionScope can be used with various types of data sources, not just NoSQL databases. It provides a simple way to define a block of code that participates in a transaction. You can use TransactionScope with any data source that has a resource manager supporting the .NET Framework's System.Transactions namespace.

The advantage of using TransactionScope is that it allows you to manage both local and distributed transactions in a consistent manner. So if you need to work with multiple resources (ex: multiple databases, message queues or a mix of SQL and NoSQL databases), TransactionScope is a good way manage the transaction across all these resources :)

[Anthony Fung]

Thanks for clarifying!