SQL 获取排他锁的正确方法

Correct way to take a exclusive lock

提问by Scott Chamberlain

I am writing a procedure that will be reconciling finical transactions on a live database. The work I am doing can not be done as a set operation so I am using two nested cursors.

我正在编写一个程序来协调实时数据库上的最终交易。我正在做的工作不能作为集合操作来完成，所以我使用了两个嵌套游标。

I need to take a exclusive lock on the transaction table while I am reconciling per client, but I would like to release the lock and let other people run their queries in between each client I process.

在对每个客户端进行协调时，我需要对事务表进行独占锁定，但我想释放锁定并让其他人在我处理的每个客户端之间运行他们的查询。

I would love to do a exclusive lock on a row level instead of a table level, but what I have read so farsays I can not do with (XLOCK, ROWLOCK, HOLDLOCK)if the other transactions are running at READCOMMITEDisolation level (which it is for me).

我很想在行级别而不是表级别上做一个排他锁，但是我到目前为止读到的内容说，with (XLOCK, ROWLOCK, HOLDLOCK)如果其他事务在READCOMMITED隔离级别运行（这对我来说），我就不能这样做。

Am I taking a table level exclusive lock correctly, and is there any way in Server 2008 R2 to make row level exclusive locks work the way I want to without modifying the other queries running on the database?

我是否正确使用了表级排他锁，Server 2008 R2 中是否有任何方法可以使行级排他锁按照我想要的方式工作，而无需修改在数据库上运行的其他查询？

declare client_cursor cursor local forward_only for 
     select distinct CLIENT_GUID from trnHistory
open client_cursor

declare @ClientGuid uniqueidentifier
declare @TransGuid uniqueidentifier

fetch next from client_cursor into @ClientGuid
WHILE (@@FETCH_STATUS <> -1)
BEGIN
    IF (@@FETCH_STATUS <> -2)
    BEGIN
        begin tran

        declare @temp int

        --The following row will not work if the other connections are running READCOMMITED isolation level
        --select @temp = 1 
    --from trnHistory with (XLOCK, ROWLOCK, HOLDLOCK) 
    --left join trnCB with (XLOCK, ROWLOCK, HOLDLOCK) on trnHistory.TRANS_GUID = trnCB.TRANS_GUID
    --left join trnClients with (XLOCK, ROWLOCK, HOLDLOCK) on trnHistory.TRANS_GUID = trnClients.TRANS_GUID
    --(Snip) --Other tables that will be "touched" during the reconcile
    --where trnHistory.CLIENT_GUID = @ClientGuid

        --Works allways but locks whole table.
    select top 1 @temp = 1 from trnHistory with (XLOCK, TABLOCK) 
    select top 1 @temp = 1 from trnCB with (XLOCK, TABLOCK)
    select top 1 @temp = 1 from trnClients with (XLOCK, TABLOCK)
    --(Snip) --Other tables that will be "touched" during the reconcile

        declare trans_cursor cursor local forward_only for 
                select TRANS_GUID from trnHistory where CLIENT_GUID = @ClientGuid order by TRANS_NUMBER
        open trans_cursor

        fetch next from trans_cursor into @TransGuid
        WHILE (@@FETCH_STATUS <> -1)
        BEGIN
            IF (@@FETCH_STATUS <> -2)
            BEGIN

                --Do Work here

            END
            fetch next from trans_cursor into @TransGuid
        END

        close trans_cursor
        deallocate trans_cursor

            --commit the transaction and release the lock, this allows other 
            -- connections to get a few queries in while it is safe to read.
        commit tran
    END

    fetch next from client_cursor into @ClientGuid
END 

close client_cursor
deallocate client_cursor

采纳答案by Laurence

If you are only worried about other readers, then you shouldn't need exclusive locks, the pattern

如果你只担心其他读者，那么你不需要排他锁，模式

Begin Transaction

  Make Data Inconsistent

  Make Data Consistent

Commit Transaction

Should be fine. The only sessions who will see inconsistent data are those that use nolockor Read Uncommitted, or those that expect to make multiple consistent reads without using Repeatable Rowsor Serializable.

应该没事。唯一会看到不一致数据的会话是那些使用nolockor 的会话Read Uncommitted，或者那些希望在不使用Repeatable Rowsor 的情况下进行多次一致读取的会话Serializable。

In answer to the question, the correct way to take an exclusive lock, in my opinion, is to arrange things so the engine does it for you.

在回答这个问题时，在我看来，获取排他锁的正确方法是安排事情，让引擎为你做。

回答by usr

I couldn't believe that an XLOCKwould not block a concurrent reader at read committedso I just reproduced it: It is true. Script:

我无法相信 anXLOCK不会阻止并发阅读器，read committed所以我只是复制它：这是真的。脚本：

Session 1:

第 1 节：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN

SELECT * FROM T WITH (ROWLOCK, XLOCK, HOLDLOCK /*PAGLOCK, TABLOCKX*/) WHERE ID = 123

Session 2:

第 2 节：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN

SELECT * FROM T WHERE ID = 123

Plug in some table name that you have at hand. Session 2 is not being blocked.

插入一些你手头的表名。会话 2 没有被阻止。

I also tried using a PAGLOCKbut that didn't work either. Next I tried a TABLOCKXbut that didn't work either!

我也尝试使用 aPAGLOCK但这也不起作用。接下来我尝试了一个，TABLOCKX但也没有用！

So your table-lock based strategy does not work. I think you'll have to modify the readers so that they either

所以你的基于表锁的策略不起作用。我认为你必须修改读者，以便他们要么

1. use snapshot isolation to get a consistent view (as of before any writes)
2. use a higher isolation level to be blocked by the writer

1. 使用快照隔离来获得一致的视图（在任何写入之前）
2. 使用更高的隔离级别来阻止编写器

Of course there is a nasty workaround to really, really lock the table: alter its schema. This will take a Sch-Mlock which conflicts with basically any access to the table. It even holds of some metadata read operations. It could look like this:

当然，有一个讨厌的解决方法来真正地锁定表：更改其架构。这将需要一个Sch-M锁，该锁基本上与对表的任何访问发生冲突。它甚至包含一些元数据读取操作。它可能看起来像这样：

--just change *any* setting in an idempotent way
ALTER TABLE T SET (LOCK_ESCALATION = AUTO)

I tested this to work.

我测试了这个工作。

---

---

Is SQL Server right not obeying the XLOCK? Or is this a flaw in the product? I think it is right because it conforms to the documented properties of READ COMMITTED. Also, even using SERIALIZABLEthere are cases where one transaction can lock a row exclusively and another can read the same row! This can happen in the presence of indexes. One transaction might X-lock on the non-clustered index IX_T_SomeColwhile another happily reads off of the clustered index PK_T.

SQL Server 不遵守XLOCK? 或者这是产品的缺陷？我认为这是正确的，因为它符合READ COMMITTED. 此外，即使使用，也SERIALIZABLE存在一个事务可以独占锁定一行而另一个可以读取同一行的情况！这可能发生在存在索引的情况下。一个事务可能对非聚集索引IX_T_SomeCol进行X 锁定，而另一个事务可能会愉快地读取聚集索引PK_T。

So it is actually quite normal that transactions can execute independently even in the presence of exclusive locking.

因此，即使存在排他锁，事务也可以独立执行实际上是很正常的。