防止实体框架中 if-exists-update-else-insert 的竞争条件

问题描述

我一直在阅读有关如何在 EF 中实现 if-exists-insert-else-update 语义的其他问题，但要么我不明白答案是如何工作的，要么他们实际上没有解决这个问题.提供的常见解决方案是将工作包装在事务范围内(例如:在没有竞争条件的情况下使用实体框架实现 if-not-exists-insert):

I've been reading other questions on how to implement if-exists-insert-else-update semantics in EF, but either I'm not understanding how the answers work, or they are in fact not addressing the issue. A common solution offered is to wrap the work in a transaction scope (eg: Implementing if-not-exists-insert using Entity Framework without race conditions):

using (var scope = new TransactionScope()) // default isolation level is serializable
using(var context = new MyEntities())
{
    var user = context.Users.SingleOrDefault(u => u.Id == userId); // *
    if (user != null)
    {
        // update the user
        user.property = newProperty;
        context.SaveChanges();
    }
    else
    {
        user = new User
        {
             // etc
        };
        context.Users.AddObject(user);
        context.SaveChanges();
    }
}

但我看不出这是如何解决任何问题的，至于它的工作原理，如果第二个线程尝试访问相同的用户 ID，我上面加星标的行应该阻塞，只有当第一个线程完成了它的工作.但是，使用事务不会导致这种情况，并且由于第二个线程尝试第二次创建同一用户时发生的密钥冲突，我们将抛出 UpdateException.

But I fail to see how this solves anything, as for this to work, the line I have starred above should block if a second thread tries to access the same user ID, unblocking only when the first thread has finished its work. Using a transaction will not cause this however, and we'll get an UpdateException thrown due to the key violation that occurs when the second thread attempts to create the same user for a second time.

与其捕获由竞争条件引起的异常，不如首先防止竞争条件发生.一种方法是让带星号的行在与其条件匹配的数据库行上取出排他锁，这意味着在此块的上下文中，一次只有一个线程可以与用户一起工作.

Instead of catching the exception caused by the race condition, it would be better to prevent the race condition from happening in the first place. One way to do this would be for the starred line to take out an exclusive lock on the database row that matches its condition, meaning that in the context of this block, only one thread at a time could work with a user.

对于 EF 的用户来说，这似乎是一个常见问题，因此我正在寻找一种可以在任何地方使用的干净、通用的解决方案.

It seems that this must be a common problem for users of the EF, so I'm looking for a clean, generic solution that I can use everywhere.

如果可能的话，我真的很想避免使用存储过程来创建我的用户.

I'd really like to avoid using a stored procedure to create my user if possible.

有什么想法吗?

编辑:我尝试使用相同的用户 ID 在两个不同的线程上并发执行上述代码，尽管取出了可序列化的事务，但它们都能够同时进入临界区 (*).这会导致当第二个线程尝试插入与第一个刚刚插入的用户 ID 相同的用户 ID 时抛出 UpdateException.这是因为，正如下面 Ladislav 所指出的，可序列化事务仅在开始修改数据后才使用排他锁，而不是读取.

EDIT: I tried executing the above code concurrently on two different threads using the same user ID, and despite taking out serializable transactions, they were both able to enter the critical section (*) concurrently. This lead to an UpdateException being thrown when the second thread attempted to insert the same user ID that the first had just inserted. This is because, as pointed out by Ladislav below, a serializable transaction takes exclusive locks only after it has begun modifying data, not reading.

推荐答案

当使用可序列化事务时，SQL Server 会在读取记录/表上发出共享锁.共享锁不允许其他事务修改锁定的数据(事务会阻塞)，但它允许其他事务在发出锁的事务开始修改数据之前读取数据.这就是该示例不起作用的原因 - 在第一个事务开始修改数据之前，允许使用共享锁进行并发读取.

When using serializable transaction SQL Server issues shared locks on read records / tables. Shared locks doesn't allow other transactions modifying locked data (transactions will block) but it allows other transactions reading data before the transaction which issued locks start modifying data. That is the reason why the example doesn't work - concurrent reads are allowed with shared locks until the first transaction starts modifying data.

您需要隔离，其中 select 命令专门为单个客户端锁定整个表.它必须锁定整个表，否则将无法解决插入相同"记录的并发问题.使用提示时，可以通过选择命令对锁定记录或表进行粒度控制，但您必须编写直接 SQL 查询才能使用它们 - EF 不支持.我描述了专门锁定该表的方法 在这里，但这就像创建对该表的顺序访问，它会影响访问该表的所有其他客户端.

You want isolation where select command locks the whole table exclusively for a single client. It must lock the whole table because otherwise it will not solve concurrency for inserting "the same" record. Granular control for locking records or tables by select commands is possible when using hints but you must write direct SQL queries to use them - EF has no support for that. I described approach for exclusively locking that table here but it is like creating sequential access to the table and it affects all other clients accessing this table.

如果您真的确定此操作仅在您的单个方法中发生并且没有其他应用程序使用您的数据库，您可以简单地将代码放入临界区(.NET 同步，例如使用 lock) 并确保在 .NET 端只有单线程可以访问临界区.这不是那么可靠的解决方案，但任何使用锁和事务级别的操作都会对数据库性能和吞吐量产生重大影响.您可以将此方法与乐观并发(唯一约束、时间戳等)结合使用.

If you are really sure that this operation happens just in your single method and there are not other applications using your database you can simply place the code into critical section (.NET synchronization for example with lock) and ensure on the .NET side that only single thread can access critical section. That is not so reliable solution but any playing with locks and transaction levels has a big impact on the database performance and throughput. You can combine this approach with optimistic concurrency (unique constraints, timestamps, etc).

这篇关于防止实体框架中 if-exists-update-else-insert 的竞争条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！