Best Approach to handle Data Concurrency In 3-Tier Application

Question

Hi,

What is the best approach to handle Data Concurrency issues in a 3 layered Application (WCF + LinqToSql or EF) with detached entities or DTOs ?

Thanks.

Answer 1

Include all changed values in your where clause. If no rows are updated you know your data is stale and you can at that point inform the user and ask what action to take.

Answer 2

Hi,

This is built into LINQ to SQL and EF With UpdateCheck property of the Column attribute. My option would be to go with LINQ to SQL with detached POCO entities as long as you are definately only ever wanting to work with MS SQL SERVER and you dont require complicated datebase to entity mappings.

In this case I would use a single concurrency field in each of your tables and perform a concurrency check against that field only. An integer field is fine and simply increment it with each update.

See here for concurrency control using LINQ to SQL.

Concurrency with LINQ to SQL

Thought this would be handy too:

LINQ to SQL Optimistic Concurrency Overview

P.S This is all assuming that your solution is going to be fine using optimistic concurrency. If you are in a high load server where data is changing a lot then a more pessimistic concurrency solution may be required.

Answer 3

If you are not passing all you values using WCF you could introduce a timestamp (rowversion) field to you table. This value would be included as a property on you entity and your DTO. Using LINQ to SQL you have the option for using optimistic concurrency checking, it is possible to specify the new timestamp property instead of comparing all values