用 sp_getapplock 实现串行执行的存储过程

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(addTime DATETIME2)
GO
IF OBJECT_ID('proc_test') IS NOT NULL DROP PROC proc_test
GO
CREATE PROC proc_test
AS
BEGIN
	SET NOCOUNT ON
	INSERT INTO t VALUES ( GETUTCDATE())
	WAITFOR DELAY '00:00:30'
END
GO
IF OBJECT_ID('proc_lock_insert') IS NOT NULL DROP PROC proc_lock_insert
GO
CREATE PROC proc_lock_insert
AS
BEGIN
	DECLARE @result int;
	BEGIN TRY
	BEGIN TRANSACTION;
		EXEC @result = sp_getapplock @Resource = 'proc_test', @LockMode = 'Exclusive';
		IF @result<0
		BEGIN
			RAISERROR ('wait' ,16,1)
			ROLLBACK TRANSACTION
		END
		EXEC proc_test
		EXEC @result =sp_releaseapplock @Resource = 'proc_test';
		COMMIT TRANSACTION;
	END TRY
	BEGIN CATCH
		EXEC @result =sp_releaseapplock @Resource = 'proc_test';
		ROLLBACK TRANSACTION
	END CATCH
END
GO
--开两个窗口，分别执行下面的存储过程
--可以看到一个没有结束，另一个只能等待
EXEC proc_lock_insert

参考： 点击打开链接