采取独占锁的正确方法

Question

我正在编写一个程序，该程序将协调实时数据库中的最终交易。我正在进行的工作无法作为set操作完成，因此我使用了两个嵌套游标。

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

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

我是否正确获取了表级互斥锁，并且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

Answer 1

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

Begin Transaction

  Make Data Inconsistent

  Make Data Consistent

Commit Transaction

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

在回答这个问题时，我认为获得排他锁的正确方法是安排事情，以便引擎为您完成。