Entity Framework Deadlocks

Question

I am having problems with a particular implementation I have been working on. I have a basic method that creates a new context, queries a table and gets the "LastNumberUsed" from the table, performs some basic checks on this number before finally incrementing and writing back - all within a transaction.

I have written a basic test application that uses Parallel.For to execute this method 5 times. Using Isolation.Serialization I'm finding I get a lot of Deadlock errors when running this code. I have read a bit on this subject and tried changing the isolation level to snapshot. I no longer get deadlocks but instead find I get isolation update conflict errors.

I'm really at a loss what to do. Each transaction takes approximately 0.009 seconds to complete so I have been toying with the idea of wrapping the code in a try..catch, checking for a deadlock error and running again but this feels like a messy solution.

Does anybody have any ideas (or preferably experience) of how to deal with this problem?

I have created a console application to demonstrate this. In program main I run the following code:

    Parallel.For(0, totalRequests, 
          x => TestContract(x, contractId, incrementBy, maxRetries));

The method TestContract looks like this:

//Define the context
using (var context = new Entities())
{
    //Define a new transaction
    var options = new TransactionOptions {IsolationLevel = IsolationLevel.Serializable};
    using (var scope = new TransactionScope(TransactionScopeOption.Required, options))
    {
        //Get the contract details
        var contract = (
            from c in context.ContractRanges
            where c.ContractId == contractId
            select c).FirstOrDefault();

        //Simulate activity
        Threading.Thread.sleep(50);

        //Increment the contract number
        contract.Number++;

        //Save the changes made to the context
        context.SaveChanges();

        //Complete the scope
        scope.Complete();
    }
}
    }

Answer 1

Putting the Isolation Level aside for a moment, let's focus on what your code is doing:

You are running 5 Tasks in Parallel that make a call to TestContract passing the same contractId for all of them, right?

In the TestContract you fetch the contract by its id, do some work with it, then increments the Number property of the contract.

All this within a transaction boundary.

Why deadlocks?

In order to understand why you are running into a deadlock, it's important to understand what the Serializable Isolation Level means.

The documentation of SQL Server Isolation Levels says the following about Serializable (emphasis mine):

• Statements cannot read data that has been modified but not yet committed by other transactions.
• No other transactions can modify data that has been read by the current transaction until the current transaction completes.
• Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction. This blocks other transactions from updating or inserting any rows that would qualify for any of the statements executed by the current transaction. This means that if any of the statements in a transaction are executed a second time, they will read the same set of rows. The range locks are held until the transaction completes. This is the most restrictive of the isolation levels because it locks entire ranges of keys and holds the locks until the transaction completes. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.

Going back to your code, for the sake of this example, let's say you have only two tasks running in parallel, TaskA and TaskB with contractId=123, all under a transaction with Serializable Isolation Level.

Let's try to describe what is going on with the code in this execution:

• TaskA Starts
• TaskB Starts
• TaskA Creates a Transaction 1234 with Serializable Isolation Level
• TaskB Creates Transaction 5678 with Serializable Isolation Level
• TaskA makes a SELECT * FROM ContractRanges WHERE ContractId = 123. At this point. SQL Server puts a lock in the ContractRanges table, in the row where ContractId = 123 to prevent other transactions from mutating that data.
• TaskB makes the same SELECT statement and also puts a lock in the ContractId = 123 row of the ContractRanges table.

So, at this point, we have two locks on that same row, one for each transaction that you created.

• TaskA then increment the Number of the contract

• TaskB increment the Number property of the contract

• TaskA calls, SaveChanges which, in turn, tries to commit the transaction.

So, when you try to commit transaction 1234, we are trying to modify the Number value in a row that has a lock created by transaction 5678 so, SQL Servers starts to wait for the lock to be release in order to commit the transaction like you requested.

• TaskB, then, also calls SaveChanges, and like it happened with TaskA, it is trying to increment the Number of the Contract 123. In this case, it finds a lock on that row created by transaction 1234 from TaskA.

Now we have Transaction 1234 from TaskA waiting on the lock from Transaction 5678 to be released and Transaction 5678 waiting on the lock from Transaction 1234 to be released. Which means that we are on a deadlock as neither transaction will never be able to finish as they are blocking each other.

When SQL Server identifies that it is in a deadlock situation, it chooses one of the transactions as a victim, kill it and allow the other one to proceed.

Going back to the Isolation Level, I don't have enough details about what you are trying to do for me to have an opinion if you really need Serializable, but there is a good chance that you don't need it. Serializable is the most safe and strict isolation level and it achieves that by sacrificing concurrency, like we saw.

If you really need Serializable guarantees you really should not be trying to update the Number of the same contract concurrently.

The Snapshot Isolation alternative

You said:

I have read a bit on this subject and tried changing the isolation level to snapshot. I no longer get deadlocks but instead find I get isolation update conflict errors.

That's exactly the behavior that you want, should you choose to use Snapshot Isolation. That's because Snapshot uses an Optimistic Concurrency model.

Here is how it's defined on the same MSDN docs (again, emphasis mine):

Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction. The transaction can only recognize data modifications that were committed before the start of the transaction. Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The effect is as if the statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction.

Except when a database is being recovered, SNAPSHOT transactions do not request locks when reading data. SNAPSHOT transactions reading data do not block other transactions from writing data. Transactions writing data do not block SNAPSHOT transactions from reading data.

During the roll-back phase of a database recovery, SNAPSHOT transactions will request a lock if an attempt is made to read data that is locked by another transaction that is being rolled back. The SNAPSHOT transaction is blocked until that transaction has been rolled back. The lock is released immediately after it has been granted.

The ALLOW_SNAPSHOT_ISOLATION database option must be set to ON before you can start a transaction that uses the SNAPSHOT isolation level. If a transaction using the SNAPSHOT isolation level accesses data in multiple databases, ALLOW_SNAPSHOT_ISOLATION must be set to ON in each database.

A transaction cannot be set to SNAPSHOT isolation level that started with another isolation level; doing so will cause the transaction to abort. If a transaction starts in the SNAPSHOT isolation level, you can change it to another isolation level and then back to SNAPSHOT. A transaction starts the first time it accesses data.

A transaction running under SNAPSHOT isolation level can view changes made by that transaction. For example, if the transaction performs an UPDATE on a table and then issues a SELECT statement against the same table, the modified data will be included in the result set.

Let's try to describe what is going on with the code when it executes under Snapshot Isolation:

• Let's say the initial value of Number is 2 for contract 123
• TaskA Starts
• TaskB Starts
• TaskA Creates a Transaction 1234 with Snapshot Isolation Level
• TaskB Creates Transaction 5678 with Snapshot Isolation Level

In both snapshots, Number = 2 for Contract 123.

• TaskA makes a SELECT * FROM ContractRanges WHERE ContractId = 123. As we are running under Snapshot isolation, there are no locks.

• TaskB makes the same SELECT statement and also does not put any locks.

• TaskA then increment the Number of the contract to 3

• TaskB increment the Number property of the contract to 3

• TaskA calls, SaveChanges which, in turn, causes SQL Server to compare the Snapshot created when the transaction was created and the current state of the DB as well as of the uncommitted changes that were made under this transaction. As it doesn't find any conflicts, it commits the transaction, and now Number has a value of 3 in the database.

• TaskB, then, also calls SaveChanges, and tries to commit its transaction. When SQL Server compares the transactions Snapshot values with the ones currently at the DB it sees a conflict. In the Snapshot, Number had a value of 2 and now it has a value of 3. It, then, throws the Update Exception.

Again, there were no deadlocks, but TaskB failed this time because TaskA mutated the data that was also being used in TaskB.

How to fix this

Now that we covered what is going on with your code when you run it under Serializable and Snapshot Isolation Levels, what can you do to fix it.

Well, the first thing you should consider is if really makes sense for you to be concurrently mutating the same Contract record. This is the first big smell that I saw in your code and I would try to understand that first. You probably need to discuss this with your business to understand if they really need this concurrency on the contract.

Assuming you really need this to happen concurrently, as we saw, you can't really use Serializable as that would incur in deadlocks like you saw. So, we are left with Snapshot isolation.

Now, when you catch an OptmisticConcurrencyException it is really up to you handle depends on you and your business to decide.

For example, one way to handle it is to simply delegate to the user to decide what to do by displaying an error message to the user informing that the data they are trying to change have been modified and ask them if they want to refresh the screen to get the latest version of the data and, if needed, try to perform the same action again.

If that is not the case, and it's OK for you to retry, another option is for you to have a retry logic in your code that would retry performing the operation when a OptmitisticConcurrencyException is thrown. This is based on the assumption that at this second time, there won't be a concurrent transaction mutating the same data and the operation will now succeed.