[Oracle] 프로시저, 패키지 중복 실행 방지 해결 방안(DBMS_LOCK)

오라클 오브젝트 중에서 Procedure, Package, Function, Trigger가 존재하며, 그 중에서 Procedure와 Package를 기준으로 진행 하도록 하겠습니다. 동시성 제어를 하기 위한 방법으로, A 세션에서 Procedure를 실행하고, B 세션에서 같은 Procedure를 실행하려고 할 때 경고 메시지를 보내주면 좋을 것 같다는 생각에서 검토를 해보았습니다.

즉, 동시에 하나의 Procedure를 실행하려고 할 때 이미 실행 중인 Procedure라면 실핼 할 수 없도록 적절한 제어를 해주는 역할을 합니다. 

1. DBMS_LOCK

  - 참고 URL : 바로가기

1.1. ALLOCATE_UNIQUE

  - 지정된 lockname에 Lock ID를 할당하는 프로시저 이며, Lock ID 값의 범위는 1073741824에서 1999999999까지입니다. 사용자에게 숫자가 아닌 이름을 통해서 관리를 쉽게 하기 위한 목적으로 제공 되었습니다.

1.1.1. 프로토타입

DBMS_LOCK.ALLOCATE_UNIQUE
(
    lockname            IN          VARCHAR2,                                                      
    lockhandle          OUT         VARCHAR2,                                                      
    expiration_secs     IN          PLS_INTEGER     DEFAULT 864000
)

1.1.2. 파라미터

파라미터 명	설명
lockname	유일한 lockhandle 값을 생성할 Lock 이름을 지정 합니다.
lockhandle	생성된 Lock ID에 대한 handle을 반환 받은 후 Handle 값을 통해 REQEST, CONVERT, RELEASE 프로시저를 호출 할 수 있습니다. Lock ID를 잘못 사용했을 때의 문제를 방지하기 위해 Lock ID를 직접 리턴하는 대신 VARCHAR2(128) 변수로 설정된 handel 값을 반환 합니다. handle은 한 세션에서만 유효하므로 다른 세션에서 해당 값을 사용해서는 안 됩니다.
expiration_secs	DBMS_LOCK_ALLOCATED 테이블을 재활용하기 위한 유효 시간을 초단위로 지정한 값입니다.(기본값 : 10일) 마지막 ALLOCATE_UNIQUE 프로시저를 홀출하는 시점 이후로 지나간 시간을 계산 합니다. DBMS_LOCK_ALLOCATED  테이블에 Lock을 직접 삭제해서는 안되고, _DBMS_LOCK_REUSE 초기화 파라미터를 Y로 설정한 경우에만 동작 하게 됩니다.

1.2.3.. 예제

DECLARE
    lock_handle VARCHAR2(128);
BEGIN
    DBMS_LOCK.ALLOCATE_UNIQUE('my_lock', lock_handle);
END;
/

1.2. RELEASE

  - 기존에 실행 중인 Lock을 명시적으로 해제하기 위한 프로시저 이며, 기본적으로 Lock은 세션이 종료되면 자동으로 해제 됩니다.

1.2.1. 프로토타입

DBMS_LOCK.RELEASE
(
    id         IN INTEGER || 
    lockhandle IN VARCHAR2
) 
RETURN INTEGER;

1.2.2. 파라미터

파라미터 명	설명
id or lockhandle	Lock을 해제하기 위한 Lock ID 또는 handle입니다.

1.2.3. 반환 값

파라미터 명	설명
0	Success
3	Parameter error
4	Owner error
5	Illegal lock handle

1.2.4. 예제

BEGIN
    DBMS_LOCK.RELEASE(lock_handle);
END;
/

2. DBMS_LOCK 권한 부여

  - DBMS_LOCK를 사용하려면 권한을 부여 해야 합니다.

/* SYS 계정에서 ESTENPARK 계정 권한 부여*/
GRANT EXECUTE ON DBMS_LOCK TO ESTENPARK

3. 소스 PL/SQL

  - 참고 URL : 바로가기

  - RAISE_APPLICATION_ERROR를 활용해서 사용자가 직접 메시지를 만들 수 있습니다.

CREATE OR REPLACE PROCEDURE ESTENPARK.SP_LOCKCHK
IS
  LOCKHANDLE   VARCHAR2(128);
  RETCODE      NUMBER;
BEGIN
  DBMS_LOCK.ALLOCATE_UNIQUE('myproclock', LOCKHANDLE);

  RETCODE   := DBMS_LOCK.REQUEST(LOCKHANDLE, TIMEOUT => 0, LOCKMODE => DBMS_LOCK.X_MODE);

  IF RETCODE <> 0
  THEN
    RAISE_APPLICATION_ERROR(-20000, 'The procedure is running.');
  END IF;

  /* sleep so that we can test with a 2nd execution */
  DBMS_LOCK.SLEEP(30);

  RETCODE   := DBMS_LOCK.RELEASE(LOCKHANDLE);
END SP_LOCKCHK;
/

4. 테스트 결과

  - 왼쪽과 오른쪽은 각각 다른 세션으로 Procedure를 실행 했으며, 만약 사용하고 있는 Procedure라면 오른쪽 에러를 출력 하게 됩니다.