Entity Framework Deadlocks

c# entity-framework sql-server


I've been working on a specific implementation, however I'm running into issues. I have a simple procedure that, all within a transaction, establishes a new context, searches a table to retrieve the "LastNumberUsed," runs some quick checks on this number, and then increments and writes back.

I've created a simple Parallel test application. to carry out this process five times. Utilizing Isolation Serialization Running this code causes a lot of Deadlock errors, I've seen. I tried setting the isolation level to snapshot after reading a little about the matter. I now experience isolation update conflict errors rather than deadlocks.

Really unsure on what to do. I've been experimenting with the notion of wrapping the function in a try..catch, checking for a deadlock issue, and running again but this feels like a messy solution because each transaction takes about 0.009 seconds to finish.

Does anyone have suggestions for solving this issue, or better yet, experience?

To illustrate this, I wrote a console application. I run the following code in the program main:

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

This is how the method TestContract looks:

//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

        //Increment the contract number

        //Save the changes made to the context

        //Complete the scope
9/30/2014 7:13:03 AM

Popular Answer

Let's ignore the isolation level for the time being and concentrate on what your code is doing instead:

You have five tasks running simultaneously, each of which callsTestContract passing similarlycontractId all of them, correct?

In theTestContract You obtain thecontract by itsid after working with it, increase theNumber belongings within the contract

In a transaction boundary, all of this.

Why do deadlocks occur?

It's crucial to comprehend what the problem is if you want to figure out why you are hitting a deadlock.Serializable Means by isolation level.

The SQL Server Isolation Levels documentation states this regardingSerializable (my emphasis):

  • 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.

For the purposes of this example, let's return to your code and assume that there are only two simultaneous tasks.TaskA and TaskB with contractId=123 , all as part of a deal withSerializable Detachment Level.

Let's attempt to explain what the code is doing during this execution:

  • TaskA begins.
  • TaskB begins.
  • TaskA develops aTransaction 1234 containing Serializable Isolation Level
  • TaskB ProducesTransaction 5678 containing Serializable Isolation Level
  • TaskA performs aSELECT * FROM ContractRanges WHERE ContractId = 123 . This moment. Using a lock, SQL Server secures theContractRanges table, in the column thatContractId = 123 in order to stop subsequent transactions from changing that data.
  • TaskB performs the sameSELECT both a statement and alock in theContractId = 123 line of theContractRanges table.

Since you created two transactions, we currently have two locks on the same row.

  • then TaskA increase theNumber of the agreement
  • the TaskB increaseNumber contract's property

  • Calls from TaskASaveChanges This tries to commit the transaction in turn.

As a result, when you attempt to commit transaction1234 , we are attempting to change theNumber value in a row where a transaction-created lock is in place5678 Consequently, SQL Servers begin to wait for thelock to be free to complete the transaction as you asked, please.

  • TaskB Additionally, callsSaveChanges similar to what occurred withTaskA it is attempting to increase theNumber of the Agreement123 . It discovers an in this caselock within that row produced by the transaction1234 from TaskA .

We currently have Transaction.1234 from TaskA awaiting the Transaction lock5678 upcoming and Transaction5678 awaiting the Transaction lock1234 to be let go. Which means that we are in a deadlock since both transactions are preventing one another from being completed.

Once SQL Server recognizes that a stalemate has occurred, it selects one of the transactions as avictim eliminate it and let the other one go.

Regarding the Isolation Level, I don't know enough about what you're attempting to do to be able to judge whether you truly needSerializable although there's a strong possibility you won't require it.Serializable is the strictest and safest isolation level, and as we saw, it does this by sacrificing concurrency.

If you're in dire needSerializable ensures you shouldn't be attempting to alter theNumber of the same contract at the same time.

The Snapshot Isolation alternative

You stated:

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.

In the event that you decide to employ Snapshot Isolation, that is precisely the conduct you desire. For this reasonSnapshot utilizes a model Confident Concurrency.

The following definition is found in the same MSDN documents (again, emphasis added):

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 attempt to explain what happens to the code during Snapshot Isolation execution:

  • Suppose the starting value ofNumber is 2 for agreement123
  • TaskA begins.
  • TaskB begins.
  • TaskA develops aTransaction 1234 along with Snapshot Isolation Level
  • TaskB ProducesTransaction 5678 along with Snapshot Isolation Level

In both images,Number = 2 with Contract123 .

  • TaskA performs aSELECT * FROM ContractRanges WHERE ContractId = 123 . while running underneathSnapshot There are none in solitude.locks .

  • TaskB performs the sameSELECT furthermore, does not make anylocks .

  • then TaskA increase theNumber of the agreement to3
  • the TaskB increaseNumber contract's property to3

  • Calls from TaskASaveChanges which prompts SQL Server to compare the Snapshot taken when the transaction was created with the current state of the DB and the uncommitted changes made under this transaction. It commits the transaction since it finds no conflicts, so this is what has happened.Number has the value3 within the database.

  • TaskB Additionally, callsSaveChanges it tries to commit its transaction and does so. SQL Server detects a conflict when it compares the values from the transaction snapshot with the values currently stored in the database. The Snapshot showsNumber the worth of2 and it currently has a value of3 . Then, it launches theUpdate Exception .

Once more, there were no impasses, butTaskB this time failed becauseTaskA changed the information that was also used inTaskB .

Solutions to this

Now that we have discussed what occurs when you run your code underSerializable and Snapshot Levels of Isolation: What Can You Do?fix it.

The first thing to think about is if it makes sense for you to be simultaneously mutating the same.Contract record. I would try to grasp that first since that is the first significant odor in your code that I have seen. To determine whether your company actually requires this concurrency on the contract, you should probably talk to them about it.

As we've shown, if you absolutely require something to occur concurrently, you can't actually useSerializable since that would result indeadlocks as you observed. Thus, what is left isSnapshot isolation.

Currently, if you catch anOptmisticConcurrencyException In the end, the choice actually rests with you and your company.

Using an error message to alert the user that the data they are trying to update has been modified and asking them if they want to refresh the screen to acquire the most recent version of the data and, if necessary, try to do the same action again are two examples of how to manage the situation.

If that's not the case and it's acceptable for you to retry, another choice is for you to include retry logic in your code, which would retry carrying out the action when a failure occurred.OptmitisticConcurrencyException is hurled. This is based on the presumption that there won't be a contemporaneous transaction changing the same data at this second attempt, and the operation will thus proceed.

2/4/2016 6:19:09 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