데이터베이스 질문과 답변
by 창조의날개 [2015.04.20 18:21:29]
-- 마스터 테이블 생성
CREATE TABLE MAST(
SEQ NUMBER
, CNT NUMBER
);
CREATE UNIQUE INDEX UK_MAST_01 ON MAST
(SEQ ASC);
ALTER TABLE MAST
ADD CONSTRAINT PK_MAST PRIMARY KEY (SEQ);
-- 서브 테이블 생성
CREATE TABLE MAST_SUB(
SEQ NUMBER
, SUB_SEQ NUMBER
);
CREATE UNIQUE INDEX UK_MAST_SUB_01 ON MAST_SUB
(SEQ ASC, SUB_SEQ ASC);
ALTER TABLE MAST_SUB
ADD CONSTRAINT PK_MAST_SUB PRIMARY KEY (SEQ, SUB_SEQ);
ALTER TABLE MAST_SUB ADD (CONSTRAINT FK_MAST_SUB_01 FOREIGN KEY (SEQ) REFERENCES MAST (SEQ));
-- 마스터 데이터 입력
INSERT INTO MAST
SELECT ROWNUM SEQ, 10 CNT FROM DUAL CONNECT BY LEVEL <= 10;
COMMIT;
-- 서브 데이터 입력
INSERT INTO MAST_SUB(SEQ,SUB_SEQ) VALUES(1,1);
INSERT INTO MAST_SUB(SEQ,SUB_SEQ) VALUES(1,2);
INSERT INTO MAST_SUB(SEQ,SUB_SEQ) VALUES(1,3);
INSERT INTO MAST_SUB(SEQ,SUB_SEQ) VALUES(1,4);
INSERT INTO MAST_SUB(SEQ,SUB_SEQ) VALUES(1,5);
INSERT INTO MAST_SUB(SEQ,SUB_SEQ) VALUES(1,6);
INSERT INTO MAST_SUB(SEQ,SUB_SEQ) VALUES(1,7);
INSERT INTO MAST_SUB(SEQ,SUB_SEQ) VALUES(1,8);
INSERT INTO MAST_SUB(SEQ,SUB_SEQ) VALUES(1,9);
INSERT INTO MAST_SUB(SEQ,SUB_SEQ) VALUES(1,10);
INSERT INTO MAST_SUB(SEQ,SUB_SEQ) VALUES(1,11);
INSERT INTO MAST_SUB(SEQ,SUB_SEQ) VALUES(1,12);
INSERT INTO MAST_SUB(SEQ,SUB_SEQ) VALUES(1,13);
INSERT INTO MAST_SUB(SEQ,SUB_SEQ) VALUES(1,14);
INSERT INTO MAST_SUB(SEQ,SUB_SEQ) VALUES(1,15);
INSERT INTO MAST_SUB(SEQ,SUB_SEQ) VALUES(1,16);
INSERT INTO MAST_SUB(SEQ,SUB_SEQ) VALUES(1,17);
INSERT INTO MAST_SUB(SEQ,SUB_SEQ) VALUES(1,18);
INSERT INTO MAST_SUB(SEQ,SUB_SEQ) VALUES(1,19);
INSERT INTO MAST_SUB(SEQ,SUB_SEQ) VALUES(1,20);
-- 이때 서브 데이터는 MAST 테이블의 SEQ값 기준으로 CNT 수만큼만 INSERT가 되어야 합니다.
-- 그러므로 SUB_SEQ값이 11번 부터는 에러가 나서 입력 되지 않으면 됩니다.
위에 샘플로 만든 MAST 테이블의 PK인 SEQ값을 기준으로 CNT 개수만큼만 MAST_SUB 테이블에 넣고 싶은데요..
프로그램에서 위에 샘플처럼 20개를 넣는다고 하여도 MAST의 CNT 기준인 10개만 들어가고
나머지 11번부터 20번까지는 에러든 어떠한 방법이든 더이상 들어가지 못하도록 해야 합니다.
트리거로 해보려 했는데.. 트리거로 자기 자신을 바꿀 수는 없기 때문에 안되더군요..
뭔가 좋은 방법이 없을까요?
참고로 DB는 ORACLE 11G R2 입니다.
감사합니다..
CREATE OR REPLACE TRIGGER trg_mast_sub
BEFORE INSERT ON mast_sub
FOR EACH ROW
DECLARE
v_cnt NUMBER;
BEGIN
SELECT cnt INTO v_cnt FROM mast WHERE seq = :NEW.seq;
IF :NEW.sub_seq > v_cnt THEN
RAISE_APPLICATION_ERROR(-20001, 'sub_seq 허용값('||v_cnt||')초과');
END IF;
END;
/
늦은시간에도 답변 감사합니다.
마농님께서 하신것과 비슷하게 해봤는데요..
문제는 commit 타임 입니다.
웹서버가 여러개라서 동시에 거의 비슷하게 들어올경우를
테스트 프로그램으로 만들어서 해보았을때...
마지막 한게는 더 들어 가는 경우가 아주 가끔 발생 합니다.
그래서 혹시 DB 쪽에서 제약조건으로 막을수 있는 방법이 있을까 해서 질문 했습니다.
비슷하게 했는데 1개 더 들어간다고요?
그럼 비슷하게 하지 말고 똑같이 해보세요.
동시성제어는 또다른 문제입니다.
위 트리거 질문은 동시성제어와는 동떨어진 질문인데요.
마농님 답변 감사합니다.
제가 비슷하게 했다는 건 거의 똑같은데..
IF :NEW.sub_seq > v_cnt THEN 문장에서 >= 로 했구요
에러 메세지는 좀 다르게 한것뿐입니다.
그리고 제가 아무래도 질문을 잘못 한듯 합니다.
마농님 말씀데로 제가 질문하고 싶은건 동시성제어 입니다.
좋은 방법이 없을가요?
역시 해당 업무에 대해서는 웹서버를 하나만 지정해서 돌려는 방법 뿐일까요?
서버 하나만으로 하는 경우 여러가지 문제점들이 또 발생해서..
DB 쪽에서 할 수 있는 방법은 없나 검토중입니다.
혹시 다른 분들께서 비슷한 경우에 해결 방법이 있으실까 해서 질문했습니다.
보통 오라클 시퀀스 객체를 이용하는데요.
건수가 정해진 경우라면 사용자 시퀀스를 만들어 사용하는 방법도 있습니다.
mast 테이블에 cnt 외에 현재 사용중인 sub_seq 를 저장하는 방안입니다.
mast_sub 입력 프로세스
1. mast 를 for update 로 락을 걸어 sub_seq 와 cnt 를 select 합니다.
2. mast 의 sub_seq 가 cnt 와 같으면 Rollback, 오류메시지처리
3. mast 의 sub_seq 가 cnt 보다 작으면 다음단계 진행
4. mast 의 sub_seq 를 1 증가 시키고 바로 COMMIT 하여 LOCK 해제
5. sub_seq + 1 을 이용하여 mast_sub 입력 진행
mast_sub 입력 프로세스 는 mast 로부터 채번을 하면서 Lock 를 설정합니다.
Lock 설정으로 동시성은 조금 떨어지지만
제약조건을 만족시키면서도 Lock 시간을 최소화합니다.
답변 감사합니다.
동시 사용자가 1분내에 몇백명까지 몰린다면 DB 락이 오히려 문제가 되지 않을가요?
일단 좋은 의견 주셔서 해당 내용으로 검토해 봐야 겠습니다.
다시한번 감사드립니다.
두가지 사항은 상반되는 상황입니다.
(동시성을 높이는것) VS (제약조건을 준수하는것)
제약조건을 반드시 준수해야 하는 상황으로 판단이 되며, 그러려면 락은 필수입니다.
대신 락 시간을 최소화해야겠지요.
락 걸고 모든 처리를 다 마무리하고 락을 해제한다면 동시성은 크게 저하가 되겠지요.
하지만 채번과정에서만 락을 걸고 나머지 처리과정에서는 락을 걸지 않는다면?
동시성이 크게 저하되지는 않겠지요.
(락 > 조회 > 업데이트 > 락해제) 이 일련의 채번 과정은 오래 걸릴 과정은 아니죠.
채번과정만 입력과정과는 별개로 순식간에 진행되므로 크게 무리가 되지 않을것 같습니다.
추가 사항 역시 꼼꼼히 챙겨주셔서 감사합니다.
해당 부분도 충분히 검토 하도록 하겠습니다.
마농님 덕분에 잘 해결 했습니다.
구지 테이블 락까지는 안걸고 트리거 걸고 프로그램에서 한번더 체크 하는 로직으로 해결 했습니다.
감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.