application lock (여러 프로시저가 있는데 동시에 하나만 돌 수 있다.)
미션
여러 프로시저가 있는데 하나만 동작하게 만들어라
CREATE DATABASE APPLOCKTEST
GO
USE APPLOCKTEST
GO
IF OBJECT_ID ('TBLX') IS NOT NULL
DROP TABLE TBLX
GO
IF OBJECT_ID ('USP_A') IS NOT NULL
DROP PROC USP_A
GO
-- 테스트 테이블
CREATE TABLE TBLX
(IDX INT IDENTITY(1,1)
,C1 INT)
GO
-- 심심풀이 데이터
INSERT INTO TBLX (C1) VALUES (1)
GO
-- 실제로 일하는 프로시져
CREATE PROC USP_A
(@V INT)
AS
WAITFOR DELAY '00:00:7'
INSERT INTO TBLX (C1) VALUES (@V)
GO
-- 동시에 실행하면 안되는 여러 프로시저들.....
IF OBJECT_ID('USP_JOB_EXEC_USP_A') IS NULL
EXEC ('CREATE PROC USP_JOB_EXEC_USP_A AS SELECT 1 ')
GO
ALTER PROC USP_JOB_EXEC_USP_A
(
@V INT = 1
, @RESULT_CODE NVARCHAR(2000) OUTPUT
)
AS
DECLARE
@LOCK_STATUS INT = 0
, @RETRYS INT = 3 -- 1초에 한번씩 실행된다. 이걸로 조절하세요~
, @KEYWORD NVARCHAR(100) = '결산'
, @CURRENT_RETRY INT = 0
SET @RESULT_CODE = N'SUCCESS'
WHILE (@CURRENT_RETRY <= @RETRYS)
BEGIN
SELECT @LOCK_STATUS = APPLOCK_TEST('PUBLIC', @KEYWORD,'Exclusive', 'SESSION')
IF (@LOCK_STATUS <> 0 )
BEGIN
EXEC SP_GETAPPLOCK @KEYWORD, 'EXCLUSIVE', 'SESSION'
-- SP_START
EXEC USP_A @V
SELECT 'AAAAAAAAAAAAAAAAAAA'
-- SP_END
EXEC SP_RELEASEAPPLOCK @KEYWORD, 'SESSION'
BREAK;
END
ELSE
BEGIN
IF @CURRENT_RETRY = @RETRYS
SET @RESULT_CODE = 'JOB CANCELED BY LOCK ' + @KEYWORD
ELSE
WAITFOR DELAY '00:00:01'
SET @CURRENT_RETRY = @CURRENT_RETRY + 1
END
END
GO
-- 동시에 실행하면 안되는 여러 프로시저들.....
IF OBJECT_ID('USP_JOB_EXEC_USP_B') IS NULL
EXEC ('CREATE PROC USP_JOB_EXEC_USP_B AS SELECT 1 ')
GO
ALTER PROC USP_JOB_EXEC_USP_B
(
@V INT = 1
, @RESULT_CODE NVARCHAR(2000) OUTPUT
)
AS
DECLARE
@LOCK_STATUS INT = 0
, @RETRYS INT = 3 -- 1초에 한번씩 실행된다. 이걸로 조절하세요~
, @KEYWORD NVARCHAR(100) = '결산'
, @CURRENT_RETRY INT = 0
SET @RESULT_CODE = N'SUCCESS'
WHILE (@CURRENT_RETRY <= @RETRYS)
BEGIN
SELECT @LOCK_STATUS = APPLOCK_TEST('PUBLIC', @KEYWORD,'Exclusive', 'SESSION')
IF (@LOCK_STATUS <> 0 )
BEGIN
EXEC SP_GETAPPLOCK @KEYWORD, 'EXCLUSIVE', 'SESSION'
-- SP_START
EXEC USP_A @V
SELECT 'AAAAAAAAAAAAAAAAAAA'
-- SP_END
EXEC SP_RELEASEAPPLOCK @KEYWORD, 'SESSION'
BREAK;
END
ELSE
BEGIN
IF @CURRENT_RETRY = @RETRYS
SET @RESULT_CODE = 'JOB CANCELED BY LOCK ' + @KEYWORD
ELSE
WAITFOR DELAY '00:00:01'
SET @CURRENT_RETRY = @CURRENT_RETRY + 1
END
END
GO
-- APPLICATION LOCK TEST CODE
-- SESSION 1
DECLARE @RESULT_CODE NVARCHAR(2000)
EXEC USP_JOB_EXEC_USP_A 5, @RESULT_CODE = @RESULT_CODE OUTPUT
SELECT @RESULT_CODE
-- SESSION 2
DECLARE @RESULT_CODE NVARCHAR(2000)
EXEC USP_JOB_EXEC_USP_B 5, @RESULT_CODE = @RESULT_CODE OUTPUT
SELECT @RESULT_CODE