entity framework 6 and pessimistic concurrency

concurrency entity-framework-6 pessimistic-locking serializable transactionscope


I'm engaged in a project to progressively retire a legacy application. We integrate the old application utilising the database during the process as a temporary fix.

The old application makes advantage of serializable isolation level transactions. I am now better suited adopting the same pessimistic concurrency model and serializable isolation level due to database interaction with an older application.

These serialised transactions should also contain certain data readings in addition to being wrapped around the SaveChanges declaration.

By doing this

  • I created a transactionScope with a serialised isolation level around my database context.
  • Establish a DbContext.
  • Read some books.
  • Make adjustments to the items
  • Call the DbContext's SaveChanges function.
  • the transaction's scope; commit (thus saving the changes)

I believe that this serialises all of my reads and writes into a single transaction before committing.

This, in my opinion, is a sort of pessimistic concurrency.

However, https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/handling-concurrency-with-the-entity-framework-in-an-asp-net-mvc-application asserts in this article that ef does not enable pessimistic concurrency.

My query is this:

  • Question: Can I use a serializable transaction to enable reads and writes in EF?
  • B: Combining reads and writes into a single transaction ensures that my read data is not altered when the transaction is committed.
  • C: You realise that this is a kind of pessimistic concurrency?
5/11/2017 10:05:21 AM

Popular Answer

Pessimistic concurrency may be achieved, for example, by using something like this:

var options = new TransactionOptions
   IsolationLevel = System.Transactions.IsolationLevel.Serializable,
   Timeout = new TimeSpan(0, 0, 0, 10)

using(var scope = new TransactionScope(TransactionScopeOption.RequiresNew, options))
{ ... stuff here ...}

It seems that with VS2017, you must right-click TransactionScope before it will create a reference for: Microsoft Framework.NET Framework Version 4.6.1 Reference Assemblies System.Transactions.dll

One thread will succeed while the other times out after 10 seconds if two threads try to increase the same number, however. This is because, in order to save changes, both transactions must upgrade their shared locks to exclusive locks, but they are unable to do so since another transaction is already holding a shared lock on the same row. After some time passes with no successful transactions to break the deadlock, SQL Server will then recognise the deadlock. If one transaction fails, the shared lock will be released, allowing the second transaction to upgrade its shared lock to an exclusive lock and continue with execution.

The solution to this deadlocking is to inform the database with a UPDLOCK hint using something like:

private static TestEntity GetFirstEntity(Context context) {
return context.TestEntities
              .SqlQuery("SELECT TOP 1 Id, Value FROM TestEntities WITH (UPDLOCK)")

This code was taken from Ladislav Mrnka's blog, which seems to no longer be accessible. The use of optimistic locking is the second option.

7/20/2019 1:01:58 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow