[TOC]
In business development, using database transactions is essential. Various ORMs are often used to execute database operations, simplifying code complexity. However, due to the encapsulation characteristics of different ORMs, developers may have varying usage patterns, making it challenging to understand how ORM handles transactions. Therefore, this article introduces the basics of database transactions, Ado.net transactions, and how to encapsulate DbContext. After mastering this content, readers can deepen their understanding of using transactions in C# and become more adept at working with various ORMs.
Generating Database Data
To demonstrate various transaction operations, we first want to create demo data. Open the filldb website and follow the prompts to generate mock data.
filldb address: https://filldb.info/dummy/step1
FillDB is a free tool that can quickly generate a large amount of custom data in MySQL format for software testing and populating databases with random data.
Then, click on Generate in the order of authors and posts to generate database data.
Since posts have a foreign key to authors, the data generation order is authors, then posts.
Finally, click on Export database to export the SQL.
Then, import the data into the database.
To connect to the MySQL database, we use the MySqlConnector driver. Please introduce this package via NuGet after creating a console project.
The main components and APIs of MySqlConnector are as follows:
| ADO.NET Type | Description | Asynchronous Method | Synchronous Method |
| ------------- | ----------- | ------------------------ | --------------------- |
| DbConnection | Connector | OpenAsync | Open |
| DbConnection | | BeginTransactionAsync | BeginTransaction |
| DbCommand | Execute Command | ExecuteNonQueryAsync | ExecuteNonQuery |
| DbCommand | | ExecuteReaderAsync | ExecuteReader |
| DbCommand | | ExecuteScalarAsync | ExecuteScalar |
| DbDataReader | Read Data | NextResultAsync | NextResult |
| DbDataReader | | ReadAsync | Read |
| DbTransaction | Database Transaction | CommitAsync | Commit |
| DbTransaction | | RollbackAsync | Rollback |
Using synchronous methods can adversely affect the managed thread pool and may slow down or lock the application if not properly tuned.
Example of MySQL connection string configuration:
const string connectionString = "Server=localhost;Port=3306;User ID=mysqltest;Password=Password123;Database=mysqldb";
Or use MySqlConnectionStringBuilder to build the connection string:
var connectionBuilder = new MySqlConnectionStringBuilder()
{
Server = "localhost",
Port = 3306,
UserID = "mysqltest",
Password = "Password123",
Database = "mysqldb"
};
var connectionString = connectionBuilder.ConnectionString;
Detailed connection string configurations can be found at https://mysqlconnector.net/connection-options/.
To enable logging with MySqlConnector, you need to manually configure the logging program.
The complete NuGet package is as follows:
<ItemGroup>
<PackageReference Include="Microsoft.Extensions.Logging" Version="8.0.0" />
<PackageReference Include="Microsoft.Extensions.Logging.Console" Version="8.0.0" />
<PackageReference Include="MySqlConnector" Version="2.3.1" />
<PackageReference Include="MySqlConnector.Logging.Microsoft.Extensions.Logging" Version="2.1.0" />
</ItemGroup>
The complete code example to configure the connection string, set up logging, and create a database connection is as follows:
var loggerFactory = LoggerFactory.Create(builder => builder.AddConsole());
var logger = loggerFactory.CreateLogger<Program>();
var dataSourceBuilder = new MySqlDataSourceBuilder(connectionString);
dataSourceBuilder.UseLoggerFactory(loggerFactory);
await using var dataSource = dataSourceBuilder.Build();
using var connection = dataSource.CreateConnection();
After the above configurations, we have a mock database and basic code. Next, let’s formally learn about MySqlConnector and database transactions.
Basics of MySQL Database Transactions
According to Baidu Baike: A database transaction is a sequence of database operations that accesses and may manipulate various data items; these operations either all succeed or all fail and roll back, representing an indivisible unit of work. A transaction consists of all database operations executed between the start and end of the transaction.
Database transactions have four characteristics:
- Atomicity: Atomicity means that the operations within a transaction either all execute successfully or all fail and roll back.
- Consistency: Consistency means the state before and after a transaction remains consistent.
- Isolation: Modifications made by one transaction are not visible to other transactions until the transaction is committed.
- Durability: Once data is committed, its changes are permanently saved to the database.
I believe everyone is familiar with database transactions, so I won't dwell on the definitions. Now let's discuss the characteristics of different database transactions.
Concurrency Consistency Issues in Databases
While database transactions help us execute operations and rollbacks, concurrent execution of transactions may lead to interference, such as dirty reads and phantom reads. When using database transactions, we need to balance between strictness and performance to choose the transaction isolation level.
When multiple transactions execute concurrently, the following issues may arise:
Dirty Read
Transaction A updates data but has not yet committed. At this moment, Transaction B reads the updated data from Transaction A. If Transaction A rolls back, the data Transaction B read becomes dirty data.
Non-Repeatable Read
Transaction A reads data multiple times, and during these reads, Transaction B performs an update operation and commits it, causing Transaction A to read inconsistent data.
Non-repeatable read means the same data is read differently at various stages of Transaction A.
Phantom Read
After Transaction A reads data, Transaction B inserts several entries among the data read by Transaction A, leading to Transaction A reading additional entries the next time it queries.
Phantom read means the number of records read is different before and after.
Lost Update
Both Transaction A and Transaction B modify the same data. Transaction A modifies first, then Transaction B modifies it. As a result, Transaction B’s modifications overwrite Transaction A’s.
Non-repeatable reads and phantom reads seem quite similar, but their primary distinction is: In non-repeatable reads, data inconsistency is mainly due to data being updated. In phantom reads, inconsistency arises mainly from the addition or deletion of data.
Transaction Isolation Levels
There are four isolation levels for database transactions, ranked from low to high:
- Read Uncommitted: A transaction's modifications are visible to other transactions before it commits.
- Read Committed: A transaction's modifications become visible to other transactions only after it commits.
- Repeatable Read: Data read multiple times within the same transaction is consistent.
- Serializable: Requires locking mechanisms and forces transactions to be executed serially.
In Ado.net, the various database transaction isolation levels are represented by the System.Data.IsolationLevel enumeration:
public enum IsolationLevel
{
// Unspecified
Unspecified = -1,
// Cannot overwrite pending changes from higher isolation transactions.
Chaos = 16,
// Read Uncommitted, allowing dirty reads, which means no shared locks are held, nor are exclusive locks used.
ReadUncommitted = 256,
// Read Committed, holding shared locks while reading data to prevent dirty reads, but data can change before transaction ends, leading to non-repeatable reads or phantom data.
ReadCommitted = 4096,
// Repeatable Read, locking all data used in the query to prevent other users from updating data. Prevents non-repeatable reads but phantom rows may still occur.
RepeatableRead = 65536,
// Serializable, placing a range lock on the DataSet to prevent other users from updating or inserting rows until the transaction is complete.
Serializable = 1048576,
// Reduces blocking by storing data versions that one application can read while another is modifying the same data.
// Indicates that changes made in other transactions cannot be seen by one transaction even on re-query.
Snapshot = 16777216
}
These isolation levels can eliminate issues such as dirty reads, non-repeatable reads, and phantom reads.
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read |
| :-------------: | :--------: | :------------------: | :----------: |
| Read Uncommitted | Allowed | Allowed | Allowed |
| Read Committed | Not Allowed | Allowed | Allowed |
| Repeatable Read | Not Allowed | Not Allowed | Allowed |
| Serializable | Not Allowed | Not Allowed | Not Allowed |
In fact, there's no need to get too hung up on these issues; it can be understood based on read/write lock scenarios.
In programming, when multiple threads concurrently operate on two dictionaries:
Dictionary<string, string> a;
Dictionary<string, string> b;
The first problem arises when concurrent operations occur on one dictionary, leading to thread concurrency exceptions.
Thus, we want to use a concurrent dictionary:
ConcurrentDictionary<string, string> a;
ConcurrentDictionary<string, string> b;
However, when thread T1 completes modifications on a and then modifies b, if thread T2 modifies dictionary a, this leads to data inconsistency.
Using read/write locks to optimize, we bundle together dictionaries a and b:
ConcurrentDictionary<string, string> a;
ConcurrentDictionary<string, string> b;
private static ReaderWriterLockSlim _lock = new ReaderWriterLockSlim();
// Read
private void Read()
{
try
{
_lock.EnterReadLock();
// Reading
}
catch { }
finally
{
_lock.ExitReadLock(); // Release read lock
}
}
// Write
public void Write(int key, int value)
{
try
{
_lock.EnterUpgradeableReadLock();
_lock.EnterWriteLock();
// Writing
_lock.ExitWriteLock();
}
catch { }
finally
{
_lock.ExitUpgradeableReadLock();
}
}
The principle of read/write locks is straightforward: reading and writing are conflicting operations. When no thread is "writing," multiple threads can read concurrently without issues. When one thread is "writing," no other thread is allowed to write or read simultaneously. In other words, "reading" is concurrent, but "writing" is exclusive.
Serializable:
Of course, database transactions are more complicated. If we were to implement a read/write lock model, the isolation level would be akin to "serializable," where the entire table is locked, preventing concurrent execution of transactions. At this point, there would be no dirty reads, non-repeatable reads, or phantom reads.
However, this puts significant pressure on the database and will severely degrade its performance while greatly reducing the concurrency of business applications.
When transaction A only needs to modify data with id=1,2,3, using the serializable level would lock the entire table. This seems wasteful.
Repeatable Read:
So, can we simply lock the specific rows that transaction A is modifying? We can lower the isolation level of the database transaction to use "repeatable read."
Using a repeatable read transaction level, the locked data remains secure from modification by other transactions, preventing dirty reads and non-repeatable reads. However, because not the entire table is locked, other transactions can still insert data, which may lead to phantom reads. Generally, the issues caused by repeatable reads often arise when transactions handle only their intended data.
An example of a phantom read problem caused by repeatable read could be: Transaction A applies a 10% discount to all models in a "notebook" category, while Transaction B inserts several models of "ideal notebooks" into this category. Consequently, when Transaction A queries the final data, it includes both discounted and non-discounted models.
InnoDB uses MVCC to achieve high concurrency while implementing all four SQL standard isolation levels. InnoDB defaults to the REPEATABLE READ isolation level, employing gap locking (next-key locking) strategies to prevent phantom reads at this isolation level. InnoDB locks not only the rows involved in the query but also the gaps in the index structure to prevent phantom rows from being inserted.
Read Committed:
Usage example:
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
UPDATE pet SET NAME = 'A';
SELECT SLEEP(5);
SELECT * from pet;
COMMIT;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
UPDATE pet SET NAME = 'B';
SELECT SLEEP(5);
SELECT * from pet;
COMMIT;
Transaction A and Transaction B are running concurrently, both modifying name, but the transactions can only see their own changes. That means before Transaction B commits, both A and B can modify the data, but they remain isolated from each other.
Transaction A changes name to "A" while Transaction B changes it to "B." Before committing, A and B read "A" and "B," respectively, which is fine; there is no interference.
However, if Transaction A commits first, the database name value becomes "A," and at this point, Transaction B has not yet committed; thus, B reads name as "A." This leads to a non-repeatable read.
Read Committed only guarantees data isolation for uncommitted transactions. Once another transaction commits, it can result in the current transaction seeing different data over time.
Read Uncommitted:
This is quite chaotic; it guarantees nothing.
When it comes to understanding database transactions, reversing the order of explanation might be easier to comprehend.
Differences Between BeginTransaction() and TransactionScope
In C# Ado.net, there are mainly two ways to manage transactions:
// Method 1:
using var tran = await connection.BeginTransactionAsync();
// Method 2:
using (TransactionScope transactionScope = new TransactionScope())
{
}
BeginTransaction() is initiated by an IDbConnection object and only affects the current IDbConnection. By calling the BeginTransaction() method on the database connection object, a database transaction is explicitly started, and thus does not conflict with synchronous and asynchronous methods.
TransactionScope encapsulates several APIs internally. Within the scope defined by TransactionScope, there's no need to explicitly call Commit() or Rollback() methods. It allows cross-IDbConnection usage, and requires extra configuration when used in asynchronous methods.
The primary difference is that BeginTransaction() explicitly manages transactions, while TransactionScope provides a more convenient automatic transaction management mechanism at the programming model level.
There are many code encapsulations related to transactions in the System.Transactions namespace. Readers can explore the documentation here:
https://learn.microsoft.com/en-us/dotnet/api/system.transactions?view=net-8.0
Now, let’s explain in detail the differences between the two transaction initiation methods.
BeginTransaction()
Let's first discuss BeginTransaction(), which returns a DbTransaction type.
Opening a transaction with BeginTransaction() is straightforward, but it requires manually setting the transaction property for IDbCommand.
await connection.OpenAsync();
// First open the transaction, then create the command
using var tran = await connection.BeginTransactionAsync();
using var command = new MySqlCommand()
{
Connection = connection,
// Note here
Transaction = tran
};
try
{
command.CommandText = "... ...";
await command.ExecuteNonQueryAsync();
if(...)
{
await tran.CommitAsync();
}else
{
await tran.RollbackAsync();
}
}
catch (Exception ex)
{
await tran.RollbackAsync();
logger.LogError(ex, "Tran error");
}
BeginTransaction() is defined as follows:
ValueTask<MySqlTransaction> BeginTransactionAsync(IsolationLevel isolationLevel,
CancellationToken cancellationToken = default)
DbTransaction can also set savepoints.
using var tran = await connection.BeginTransactionAsync();
try
{
command.CommandText = "... ...";
await command.ExecuteNonQueryAsync();
// Savepoint
await tran.SaveAsync("stepa");
// Release savepoint, rollback to this savepoint
if(...)
{
await tran.ReleaseAsync("stepa");
}
}
The usage of BeginTransaction() is quite simple and not prone to errors.
No Manual Rollback Required
Many times, we will rollback the transaction in catch{} as shown in the code below.
try
{
... ...
await tran.CommitAsync();
}
catch (Exception ex)
{
logger.LogError(ex, "Tran error");
await tran.RollbackAsync();
}
In fact, when a transaction in IDbConnection or IDbCommand is not explicitly committed, the transaction will automatically rollback when the object's lifetime ends, when the connection is actively disconnected, or when it is reclaimed by the connection pool. As long as there is no explicit commit, the previous operations are invalid.
For example, when we execute the SQL below, a new record will be inserted into the posts table with id 101.
-- Start transaction
BEGIN; -- or use START TRANSACTION;
INSERT INTO demo.posts (id, author_id, title, description, content, date)
VALUES (101, 1, 'test', 'test', 'test', '2023-12-08');
COMMIT;
However, when executing the code below, since CommitAsync() method was not called to commit the transaction, the data inserted into the database won’t take effect after the program ends.
using var connection = dataSource.CreateConnection();
await connection.OpenAsync();
using var tran = await connection.BeginTransactionAsync();
using var command = new MySqlCommand()
{
Connection = connection,
Transaction = tran
};
try
{
command.CommandText = """
INSERT INTO demo.posts (id, author_id, title, description, content, date)
VALUES (102, 1, 'test', 'test', 'test', '2023-12-08');
""";
await command.ExecuteNonQueryAsync();
// await tran.CommitAsync();
}
catch (Exception ex)
{
logger.LogError(ex, "Tran error");
}
TransactionScope
As shown in the following code, although no error is reported during code execution, it is not controlled by the transaction, meaning that even though it wasn’t committed, a new entry was genuinely inserted into the database.
This is because the transaction was not effective at all, only database connections opened within TransactionScope take effect.
using var connection = dataSource.CreateConnection();
await connection.OpenAsync();
using (TransactionScope transactionScope = new TransactionScope())
{
var command = connection.CreateCommand();
try
{
command.CommandText =
"""
INSERT INTO demo.posts (id, author_id, title, description, content, date)
VALUES (103, 1, 'test', 'test', 'test', '2023-12-08');
""";
await command.ExecuteNonQueryAsync();
//transactionScope.Complete();
}
catch (Exception ex)
{
logger.LogError(ex, "Tran error");
}
}
Corrected version:
using (TransactionScope transactionScope = new TransactionScope())
{
using var connection = dataSource.CreateConnection();
await connection.OpenAsync();
var command = connection.CreateCommand();
try
{
command.CommandText =
"""
INSERT INTO demo.posts (id, author_id, title, description, content, date)
VALUES (104, 1, 'test', 'test', 'test', '2023-12-08');
""";
await command.ExecuteNonQueryAsync();
//transactionScope.Complete();
}
catch (Exception ex)
{
logger.LogError(ex, "Tran error");
}
}
However, the above code will still throw an error. This is because TransactionScope does not support asynchronous methods by default, and the code uses asynchronous operations, leading to a situation where it is released without using the same thread.
System.InvalidOperationException:“A TransactionScope must be disposed on the same thread that it was created.”
Of course, TransactionScope supports asynchronous operations; we just need to enable the configuration.
using (TransactionScope transactionScope =
new TransactionScope(asyncFlowOption: TransactionScopeAsyncFlowOption.Enabled))
{
using var connection = dataSource.CreateConnection();
await connection.OpenAsync();
var command = connection.CreateCommand();
try
{
command.CommandText =
"""
INSERT INTO demo.posts (id, author_id, title, description, content, date)
VALUES (104, 1, 'test', 'test', 'test', '2023-12-08');
""";
await command.ExecuteNonQueryAsync();
//transactionScope.Complete();
}
catch (Exception ex)
{
logger.LogError(ex, "Tran error");
}
}
As shown in the code below, after executing the code and failing to explicitly commit the transaction, no actual data will be inserted into the database.
using (TransactionScope transactionScope =
// Enable support for async
new TransactionScope(asyncFlowOption: TransactionScopeAsyncFlowOption.Enabled))
{
using var connection = dataSource.CreateConnection();
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = """
INSERT INTO demo.posts (id, author_id, title, description, content, date)
VALUES (105, 1, 'test', 'test', 'test', '2023-12-08');
""";
await command.ExecuteNonQueryAsync();
//transactionScope.Complete();
}
After gaining experience, we find that if we do not call the Complete() method, then data will not actually be inserted into the database.
However, the question arises: since the IDbConnection is created and opened within the TransactionScope, it means the scope of TransactionScope is greater than IDbConnection. Therefore, after releasing IDbConnection, can we still commit TransactionScope?
using (TransactionScope transactionScope = new TransactionScope(asyncFlowOption: TransactionScopeAsyncFlowOption.Enabled))
{
using (var connection = dataSource.CreateConnection())
{
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = """
INSERT INTO demo.posts (id, author_id, title, description, content, date)
VALUES (105, 1, 'test', 'test', 'test', '2023-12-08');
""";
await command.ExecuteNonQueryAsync();
}
transactionScope.Complete();
}
The answer is everything works fine.
Here’s a simplified version of the code:
using (TransactionScope transactionScope = ...)
{
using (var connection = dataSource.CreateConnection())
{
await connection.OpenAsync();
await command.ExecuteNonQueryAsync();
}
transactionScope.Complete();
}
Although the IDbConnection is within a using block and transactionScope.Complete() is outside of the using block, transactions can still take effect. If Complete() is called, the transaction is committed. If Complete() is not called, the transaction will not be committed.
Returning to the first code example in this section, the issue of transactions not being effective has been identified as caused by the IDbConnection not being created within the TransactionScope.
However, for ASP.NET Core applications, ORM in context format, repository pattern ORM, etc., since they are encapsulated within a context, it is unlikely that developers will manually open IDbConnection.Open() when using TransactionScope. However, most of these ORM frameworks have encapsulated the functionalities, and various encapsulation methods are also discussed at the end of this article.
Summary
Transactions created through BeginTransaction() will not experience issues due to asynchronous operations, as they explicitly take effect within an IDbCommand and IDbConnection.
using var tran = await connection.BeginTransactionAsync();
using var command = new MySqlCommand()
{
Connection = connection,
// Note here
Transaction = tran
};
Therefore, using .BeginTransactionAsync() for transactions is the simplest and least error-prone method, and it is clear which IDbCommand the transaction is utilized in, making it relatively straightforward to debug when issues arise.
On the other hand, for TransactionScope, the author has spent considerable effort experimenting and explaining that TransactionScope implements implicit transactions within a transaction scope, and using it can be challenging and prone to errors.
Whether DML Can Use Transactions
At the beginning, the author did not consider this aspect until it came up during a casual conversation with a colleague.
The transactions in MySQL are ineffective for operations such as deleting tables or creating tables; they affect only operations related to table data, namely insert, update, and delete statements.
As shown in the SQL below, although the transaction was rolled back, the view was still created in the end.
-- Start transaction
USE demo;
BEGIN;
CREATE VIEW v_posts AS SELECT * FROM posts;
ROLLBACK;
-- COMMIT;
Sequential Multiple Operations
Starting with TransactionScope, the situation is as shown in the following code:
TransactionScope contains and creates two IDbConnection, and both IDbConnection insert data.
In other words, TransactionScope manages multiple IDbConnection simultaneously.
<br />
```csharp
using (TransactionScope transactionScope = new TransactionScope(asyncFlowOption: TransactionScopeAsyncFlowOption.Enabled))
{
using (var connection = dataSource.CreateConnection())
{
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = """
INSERT INTO demo.posts (id, author_id, title, description, content, date)
VALUES (108, 1, '测试', '测试', '测试', '2023-12-08');
""";
await command.ExecuteNonQueryAsync();
}
using (var connection = dataSource.CreateConnection())
{
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = """
INSERT INTO demo.posts (id, author_id, title, description, content, date)
VALUES (109, 1, '测试', '测试', '测试', '2023-12-08');
""";
await command.ExecuteNonQueryAsync();
}
//transactionScope.Complete();
}
This is acceptable. The TransactionScope manages all IDbConnection objects within its scope, ensuring they remain consistent under the current transaction.
However, BeginTransaction() created using IDbConnection.BeginTransaction() cannot be used across IDbConnection instances.
For example, the following code will throw an error:
using var connection1 = dataSource.CreateConnection();
using var connection2 = dataSource.CreateConnection();
await connection1.OpenAsync();
await connection2.OpenAsync();
try
{
var tran1 = connection1.BeginTransaction();
var command1 = connection1.CreateCommand();
command1.Transaction = tran1;
var command2 = connection2.CreateCommand();
command2.Transaction = tran1;
command1.CommandText = """
INSERT INTO demo.posts (id, author_id, title, description, content, date)
VALUES (108, 1, '测试', '测试', '测试', '2023-12-08');
""";
await command1.ExecuteNonQueryAsync();
command2.CommandText = """
INSERT INTO demo.posts (id, author_id, title, description, content, date)
VALUES (108, 1, '测试', '测试', '测试', '2023-12-08');
""";
await command2.ExecuteNonQueryAsync();
tran1.Commit();
}
catch (Exception ex)
{
logger.LogError(ex, "Tran error");
}
Thus, there is a distinction here.
Nested Transactions
.BeginTransaction() does not support nested transactions. The code is as follows:
static async Task Main(string[] args)
{
using var connection = dataSource.CreateConnection();
await connection.OpenAsync();
var tran = connection.BeginTransaction();
try
{
var command = connection.CreateCommand();
command.Transaction = tran;
command.CommandText = """
INSERT INTO demo.posts (id, author_id, title, description, content, date)
VALUES (110, 1, '测试', '测试', '测试', '2023-12-08');
""";
await command.ExecuteNonQueryAsync();
// Nested transaction
try
{
await InsertAsync(connection);
}
catch (Exception ex)
{
logger.LogError(ex, "Tran error.");
await tran.RollbackAsync();
return;
}
await tran.RollbackAsync();
}
catch (Exception ex)
{
logger.LogError(ex, "Tran error");
}
}
// Nested sub-transaction
private static async Task InsertAsync(MySqlConnection connection)
{
var tran = connection.BeginTransaction();
var command = connection.CreateCommand();
command.Transaction = tran;
command.CommandText = """
INSERT INTO demo.posts (id, author_id, title, description, content, date)
VALUES (112, 1, '测试', '测试', '测试', '2023-12-08');
""";
await command.ExecuteNonQueryAsync();
await tran.CommitAsync();
}
When an IDbConnection calls .BeginTransaction() twice, it will throw the following error:
System.InvalidOperationException: Transactions may not be nested.
Therefore, we can only rely on TransactionScope.
Using TransactionScope for nested transactions allows for flexible logic customization, where each nested sub-transaction has its own logic.
Each sub-transaction simply needs to write its own TransactionScope normally. Even if the sub-transaction's TransactionScope has been completed, if the outer TransactionScope has not been committed, none of the transactions will be committed.
The following code demonstrates this:
static async Task Main(string[] args)
{
using var connection = dataSource.CreateConnection();
using (TransactionScope transactionScope = new TransactionScope(asyncFlowOption: TransactionScopeAsyncFlowOption.Enabled))
{
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = """
INSERT INTO demo.posts (id, author_id, title, description, content, date)
VALUES (110, 1, '测试', '测试', '测试', '2023-12-08');
""";
await command.ExecuteNonQueryAsync();
// Nested transaction
try
{
await InsertAsync(connection);
}
catch (Exception ex)
{
logger.LogError(ex, "Tran error.");
return;
}
// transactionScope.Complete();
}
}
// Nested sub-transaction
private static async Task InsertAsync(MySqlConnection connection)
{
using (TransactionScope transactionScope = new TransactionScope(asyncFlowOption: TransactionScopeAsyncFlowOption.Enabled))
{
var command = connection.CreateCommand();
command.CommandText = """
INSERT INTO demo.posts (id, author_id, title, description, content, date)
VALUES (112, 1, '测试', '测试', '测试', '2023-12-08');
""";
await command.ExecuteNonQueryAsync();
transactionScope.Complete();
}
}
Although the transaction in InsertAsync() has already been committed, because it is affected by the outer TransactionScope transaction, if the outer transaction is not committed, the sub-transaction will not be committed either.
Of course, this can work even if they are not the same IDbConnection.
static async Task Main(string[] args)
{
using var connection = dataSource.CreateConnection();
using (TransactionScope transactionScope = new TransactionScope(asyncFlowOption: TransactionScopeAsyncFlowOption.Enabled))
{
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = """
INSERT INTO demo.posts (id, author_id, title, description, content, date)
VALUES (110, 1, '测试', '测试', '测试', '2023-12-08');
""";
await command.ExecuteNonQueryAsync();
// Nested transaction
try
{
await InsertAsync();
}
catch (Exception ex)
{
logger.LogError(ex, "Tran error.");
return;
}
// transactionScope.Complete();
}
}
// Nested sub-transaction
private static async Task InsertAsync()
{
using var connection = dataSource.CreateConnection();
using (TransactionScope transactionScope = new TransactionScope(asyncFlowOption: TransactionScopeAsyncFlowOption.Enabled))
{
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = """
INSERT INTO demo.posts (id, author_id, title, description, content, date)
VALUES (112, 1, '测试', '测试', '测试', '2023-12-08');
""";
await command.ExecuteNonQueryAsync();
transactionScope.Complete();
}
}
Thus, each method's code only needs to focus on its own logic, which is useful for module separation and responsibility separation in code.
Transaction Scope
As mentioned earlier, TransactionScope handles nested transactions.
TransactionScope has a TransactionScopeOption enumeration for configuration.
public enum TransactionScopeOption
{
// This scope requires a transaction. If an ambient transaction already exists, it will use that. Otherwise, a new transaction is created before entering the scope. This is the default value.
Required = 0,
// Always creates a new transaction for this scope.
RequiresNew = 1,
// If the scope is instantiated with Suppress, it will not participate in a transaction, regardless of whether an ambient transaction exists. The scope instantiated with this value will always have null as its ambient transaction.
Suppress = 2
}
Example usage:
using(TransactionScope scope1 = new TransactionScope())
{
// Default supports nesting
using(TransactionScope scope2 = new TransactionScope(TransactionScopeOption.Required))
{
//...
}
// Not affected by scope1
using(TransactionScope scope3 = new TransactionScope(TransactionScopeOption.RequiresNew))
{
//...
}
// If suppress is used to instantiate the scope, it will not participate in any transaction, regardless of ambient transaction existence.
using(TransactionScope scope4 = new TransactionScope(TransactionScopeOption.Suppress))
{
//...
}
}
Readers can learn more about nested transaction scopes from this article: https://learn.microsoft.com/en-us/previous-versions/ms172152(v=vs.90)?redirectedfrom=MSDN#Y1642
Encapsulating DbContext
As mentioned, IDbConnection needs to be opened within a TransactionScope for the TransactionScope to manage its transactional connections.
However, some database drivers already support TransactionScope, meaning that even without opening the connection within it, they can still be used. For instance, the EFCore framework automatically manages the lifecycle of IDbConnection, so we often do not manage the connection manually; thus, it's not common to see:
MyContext _context;
using (TransactionScope transactionScope = ...)
{
_context.Connection.Open()
}
Typically, the connection has already been opened before using database transactions.
MyContext _context;
_context.SelectAsync()....
_context.User.SectAsync()....
using (TransactionScope transactionScope = ...)
{
}
Therefore, we need to encapsulate a context type that can automatically use the context's transactions once the connection is opened.
TransactionScope
Encapsulating a database context, when executing commands, if it finds itself within a transaction scope, it will proactively use the context's transaction.
public class DbContext
{
private readonly DbConnection _connection;
public DbContext(DbConnection connection)
{
_connection = connection;
}
public async Task ExecuteAsync(string sql)
{
var command = _connection.CreateCommand();
// Get current transaction
var tran = Transaction.Current;
if (tran != null)
{
// Note this part...
_connection.EnlistTransaction(tran);
}
command.CommandText = sql;
await command.ExecuteNonQueryAsync();
}
}
Usage example:
using var connection = dataSource.CreateConnection();
// Open outside
await connection.OpenAsync();
var context = new DbContext(connection);
using (TransactionScope transactionScope = new TransactionScope(asyncFlowOption: TransactionScopeAsyncFlowOption.Enabled))
{
var sql = """
INSERT INTO demo.posts (id, author_id, title, description, content, date)
VALUES (111, 1, '测试', '测试', '测试', '2023-12-08');
""";
await context.ExecuteAsync(sql);
}
BeginTransaction()
Encapsulating the transaction started by BeginTransaction() in the form of a context is relatively simple; you only need to manually manage the DbTransaction.
public class DbContext
{
private readonly DbConnection _connection;
private DbTransaction? _tran;
public DbContext(MySqlConnection connection)
{
_connection = connection;
}
public async Task OpenTran()
{
if (_tran != null) throw new Exception("请勿重复开启事务");
_tran = await _connection.BeginTransactionAsync();
}
public async Task ExecuteAsync(string sql)
{
var command = _connection.CreateCommand();
command.CommandText = sql;
if (_tran != null)
{
command.Transaction = _tran;
}
await command.ExecuteNonQueryAsync();
}
public async Task EndTran()
{
if (_tran == null) throw new Exception("未开启事务");
await _tran.CommitAsync();
_tran.Dispose();
_tran = null;
}
}
Usage method:
using var connection = dataSource.CreateConnection();
await connection.OpenAsync();
DbContext context = new DbContext(connection);
await context.OpenTran();
var sql = """
INSERT INTO demo.posts (id, author_id, title, description, content, date)
VALUES (111, 1, '测试', '测试', '测试', '2023-12-08');
""";
await context.ExecuteAsync(sql);
Of course, since different ORMs encapsulate database transaction methods differently, the differences between ORMs are quite significant.
XA Transactions
In daily development, it's easy to encounter MySQL XA transaction errors, such as:
MySqlConnector.MySqlException (0x80004005): XA_RBDEADLOCK: Transaction branch was
rolled back: deadlock was detected\n at
MySqlConnector.Core.ServerSession.ReceiveReplyAsync(IOBehavior ioBehavior,
CancellationToken cancellationToken)
Or timeouts:
XA_RBTIMEOUT: Transaction branch was rolled back: took too long\n at
MySqlConnector.Core.ServerSession.ReceiveReplyAsync(IOBehavior ioBehavior,
CancellationToken cancellationToken) in
In fact, database XA transactions are a type of distributed transaction, directly supported at the database level. For example, an application can simultaneously connect to both PostgreSQL and MySQL databases and start an XA transaction. Even though they are different databases with different TCP connections, both will still roll back within the same transaction, significantly simplifying the workload of developers managing multiple databases.
Of course, this also applies to different connection objects within the same database. For example, in a project that simultaneously uses two ORM frameworks like EFCore and FreeSQL, each has its own connection pool and DBConnection object. However, regular transactions only apply to the current DBConnection and cannot be used across connections or threads, so using XA is also a solution.
However, XA transactions consume more performance than local transactions; hence if XA transactions are not needed, there is no need to enable them.
For XA transactions, readers can refer to: https://mariadb.com/kb/en/xa-transactions/
Using XA transactions in C# is very straightforward. By default, using TransactionScope will automatically use XA transactions, which is why in the previous example using TransactionScope, there's no need to worry about DBConnection, multiple DBConnections, or nested transactions.
The reason that TransactionScope defaults to automatically using XA transactions can be referenced here: https://github.com/mysql-net/MySqlConnector/issues/919
It is also very easy to turn off this feature; just add the following parameter to the database connection string:
UseXaTransactions=false
文章评论