取排他锁的正确方法

2022-01-16 00:00:00 sql locking sql-server sql-server-2008-r2

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

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.

我很想在行级别而不是表级别上进行排他锁，但是 我到目前为止所读到的内容 说如果其他事务在 READCOMMITED 隔离级别(这是为我准备的)运行，我不能使用 (XLOCK, ROWLOCK, HOLDLOCK) 进行 .

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

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

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?

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

推荐答案

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

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

应该没问题.唯一会看到不一致数据的会话是那些使用 nolock 或 Read Uncommitted 的会话，或者那些希望在不使用 Repeatable Rows 或 可序列化.

Should be fine. The only sessions who will see inconsistent data are those that use nolock or Read Uncommitted, or those that expect to make multiple consistent reads without using Repeatable Rows or 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.

相关文章