이상징후조기경보 시스템 – 부서(담당/대응) 집합 동기화 로직 설계 & 무결성·동시성 보강

1. 문제 정의

• MNG_DEPT_DTL 테이블에서
  담당부서(ROLE=01)는 정확히 1개,
  대응부서(ROLE=02)는 0..N개 존재해야 해요.

• 화면에서 지표 정보를 수정할 때 담당·대응 전체 집합을 매번 통째로 갱신하지만,
  기존 로직으로는

  • 어떤 대응부서가 추가/삭제/변경된 것인지 구분이 어렵고,

  • 단순 전체 DELETE → 재삽입 방식은

    • 이력(등록일시 등)이 전부 리셋되고
    • 무결성/동시성 문제를 유발할 위험이 있었어요.

2. 해결 전략: 2-스텝 집합 동기화(MERGE + NOT-IN DELETE) 도입

(1) MERGE – Upsert

• 담당 부서 1개 + 대응 부서 N개를 DTO 리스트로 받아 Source 집합을 구성
• 동일 TASK/ROLE/DEPT_CD 조합 존재 시 → UPDATE (최초등록일시 보존)
• 없으면 → INSERT

(2) DELETE – Source에 없는 행만 제거

• 담당부서는 chrgDeptCd 이외의 행을 삭제
• 대응부서는 DTO 리스트에 없는 DEPT_CD만 삭제
• → DB의 최종 상태가 요청 Payload와 정확히 동일한 집합이 되도록 보장

(3) 전체 흐름을 @Transactional 로 감싸기

MERGE → DELETE → SELECT 까지를 하나의 트랜잭션으로 atomic 하게 처리했어요.
→ 중간 불일치 상태 없이 ACID 보장

3. 동시성 제어: SELECT … FOR UPDATE 기반 선점 락 적용

동일 TASK에 대해 동시에 “부서 수정”이 들어오는 충돌을 방지하기 위해:

• 부모 TASK 테이블 기준으로

  sqlCopy
  SELECT 1
    FROM TASK_DTL
   WHERE TASK_ID = :taskId
   FOR UPDATE

• 서비스에서 가장 먼저 락을 획득한 요청만 MERGE/DELETE 수행 가능

• 다른 요청은 WAIT or NOWAIT 정책에 따라 차단
  → 동일 TASK에 대한 동시 저장 충돌 방지

이로써 애플리케이션 레벨뿐 아니라 DB 레벨에서도 정합성을 확보 할 수 있어요.

4. 무결성 강화: DB 레벨 제약 설계(DDL)

권한 문제로 직접 생성은 불가했지만,
설계 제안 및 인덱스 정의서를 작성하여 DBA에 전달.

✔ 담당부서 1개 보장을 위한 함수 기반 유니크 인덱스

sqlCopy
CREATE UNIQUE INDEX UX_MNG_DEPT_CHRGR
  ON MNG_DEPT_DTL(
    TASK_ID,
    CASE WHEN DEPT_ROLE_TP_CD='01' THEN '01' END
  );

✔ 대응부서 중복 방지를 위한 유니크 인덱스

sqlCopy
CREATE UNIQUE INDEX UX_MNG_DEPT_RSPN
  ON MNG_DEPT_DTL(
    TASK_ID, DEPT_ROLE_TP_CD, DEPT_CD
  );

5. 결과 및 효과

• 전체 삭제 후 재삽입 대비
  변경분만 반영 → DB 변경량 최소화
• 담당/대응 부서의 이력(최초등록일시 등) 보존
• DTO 리스트 그대로 사용하여
  불필요한 문자열 파싱 제거 → 안정성 향상
• 트랜잭션 + 락으로
  동일 TASK에 대한 동시 수정 충돌 제거
• 인덱스 기반 무결성 설계로
  데이터 품질을 DB 레벨에서 이중으로 보증