标签归档:串行存储过程

mssql sqlserver 串行之行存储过程的方法分享


摘要:
下文介绍在sqlserver中串行之行存储过程的方法分享,如下所示:
实验环境:sql server 2008 R2


在一些复杂的业务系统中,有时候我们需要保证存储过程禁止并发执行,下文讲述使用sqlserver中sp_getapplock关键字设定存储过程依次执行的方法分享,如下:


USE tempdb
GO

 CREATE TABLE [maomao](keyId varchar(30),writeDate datetime default getdate())
GO
IF OBJECT_ID('pr_testInsert')   IS NOT NULL   drop proc pr_testInsert
GO
CREATE PROC pr_testInsert
AS
BEGIN
	SET NOCOUNT ON
	INSERT INTO [maomao] VALUES (newid(),getdate())
	WAITFOR DELAY '00:00:20'
END
GO
/*
依次执行存储过程方法
*/
IF OBJECT_ID('pr_lock_insert') IS NOT NULL DROP PROC pr_lock_insert
GO
CREATE PROC pr_lock_insert
AS
BEGIN
	DECLARE @result int;
	BEGIN TRY
	BEGIN TRANSACTION;
                //获取执行,如果被执行,则返回需等待执行
		EXEC @result = sp_getapplock @Resource = 'pr_testInsert', @LockMode = 'Exclusive';
		IF @result<0
		BEGIN
			RAISERROR ('wait' ,16,1)
			ROLLBACK TRANSACTION
		END
                //执行存储过程
		EXEC pr_testInsert  
		EXEC @result =sp_releaseapplock @Resource = 'pr_testInsert';
		COMMIT TRANSACTION;
	END TRY
	BEGIN CATCH
		EXEC @result =sp_releaseapplock @Resource = 'pr_testInsert';
		ROLLBACK TRANSACTION
	END CATCH
END
GO


/*执行依次指定存储过程*/
EXEC pr_lock_insert