存储过程中的 TSQL 互斥访问 [英] TSQL mutual exclusive access in a stored procedure

问题描述

多个网络服务器访问 SQL Server 以获取数字代码，当此代码不存在时，它必须由 SQL Server 自动生成.

我需要确保即使有两个并发调用进来并且代码不存在，也只会创建一个代码并且两个调用都返回相同的代码.所以我必须这样做:

开始锁定如果代码存在返回码别的生成代码返回码端锁

我已经阅读了一些关于隔离级别和表锁定的内容，但我对所有这些都感到非常混乱.首先，我认为 SERIALIZABLE 隔离级别是我需要的，但显然不是.

那么，您将如何在 TSQL 中完成锁定"?

非常感谢.

更新:

当我尝试使用 this 为例:

设置 ANSI_NULLS ON去设置 QUOTED_IDENTIFIER ON去更改程序 get_code作为开始设置事务隔离级别可序列化去开始交易从授权为空的代码中选择代码；结尾去

<块引用>

消息 1018，级别 15，状态 1，程序get_code，第 4 行附近的语法不正确可序列化".如果这是为了表提示的一部分，一个 WITH 关键字现在需要括号.看SQL Server 联机丛书句法.消息 102，级别 15，状态 1，第 5 行 'END' 附近的语法不正确.

什么意思?

解决方案

SERIALIZABLE 是锁定的隔离级别，而不是 信号量.

在这种情况下它不会起作用，你要做的就是在 TXN 的末尾保持一个读取锁，这不会阻止另一个进程读取代码.

您需要在事务模式下使用 sp_getapplock.您可以将其配置为等待、立即轰炸等:由您决定

这是基于我的模板来自 嵌套存储包含 TRY CATCH ROLLBACK 模式的程序?

ALTER PROCEDURE get_code作为SET XACT_ABORT，NOCOUNT ON声明@starttrancount int，@result int；开始尝试选择@starttrancount = @@TRANCOUNTIF @starttrancount = 0 开始交易EXEC @result = sp_getapplock 'get_code', 'Exclusive', 'Transaction', 0如果@结果 <0RAISERROR('INFO: 请一次一个`！', 16, 1);[...执行工作...]如果@starttrancount = 0提交交易别的执行 sp_releaseapplock 'get_code';结束尝试开始捕捉IF XACT_STATE() <>0 AND @starttrancount = 0回滚交易RAISERROR [使用@ErrorNumber、@ErrorMessage 等重新抛出捕获的错误]结束捕获去

Several web servers access a SQL Server to get a numeric code, when this code doesn't exist, it has to be autogenerated by the SQL Server.

I need to ensure that even if two concurrent calls come in and the code doesn't exist, only one code is created and both calls return the same code. So I have to do something like this:

begin lock
  if code exists
    return code
  else
    generate code
    return code
end lock

I've been reading a little about isolation levels and table locking, but I have a terrible mess with all that. First I thought that a SERIALIZABLE isolation level is what I need, but apparently it's not.

So, what would you do to accomplish a "lock" in TSQL?

Thanks a lot.

UPDATE:

I got this error when I try to set the serializable level using this as example:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE get_code 
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    GO
    BEGIN TRANSACTION

    select code from codes where granted is null;
END
GO

Msg 1018, Level 15, State 1, Procedure get_code, Line 4 Incorrect syntax near 'SERIALIZABLE'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax. Msg 102, Level 15, State 1, Line 5 Incorrect syntax near 'END'.

What does it means?

解决方案

SERIALIZABLE is an isolation level for locking, not a semaphore.

It won't work in this case all you'll do is persist a read lock to the end of the TXN that doesn't prevent another process into the code reading.

You need to use sp_getapplock in Transaction mode. You can configure it to wait, bomb immediately etc: up to you

This is based on my template from Nested stored procedures containing TRY CATCH ROLLBACK pattern?

ALTER PROCEDURE get_code 
AS
SET XACT_ABORT, NOCOUNT ON

DECLARE @starttrancount int, @result int;

BEGIN TRY
    SELECT @starttrancount = @@TRANCOUNT

    IF @starttrancount = 0 BEGIN TRANSACTION

    EXEC @result = sp_getapplock 'get_code', 'Exclusive', 'Transaction', 0 
    IF @result < 0
        RAISERROR('INFO: One at a time please`!', 16, 1);

    [...Perform work...]


    IF @starttrancount = 0 
        COMMIT TRANSACTION
    ELSE
        EXEC sp_releaseapplock 'get_code';
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 AND @starttrancount = 0 
        ROLLBACK TRANSACTION
    RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc]
END CATCH
GO

这篇关于存储过程中的 TSQL 互斥访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！