EntityFramework and TransactionScope transaction and Concurrency Control

I recently saw an article about TransactionScope in the garden, and found that Transaction and concurrency control are hard-to-understand problems for those who are new to Entity Framework and Transaction Scope, organized this article to discuss with you.

First, I think everyone should know the ACID feature of transactions as the most basic knowledge. ADO.. NET SQLTransaction is. NET Framework, which can be used to encapsulate multiple database accesses as "Atomic operations ", you can also modify the isolation level to control the concurrent behavior. TransactionScope is a tool used to complete transactions on Distributed Data nodes. It is often used in the business logic layer to organize multiple database operations into business transactions, it can achieve transparent distributed transaction control and implicit failure rollback. However, at the same time, it is often mentioned that TransactionScope has performance and deployment problems. According to MSDN's Using the TransactionScope Class, when a TransactionScope contains operations connected to the same database, its behavior is similar to that of SqlTransaction. When it performs data operations on multiple database connections, local database transactions are promoted to distributed transactions, this improvement requires that each node install and start the DTC service to support the coordination of distributed transactions. Its performance is much lower than that of local database transactions, this is also a typical example of the Consistency and Availability of a distributed system based on CAP law. So when we choose whether to use TransactionScope, We must confirm whether it will lead to a distributed transaction that we don't want to happen, and ensure that the Transaction Completes what it should do as soon as possible, to check whether the transaction is promoted, we can use SQL Profiler to track related events.

Then let's take a look at Entity Framework. In fact, EF is also related to transactions. Its Context concept comes from the Unit of Work mode. Context records all Entity changes before submission, and initiates real database operations when the SaveChanges method is called, by default, the SaveChanges method implies a transaction and tries to use Optimistic Concurrency Control to submit data. However, to control concurrency, we need to set the ConcurrencyMode of the Entity Property to Fixed, otherwise, EF ignores the concurrent modifications that occur on this Entity. For details, refer to MSDN Saving Changes and Managing Concurrency. Microsoft recommends that you use the following methods to capture conflicting concurrent operations and use RefreshMode to overwrite or discard failed operations:

                                     num =             Console.WriteLine( +                 num.ToString() +                                                                   Console.WriteLine(             +          }

Of course, in addition to optimistic concurrency control, we can also perform pessimistic concurrency control on Use Cases with extremely frequent conflicts and a high cost for resolving conflicts. The basic idea of pessimistic concurrency is to prevent data from being modified offline at the same time, that is, like the "Lock" function in the source code management, the Code rural A locks the file and B cannot modify it any more, in this way, this file cannot conflict. pessimistic concurrency control is implemented, such as adding the IsLocked field to the data row.

At last, TransactionScope and EF can be used together for multi-Context transactions.

After a brief introduction of the theory, the following example shows the effects of several different methods on concurrency control. The requirement is that each Member has a HasMessage field, which is initially False, we need to add a unique MemberMessage to a Member and. set HasMessage to True.

Database creation script:

. (,) (PAD_INDEX, STATISTICS_NORECOMPUTE, IGNORE_DUP_KEY, ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS )).. (,) (, N ,. (,) (, N ,.. (,) (PAD_INDEX, STATISTICS_NORECOMPUTE, IGNORE_DUP_KEY, ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS )). (. (.View Code

Method 1:Instead of using TransactionScope, it only depends on the default concurrency control of each field of Entity.

MyDbContext (): (MyDbContext (DbSet <Member> Members {; [Table (Id {; Name {; HasMessage {; ICollection <MemberMessage> Messages {; [Table (Id {; message {; MemberId {; [ForeignKey (Member {;}View Code

(Context = tom = context. Members. FirstOrDefault (m => m. Id = (tom! = &&! Console. WriteLine (tom. Messages. Add (Message = tom. HasMessage = Console. WriteLine (Console. WriteLine (+}View Code

 

Through analysis, it is not difficult to find that the key to concurrency control in this scenario is to check the HasMessage before insertion. If the value of HasMessage is False, insert the MemberMessage and update the Member. when the HasMessage field is used, check again whether the HasMessage field in the database is False. If it is set to True, this field is modified by another user. This save should be rolled back or processed. Therefore, targeted concurrency control is required.

Method 2:Add a concurrent check to the HasMessage Field

     [Table(                   Id { ;             Name { ;              HasMessage { ;             ICollection<MemberMessage> Messages { ;      }

The test code of method 1 is still used. The result is that OptimisticConcurrencyException will be thrown during one data insertion, which means the goal of preventing repeated data insertion has been achieved.

 

Let's look at whether you can use TransactionScope to control the concurrency of EF.Method 3:Use TransactionScope but do not add concurrent checks to Entity

The definitions of Context and Entity are exactly the same as those of method 1. The test code is

(Scope = (context = tom = context. Members. FirstOrDefault (m => m. Id = (tom! = &&! Console. WriteLine (tom. Messages. Add (Message = tom. HasMessage = Console. WriteLine (Console. WriteLine (+}View Code

We also started two program tests and found that DbUpdateException was thrown during one save operation. The internal cause was that the operation was killed by a Transaction deadlock. Therefore, it seems that concurrency control can also be achieved. The advantage of this method is that you do not need to carefully identify which operations in the business will lead to concurrent Field update conflicts, and all Entity can not add ConcurrencyCheck, the disadvantage is that when there are not many conflicts, the deadlock competition and coordination will have lower performance than optimistic concurrency control.

Finally, let's give a try to complete the test.Method 4:Use TransactionScope and add ConcurrencyCheck to the HasMessage field. For Entity code, refer to method 1. For test code, refer to method 3. The result is that TransactionScope detects the deadlock and selects one of the competitors to throw an exception.

 

Conclusion: