데이터베이스 질문과 답변

채번함수에 select for update 구문 적용 문의 0 14 3,275

by SQL [2023.11.27 10:26:03]

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
[채번방식 요약]
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개가 중복이 납니다ㅠㅠㅠㅠ
 
어떻게사용해야하는걸까요ㅠㅠ
by 아발란체 [2023.11.27 11:03:58]

와.. 채번 규칙이 눈 돌아가네용 @.@)/ 빙글빙글

채번이 00000 ~ 99999 라는 단서가 있는데 
채번 만드는 규칙을 보면 50001부터 시작하는 거 아닌가요?


방법이 좋아보이지 않아용..

트랜젝션 단위를 묶었어도 TABLE_A 값을 가져다 쓰는 구문은 이 트랜젝션과 별개이기 때문에

문제 여지도 있어보입니다.

by SQL [2023.11.27 11:14:29]

00000~50000까지는 채번프로시저에서 사용하고있어서 함수에서는 50001~99999까지 사용합니다ㅠ 정정할게요!

by SQL [2023.11.27 11:16:35]

트랜젝션 단위를 묶었어도 TABLE_A 값을 가져다 쓰는 구문은 이 트랜젝션과 별개이기 때문에

문제 여지도 있어보입니다.

-> 그래서 select for update 구문을 적용하려고 한건데 그게 적용이 어려운케이스일까요ㅠ아니면 효과가없으려나요ㅠ

by 아발란체 [2023.11.27 11:25:42]

함수 안에서 트랜젝션 단위는 보장이 되나 이를 통해 TABLE_A에 쌓이는 채번을 가져다 쓸 때 트랜젝션 단위가 보장되지 않습니다. 즉 이를 통해 TABLE_A에 있는 시퀀스를 이용 대량 입력하는 SQL이 있다면 대량 중복 에러가 발생할 여지가 있습니다. 즉 중복이 100건에서 10만건 증가 되는 것은, 이를 이용하여 INSERT하는 구문이 10건이면 9건이 중복 에러가 발생할 구조가 될 거 같습니다.

by 아발란체 [2023.11.27 11:21:59]

해당 함수를 쓰지 않고


채번을 입력하는 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개 쿼리로 채번을 일괄 부여하는 것이 좋을 것 같습니다.

by SQL [2023.11.27 11:29:05]

지금 수정한 소스가 왜 기존소스보다 중복이 훨씬 많이 발생하는건지 혹시 알수있을까요?ㅠㅠ

 

?
1
2
SELECT MAKE_SEQENCE('A01', '20231031') FROM DUAL
CONNECT BY LEVEL <=10000;

이런식으로 DB툴을 두개 열어서 동시에 실행해서 엑셀로 대사하는 방식으로 테스트했는데

기존소스가 훨씬 중복이 적어서요ㅠSELECT FOR UPDATE 구문이 제대로 안먹히는거같아요ㅠ

by 아발란체 [2023.11.27 11:30:01]

TABLE_A에 채번을 두고 INSERT를 다량 실행하는 구조라면 함수를 쓰지 않고 

트렌젝션1 시작

   채번을 구하여 변수1에 담아두고 
   INSERT 다량 처리 할 때
   INSERT 구문1 변수1 + 1 
   INSERT 구문2 변수1 + 2
   INSERT 구문3 변수1 + 3
   INSERT 구문n 변수1 + n

트랜젝션 종료

이런 식으로 구성하면 채번(key) 중복 오류는 발생하지 않습니다.

by 아발란체 [2023.11.27 11:33:43]

중복 개수가 다르게 되는 것은 상황을 보면
독립된 트랜젝션을 통해 TABLE_A에 최종 채번을 두고 
이를 이용하여 채번을 입력 할 때
이미 이용한 채번을 쓰기 때문에 채번 중복 오류가 발생하는 것으로 보이며

즉 채번을 구하고
입력을 1000건 때리면 999건이 중복 오류가 발생하고
입력을 2000건 때리면 1999건이 중복나는 내용으로 보입니다.

증명은
중복 에러가 발생하는 키 값을 비활성화 하면 중복 에러가 발생하지 않는데
이 때 실제 들어간 채번 값을 확인해보시면 정확하게 어떤 문제인지 확인이 될 것 같습니다.

아무튼 생성한 채번과 이를 이용하여 입력 할 때 트렌젝션이 보장되지 않아 발생하는 문제로 보입니다.

by SQL [2023.11.27 11:42:11]

디비링크로 여러 시스템에 채번데이터를 제공해야해서 현재 함수로 구현된부분인데 말씀해주신방식 적용하려면 API 변을 검토해봐야하는거군요ㅠ

by 아발란체 [2023.11.27 13:26:15]

기존 쿼리와 변경 쿼리 차이는

[구분값, 날짜] 조건절은 같고

조회 내용이 MAX를 쓰냐 쓰지 않냐 차이만 보입니다. 

해당 조회로 반환 값이 1개라면 MAX, MIN, Current 결과는 똑같을 것 같습니다.

by 아발란체 [2023.11.27 13:32:17]
?
1
2
SELECT MAKE_SEQENCE('A01', '20231031') FROM DUAL
CONNECT BY LEVEL <=10000;

이 구문으로 트랜젝션 2개 발생했는데도 에러가 100개 넘게 발생하나요? 그럼 제가 완전 다른 얘기를 한 것인데.. ㅠㅠ

by SQL [2023.11.27 13:57:50]

네ㅠㅠㅠ흑흑....동시에 두개돌리면 기존쿼리는 4~50개정도 중복이 나고, 새 쿼리는 1200개정도 오류가 납니다ㅠ

by 마농 [2023.11.28 16:12:36]

table_a 에는 사전에 데이터가 들어 있어야 합니다. (초기값 50000 세팅)
즉, insert 가 발생할 일이 없도록, update 만 발생하도록 사전 세팅.
이 상태에서 for update 거시면 될 것 같습니다.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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;
/

 

by SQL [2023.11.28 17:52:59]

오...! 감사합니다ㅠSELECT 결과가 없는 경우 함수에서 오류를 뱉다보니 쿼리가 산으로 갔었는데 생각해보니 테이블에 애초에 데이터가 있으면 가능하긴 하겠네요 감사합니다! 그방법을 고민해보겠습니다~!ㅎㅎ

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입