Menu
We are trying to build a High-Volume Orders Record System. There are three primary tables: 1. Orders 2. OrderDetails 3. OrderShipment
The Shipment table contains n record per order and any record shipment entry can be changed before the Customer accepts th order, after which it is frozen. (A business requirement)
Although this may not happen in real world scenarios... during our load tests, we are getting System.Data.Linq.ChangeConflictException exceptions. Wrapping up the submit inside a transacion is not helping either. Can't we force LINQ to get a lock on the row for the entire duration of the update operation?
Is there any other way to get over this?
448
LINQ gives three ways by which we can handle concurrency conflicts. To handle concurrency conflicts, we need to wrap the LINQ to SQL code in a TRY block and catch the ChangeConflictException . We can then loop through the ChangeConflicts collection to specify how we want the conflict to be resolved.
LINQ to SQL was the first object-relational mapping technology released by Microsoft. It works well in basic scenarios and continues to be supported in Visual Studio, but it's no longer under active development.
By default, LINQ to SQL uses optimistic concurrency to handle concurrent updates. Optimistic concurrency does not involve locking rows when reading. Instead, this model checks if two users tried to update the same record at the same time.
Stored procedures are faster as compared to LINQ query since they have a predictable execution plan and can take the full advantage of SQL features. Hence, when a stored procedure is being executed next time, the database used the cached execution plan to execute that stored procedure.
If you are having genuine issues with concurrent updates on the same data, then you might consider performing the entire operation in a transaction - i.e. getting the data and committing it. As long as you treat the get/update/commit as a short-lived, atomic operation (i.e. you don't pause for user-input in the middle) it should be OK.
In particular, with a serializable isolation level, nobody can update data that you have a read lock on (i.e. anything you have queried). The only problem is that this might lead to deadlock scenarios if different queries are reading data in different orders. AFAIK, there is no way to get LINQ-to-SQL to issue the (UPDLOCK) hint, which is a shame.
Either a TransactionScope or a SqlTransaction would do, as long as they are set as serializable isolation (which is the default for TransactionScope).
180
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
