c# - Entity Framework 事务

Question

我有一个程序在两台服务器上重复运行。我需要在程序中选择和更新数据库记录，所以我需要来自 EF 的表锁或事务，否则程序的每个副本都可以选择和修改相同的记录。虽然第一个副本使 DB 发生更改，但另一个副本不应运行相同的代码部分。

我在 EF 中找到了 TransactionScope，但它无法正常工作，因为当第一个副本正在运行时，我可以在 SQL Server MGM studio 中对该表进行多次选择和更新。

我有一个简短的代码片段，请验证它:

using (TransactionScope transaction = new TransactionScope())
{
    //select some records which aren't locked by the other copy of the program
    //condition: Locked==null
    recipientsList = (from c in context.Recipients
                      where
                          c.SentToPlatform == false && c.PopupID != null &&
                          c.Message.MessageStatus == 2 && c.Locked == null
                      select c).Take(piecePerMinute).ToList();

    foreach (var recipient in recipientsList)
    {
        //i need make some changes on the record, prevent it from the other copy of program
        //I need to change locked column to true
        recipient.Locked = true;
        recipient.LockBy = ipAddress;
        Console.Write("I");
        Thread.Sleep(1000);
    }

    //close transaction
    try
    {
        context.SaveChanges();
        transaction.Complete();
    } catch (Exception ex )
    {


    }
}

Answer 1

技术上您要求的是一个隔离级别高于已提交读取(默认级别)的长时间(呃)运行事务。没有足够的信息让我知道您是想要 RepeatableRead 还是 Serialzable(以避免幻像插入)。

你可以通过做这样的事情来完成你所要求的:

var opt = new TransactionOptions();
    opt.IsolationLevel = IsolationLevel.Serializable;

using (var scope = new TransactionScope(TransactionScopeOption.Required, opt) ){

   //read table code

   //write table code

   //save context & complete scope

}

话虽如此，我非常怀疑这是您真正想要的。可序列化事务会使数据库的大部分处于锁定状态。那是什么意思？以下是 Microsoft 对可序列化事务的描述:

SERIALIZABLE specifies the following:

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

....

Because concurrency is lower, use this option only when necessary.

正如@Bertie 指出的那样， Entity Framework 是围绕乐观并发模型构建的。使用乐观并发 (OC) 和大量模式来处理不可避免的冲突有很多原因。 OC 让你更高更有趣。对所有事物都使用 serializalbe 事务会让您像 12 只猴子中的布鲁斯威利斯 --- 塞满了大块的 Thorazine，流口水在您的软垫房间的地板上到处都是。你不想要那个，现在是吗？