Entity Framework更新丢失求助：插入正常但实体更新偶发失效

1. 修复代码中的语法错误 #

你提供的代码存在致命语法问题：new Context().Database.ExecuteSqlCommand(...)返回的是受影响行数（int类型），不能赋值给db变量用于后续的实体操作。正确的写法应该先创建DbContext实例，再执行SQL命令：

using (var scope = new TransactionScope())
{
    using (var db = new Context())
    {
        // 正确执行隔离级别设置
        db.Database.ExecuteSqlCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");

        var student = db.Students.SingleOrDefault(x => x.ID == 1);
        if (student == null) return;

        var schoolName = "TestSchool";
        db.Schools.Add(new School { Name = schoolName });
        student.School = schoolName;

        db.SaveChanges(); // 替换错误的db.Save()
    }
    scope.Complete();
}

2. 统一事务隔离级别管理，避免手动修改 #

手动执行SET TRANSACTION ISOLATION LEVEL会脱离TransactionScope的事务管理，导致隔离级别冲突（TransactionScope默认隔离级别为Serializable）。应通过TransactionOptions指定隔离级别，确保事务行为一致：

var transactionOptions = new TransactionOptions
{
    IsolationLevel = IsolationLevel.ReadCommitted, // 推荐使用ReadCommitted，避免脏读
    Timeout = TransactionManager.DefaultTimeout
};

using (var scope = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
{
    using (var db = new Context())
    {
        var student = db.Students.SingleOrDefault(x => x.ID == 1);
        if (student == null) return;

        var schoolName = "TestSchool";
        db.Schools.Add(new School { Name = schoolName });
        student.School = schoolName;

        db.SaveChanges();
    }
    scope.Complete();
}

3. 引入乐观并发控制解决高并发更新丢失 #

高并发场景下，多个事务同时修改同一Student实体时，会出现更新覆盖问题。给Student实体添加并发校验属性，EF会自动检测数据是否被其他事务修改：

public class Student
{
    public int ID { get; set; }
    public string School { get; set; }
    // 添加时间戳属性用于并发校验
    [Timestamp]
    public byte[] RowVersion { get; set; }
}

然后在保存时处理并发冲突，重试更新操作：

bool saveFailed;
do
{
    saveFailed = false;
    using (var scope = new TransactionScope())
    using (var db = new Context())
    {
        var student = db.Students.SingleOrDefault(x => x.ID == 1);
        if (student == null) break;

        var schoolName = "TestSchool";
        var newSchool = new School { Name = schoolName };
        db.Schools.Add(newSchool);

        student.School = schoolName;

        try
        {
            db.SaveChanges();
        }
        catch (DbUpdateConcurrencyException ex)
        {
            saveFailed = true;
            // 重新加载最新的实体数据，准备重试
            ex.Entries.Single().Reload();
        }
    }
} while (saveFailed);

4. 优化实体关联逻辑（若为外键关联场景） #

如果Student.School是外键关联属性，直接赋值名称存在可靠性问题（比如School名称重复）。建议通过School的ID关联：

using (var scope = new TransactionScope())
using (var db = new Context())
{
    var student = db.Students.SingleOrDefault(x => x.ID == 1);
    if (student == null) return;

    var schoolName = "TestSchool";
    var newSchool = new School { Name = schoolName };
    db.Schools.Add(newSchool);
    // 先保存School获取自动生成的ID
    db.SaveChanges();

    // 关联外键ID而非名称
    student.SchoolId = newSchool.ID;
    db.SaveChanges();
}
scope.Complete();

5. 避免使用Read Uncommitted隔离级别 #

Read Uncommitted会允许读取未提交的脏数据，在高并发场景下极易导致读取到过时的Student数据，修改后覆盖其他事务的合法变更。优先使用ReadCommitted或RepeatableRead隔离级别。

内容的提问来源于stack exchange，提问作者dotNaki