Entity Framework Couplings

Question

I am having problems with the specific implementation I'm working on. I have a basic method that creates a new context, queries the table and gets "LastNumberUsed" from the table, performs some basic checks on this number, before finally incrementing and returning everything inside the transaction.

I wrote a basic test application that uses Parallel.For to execute this method 5 times. Using Isolation.Serialization I believe that when I run this code I get a lot of Deadlock errors. I read this topic a bit and tried to change the isolation level to a snapshot. I no longer receive deadlocks, but instead find that I am having conflicts with removing isolation.

I am really at a loss what to do. Each transaction takes approximately 0.009 seconds, so I played with the idea of ​​wrapping the code in try..catch, checking for a deadlock error and running it again, but this seems like a messy solution.

Does anyone have any ideas (or, preferably, experience) on how to deal with this problem?

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

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

The TestContract method 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

Having reliably set aside the isolation level for a while, let's focus on what your code does:

5 Parallel, TestContract, contractId , ?

TestContract contract id, , Number .

.

?

, , , Serializable.

SQL Server Serializable ( ):

• , , .
• , , .
• , , .

, , . , , . , - , . . , . concurrency , . , HOLDLOCK SELECT .

, , , , TaskA TaskB contractId=123, Serializable .

, :

• TaskA
• TaskB
• TaskA Transaction 1234 Serializable Isolation Level
• TaskB Transaction 5678 Serializable Isolation Level
• TaskA SELECT * FROM ContractRanges WHERE ContractId = 123. . SQL Server ContractRanges , ContractId = 123, .
• TaskB SELECT, lock ContractId = 123 ContractRanges.

, , .

• TaskA Number

• TaskB Number

• TaskA SaveChanges, , , .

, 1234, Number , , 5678, SQL lock , , .

• TaskB, SaveChanges, , TaskA, Number 123. lock , 1234 TaskA.

1234 TaskA, Transaction 5678, 5678, Transaction 1234, , , , , .

SQL Server , , victim, .

, , , Serializable, , . Serializable - , , concurrency, .

Serializable, Number .

Snapshot Isolation

:

. , , .

, , . , Snapshot concurrency.

MSDN ( , ):

, , , , , . . , , . , , .

, , SNAPSHOT . SNAPSHOT . , , SNAPSHOT .

, SNAPSHOT , , , . SNAPSHOT , . .

ALLOW_SNAPSHOT_ISOLATION ON, , SNAPSHOT . SNAPSHOT , ALLOW_SNAPSHOT_ISOLATION ON .

SNAPSHOT , ; . SNAPSHOT , , SNAPSHOT. .

, SNAPSHOT, , . , UPDATE , SELECT , .

, " ":

• , Number 2 123
• TaskA
• TaskB
• TaskA Transaction 1234
• TaskB Transaction 5678

Number = 2 123.

• TaskA SELECT * FROM ContractRanges WHERE ContractId = 123. Snapshot , locks.

• TaskB SELECT, locks.

• TaskA Number 3

• TaskB Number 3

• TaskA, SaveChanges, , , SQL Server , , , , , , , Number 3 .

• TaskB, SaveChanges . SQL Server , , . Number 2, 3. Update Exception.

, , TaskB , TaskA , TaskB.

, , , Serializable Snapshot , fix it.

, , , - , contract. , , . , , , concurrency .

, , , , Serializable, deadlocks, . , Snapshot .

, OptmisticConcurrencyException, , .

, , , , , , , , , , , , , .

, , - , OptmitisticConcurrencyException. , , , .