C # Database concurrency Solution (General Edition, EF Edition)

Or the old saying: 10 years Hedong, 10 years hexi, MO bullying year poor! ~_~ dozen wrong words, should be MO bullying Juvenile poor!

Education represents your past, ability represents your present, study represents your future.

Lifelong learning, excellence.

Since the birth of ASP, Microsoft has provided a lot of control concurrency method, before we understand these control concurrency method, we first to briefly introduce the concurrency!

Concurrency: Concurrency occurs when multiple visitors simultaneously access an update operation at the same time or at the same moment!

The processing of concurrency is divided into pessimistic concurrency processing and optimistic concurrency processing.

The so-called pessimistic/optimistic concurrent processing, it can be understood:

Pessimists think: In the process of running the program, concurrency is very easy to drip, so the pessimists put forward their processing mode: When I execute a method, other visitors are not allowed to intervene in this method. (pessimists often think that something bad is going to happen to them)

Optimists argue that, in the process of running a program, concurrency is infrequent, so optimists present their handling patterns: When I execute a method, allow other visitors to intervene in the method. (optimists often think that a bad thing doesn't happen to them)

So in the C # language, what are those pessimists?

In C #, such as: Lock, Monitor, interlocked and other methods of locking data, belong to pessimistic concurrency processing category! Once the data is locked, other visitors are not authorized to access it. Interested can be consulted: locks, monitor and lock in C #, and differences

However, there is a common problem with pessimists dealing with concurrency, which is that it can result in very low execution efficiency.

Here: For a simple example:

Ticket system, Xiao Ming to buy tickets to Beijing to Shanghai D110 train, if the use of pessimists to deal with the concurrent mode, then the conductor will D110 the ticket lock, and then make the ticket operation. However, during the D110 train ticket was locked, the conductor went to the toilet, or drank a cup of coffee, the other window conductor is not allowed to make ticket drops! With this approach, China's 1.4 billion people don't have to travel because they can't buy tickets ~_~

Therefore: in the processing of database concurrency, pessimistic locks should be used with caution! Specifically also depends on the database concurrency is not large, if relatively large, the proposed use of optimistic processing mode, if relatively small, can be appropriate to use the pessimistic processing mode!

Ok. Said so much, that is, to do a cushion, the title of this section is called the database concurrency solution, we finally have to go to the basics, from the database concurrency to solve the talk!

So that's the problem?

What are the processing methods for database concurrency?

In fact, the concurrency of database processing is also divided into optimistic and pessimistic lock, but is based on the database level! About concurrent processing at the database level you can refer to my blog: Optimistic Lock pessimistic lock application

pessimistic Lock: it is assumed that concurrency conflicts occur and that all operations that may violate data integrity are masked. [1]

optimistic Lock: Assume that there will be no concurrency conflicts and only check for violation of data integrity when committing the operation. [1] Optimistic locking does not solve the problem of dirty reading.

The most common method of handling multiuser concurrent access is locking. When a user locks an object in the database, other users can no longer access the object. The impact of locking on concurrent access is reflected in the granularity of the lock. For example, a lock placed on a table restricts concurrent access to an entire table, a lock placed on a data page restricts access to the entire data page, and a lock on a row restricts concurrent access to that row. Visible row lock granularity is minimal, concurrent access is best, page lock granularity is greatest, and the performance of concurrent access will be lower.

pessimistic Lock: it is assumed that concurrency conflicts occur and that all operations that may violate data integrity are masked. [1] Pessimistic locking assumes that other users attempt to access or change the object you are accessing, change the probability is very high, so in a pessimistic lock environment, before you start to change the object is locked, and until you commit the changes to release the lock. The pessimistic flaw is whether a page lock or a row lock, lock the time may be very long, so that may long lock an object, restrict the access of other users, that is, pessimistic lock concurrent access is not good.

optimistic Lock: Assume that there will be no concurrency conflicts and only check for violation of data integrity when committing the operation. [1] Optimistic locking does not solve the problem of dirty reading. Optimistic locking holds the probability that other users are trying to change the object you are changing, so the optimistic lock locks the object until you are ready to commit the changes, and does not lock when you read and change the object. It can be seen that the optimistic lock and lock time is shorter than the pessimistic lock, and the optimistic lock may obtain better concurrent access performance with larger lock granularity. But if the second user reads the object exactly before the first user commits the change, the database will find that the object has changed when he has made his own changes, so that the second user has to reread the object and make changes. This means that in an optimistic lock environment, the number of times the object is read by the concurrent user is increased.

The main purpose of this article is to explain the C #-based database Concurrency solution (General edition, EF version), so we want to start with C #, preferably with a small project

The project has been prepared for everyone, as follows:

First we need to create a small database:

View Code

The database created is simple, three sheets: Commodity table, Inventory table, log table

With the database, we create a C # project that uses the C # Databasefirst pattern, with the following structure:

The project is simple and is well built using the EF databasefirst model.

The project is built, and below we simulate the occurrence of concurrency?

The main code is as follows (reduce inventory, insert log):

#region did not do concurrent processing//<summary>///simulate a reduced inventory operation without concurrency control///</summary> public void Sub            Mitorder_3 () {int productId = 1; using (bingfatestentities context = new Bingfatestentities ()) {var Inventorylogdbset = context.                Inventorylog; var Inventorydbset = context. inventory;//inventory table using (var Transaction = context. Database.begintransaction ()) {//Reduce inventory operation var inventory_mol = Inventory Dbset.where (A = A.productid = = ProductId).                    FirstOrDefault ();//Inventory Object inventory_mol.productcount = Inventory_mol.productcount-1; int A4 = context.                    SaveChanges (); Insert Log Inventorylog Logmodel = new Inventorylog () {Title = "                    Inserts a piece of data to calculate if concurrency ",};              Inventorylogdbset.add (Logmodel);      Context.                    SaveChanges (); 1.5 Simulation Time Thread.Sleep (500);                Consumes half a second transaction.commit (); }}} #endregion

At this point we add a breakpoint at int productid=1 and run the program (open four browsers simultaneously), as follows:

By the way, four visitors visit this method without concurrency control at the same time, resulting in the following results:

The results show that the log generates four data, while the inventory quantity is reduced by 1. The result is obviously incorrect because of the concurrency, which is essentially due to dirty reading, misreading, and non-stress.

Then, since the problem has occurred, we will find ways to solve the method, there are two ways: pessimistic locking method, optimistic locking method.

Pessimist Method:

Pessimistic method (with Uodlock lock, lock update operation, that is, once locked, other visitors do not allow access to this action) similar to this method, can be implemented through a stored procedure, not explained here

Optimist method (Universal version/Stored procedure implementation):

In the above database script, there are fields called: Versionnum, type: TimeStamp.

Field versionnum You can understand the version number, the role of the version number is that once a visitor modifies the data, the value of the version number will change accordingly. Of course, the version number of the synchronization changes are related to the database, in SQL Server will be updated with the data update version number, but in MySQL will not change as the data changes. Therefore, if you are using a MySQL database, you need to write a trigger, as follows:

OK, we know the field of type timestamp, below we combine the small database above to create a processing concurrent stored procedure, as follows

Create proc Lockproc-optimistic lock control concurrency (@ProductId int, @IsSuccess bit=0 output) asdeclare @count as intDECLARE @flag as Timestampde  Clare @rowcount as int begin transelect @count =productcount, @flag =versionnum from Inventory where [email protected] Update Inventory set [email protected] where [email protected] and [email protected]insert into inventorylog values (' Insert a piece of data for calculation Concurrency ') set @[email protected] @ROWCOUNTif @rowcount >0set @IsSuccess =1elseset @IsSuccess =0commit Tran

This stored procedure is simple and performs two operations: reducing inventory and inserting a single piece of data. There is an input parameter: productId, an output parameter, issuccess. If concurrency occurs, the value of Issuccess is false and if execution succeeds, the Issuccess value is true.

Here, to tell you a point: The program uses pessimistic lock, is serial, using optimistic lock, is parallel.

In other words: With pessimistic locks, only one visitor's request is executed at a time, and when the previous visitor accesses the lock, the next visitor enters the locked program and executes until all the visitors have finished executing. As a result, pessimistic locks are executed in a strictly sequential pattern to ensure that all visitors succeed.

With optimistic locking, the visitor executes in parallel, and everyone accesses a method, except that only one visitor succeeds at the same time and the other visitor fails. So what do visitors do with these failed executions? It is unreasonable to return the failure message directly and the user experience is not good, so a rule needs to be customized so that the failed visitor can execute the previous request again.

Time is limited, there is not much to write about ... Because concurrency control is stored on a database-side process, C # code is simple. As follows:

View Code

Here, the following should be stated:

When the value of Issuccess is False, the method should be repeated, and my rule is to repeat the request 10 times, so it is good to resolve the direct feedback to the user failed message. Improves the user experience.

The following highlights how the EF framework avoids database concurrency, allowing me to cite a few words from someone else's blog before explaining it:

Concurrency control is a mechanism for ensuring that errors caused by concurrent operations are corrected in a timely manner during software development. From ADO to SQL and to today's ADO-Framework,.net, it provides a good support scheme for concurrency control.

Compared with the concurrent processing in the database, the concurrency processing in the Entity Framework has been implemented in many ways.

In the SYSTEM.DATA.METADATA.EDM namespace, there is a Concurencymode enumeration that specifies the concurrency options for attributes in the conceptual model.
There are two members of the Concurencymode:

Member name	Description
None	This property is never validated on write. This is the default concurrency mode.
Fixed	This property is always validated on write.

When the Model property is the default value of None, the system does not detect this model property, and when this property is modified at the same time, the input property values are processed in a data merge manner.
This model property is instrumented when the model property is fixed, and the system fires a Optimisticconcurrencyexception exception when the property is modified at the same time.

Developers can define different Concurencymode options for each property of an object, which can be in the *. EDMX looks for:

The edmx file is opened with Notepad as follows:

View Code

In fact, in EF Databasefirst, we just need to set a property of type TimeStamp version number, as follows:

Once you have set the version number property, you can test the concurrency, and when concurrency occurs, the program throws an exception, and all we have to do is catch the exception, and then repeat the request in accordance with our own rules until the return is successful.

So how do you catch concurrency exceptions?

In C # code, you need to use the exception class: Dbupdateconcurrencyexception to capture, the specific usage of EF is as follows:

public class Savechangesforbf:bingfatestentities    {public        override int SaveChanges ()        {            try            {                return base. SaveChanges ();            }            catch (Dbupdateconcurrencyexception ex)//(Optimisticconcurrencyexception)            {                //concurrency Save error                return-1;            }        }    }

After setting the properties, EF will help us automatically detect concurrency and throw exceptions, and after we have caught the exception using the above method, we can execute the rules we repeatedly execute, with the following code:

View Code

At this point, C # concurrency processing is finished, is it very simple?

Project Source Address: http://download.csdn.net/download/wolongbb/9977216

@ Chen Wolong's blog

C # Database concurrency Solution (General Edition, EF Edition)