Shulou(Shulou.com)06/02 Report--

In this issue, the editor will bring you about how to solve database concurrency in C#. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

What are the ways to deal with database concurrency?

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

Pessimistic locks: assume that concurrency conflicts occur, shielding all operations that may violate data integrity. [1]

Optimistic locks: assuming that there are no concurrency conflicts, only check for violations of data integrity when committing operations. [1] optimistic lock can not solve the problem of dirty reading.

The most common way to handle multi-user concurrent access is to lock it. 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 the entire table; a lock placed on a data page restricts access to the entire data page; and a lock placed on a row restricts concurrent access to that row. It can be seen that the row lock granularity is the smallest, the concurrent access is the best, and the page lock granularity is the largest, the lower the concurrent access performance.

Pessimistic locks: assume that concurrency conflicts occur, shielding all operations that may violate data integrity. [1] pessimistic locks assume that there is a high probability that other users will attempt to access or change the object you are accessing or changing, so in a pessimistic lock environment, lock the object before you start to change the object. and don't release the lock until you commit the changes. The downside of pessimism is that whether it is a page lock or a row lock, the locking time may be very long, which may lock an object for a long time and restrict the access of other users, that is, pessimistic locks have poor concurrent access.

Optimistic locks: assuming that there are no concurrency conflicts, only check for violations of data integrity when committing operations. [1] optimistic lock can not solve the problem of dirty reading. Optimistic locks assume that there is little chance that other users will attempt to change the object you are changing, so optimistic locks do not lock the object until you are ready to commit the change, and do not lock it when you read and change the object. It can be seen that the locking time of optimistic lock is shorter than that of pessimistic lock, and optimistic lock can obtain better concurrent access performance with larger lock granularity. But if the second user reads the object just before the first user commits the change, then when he completes his change and commits, the database will find that the object has changed, so the second user has to reread the object and make changes. This indicates that in an optimistic locking environment, the number of times concurrent users read objects is increased.

The purpose of this article is to explain the database concurrency solution based on C # (general version, EF version), so we should start with C #, preferably with a small project.

The project is ready for you, as follows:

First we need to create a small database:

Create database BingFaTestgouse BingFaTestgo create table Product-- merchandise table (ProductId int identity (1)) primary key,-- commodity ID key ProductName nvarchar (50),-- trade name ProductPrice money,-- unit price ProductUnit nvarchar (10) default ('yuan / jin'), AddTime datetime default (getdate ())-- add time) create table Inventory-- inventory table (InventoryId int identity (1pc1) primary key,ProductId int FOREIGN KEY REFERENCES Product (ProductId),-- foreign key ProductCount int,-- inventory quantity VersionNum TimeStamp not null InventoryTime datetime default (getdate ()),-- time) create table InventoryLog (Id int identity (1) primary key,Title nvarchar (50),)-- Test data: insert into Product values ('Apple', 1) 'yuan / jin', GETDATE () insert into Inventory (ProductId,ProductCount,InventoryTime) values (1)

The database created is very simple, with three tables: commodity table, inventory table, and log table.

With the database, we create a C# project, which adopts the C# DataBaseFirst mode and is structured as follows:

# region does not do concurrency processing / simulates an inventory reduction operation without concurrency control / public void SubMitOrder_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 ()) {/ / inventory reduction operation var Inventory_Mol = InventoryDbSet.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 = "insert a piece of data to calculate whether concurrency occurs",}; InventoryLogDbSet.Add (LogModel) Context.SaveChanges (); / 1.5 Simulation time Thread.Sleep; / / half a second Transaction.Commit ();} # endregion

As can be seen from the figure above, four visitors visit this method without concurrency control at the same time, and the results are as follows:

Optimist approach (general version / stored procedure implementation):

In the above database script, there is a field called: VersionNum and the type is: TimeStamp.

The field VersionNum can be understood as the version number. The function of the version number is that once a visitor modifies the data, the value of the version number will change accordingly. Of course, the synchronous change of the version number is related to the database, and the version number will be updated synchronously as the data is modified in SQLserver, but it will not change as the data is modified in MySQL. So, if you are using a MYSQL database, you need to write a trigger like this:

OK, knowing the field of type Timestamp, let's create a stored procedure that handles concurrency with the small database above, as follows

Create proc LockProc-optimistic lock control concurrency (@ ProductId int, @ IsSuccess bit=0 output) asdeclare @ count as intdeclare @ flag as TimeStampdeclare @ rowcount As int begin transelect @ count=ProductCount,@flag=VersionNum from Inventory where ProductId=@ProductId update Inventory set ProductCount=@count-1 where VersionNum=@flag and ProductId=@ProductIdinsert into InventoryLog values ('insert a piece of data to calculate whether concurrency occurs') set @ rowcount=@@ROWCOUNTif @ rowcount > 0set @ IsSuccess=1elseset @ IsSuccess=0commit tran

This stored procedure is simple, performing two operations: reducing inventory and inserting a piece of data. There is one input parameter: productId, and one output parameter, IsSuccess. If concurrency occurs, the value of IsSuccess is False, and if the execution is successful, the value of IsSuccessIsSuccessis True.

Here, I would like to explain one point: the program uses pessimistic lock, which is serial, and optimistic lock, which is parallel.

That is to say, with pessimistic lock, only one visitor's request is executed at a time, and when the previous visitor's access is completed and the lock is released, the next visitor will enter the locked program and execute it until all visitors finish executing. Therefore, the pattern in which pessimistic locks are executed in strict order can ensure the success of all visitors.

When using optimistic locks, visitors execute in parallel, and everyone accesses the same method at the same time, but only one visitor succeeds and the other visitors fail at the same time. So what do you do with visitors who fail to execute? It is unreasonable to return failure information directly, and the user experience is not good, so you need to customize a rule to allow the failed visitor to re-execute the previous request.

Time is limited, so there is no more writing. Because concurrency control is a stored procedure on the database side, the C # code is also very simple. As follows:

# region Universal concurrent processing Mode stored procedure implementation / stored procedure implementation / public void SubMitOrder_2 () {int productId = 1; bool bol = LockForPorcduce (productId); / / 1.5 Simulation time-consuming Thread.Sleep / / half a second int retry = 10; while (! bol & & retry > 0) {retry--; LockForPorcduce (productId) } private bool LockForPorcduce (int ProductId) {using (BingFaTestEntities context = new BingFaTestEntities ()) {SqlParameter [] parameters = {new SqlParameter ("@ ProductId", SqlDbType.Int), new SqlParameter ("@ IsSuccess", SqlDbType.Bit)} Parameters [0] .value = ProductId; parameters [1] .Direction = ParameterDirection.Output; var data = context.Database.ExecuteSqlCommand ("exec LockProc @ ProductId,@IsSuccess output", parameters); string N2 = parameters [1] .Value.ToString (); if (N2 = = "True") {return true } else {return false;} # endregion

The Edmx file is opened in notepad as follows:

After you have set the version number property, you can do concurrent testing. When concurrency occurs in the system, the program will throw an exception, and all we need to do is to catch the exception, and then repeat the method of executing the request according to our own rules until the return is successful.

So how do you catch concurrent exceptions?

In the C # code, you need to use the exception class: DbUpdateConcurrencyException to catch. The specific usage in EF is as follows:

Public class SaveChangesForBF: BingFaTestEntities {public override int SaveChanges () {try {return base.SaveChanges ();} catch (DbUpdateConcurrencyException ex) / / (OptimisticConcurrencyException) {/ / concurrent save error return-1;}

After setting the property, EF will automatically detect concurrency and throw an exception. After we catch the exception using the above method, we can execute the rules we have executed repeatedly. The specific code is as follows:

# region EF exclusive concurrent processing mode / stored procedure implementation / public void SubMitOrder () {int C = LockForEF (); / / 1.5 Simulation time-consuming Thread.Sleep (500); / / half a second int retry = 10 While (C 0) {retry--; C = LockForEF ();}} / mimic an inventory reduction operation EF exclusive concurrent processing mode / public int LockForEF () {int productId = 1 Int C = 0; using (SaveChangesForBF context = new SaveChangesForBF ()) {var InventoryLogDbSet = context.InventoryLog; var InventoryDbSet = context.Inventory / / inventory table using (var Transaction = context.Database.BeginTransaction ()) {/ / inventory reduction operation var Inventory_Mol = InventoryDbSet.Where (A = > A.ProductId = = productId). FirstOrDefault (); / / inventory object Inventory_Mol.ProductCount = Inventory_Mol.ProductCount-1 C = context.SaveChanges (); / / insert log InventoryLog LogModel = new InventoryLog () {Title = "insert a piece of data to calculate whether concurrency occurs",} InventoryLogDbSet.Add (LogModel); context.SaveChanges (); / 1.5Simulation time Thread.Sleep; / / half a second Transaction.Commit ();}} return C } # endregion above is how to solve database concurrency in C# shared by Xiaobian. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.