Concurrency erro when deleteing or updating multiple key field

If you lock a row for read-only access, others can also lock it for read-only access but not for update. Managing locks has disadvantages. It can be complex to program. It requires significant database management resources, and it can cause performance problems as the number of users of an application increases.

For these reasons, not all database management systems support pessimistic concurrency. Entity Framework Core provides no built-in support for it, and this tutorial doesn't show you how to implement it. The alternative to pessimistic concurrency is optimistic concurrency. Optimistic concurrency means allowing concurrency conflicts to happen, and then reacting appropriately if they do. Jane clicks Save first and sees her change when the browser returns to the Index page.

What happens next is determined by how you handle concurrency conflicts. You can keep track of which property a user has modified and update only the corresponding columns in the database. In the example scenario, no data would be lost, because different properties were updated by the two users.

This method of updating can reduce the number of conflicts that could result in data loss, but it can't avoid data loss if competing changes are made to the same property of an entity. Whether the Entity Framework works this way depends on how you implement your update code. It's often not practical in a web application, because it can require that you maintain large amounts of state in order to keep track of all original property values for an entity as well as new values.

Maintaining large amounts of state can affect application performance because it either requires server resources or must be included in the web page itself for example, in hidden fields or in a cookie.

This is called a Client Wins or Last in Wins scenario. All values from the client take precedence over what's in the data store. As noted in the introduction to this section, if you don't do any coding for concurrency handling, this will happen automatically. Typically, you would display an error message, show him the current state of the data, and allow him to reapply his changes if he still wants to make them.

This is called a Store Wins scenario. The data-store values take precedence over the values submitted by the client. You'll implement the Store Wins scenario in this tutorial. This method ensures that no changes are overwritten without a user being alerted to what's happening. You can resolve conflicts by handling DbConcurrencyException exceptions that the Entity Framework throws. In order to know when to throw these exceptions, the Entity Framework must be able to detect conflicts.

Therefore, you must configure the database and the data model appropriately. Some options for enabling conflict detection include the following:. In the database table, include a tracking column that can be used to determine when a row has been changed. The data type of the tracking column is typically rowversion. The rowversion value is a sequential number that's incremented each time the row is updated.

Database concurrency refers to situations in which multiple processes or users access or change the same data in a database at the same time. Concurrency control refers to specific mechanisms used to ensure data consistency in presence of concurrent changes. EF Core implements optimistic concurrency control , meaning that it will let multiple processes or users make changes independently without the overhead of synchronization or locking.

In the ideal situation, these changes will not interfere with each other and therefore will be able to succeed. In the worst case scenario, two or more processes will attempt to make conflicting changes, and only one of them should succeed.

Properties configured as concurrency tokens are used to implement optimistic concurrency control: whenever an update or delete operation is performed during SaveChanges , the value of the concurrency token on the database is compared against the original value read by EF Core. The situation when another user has performed an operation that conflicts with the current operation is known as concurrency conflict.

After executing the statements, EF Core reads the number of rows that were affected. For example, we may want to configure LastName on Person to be a concurrency token. Here is an example that illustrates this:. In short, the principle is to read a value from a given record and update this record based on the returned value inside the same transaction, with the insurance that no other session will modify the value that has just been read.

The experimentation scripts presented here are all designed using the AdventureWorks database. We are in a bank system. Your bank account has an initial balance of currency does not matter. You will find below the T-SQL statement to set up the test.

This will consist of your first session for which you will find T-SQL statement corresponding to the action that has to be done inside the bank system. Following code will be run:. To illustrate dirty reads, we will update data in Person. So, we can say that, by default, SQL Server protects you from dirty reads.

If you remind the explanation above about non-repeatable reads, this problem occurs when two consecutive reads of a given column value in a particular table row lead to two different values, meaning that values returned by a query are time-dependent even inside the same transaction.

Once again, we will use two sessions for this experiment. The first session will run a query returning five first rows from Person. Person table, wait for some time then rerun the exact same query. With this simple change, we have a lock that is held by first session. This leads the second session to wait for the first one to complete before actually modify rows.

In the next article, you will get more details about each transaction isolation level, included what common concurrency problem s they solve…. A SQL Sequence will return to me an incremental value based on its starting and increment values. All three of these sequences will start at 1, and will increment by 1 each time it is used. I wanted to start with zero, but the file's owner did not like that idea. The three statements are almost identical.

The only difference being which Sequence they use, lines 5. I did try and come up with a statement that would do it all, but no matter what I tried I received an error. This was my favorite failure:. If you know a way to update all three account types in one statement please put your suggestion in the comments, below.