问题描述
多个Web服务器访问SQL Server以获得数字代码,如果该代码不存在,则必须由SQL Server自动生成.
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
我一直在阅读一些有关隔离级别和表锁定的信息,但是我对所有这些事情一无所知.首先,我认为我需要一个SERIALIZABLE隔离级别,但显然不是.
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.
那么,您将如何在TSQL中完成锁定"?
So, what would you do to accomplish a "lock" in TSQL?
非常感谢.
更新:
当我尝试使用此作为示例:
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
这是什么意思?
推荐答案
SERIALIZABLE is an isolation level for locking, not a semaphore.
在这种情况下,您要做的就是将读取锁定持久保存到TXN的末尾,这不会阻止其他进程读取代码.
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.
您需要在事务模式下使用 sp_getapplock .您可以将其配置为等待,立即炸弹等:由您决定
You need to use sp_getapplock in Transaction mode. You can configure it to wait, bomb immediatekly etc: up to you
这是基于我的模板,该模板来自嵌套的存储程序包含TRY CATCH ROLLBACK模式?
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互斥访问的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!