（4.51）sql server用 sp_getapplock 实现串行执行的存储过程

【1】需求

sqlserver的可重复读不上间隙锁，会导致幻读，而mysql就采取了间隙锁。
那么，sqlserver想要根据是否存在对应的记录，如果不存在就新增。怎么做到万无一失呢？

比如下面这种，（在并发高的情况下）经常就引发报错：报错 id 已存在 

if not exists(select 1 from table where id = 1)
　　insert into table(id) values(1)
else
　　delete .....

【2】解决思路

存储过程级别可以指定只能有一个实例在运行的,所有的对这个表的insert操作都走存储过程

核心就是：就是对数据库用户不授予表的insert权限，而授予指定存储过程的执行权限，在存储过程中进行insert操作，这种方式我用过。

 

【3】sp_getapplock

【3.1】一般形式

sp_getapplock [ @Resource = ] 'resource_name' ,  
     [ @LockMode = ] 'lock_mode'   
     [ , [ @LockOwner = ] 'lock_owner' ]   
     [ , [ @LockTimeout = ] 'value' ]  
     [ , [ @DbPrincipal = ] 'database_principal' ]  
[ ; ]  

 

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

【参考文档】

官网：https://docs.microsoft.com/zh-cn/sql/relational-databases/system-stored-procedures/sp-getapplock-transact-sql?view=sql-server-ver15

代码转自：https://blog.csdn.net/yenange/article/details/78903480

sp_getapplock [ @Resource = ] 'resource_name' , [ @LockMode = ] 'lock_mode' [ , [ @LockOwner = ] 'lock_owner' ] [ , [ @LockTimeout = ] 'value' ] [ , [ @DbPrincipal = ] 'database_principal' ] [ ; ]