使用SQL中的联接进行原子更新 [英] Atomic update with joins in SQL

问题描述

我有一个表来跟踪已签出"的对象，但是对象存在于其他各种表中.目的是允许用户检出符合其条件的对象，这样一个给定的对象只能检出一次(即，两个用户都不能检出同一对象).在某些情况下，单个对象可能会跨越多个表，需要使用联接来检查所有用户条件(以防万一).

I've got a table that tracks "checked out" objects, but objects live in various other tables. The goal is to allow users to check out objects that match their criteria such that a given object can only be checked out once (i.e. no two users should be able to check out the same object). In some cases, a single object may span multiple tables, requiring the use of joins to check for all the user's criteria (in case that matters).

这是一个非常简单的示例查询(希望您可以推断出架构):

Here's a very simple example query (hopefully you can infer the schema):

update top (1) Tracker
set IsCheckedOut = 1
  from Tracker t
  join Object o on t.ObjectId = o.Id
  join Property p on p.ObjectId = o.Id
  where t.IsCheckedOut = 0
  and o.SomePropertyColumn = 'blah'
  and p.SomeOtherPropertyColumn = 42

由于from子查询，我怀疑此查询不是原子查询，因此两个同时请求相同风味对象的用户最终可能会检出同一对象.

Due to the from subquery, I suspect that this query is not atomic and therefore two users requesting the same flavor of object at the same time can end up checking out the same object.

是真的吗?如果是这样，我该如何解决?

Is that true? And if so, how do I fix this?

我考虑过添加output DELETED.*子句，如果IsCheckedOut列的返回值为1，则让用户重试他们的查询，我认为这样可以解决问题(如果我错了，请纠正我)...但是我想得到一些用户不必担心重试的地方.

I thought about adding an output DELETED.* clause and having the user retry their query if the returned value of the IsCheckedOut column is 1, which I think would work (correct me if I'm wrong)... But I'd like to get something where the user doesn't have to worry about retries.

编辑

有关详细说明，请参见下面的SqlZim的答案，但是对于这种简单的情况，我可以直接将提示添加到上面发布的查询中:

For a thorough explanation see SqlZim's answer below, but for this simple case I can just add the hints directly to the query posted above:

update top (1) Tracker
set IsCheckedOut = 1
  from Tracker t (updlock, rowlock, readpast)
  join Object o on t.ObjectId = o.Id
  join Property p on p.ObjectId = o.Id
  where t.IsCheckedOut = 0
  and o.SomePropertyColumn = 'blah'
  and p.SomeOtherPropertyColumn = 42

推荐答案

使用事务和某些表

Using a transaction and some table hints for locking, we can grab just one row and hold it to be updated.

declare @TrackerId int;

begin tran;

select top 1 @TrackerId = TrackerId
  from Tracker t with (updlock, rowlock, readpast)
    inner join Object o on t.ObjectId = o.Id
    inner join Property p on p.ObjectId = o.Id;
  where t.IsCheckedOut = 0
    and o.SomePropertyColumn = 'blah'
    and p.SomeOtherPropertyColumn = 42;

if @TrackerId is not null 
begin;
update Tracker
  set IsCheckedOut = 1
  where TrackerId = @TrackerId;
end;

commit tran

• updlock在我们的select中的行上放置了更新锁.其他事务将无法更新或删除该行，但允许他们选择该行，但是并发选择试图获取该行的更新锁(即，从具有相同搜索条件的差异过程中再次执行此过程)将无法选择该特定行，但是可以选择并锁定下一行，因为我们也在使用readpast.

  • updlock places an update lock on the row from our select. Other transaction will not be able to update or delete the row but they are allowed to select it, however a concurrent select trying to aquire an update lock on this row (i.e. another run of this procedure from a difference process with the same search criteria) will not be able to select this particular row, it can however select and lock the next row because we are also using readpast.

    rowlock尝试仅锁定我们将要更新的特定行，而不是页面或表锁定.

    rowlock tries to only lock the specific row we are going to update, instead of a page or table lock.

    readpast跳过具有行级锁的行.

    readpast skips rows that have row-level locks.

    参考文献:

    • 提示-msdn
    • 锁定模式
    • 锁定兼容性(数据库引擎)-msdn
    • Hints - msdn
    • Lock Modes
    • Lock Compatibility (Database Engine) - msdn

    使用通用表表达式替换一步代码:

    alternate one step code using a common table expression:

    begin tran;
    
      with cte as (
        select top 1 
            t.*
          from Tracker t with (updlock, rowlock, readpast)
            inner join Object o 
              on t.ObjectId = o.Id
            inner join Property p 
              on p.ObjectId = o.Id;
          where t.IsCheckedOut = 0
            and o.SomePropertyColumn = 'blah'
            and p.SomeOtherPropertyColumn = 42
          --order by TrackerId asc /* optional order by */
      )
      update cte
        set IsCheckedOut = 1
        output inserted.*;
    
    commit tran;
    

    这篇关于使用SQL中的联接进行原子更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！