ADO.NET Core Transaction Isolation Level

Back to: ADO.NET Core Tutorial For Beginners and Professionals

ADO.NET Core Transaction Isolation Level

In this article, I will discuss How to Implement Different Transaction Isolation Levels in ADO.NET Core Transaction with Examples. Please read our previous article discussing How to Implement Transactions in ADO.NET Core. At the end of this article, you will understand the following pointers:

  1. What are Concurrency Issues in a Transaction?
  2. What are ADO.NET Core Transaction Isolation Levels?
  3. How to Implement Transaction Isolation Level in ADO.NET Core Transactions?
  4. Example to Understand Transaction Isolation Level using ADO.NET Core
  5. Read Uncommitted Transaction Isolation Level Example using ADO.NET Core
  6. Read Committed Transaction Isolation Level using ADO.NET Core
  7. Repeatable Read Transaction Isolation Level using ADO.NET Core
  8. Serializable Transaction Isolation Level using ADO.NET Core
  9. Snapshot Transaction Isolation Level using ADO.NET Core
  10. What is the Default Transaction Isolation Level in ADO.NET Core Transaction?
  11. When Should We Use Which Transaction Isolation Level in ADO.NET Core?

What are Concurrency Issues in a Transaction?

Before understanding the Transaction Isolation Level, let us first understand the Concurrency Issues that might arise in a transaction. Understanding concurrency issues like Dirty Reads, Non-Repeatable Reads, and Phantom Reads is crucial for developing reliable and consistent database applications. These terms describe potential problems that can occur in database systems when multiple transactions are executed simultaneously without adequate isolation.

Dirty Reads

A Dirty Read occurs when a transaction reads data that has been modified by another transaction but has not yet been committed. Since the data might be rolled back if the modifying transaction fails or is rolled back, the reading transaction might end up with data that never actually exists in the database. This can lead to inconsistencies and erroneous operations in applications that rely on this data.

Example: Suppose Transaction 1 updates a row but hasn’t committed the change. Transaction 2 reads the same row before Transaction 1 commits or rolls back. If Transaction 1 rolls back, Transaction 2 has read data that was never committed, leading to a Dirty Read.

Non-Repeatable Reads

A Non-Repeatable Read occurs when a transaction reads the same row twice and gets different data each time. This happens when another transaction modifies or deletes that row between the two reads. Non-Repeatable Reads can cause confusion and errors in applications that expect consistent results from repeated reads within the same transaction.

Example: Transaction 1 reads a row. Transaction 2 then updates or deletes that row and commits the change. If Transaction 1 reads the same row again, it gets different data (or finds the row missing), leading to a Non-Repeatable Read.

Phantom Reads

A Phantom Read is a scenario where a transaction re-executes a query returning a set of rows that satisfy a search condition and finds that the set of rows has changed due to another recently committed transaction. This change can be due to new rows added by the other transaction that meet the query’s criteria. Phantom Reads concern the visibility of newly inserted or deleted records.

Example: Transaction 1 retrieves a list of rows that meet certain criteria. Transaction 2 inserts a new row that meets the same criteria and commits. If Transaction 1 re-executes the same query, it finds the new row, which did not exist in its initial read, hence experiencing a Phantom Read.

ADO.NET Core Transaction Isolation Level

ADO.NET Core, much like its predecessor ADO.NET, supports the concept of Transaction Isolation Levels. Transaction isolation levels are an essential feature for managing concurrency and consistency in database operations, ensuring that transactions are executed with the desired level of visibility of other transactions’ intermediate states. They help balance between the performance and data accuracy/integrity.

The isolation levels available in ADO.NET Core are consistent with those defined in the SQL standard and are implemented by most relational database systems. Here are the standard transaction isolation levels that are supported in ADO.NET Core:

  • Read Uncommitted: This is the lowest isolation level, where one transaction can see the changes made by other transactions even before they are committed. This level allows dirty reads, where a transaction might read data that another transaction has written but not yet committed. This can lead to inconsistencies but provides the highest level of concurrency.
  • Read Committed: This isolation level prevents dirty reads. A transaction can only see data changes that have been committed by other transactions. This level is a good balance between consistency and concurrency, preventing dirty reads but still allowing non-repeatable reads and phantom reads.
  • Repeatable Read: This level ensures that if a transaction reads a row, it can read that row again later in the transaction and see the same data, even if other transactions are modifying the database. This level prevents dirty reads and non-repeatable reads but still might allow phantom reads, where new rows can be added by other transactions.
  • Serializable: This is the highest isolation level, ensuring complete isolation from other transactions. It prevents dirty reads, non-repeatable reads, and phantom reads. This level makes a transaction appear as if it is the only transaction running in the system. However, it can significantly reduce concurrency and lead to performance bottlenecks due to locking.
  • Snapshot: Snapshot isolation level provides a view of the database from the point in time when the transaction started. It allows a transaction to work with a consistent snapshot of the database, preventing dirty reads, non-repeatable reads, and phantom reads. This isolation level is achieved by keeping versioned histories of data changes, allowing transactions to read older versions of data.

How to Implement Transaction Isolation Level in ADO.NET Core Transactions

Implementing transaction isolation levels in ADO.NET Core involves specifying the desired level of isolation when beginning a transaction. This is crucial for managing how transactions interact with each other, particularly in terms of visibility of changes made by other transactions.

Here is how to implement transaction isolation levels in ADO.NET Core, using a SQL Server database as an example. The same principles apply to other databases compatible with ADO.NET Core, with some variations in the specific isolation levels supported.

Step 1: Open a Database Connection

First, you need to establish a connection to your database. Use the SqlConnection object (for SQL Server) from the Microsoft.Data.SqlClient namespace.

using Microsoft.Data.SqlClient;

// Your database connection string
var connectionString = "YourConnectionStringHere";

// Create and open a connection to the database
using (var connection = new SqlConnection(connectionString))
{
    connection.Open();

    // Proceed to the next steps within this using block
}
Step 2: Begin a Transaction with a Specific Isolation Level

Once the connection is open, you can begin a transaction at your desired isolation level. Use the BeginTransaction method of the SqlConnection object, specifying the isolation level you need.

// Begin a transaction at the desired isolation level
var isolationLevel = System.Data.IsolationLevel.ReadCommitted; // Example isolation level
using (var transaction = connection.BeginTransaction(isolationLevel))
{
    // Proceed with your database operations within this transaction
}
Step 3: Perform Database Operations Within the Transaction

With the transaction started you can execute commands within its scope. Ensure that you associate your SqlCommand objects with both the connection and the transaction.

using (var command = connection.CreateCommand())
{
    command.Transaction = transaction;
    command.CommandText = "YOUR SQL COMMAND HERE";

    // Execute the command, for example, to insert, update, or delete records
    var result = command.ExecuteNonQuery();

    // Handle the result if necessary
}
Step 4: Commit or Rollback the Transaction

After executing your commands, decide whether to commit or roll back the transaction based on the success or failure of your operations.

try
{
    // Attempt to commit the transaction
    transaction.Commit();
}
catch
{
    // Roll back the transaction in case of an error
    transaction.Rollback();
    throw; // Re-throw the exception to handle it further or notify the user
}

Example to Understand Transaction Isolation Level using ADO.NET Core

Let us understand Each Transaction Isolation Level using ADO.NET Core with some examples. For this demo, we are going to use the following tables. So, please execute the following SQL Scripts on an SQL Server database:

CREATE DATABASE OrderDB;
GO

USE OrderDB;
GO

CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(100),
    Email NVARCHAR(100)
);

CREATE TABLE Product (
    ProductID INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(100),
    Price DECIMAL(18,2)
);

CREATE TABLE [Order] (
    OrderID INT PRIMARY KEY IDENTITY,
    CustomerID INT FOREIGN KEY REFERENCES Customer(CustomerID),
    OrderDate DATETIME
);

CREATE TABLE OrderItem (
    OrderItemID INT PRIMARY KEY IDENTITY,
    OrderID INT FOREIGN KEY REFERENCES [Order](OrderID),
    ProductID INT FOREIGN KEY REFERENCES Product(ProductID),
    Quantity INT
);

CREATE TABLE Payment (
    PaymentID INT PRIMARY KEY IDENTITY,
    OrderID INT FOREIGN KEY REFERENCES [Order](OrderID),
    PaymentDate DATETIME,
    Amount DECIMAL(18,2)
);

Also, execute the following SQL Script to insert some demo data:

-- Insert dummy Customers
INSERT INTO Customer (Name, Email) VALUES ('John Doe', 'john.doe@example.com'), ('Jane Smith', 'jane.smith@example.com');

-- Insert dummy Products
INSERT INTO Product (Name, Price) VALUES ('Laptop', 1200.00), ('Smartphone', 800.00);

-- Insert an Order for John Doe
INSERT INTO [Order] (CustomerID, OrderDate) VALUES (1, GETDATE());

-- Insert Order Items for the Order
INSERT INTO OrderItem (OrderID, ProductID, Quantity) VALUES (1, 1, 1), (1, 2, 2);

-- Insert Payment for the Order
INSERT INTO Payment (OrderID, PaymentDate, Amount) VALUES (1, GETDATE(), 2800.00);

Read Uncommitted Transaction Isolation Level Example using ADO.NET Core

Let us see an example of understanding the Read Uncommitted transaction isolation level with ADO.NET Core. Let’s focus on a scenario where you might want to see uncommitted data from another transaction. This isolation level allows a transaction to read data that has been modified but not yet committed by other transactions, leading to “dirty reads.” For a better understanding, please modify the Program class as follows:

using Microsoft.Data.SqlClient;
using System.Data;
namespace ADODOTNETCoreDemo
{
    internal class Program
    {
        static async Task Main(string[] args)
        {
            try
            {
                string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=OrderDB;Trusted_Connection=True;TrustServerCertificate=True;";

                // Start two tasks to simulate concurrent transactions
                Task t1 = Task.Run(() => Transaction1(connectionString));
                Task t2 = Task.Run(() => Transaction2(connectionString));

                await Task.WhenAll(t1, t2);
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Something went wrong: {ex.Message}");
            }
        }

        static void Transaction1(string connectionString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Use Read Uncommitted Isolation Level
                using (SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted))
                {
                    try
                    {
                        SqlCommand command = new SqlCommand("SELECT * FROM Product;", connection, transaction);
                        SqlDataReader reader = command.ExecuteReader();

                        while (reader.Read())
                        {
                            Console.WriteLine($"Product: {reader["Name"]}, Price: {reader["Price"]}");
                        }
                        reader.Close();

                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"Exception in Transaction1: {ex.Message}");
                        transaction.Rollback();
                    }
                }
            }
        }

        static void Transaction2(string connectionString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        // Simulate a long-running update operation
                        SqlCommand command = new SqlCommand("UPDATE Product SET Price = Price * 1.1;", connection, transaction);
                        command.ExecuteNonQuery();

                        // Wait for a bit before committing to simulate delay
                        Thread.Sleep(5000);

                        transaction.Commit();
                        Console.WriteLine("Transaction2: Prices updated.");
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"Exception in Transaction2: {ex.Message}");
                        transaction.Rollback();
                    }
                }
            }
        }
    }
}
Explanation:
  • Transaction1 uses the Read Uncommitted isolation level to select and read data from the Product table. It should be able to read uncommitted changes made by Transaction2.
  • Transaction2 performs an update on the Product table and simulates a delay before committing. This allows Transaction1 to read the uncommitted changes.

Execute the application to see how Transaction1 is able to read the uncommitted changes made by Transaction2. When you run the above code, you will get the following output:

Read Uncommitted Transaction Isolation Level Example using ADO.NET Core

Considerations:
  • Read Uncommitted is the least restrictive isolation level, which can lead to several issues, including dirty reads, non-repeatable reads, and phantom reads.
  • This isolation level can significantly increase performance because it does not acquire locks for select statements, allowing other transactions to continue without waiting.
  • However, use this isolation level with caution and only when you are willing to accept the risks associated with it in exchange for the performance benefit.

Read Committed Transaction Isolation Level using ADO.NET Core.

Let us see an example of understanding the Read Committed transaction isolation level using ADO.NET Core. Let’s focus on a scenario that ensures a transaction can only read data that has been committed by other transactions. This isolation level prevents dirty reads, which means you cannot read data from a transaction that is not yet committed, providing a more stable view of the database at the cost of potential blocking. For a better understanding, please modify the Program class as follows:

using Microsoft.Data.SqlClient;
using System.Data;
namespace ADODOTNETCoreDemo
{
    internal class Program
    {
        static async Task Main(string[] args)
        {
            try
            {
                string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=OrderDB;Trusted_Connection=True;TrustServerCertificate=True;";

                // Start two tasks to simulate concurrent transactions
                Task t1 = Task.Run(() => Transaction1(connectionString));
                Task t2 = Task.Run(() => Transaction2(connectionString));

                await Task.WhenAll(t1, t2);
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Something went wrong: {ex.Message}");
            }
        }

        static void Transaction1(string connectionString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Use Read Committed Isolation Level
                using (SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted))
                {
                    try
                    {
                        SqlCommand command = new SqlCommand("SELECT * FROM Product;", connection, transaction);
                        SqlDataReader reader = command.ExecuteReader();

                        while (reader.Read())
                        {
                            Console.WriteLine($"Product: {reader["Name"]}, Price: {reader["Price"]}");
                        }
                        reader.Close();

                        // Wait a bit to try and catch uncommitted changes from Transaction2 (which won't happen due to isolation level)
                        Thread.Sleep(5000);

                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"Exception in Transaction1: {ex.Message}");
                        transaction.Rollback();
                    }
                }
            }
        }

        static void Transaction2(string connectionString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        // Simulate a long-running update operation
                        SqlCommand command = new SqlCommand("UPDATE Product SET Price = Price * 1.1 WHERE ProductID = 1;", connection, transaction);
                        command.ExecuteNonQuery();

                        // Wait for a bit before committing to simulate delay
                        Thread.Sleep(2000);

                        transaction.Commit();
                        Console.WriteLine("Transaction2: Price updated for ProductID = 1.");
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"Exception in Transaction2: {ex.Message}");
                        transaction.Rollback();
                    }
                }
            }
        }
    }
}
Explanation:
  • Transaction1 uses the Read Committed isolation level to select and read data from the Product table. It tries to read data after Transaction2 has started but before it commits, showing how Read Committed prevents dirty reads.
  • Transaction2 performs an update on the Product table and commits after a delay. This delay is designed to test if Transaction1 can see uncommitted changes, which it should not due to the Read Committed isolation level.

Execute the application to observe how Transaction1 does not read the uncommitted changes made by Transaction2. When you run the above code, you will get the following output:

Read Committed Transaction Isolation Level using ADO.NET Core

Considerations
  • Read Committed is a widely used isolation level that strikes a balance between consistency and concurrency. It prevents dirty reads but does not protect against non-repeatable reads or phantom reads.
  • Transactions under this level will wait for other transactions to commit their changes before reading the affected data, which can lead to blocking and potentially affect performance under high load.
  • Despite these potential drawbacks, Read Committed remains a practical choice for many applications where absolute consistency for every read is not a critical requirement.

Repeatable Read Transaction Isolation Level using ADO.NET Core

Let us see an example of understanding the Repeatable Read Transaction Isolation Level using ADO.NET Core. Let’s focus on a scenario that emphasizes data consistency by ensuring that if a transaction reads a row, no other transaction can modify or delete that row until the first transaction completes. This isolation level prevents dirty reads and non-repeatable reads but does not protect against phantom reads. For a better understanding, please modify the Program class as follows:

using Microsoft.Data.SqlClient;
using System.Data;
namespace ADODOTNETCoreDemo
{
    internal class Program
    {
        static async Task Main(string[] args)
        {
            try
            {
                string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=OrderDB;Trusted_Connection=True;TrustServerCertificate=True;";
                // Start two tasks to simulate concurrent transactions with a slight delay between them
                Task t1 = Transaction1(connectionString);
                Task.Delay(1000).Wait(); // Ensure Transaction1 starts first
                Task t2 = Transaction2(connectionString);

                await Task.WhenAll(t1, t2);
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Something went wrong: {ex.Message}");
            }
        }

        static async Task Transaction1(string connectionString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Use Repeatable Read Isolation Level
                using (SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.RepeatableRead))
                {
                    try
                    {
                        SqlCommand command = new SqlCommand("SELECT * FROM Product WHERE ProductID = 1;", connection, transaction);
                        SqlDataReader reader = command.ExecuteReader();
                        if (reader.Read())
                        {
                            Console.WriteLine($"Transaction1: Product: {reader["Name"]}, Price: {reader["Price"]}");
                        }
                        reader.Close();

                        // Simulate some work by waiting
                        Console.WriteLine("Transaction1: Working...");
                        await Task.Delay(5000);

                        // Try to read again
                        reader = command.ExecuteReader();
                        if (reader.Read())
                        {
                            Console.WriteLine($"Transaction1: Product: {reader["Name"]}, Price: {reader["Price"]} (repeat read)");
                        }
                        reader.Close();

                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"Exception in Transaction1: {ex.Message}");
                        transaction.Rollback();
                    }
                }
            }
        }

        static async Task Transaction2(string connectionString)
        {
            await Task.Delay(2000); // Ensure this runs after Transaction1 starts
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Attempt to update the row read by Transaction1
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SqlCommand command = new SqlCommand("UPDATE Product SET Price = Price * 1.1 WHERE ProductID = 1;", connection, transaction);
                        int rowsAffected = command.ExecuteNonQuery();
                        if (rowsAffected > 0)
                        {
                            Console.WriteLine("Transaction2: Product price updated.");
                        }
                        else
                        {
                            Console.WriteLine("Transaction2: No product price update occurred.");
                        }

                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"Exception in Transaction2: {ex.Message}");
                        transaction.Rollback();
                    }
                }
            }
        }
    }
}
Explanation:
  • Transaction1 uses the Repeatable Read isolation level to read data from the Product table. It attempts to read the same data again after a delay, expecting the same data to be returned if it tries to read the same row again.
  • Transaction2 attempts to update a row that Transaction1 has read after a delay. Under the Repeatable Read isolation level, this update should be blocked until Transaction1 completes, ensuring repeatable reads for Transaction1.

Execute the application to observe how Transaction2’s attempt to update the row read by Transaction1 is handled. Transaction1 should be able to read the same data twice without seeing any changes, even though Transaction2 attempts an update. When you run the above code, you will get the following output:

Repeatable Read Transaction Isolation Level using ADO.NET Core

Considerations
  • Repeatable Read prevents dirty and non-repeatable reads but can lead to locking issues that may affect performance. It does not prevent phantom reads.

Serializable Transaction Isolation Level using ADO.NET Core

Let us see an example of understanding the Serializable Transaction Isolation Level using ADO.NET Core. Let’s consider a scenario that showcases the highest level of isolation. At this level, transactions are completely isolated from one another, preventing dirty reads, non-repeatable reads, and phantom reads. This is achieved by locking the rows involved in a transaction and preventing new rows from being added by other transactions that might affect the results of the current transaction. For a better understanding, please modify the Program class as follows:

using Microsoft.Data.SqlClient;
using System.Data;
namespace ADODOTNETCoreDemo
{
    internal class Program
    {
        static async Task Main(string[] args)
        {
            try
            {
                string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=OrderDB;Trusted_Connection=True;TrustServerCertificate=True;";
                // Start two tasks to simulate concurrent transactions with a slight delay between them
                Task t1 = Transaction1(connectionString);
                Task.Delay(1000).Wait(); // Ensure Transaction1 starts first
                Task t2 = Transaction2(connectionString);

                await Task.WhenAll(t1, t2);
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Something went wrong: {ex.Message}");
            }
        }

        static async Task Transaction1(string connectionString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Use Serializable Isolation Level
                using (SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.Serializable))
                {
                    try
                    {
                        SqlCommand command = new SqlCommand("SELECT * FROM Product WHERE ProductID = 1;", connection, transaction);
                        SqlDataReader reader = command.ExecuteReader();
                        if (reader.Read())
                        {
                            Console.WriteLine($"Transaction1: Product: {reader["Name"]}, Price: {reader["Price"]}");
                        }
                        reader.Close();

                        // Simulate some work by waiting
                        Console.WriteLine("Transaction1: Working...");
                        await Task.Delay(5000);

                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"Exception in Transaction1: {ex.Message}");
                        transaction.Rollback();
                    }
                }
            }
        }

        static async Task Transaction2(string connectionString)
        {
            await Task.Delay(2000); // Ensure this runs after Transaction1 starts
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Attempt to insert a new row which might affect Transaction1
                using (SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.Serializable))
                {
                    try
                    {
                        SqlCommand command = new SqlCommand("INSERT INTO Product (Name, Price) VALUES ('NewProduct', 500);", connection, transaction);
                        int rowsAffected = command.ExecuteNonQuery();
                        if (rowsAffected > 0)
                        {
                            Console.WriteLine("Transaction2: New product inserted.");
                        }
                        else
                        {
                            Console.WriteLine("Transaction2: No new product was inserted.");
                        }

                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"Exception in Transaction2: {ex.Message}");
                        transaction.Rollback();
                    }
                }
            }
        }
    }
}
Explanation:
  • Transaction1 uses the Serializable isolation level to read data from the Product table. It locks the data to ensure no changes can be made until it is completed.
  • Transaction2 attempts to insert a new row into the Product table. However, due to the Serializable isolation level used by Transaction1, this insert will be blocked until Transaction1 completes, demonstrating the prevention of phantom reads.

Execute the application to observe how Transaction2’s attempt to insert a new product is handled. Due to the Serializable isolation level, Transaction1 should complete its operation before Transaction2 can successfully insert a new row, ensuring complete isolation. When you run the above code, you will get the following output:

Serializable Transaction Isolation Level using ADO.NET Core

Considerations
  • Serializable provides the highest isolation level but at the cost of concurrency, potentially leading to significant performance overhead due to extensive locking.
  • This level is suitable for transactions requiring absolute consistency, such as financial operations, but it might be overkill for applications where such strict consistency is unnecessary.
  • Use this isolation level judiciously, considering the impact on application performance and user experience.

Snapshot Transaction Isolation Level using ADO.NET Core

Let us see an example of understanding the Snapshot Transaction Isolation Level using ADO.NET Core. Let’s create a scenario that showcases how this isolation level provides a view of the database from when the transaction starts, ignoring other concurrent transaction changes. It allows transactions to work with a consistent snapshot of the data, preventing dirty, non-repeatable, and phantom reads without locking resources. This isolation level is particularly useful for applications that require consistency without the performance penalty of extensive locking.

Preliminary Setup

Before using the Snapshot isolation level, ensure your SQL Server database is configured to allow it. This involves enabling snapshot isolation by setting the ALLOW_SNAPSHOT_ISOLATION options on the database. If not already done, execute the following SQL command against your database:

ALTER DATABASE [YourDatabaseName] SET ALLOW_SNAPSHOT_ISOLATION ON;

Please replace [YourDatabaseName] with the name of your database. Next, modify the Program class as follows:

using Microsoft.Data.SqlClient;
using System.Data;
namespace ADODOTNETCoreDemo
{
    internal class Program
    {
        static async Task Main(string[] args)
        {
            try
            {
                string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=OrderDB;Trusted_Connection=True;TrustServerCertificate=True;";
                // Start two tasks to simulate concurrent transactions with a slight delay between them
                Task t1 = Transaction1(connectionString);
                Task.Delay(1000).Wait(); // Ensure Transaction1 starts first
                Task t2 = Transaction2(connectionString);

                await Task.WhenAll(t1, t2);
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Something went wrong: {ex.Message}");
            }
        }

        static async Task Transaction1(string connectionString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Use Snapshot Isolation Level
                using (SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.Snapshot))
                {
                    try
                    {
                        SqlCommand command = new SqlCommand("SELECT * FROM Product WHERE ProductID = 1;", connection, transaction);
                        SqlDataReader reader = command.ExecuteReader();
                        if (reader.Read())
                        {
                            Console.WriteLine($"Transaction1: Product: {reader["Name"]}, Price: {reader["Price"]}");
                        }
                        reader.Close();

                        // Simulate some work by waiting
                        Console.WriteLine("Transaction1: Working...");
                        await Task.Delay(5000);

                        // Try to read again
                        reader = command.ExecuteReader();
                        if (reader.Read())
                        {
                            Console.WriteLine($"Transaction1: Product: {reader["Name"]}, Price: {reader["Price"]} (repeat read)");
                        }
                        reader.Close();

                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"Exception in Transaction1: {ex.Message}");
                        transaction.Rollback();
                    }
                }
            }
        }

        static async Task Transaction2(string connectionString)
        {
            await Task.Delay(2000); // Ensure this runs after Transaction1 starts
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Attempt to update the row read by Transaction1
                using (SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted))
                {
                    try
                    {
                        SqlCommand command = new SqlCommand("UPDATE Product SET Price = Price * 1.1 WHERE ProductID = 1;", connection, transaction);
                        command.ExecuteNonQuery();

                        transaction.Commit();
                        Console.WriteLine("Transaction2: Product price updated.");
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"Exception in Transaction2: {ex.Message}");
                        transaction.Rollback();
                    }
                }
            }
        }
    }
}
Explanation:
  • Transaction1 uses the Snapshot isolation level to read data from the Product table and attempts to read the same data again after a delay. Despite updates from Transaction2, it sees the data as it was at the start of the transaction.
  • Transaction2 performs an update on the Product table under the Read Committed isolation level after Transaction1 has started, demonstrating how Transaction1 remains unaffected by these concurrent changes.

When you run the above code, you will get the following output:

Snapshot Transaction Isolation Level using ADO.NET Core

What is the Default Transaction Isolation Level in ADO.NET Core Transaction?

In ADO.NET Core, the default transaction isolation level when you initiate a transaction without explicitly specifying the isolation level is Read Committed. This isolation level is designed to prevent dirty reads, ensuring that any data read within a transaction will only see data that has been committed in the database. However, it does not prevent non-repeatable reads or phantom reads.

When Should We Use Which Transaction Isolation Level in ADO.NET Core?

Choosing the appropriate transaction isolation level in ADO.NET Core depends on your application’s data consistency, integrity, and performance requirements. Here’s a guide on when to use each transaction isolation level, considering the balance between consistency (avoiding concurrency issues) and performance (higher concurrency and throughput).

Read Uncommitted
  • When to Use: Opted for Read Uncommitted when your application can tolerate dirty reads and you prioritize performance over data accuracy. This level is useful for operations requiring speed and unaffected by uncommitted changes, such as analytics on large volumes of data where absolute precision is not critical.
  • Considerations: Be cautious; this level can read uncommitted data that might never be committed, leading to potential inconsistencies.
Read Committed
  • When to Use: This is the default level for many databases and a good balance for general applications. Use it when you need to avoid dirty reads but can tolerate non-repeatable reads. It is suitable for most applications where seeing committed data is essential, but precise consistency in a transaction is not critical.
  • Considerations: It prevents dirty reads but still allows for possible non-repeatable reads.
Repeatable Read
  • When to Use: Choose Repeatable Read when your application logic requires that data read within a transaction remains unchanged during the transaction’s lifetime. It’s suitable for scenarios where data consistency and the ability to repeat reads accurately are more important than the potential decrease in concurrency.
  • Considerations: This level can significantly reduce concurrency by holding locks on all rows it reads, preventing other transactions from modifying them. Phantom reads are still possible.
Serializable
  • When to Use: Use the Serializable isolation level for critical transactions that must be completely isolated from concurrent modifications. It’s the highest level of isolation, ensuring total accuracy and consistency by making transactions appear as if they were executed serially.
  • Considerations: This level can lead to significant performance overhead due to extensive locking, potentially causing blockages and deadlocks. Opted for this level only when absolute consistency is necessary and the transaction volume is manageable.
Snapshot
  • When to Use: Snapshot isolation suits applications requiring high concurrency without sacrificing data consistency. It allows a transaction to work with a consistent database snapshot, preventing dirty, non-repeatable, and phantom reads. It’s ideal for systems where read consistency is critical, but the performance impact of locking is prohibitive.
  • Considerations: Additional storage is required for row versions and can have a higher impact on tempdb in SQL Server. It is best when the overhead of maintaining row versions is acceptable for the benefits of non-blocking reads.
Choosing the Right Level
  • Data Sensitivity: The more sensitive the data and operation, the higher the isolation level you might choose.
  • Performance vs. Consistency: Higher isolation levels increase consistency but can degrade performance. Analyze your application’s tolerance for stale or inconsistent data versus its need for speed.
  • Testing: Test under realistic workloads to understand the impact of different isolation levels on both performance and data integrity in your specific context.

In the next article, I will discuss ADO.NET Core Implicit vs Explicit Transaction with Examples. In this article, I explain the ADO.NET Core Transaction Isolation Level with Examples. I would like to have your feedback. Please post your feedback, questions, or comments about this ADO.NET Core Transaction Isolation Level article.

Leave a Reply

Your email address will not be published. Required fields are marked *