SQL Server:如何获取排他锁以防止出现竞争状况? [英] SQL Server: how to acquire exclusive lock to prevent race condition?

问题描述

我有以下T-SQL代码:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION T1_Test

    /*This is a dummy table used for "locking" 
    and it doesn't contain any meaningful data.*/        
    UPDATE lockTable 
        SET ID = 1    
        WHERE ID = 1

    DECLARE @Count TINYINT 

    SELECT @Count = COUNT(*)
    FROM debugSP 

    WAITFOR DELAY '00:00:5';

    INSERT INTO debugSP 
        (DateCreated, ClientId, Result)
    SELECT 
        GETDATE(), @@SPID, @Count

COMMIT TRANSACTION T1_Test

我正在使用带有注释的锁定"黑客来获取排他锁定.

注意:使用TABLOCKX或UPDLOCK提示将不起作用，因为我通过拆分语句并在中间添加WAITFOR命令进行测试而破坏了ATOMIC-ity. 我不要这样的东西:

INSERT INTO debugSP (DateCreated, ClientId, Result)
SELECT GETDATE(), @@SPID, COUNT(*) 
FROM debugSP

这是同时运行两个会话(带锁表)后的正确结果

Id DateCreated           ClientId Result
-- ----------------------- -------- ------
 1 2011-03-17 15:52:12.287       66      0
 2 2011-03-17 15:52:24.534       68      1

这是在注释掉锁的情况下运行代码的不正确结果

Id DateCreated           ClientId Result
-- ----------------------- -------- ------
 1 2011-03-17 15:52:43.128       66      0
 2 2011-03-17 15:52:46.341       68      0

是否有更好的方法来获得没有这种黑客手段的交易范围内的排他锁?

解决方案

我不确定您要从发布的代码中尝试做什么.我想您只是在尝试序列化对那段代码的访问?如果是这样，sp_getapplock应该执行您需要的操作，而不是创建仅用于锁定的新虚拟表.

此处的详细信息

I have the following T-SQL code:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION T1_Test

    /*This is a dummy table used for "locking" 
    and it doesn't contain any meaningful data.*/        
    UPDATE lockTable 
        SET ID = 1    
        WHERE ID = 1

    DECLARE @Count TINYINT 

    SELECT @Count = COUNT(*)
    FROM debugSP 

    WAITFOR DELAY '00:00:5';

    INSERT INTO debugSP 
        (DateCreated, ClientId, Result)
    SELECT 
        GETDATE(), @@SPID, @Count

COMMIT TRANSACTION T1_Test

I am using "locking" hack marked with comment to acquire the exclusive lock.

NOTE: using TABLOCKX or UPDLOCK hints will not work because I have broken ATOMIC-ity by splitting statements and adding WAITFOR command in the middle for testing purposes. I don't want something like that:

INSERT INTO debugSP (DateCreated, ClientId, Result)
SELECT GETDATE(), @@SPID, COUNT(*) 
FROM debugSP

This is the correct result after running two simultaneous sessions (with lock table)

Id DateCreated           ClientId Result
-- ----------------------- -------- ------
 1 2011-03-17 15:52:12.287       66      0
 2 2011-03-17 15:52:24.534       68      1

and that is the incorrect result of running the code with lock commented out

Id DateCreated           ClientId Result
-- ----------------------- -------- ------
 1 2011-03-17 15:52:43.128       66      0
 2 2011-03-17 15:52:46.341       68      0

Is there a better way of acquiring transaction-wide exclusive lock without such hacks?

解决方案

I'm not exactly sure what you are trying to do from the posted code. I presume you are just trying to serialize access to that piece of code? If so sp_getapplock should do what you need instead of creating a new dummy table that you just use to take locks on.

Details here

这篇关于SQL Server:如何获取排他锁以防止出现竞争状况?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！