Data access code follows simple implementations patterns. One of very common patterns consist of fetching data from a database, modifying those data and persisting those data back to the database in the same transaction. Using this pattern in any concurrent environment where multiple threads (or processes) can work with the same record concurrently results in new challenges. How should our application handle scenario when another thread updated the record after the first thread fetched the record from database but before it was able to persist its changes back to the database? This area of database access implementation is in general referred as concurrency. We differ between two common solutions: Optimistic concurrency handling and Pessimistic concurrency handling.
Optimistic concurrency uses a special column in the table to track if the record was changed. The main requirement is that the column must change its value every time when the record is updated. Each relational database offers a special data type for this purpose which is automatically updated every time its record is updated. Microsoft SQL Server offers deprecated TIMESTAMP and newer recommended ROWVERSION data types for this purpose.
If we use Optimistic concurrency to solve the initial scenario with two threads the first thread will load the record together with an actual value of the time stamp and when it tries persisting the change it uses time stamp's value in WHERE condition of UPDATE command together with the record identification (for example key). If another thread updates the record in the mean time the update from the first thread doesn't find any record matching its WHERE condition. This will result in 0 updated records (in Microsoft SQL Server this information is received by executing SELECT @@ROWCOUNT query after the update command) and the thread should somehow react to this situation. The reaction follows common pattern of reloading the record to get current values, applying changes to reloaded data and persisting them again. The real implementation of this pattern may involve for example user interaction where a user must resolve the conflict. Entity framework has direct support for Optimistic concurrency in both ObjectContext API (also shows required mapping in EDMX) and in DbContext API. Code first supports mapping in both data annotations and fluent API.
Pessimistic concurrency doesn't use any special column but it instead uses transactional and locking mechanisms offered by relational database. The whole idea of pessimistic concurrency can be compared to critical section. The fetching of the record, its update and followed persistence must be considered as atomic operation which can be executed by only single thread (per record or record set). When the first thread enters this critical section no other thread can modify the record or enter critical section for the same record. Using this mechanism requires understanding of transaction isolation levels and locking mechanism in used database. The bad news is that Entity framework doesn't have any built-in support for Pessimistic concurrency.
This article was inspired by question asked by Ari on Stack Overflow. The rest of the article will show why base Entity framework update patterns do not provide pessimistic concurrency and why approach described in the mentioned question from Stack Overflow doesn't work. The article will also provide a real solution for pessimistic concurrency which requires usage of native SQL.