Transaction is an important concept in database system. In this paper, the author explains the summary of various usage modes of transaction in CRUD for more than ten years.
- All the following contents are for stand-alone transactions and do not involve things related to distributed transactions!
- The explanation of transaction principle is not specific to a specific database implementation, so some places may be inconsistent with your practical experience.
Cognitive affairs
Why do you need database transactions?
Transfer is A common operation in life, such as transferring 100 yuan from account A to account B. From the perspective of users, this is A logical single operation. However, in the database system, it will be completed in at least two steps:
1. Reduce the amount of account A by 100 yuan
2. Increase the amount of account B by 100 yuan.
During this process, the following problems may occur:
1. The first step of the transfer operation was successful. The money in account A was reduced by 100 yuan, but the second step failed or the system crashed, resulting in no corresponding increase of 100 yuan in account B.
2. The system crashes as soon as the transfer operation is completed. When the system restarts and recovers, the transfer records before the crash are lost.
3. At the same time, another user transferred to account B. the amount of account B was abnormal due to the simultaneous operation on account B.
In order to solve these problems, the concept of database transaction needs to be introduced.
The above is quoted from: https://www.cnblogs.com/takumicx/p/9998844.html
Know ADO.NET
ADO.NET is an important component in. NET framework, which is mainly used to complete C# application access to database.
Composition of ADO.NET:
System.Data.Common → base classes and interfaces of various data access classes
System.Data.SqlClient → data access class for Sql Server operation
a) SqlConnection → database connector
b) SqlCommand → database named object
d) SqlDataReader → data reader
f) SqlParameter → define parameters for stored procedure
g) SqlTransaction → database transaction
Transaction 1: ADO.NET
The most original transaction usage, disadvantages:
- The code is smelly and long
- The logic is difficult to control. You forget to commit or roll back accidentally. Then, the database lock cannot be released or the connection pool is insufficient
- It's too cumbersome to pass Tran objects across methods
Recommendation: ★☆☆☆☆
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
try
{
conn.Open();
cmd.Transaction = conn.BeginTransaction();//Open transaction
int result = 0;
foreach (string sql in sqlList)
{
cmd.CommandText = sql;
result += cmd.ExecuteNonQuery();
}
cmd.Transaction.Commit();//Commit transaction
return result;
}
catch (Exception ex)
{
//Write log
if (cmd.Transaction != null)
cmd.Transaction.Rollback();//Rollback transaction
throw new Exception("An exception occurred while calling the transaction update method:" + ex.Message);
}
finally
{
if (cmd.Transaction != null)
cmd.Transaction = null;//Clear transaction
conn.Close();
}
Transaction 2: SqlHelper
The original ADO.NET transaction code is smelly and long. It's time to encapsulate a SqlHelper to operate ADO.NET. For example:
SqlHelper.ExecuteNonQuery(...); SqlHelper.ExecuteScaler(...);
In this way, there are many methods to execute a single command after encapsulation. How to deal with the transaction? Truncate an IDbTransaction parameter and pass it in? For example:
SqlHelper.ExecuteNonQuery(tran, ...); SqlHelper.ExecuteScaler(tran, ...);
Recommendation: ★☆☆☆☆
It seems OK. It's barely acceptable.
With the continuous practice in the project, one day we can't stand this tran transmission method, because it is too easy to miss transmission, especially when it is transmitted from one method to another, it is really too difficult.
Transaction 3: using thread id
In the early days when. NET had no asynchronous method, the defect of transaction 2 was simply encapsulated to avoid the problem of passing transaction tran objects around.
The principle is to use the thread id to save it to the staic dictionary < int, idbtransaction > when the transaction is opened, obtain the transaction object of the current thread and execute the command before the SqlHelper.ExecuteXxx method is executed.
In this way, the nightmare of transaction delivery is avoided. The final transaction code is as follows:
SqlHelper.Transaction(() =>
{
SqlHelper.ExecuteNonQuery(...); //You no longer need to explicitly pass tran
SqlHelper.ExecuteScaler(...);
});
This transaction is very simple to use and does not need to consider the transaction commit / release problem. It is applied in FreeSql by default. Disadvantage: asynchronous is not supported.
Recommendation: ★★★☆☆
The same thread transaction is simple to use and has design limitations:
- The default is submit, and rollback in case of exception;
- Transaction objects are mounted on threads. Each thread can only open one transaction connection, and the same transaction is nested;
- The code in the transaction body cannot switch threads, so no asynchronous methods can be used, including the database asynchronous method provided by FreeSql (any Curd synchronous method can be used);
Transaction 4: unit of work
Explicitly pass the ITransaction object around, saying that it's not safe to be direct like a girl running around in the street without clothes. And at that time, I want to bring some goods (status) to the girl. How can I bring goods (no pocket) without clothes.
At this time, it is necessary to wrap ITransaction. More state attributes can be defined in IUnitOfWork.
Recommendation: ★★★★★
The IUnitOfWork interface is defined as follows:
public interface IUnitOfWork : IDisposable
{
IDbTransaction GetOrBeginTransaction(); //Create or get the corresponding IDbTransaction
IsolationLevel? IsolationLevel { get; set; }
void Commit();
void Rollback();
}
Transaction 5: AOP transaction
With the continuous development of technology, let's start with a bunch of theories:
The following is quoted from: https://www.cnblogs.com/zhugenqiang/archive/2008/07/27/1252761.html
AOP (aspect oriented programming) can be said to be the supplement and improvement of OOP (object-oriented programming). OOP introduces the concepts of encapsulation, inheritance and polymorphism to establish an object hierarchy to simulate a set of public behaviors. When we need to introduce public behavior for decentralized objects, OOP is powerless. In other words, OOP allows you to define the relationship from top to bottom, but it is not suitable for defining the relationship from left to right. For example, log function. Log code is often spread horizontally across all object levels, regardless of the core functions of the objects it spreads. The same is true for other types of code, such as security, exception handling, and transparent persistence. This kind of irrelevant code scattered everywhere is called cross cutting code. In OOP design, it leads to a large number of code duplication, which is not conducive to the reuse of each module.
On the contrary, AOP technology uses a technology called "crosscutting" to dissect the interior of encapsulated objects, encapsulate the public behaviors that affect multiple classes into a reusable module, and call it "Aspect", that is, Aspect. The so-called "Aspect", in short, is to encapsulate the logic or responsibilities that have nothing to do with the business but are jointly called by the business modules, so as to reduce the repeated code of the system, reduce the coupling degree between modules, and facilitate the operability and maintainability in the future. AOP represents a horizontal relationship. If an "object" is a hollow cylinder, it encapsulates the attributes and behavior of the object; Then the Aspect oriented programming method is like a sharp blade to cut open these hollow cylinders to obtain their internal messages. The cut section is the so-called "Aspect". Then it restored these cut sections without leaving any trace with its skillful hand of seizing heavenly power.
Using "crosscutting" technology, Aop divides the software system into two parts: core concerns and crosscutting concerns. The main process of business processing is the core concern, and the part that has little to do with it is the crosscutting concern. A characteristic of crosscutting concerns is that they often occur in multiple parts of the core concerns, and they are basically similar everywhere. Such as authority authentication, logging, and transaction processing. The role of Aop is to separate various concerns in the system and separate the core concerns from the crosscutting concerns. As Adam Magee, senior solution architect of Avanade, said, the core idea of Aop is to "separate the business logic in the application from the common services that support it."
The technologies to realize AOP are mainly divided into two categories: one is to use dynamic agent technology to decorate the message by intercepting the message to replace the execution of the original object behavior; The second is to use static weaving and introduce specific syntax to create "aspects", so that the compiler can weave the code related to "aspects" during compilation.
The usage code of the final presentation is as follows:
[Transactional]
public void SaveOrder()
{
SqlHelper.ExecuteNonQuery(...);
SqlHelper.ExecuteScaler(...);
}
Recommendation: ★★★★★
Using the [Transactional] attribute to mark SaveOrder to start a transaction, it actually performs operations like this:
public void SaveOrder()
{
var (var tran = SqlHelper.BeginTransaction())
{
try
{
SqlHelper.ExecuteNonQuery(tran, ...);
SqlHelper.ExecuteScaler(tran, ...);
tran.Commit();
}
catch
{
tran.Roolback();
throw;
}
}
}
It solves the problem that it is difficult to control asynchronous logic without significantly passing tran objects.
At present, this transaction method is widely used in ASP. Net core and is quite simple to implement. It uses dynamic agent technology to replace the content injected in Ioc and dynamically intercept [Transactional] feature tags.
After using Ioc, you can no longer use SqlHelper technology. At this time, you should use Repository.
Composite technology: Ioc + Repository + UnitOfWork
It is important to understand the principle. This section is more abstract. If you want to deeply understand the principle, please refer to the use and implementation code of FreeSql as follows:
User defined warehouse base class
public class UnitOfWorkRepository<TEntity, TKey> : BaseRepository<TEntity, TKey>
{
public UnitOfWorkRepository(IFreeSql fsql, IUnitOfWork uow) : base(fsql, null, null)
{
this.UnitOfWork = uow;
}
}
public class UnitOfWorkRepository<TEntity> : BaseRepository<TEntity, int>
{
public UnitOfWorkRepository(IFreeSql fsql, IUnitOfWork uow) : base(fsql, null, null)
{
this.UnitOfWork = uow;
}
}
Injection warehousing, single instance IFreeSql, AddScoped(IUnitOfWork)
public static IServiceCollection AddFreeRepository(this IServiceCollection services, params Assembly[] assemblies)
{
services.AddScoped(typeof(IReadOnlyRepository<>), typeof(UnitOfWorkRepository<>));
services.AddScoped(typeof(IBasicRepository<>), typeof(UnitOfWorkRepository<>));
services.AddScoped(typeof(BaseRepository<>), typeof(UnitOfWorkRepository<>));
services.AddScoped(typeof(IReadOnlyRepository<,>), typeof(UnitOfWorkRepository<,>));
services.AddScoped(typeof(IBasicRepository<,>), typeof(UnitOfWorkRepository<,>));
services.AddScoped(typeof(BaseRepository<,>), typeof(UnitOfWorkRepository<,>));
if (assemblies?.Any() == true)
foreach (var asse in assemblies)
foreach (var repo in asse.GetTypes().Where(a => a.IsAbstract == false && typeof(UnitOfWorkRepository).IsAssignableFrom(a)))
services.AddScoped(repo);
return services;
}
Transaction 6: UnitOfWorkManager
Recommendation: ★★★★★
(5) declarative transaction management is based on AOP at the bottom. Its essence is to intercept before and after the method, then create or join a transaction before the target method starts, and commit or roll back the transaction according to the execution after the target method is executed.
The biggest advantage of declarative transactions is that they do not need to manage transactions through programming, so there is no need to mix transaction management code with business logic code. Transaction rules can be applied to business logic by making relevant transaction rule declarations in the configuration file (or by equivalent annotation based methods). Because transaction management itself is a typical crosscutting logic, which is where AOP can be used.
In general, the author strongly recommends the use of declarative transactions in development, not only because it is simple, but also because it makes the pure business code not polluted and greatly facilitates the later code maintenance.
Compared with programmatic transactions, the only disadvantage of declarative transactions is that the finer granularity of the latter can only act at the method level, and can not act at the code block level like programmatic transactions. However, even if there are such requirements, there are many flexible methods. For example, you can separate the code blocks that need transaction management into methods, and so on.
Transaction 6 UnitOfWorkManager refers to the powerful java spring transaction management mechanism next door, while transaction 5 can only define a single transaction behavior (for example, it cannot be nested). The behavior mechanism implemented by transaction 6 UnitOfWorkManager is as follows:
Six propagation modes mean that cross method transactions are very convenient and support synchronous and asynchronous:
- Requisited: if there is no transaction currently, a new transaction will be created. If there is already a transaction, it will be added to the transaction. It is the default choice.
- Supports: supports the current transaction. If there is no current transaction, it will be executed in a non transaction method.
- Mandatory: use the current transaction. If there is no current transaction, an exception will be thrown.
- NotSupported: perform operations in a non transactional manner. If there is a current transaction, suspend the current transaction.
- Never: perform operations in a non transactional manner. Throw an exception if the current transaction exists.
- Nested: executed as nested transactions.
Refer to the usage of FreeSql as follows:
Step 1: configure Startup.cs injection
//Startup.cs
public void ConfigureServices(IServiceCollection services)
{
services.AddSingleton<IFreeSql>(fsql);
services.AddScoped<UnitOfWorkManager>();
services.AddFreeRepository(null, typeof(Startup).Assembly);
}
| UnitOfWorkManager member | explain |
|---|---|
| IUnitOfWork Current | Returns the current unit of work |
| void Binding(repository) | Leave the warehousing business to it |
| IUnitOfWork Begin(propagation, isolationLevel) | Create unit of work |
Step 2: define transaction characteristics
[AttributeUsage(AttributeTargets.Method)]
public class TransactionalAttribute : Attribute
{
/// <summary>
///Transaction propagation mode
/// </summary>
public Propagation Propagation { get; set; } = Propagation.Requierd;
/// <summary>
///Transaction isolation level
/// </summary>
public IsolationLevel? IsolationLevel { get; set; }
}
Step 3: introduce dynamic agent Library
Get UnitOfWorkManager from the container Before and call its var uow = Begin(attr.Propagation, attr.IsolationLevel) method
Call the uow.Commit or Rollback method in Before at After, and finally call uow.Dispose.
Step 4: use the transaction feature in the Controller or Service or Repository
public class SongService
{
BaseRepository<Song> _repoSong;
BaseRepository<Detail> _repoDetail;
SongRepository _repoSong2;
public SongService(BaseRepository<Song> repoSong, BaseRepository<Detail> repoDetail, SongRepository repoSong2)
{
_repoSong = repoSong;
_repoDetail = repoDetail;
_repoSong2 = repoSong2;
}
[Transactional]
public virtual void Test1()
{
//Here_ repoSong,_ repoDetail,_ repoSong2 all operations are a unit of work
this.Test2();
}
[Transactional(Propagation = Propagation.Nested)]
public virtual void Test2() //Nested transactions, new (transactions without Test1)
{
//Here_ repoSong,_ repoDetail,_ repoSong2 all operations are a unit of work
}
}
Question: is it possible to start a business by entering the method?
It may not be a real transaction, but it may be a false unit of work (without transaction).
It is also possible to extend the last transaction.
It may also be a new business, depending on the communication mode.
Conclusion
With the continuous evolution and progress of technology, it takes a long and slow process from 1.0 to 10.0.
At the same time, we call on everyone not to use microservices blindly. The evolution process has a long cycle and the risk of the project is too high.
Wake up at 5:30 in the morning and write a summary of this article's understanding of affairs. thank you!!
The above transaction mechanisms are implemented in FreeSql. FreeSql is a powerful object relational mapping technology (O/RM) that supports. NETCore 2.1 + or. Net framework 4.0 + or Xamarin. Support MySql/SqlServer/PostgreSQL/Oracle/Sqlite / Dameng / NPC Jincang / Shenzhou general / Access; The number of unit tests is 5000 + and is hosted in github with MIT open source protocol: https://github.com/dotnetcore/FreeSql
Author: Mr. Ye_ 34e6
Link: https://www.jianshu.com/p/7c85a7052dda
Source: Jianshu
The copyright belongs to the author. For commercial reprint, please contact the author for authorization, and for non-commercial reprint, please indicate the source.