多个人可以同时更新SQL Server中的相同记录吗？

I have a SQL statement similar to the one below. Basically, it assigns an item to a user with an assigned status, if the item was in an unassigned status. If two people call this statement at exactly the same time, can they both update the same record with their user id, or does the database automatically lock the record as it is happening, because it is an update? I am currently not running the statement in any kind of transaction. Do I need to to ensure that only one person can set the row to assigned?

我有一个类似于下面的SQL语句。基本上,如果项目处于未分配状态,它会将项目分配给具有指定状态的用户。如果两个人在同一时间调用此语句,他们是否可以使用其用户ID更新相同的记录,或者数据库是否会自动锁定记录,因为它是更新?我目前没有在任何类型的交易中运行该声明。我是否需要确保只有一个人可以将行设置为已分配?

Update top (1) QUE.[Queue] set
                QueueStatusId = 2 -- set to assigned
                , QueueAssignedUserId = @QueueAssignedUserId
                , QueueAssignedDateTimeUTC = getutcdate()
from QUE.[Queue] updateq
where updateq.QueueStatusId = 1 -- only select unassigned

Update:

Here is a slightly closer representation of my code. You can see that I do not actually have a where clause in here to ensure that I only get items with a status of 1. Instead, I join to the table, taking into account the status and an assignment expiration date. Is it possible for that inner join query to be snapshotted, so that multiple users are joining off of a dirty copy of the data?

这是我的代码略微更接近的表示。您可以看到我实际上没有where子句,以确保我只获得状态为1的项目。相反,我加入到表中,考虑到状态和分配到期日期。是否可以对该内部联接查询进行快照,以便多个用户加入数据的脏副本?

Update top (1) QUE.[Queue] set
                QueueStatusId = 2 -- set to assigned
                , QueueAssignedUserId = @QueueAssignedUserId
                , QueueAssignedDateTimeUTC = getutcdate()
from QUE.[Queue] updateq
inner join (select * from QUE.[Queue] 
    where QueueStatusId = 1 
        or (QueueStatusId = 2 and QueueAssignmentExpirationDateTimeUTC > getutcdate()) qStatuses 
    on qStatuses.QueueId = updateq.QueueId

1 个解决方案

#1

Every statement is wrapped in an implicit transaction. So if the statement was executed twice at nearly the same time... SQL Server would pick one of them, execute the update, and then the second update execution would not be able to pick that record because it would have had it's QueueStatusId field changed in the first query.

每个语句都包含在一个隐式事务中。因此,如果语句几乎同时执行了两次...... SQL Server将选择其中一个,执行更新,然后第二次更新执行将无法选择该记录,因为它会更改它的QueueStatusId字段在第一个查询中。

The sub-query in your second version however can cause a race condition if not wrapped in an explicit transaction. It could be possible that two users both run the sub-query before either one updates and then both try to update the same record. You can structure your update without the sub-query and eliminate the possibility of a race condition:

但是,如果未在显式事务中包装,则第二个版本中的子查询可能会导致竞争条件。有两个用户可能在任何一个更新之前都运行子查询,然后两个用户都尝试更新相同的记录。您可以在没有子查询的情况下构建更新,并消除竞争条件的可能性:

Update top (1) QUE.[Queue]
set
    QueueStatusId = 2 -- set to assigned
    , QueueAssignedUserId = @QueueAssignedUserId
    , QueueAssignedDateTimeUTC = getutcdate()
from QUE.[Queue] updateq
where QueueStatusId = 1 
    or (QueueStatusId = 2 and QueueAssignmentExpirationDateTimeUTC > getutcdate())

This question also includes some helpful information and links: Transaction isolation levels and subqueries

此问题还包括一些有用的信息和链接:事务隔离级别和子查询