데이터베이스 질문과 답변
by SQL [2023.11.27 10:26:03]
[채번방식 요약]
1. 채번테이블구조는 구분값(y01, x01, x02 등...)/날짜(20230101,20230203, 20231031), 시퀀스(50001~99999)가 저장되어있으며
구분값, 날짜별로 시퀀스를 조회해와서 +1 한 값이 새 시퀀스가 된다
2. 새 시퀀스를 채번테이블에 저장한다
3. 날짜+구분값+새 시퀀스를 조합하여 리턴한다.
[수정하고자 하는 이유]
시퀀스 사용이 아니기 때문에 중복채번의 여지가 있어서 수정을 고려하게 되었습니다.
시퀀스 사용을 고려해봤으나, 채번에 사용되는 날짜가 현재날짜가 아닌 특정일을 넣고 호출하는 방식이라
시퀀스가 모두 차서 초기화되는 경우 특정일을 넣고 채번시 시퀸스가 같은 데이터가 나올 확률이 있어 배제한 상황으로
차선책으로 은행에서 잔액관리시 사용하는 select for update로 변경을 고려하게 되었습니다.
[기존함수 소스]
CREATE OR REPLACE FUNCTION Make_Sequence(
v_Module IN VARCHAR2,
v_Date IN VARCHAR2
)
RETURN VARCHAR2 IS v_issue_tax_id varchar2(16);
v_temp_id number(5);
pragma AUTONOMOUS_TRANSACTION;
BEGIN
SELECT DECODE(NVL(MAX(SERIAL), 0),0,NVL(MAX(SERIAL), 0) + 50001,NVL(MAX(SERIAL), 0) +1)
,v_Date ||v_Module || lpad(to_char(DECODE(NVL(MAX(SERIAL), 0),0,NVL(MAX(SERIAL), 0) + 50001,NVL(MAX(SERIAL), 0) +1)), 5, '00000')
into v_temp_id, v_issue_tax_id
FROM table_a
WHERE TYPE = v_Module
AND DATE = v_Date
;
IF v_temp_id = 50001 THEN
INSERT INTO table_a(TYPE, DATE, SERIAL)
VALUES (v_Module, v_Date, v_temp_id);
ELSE
UPDATE table_a SET
SERIAL = v_temp_id
WHERE TYPE = v_Module
AND DATE = v_Date;
END IF;
COMMIT;
RETURN v_issue_tax_id ;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK;
v_issue_tax_id := '-1';
DBMS_OUTPUT.PUT_LINE('DUP_VAL_ON_INDEX ERROR');
WHEN NO_DATA_FOUND THEN
ROLLBACK;
v_issue_tax_id := '-2';
DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
WHEN OTHERS THEN
ROLLBACK;
v_issue_tax_id := '-3';
DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);
END Make_Sequence;
[수정한 함수]
CREATE OR REPLACE FUNCTION .MAKE_SEQUENCE(
V_MODULE IN VARCHAR2,
V_DATE IN VARCHAR2
)
RETURN VARCHAR2 IS V_ISSUE_TAX_ID VARCHAR2(16);
V_TEMP_ID NUMBER(5);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT coalesce((
SELECT DECODE(NVL(SERIAL, 0),0,NVL(SERIAL, 0) + 50001,NVL(SERIAL, 0) +1)
FROM table_a
WHERE TYPE = V_MODULE
AND DATE = V_DATE
), 50001),
V_DATE ||V_MODULE || LPAD(TO_CHAR(coalesce((
SELECT DECODE(NVL(SERIAL, 0),0,NVL(SERIAL, 0) + 50001,NVL(SERIAL, 0) +1)
FROM table_a
WHERE TYPE = V_MODULE
AND DATE = V_DATE
), 50001)
), 5, '00000')
INTO V_TEMP_ID, V_ISSUE_TAX_ID
FROM DUAL FOR UPDATE WAIT 5
;
DBMS_OUTPUT.PUT_LINE(V_TEMP_ID);
IF V_TEMP_ID = 50001 THEN
INSERT INTO table_a (TYPE, DATE, SERIAL)
VALUES (V_MODULE, V_DATE, V_TEMP_ID);
ELSE
UPDATE table_a SET
SERIAL = V_TEMP_ID
WHERE TYPE = V_MODULE
AND DATE = V_DATE;
END IF;
COMMIT;
RETURN V_ISSUE_TAX_ID ;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK;
V_ISSUE_TAX_ID := '-1';
DBMS_OUTPUT.PUT_LINE('DUP_VAL_ON_INDEX ERROR');
WHEN NO_DATA_FOUND THEN
ROLLBACK;
V_ISSUE_TAX_ID := '-2';
DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
WHEN OTHERS THEN
ROLLBACK;
V_ISSUE_TAX_ID := '-3';
DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);
END MAKE_SEQUENCE;
[결과(고민)]
select for update가 dual에 적용된것같습니다ㅠㅠㅠ
만건중 40건 정도 중복되던게 1200개가 중복이 납니다ㅠㅠㅠㅠ
어떻게사용해야하는걸까요ㅠㅠ
와.. 채번 규칙이 눈 돌아가네용 @.@)/ 빙글빙글
채번이 00000 ~ 99999 라는 단서가 있는데
채번 만드는 규칙을 보면 50001부터 시작하는 거 아닌가요?
방법이 좋아보이지 않아용..
트랜젝션 단위를 묶었어도 TABLE_A 값을 가져다 쓰는 구문은 이 트랜젝션과 별개이기 때문에
문제 여지도 있어보입니다.
00000~50000까지는 채번프로시저에서 사용하고있어서 함수에서는 50001~99999까지 사용합니다ㅠ 정정할게요!
트랜젝션 단위를 묶었어도 TABLE_A 값을 가져다 쓰는 구문은 이 트랜젝션과 별개이기 때문에
문제 여지도 있어보입니다.
-> 그래서 select for update 구문을 적용하려고 한건데 그게 적용이 어려운케이스일까요ㅠ아니면 효과가없으려나요ㅠ
함수 안에서 트랜젝션 단위는 보장이 되나 이를 통해 TABLE_A에 쌓이는 채번을 가져다 쓸 때 트랜젝션 단위가 보장되지 않습니다. 즉 이를 통해 TABLE_A에 있는 시퀀스를 이용 대량 입력하는 SQL이 있다면 대량 중복 에러가 발생할 여지가 있습니다. 즉 중복이 100건에서 10만건 증가 되는 것은, 이를 이용하여 INSERT하는 구문이 10건이면 9건이 중복 에러가 발생할 구조가 될 거 같습니다.
해당 함수를 쓰지 않고
채번을 입력하는 INSERT {테이블} 구문에서
v_Date ||v_Module || lpad(to_char(DECODE(NVL(MAX(SERIAL), 0),0,NVL(MAX(SERIAL), 0) + 50001,NVL(MAX(SERIAL), 0) +1)), 5, '00000')... 식의
만드신 채번 생성 쿼리를 동시에 쓴다면
채번 생성과 채번 사용이 한 트렌젝션으로 묶여서
채번 중복 오류는 없을 것 같으나
한번 수행에 10만건 단위로 INSERT가 일어난다면 부하가 많기 때문에
채번 연산 없이 일괄 선입력 후 1개 쿼리로 채번을 일괄 부여하는 것이 좋을 것 같습니다.
지금 수정한 소스가 왜 기존소스보다 중복이 훨씬 많이 발생하는건지 혹시 알수있을까요?ㅠㅠ
SELECT MAKE_SEQENCE('A01', '20231031') FROM DUAL
CONNECT BY LEVEL <=10000;
이런식으로 DB툴을 두개 열어서 동시에 실행해서 엑셀로 대사하는 방식으로 테스트했는데
기존소스가 훨씬 중복이 적어서요ㅠSELECT FOR UPDATE 구문이 제대로 안먹히는거같아요ㅠ
TABLE_A에 채번을 두고 INSERT를 다량 실행하는 구조라면 함수를 쓰지 않고
트렌젝션1 시작
채번을 구하여 변수1에 담아두고
INSERT 다량 처리 할 때
INSERT 구문1 변수1 + 1
INSERT 구문2 변수1 + 2
INSERT 구문3 변수1 + 3
INSERT 구문n 변수1 + n
트랜젝션 종료
이런 식으로 구성하면 채번(key) 중복 오류는 발생하지 않습니다.
중복 개수가 다르게 되는 것은 상황을 보면
독립된 트랜젝션을 통해 TABLE_A에 최종 채번을 두고
이를 이용하여 채번을 입력 할 때
이미 이용한 채번을 쓰기 때문에 채번 중복 오류가 발생하는 것으로 보이며
즉 채번을 구하고
입력을 1000건 때리면 999건이 중복 오류가 발생하고
입력을 2000건 때리면 1999건이 중복나는 내용으로 보입니다.
증명은
중복 에러가 발생하는 키 값을 비활성화 하면 중복 에러가 발생하지 않는데
이 때 실제 들어간 채번 값을 확인해보시면 정확하게 어떤 문제인지 확인이 될 것 같습니다.
아무튼 생성한 채번과 이를 이용하여 입력 할 때 트렌젝션이 보장되지 않아 발생하는 문제로 보입니다.
디비링크로 여러 시스템에 채번데이터를 제공해야해서 현재 함수로 구현된부분인데 말씀해주신방식 적용하려면 API 변을 검토해봐야하는거군요ㅠ
기존 쿼리와 변경 쿼리 차이는
[구분값, 날짜] 조건절은 같고
조회 내용이 MAX를 쓰냐 쓰지 않냐 차이만 보입니다.
해당 조회로 반환 값이 1개라면 MAX, MIN, Current 결과는 똑같을 것 같습니다.
SELECT MAKE_SEQENCE('A01', '20231031') FROM DUAL
CONNECT BY LEVEL <=10000;
이 구문으로 트랜젝션 2개 발생했는데도 에러가 100개 넘게 발생하나요? 그럼 제가 완전 다른 얘기를 한 것인데.. ㅠㅠ
네ㅠㅠㅠ흑흑....동시에 두개돌리면 기존쿼리는 4~50개정도 중복이 나고, 새 쿼리는 1200개정도 오류가 납니다ㅠ
table_a 에는 사전에 데이터가 들어 있어야 합니다. (초기값 50000 세팅)
즉, insert 가 발생할 일이 없도록, update 만 발생하도록 사전 세팅.
이 상태에서 for update 거시면 될 것 같습니다.
CREATE OR REPLACE FUNCTION Make_Sequence
( v_Module IN VARCHAR2
, v_Date IN VARCHAR2
)
RETURN VARCHAR2
IS
v_temp_id NUMBER(5);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT serial + 1
INTO v_temp_id
FROM table_a
WHERE type = v_Module
AND date = v_Date
FOR UPDATE
;
UPDATE table_a
SET serial = v_temp_id
WHERE type = v_Module
AND date = v_Date;
;
COMMIT;
RETURN v_Date || v_Module || v_temp_id ;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);
RETURN '-3';
END Make_Sequence;
/
오...! 감사합니다ㅠSELECT 결과가 없는 경우 함수에서 오류를 뱉다보니 쿼리가 산으로 갔었는데 생각해보니 테이블에 애초에 데이터가 있으면 가능하긴 하겠네요 감사합니다! 그방법을 고민해보겠습니다~!ㅎㅎ
) 버튼을 클릭하여 작성 하시면 됩니다.