SKCT API의 반복 DB 왕복 호출 줄이기

• 분야: DB
• 기술 스택: TypeScript · Cloudflare Workers · D1 · SQLite
• 관련 기술: 트랜잭션 배치 · CAS · 멱등성 · 요청별 D1 계측

문제와 원인

SKCT의 답안 저장·모의고사 시작·그룹 상태 갱신은 HTTP 요청 하나 안에서 DB를 여러 번 왕복했다. 계정·점검 상태를 읽고, 응시 기록을 조회한 뒤, 변경을 커밋하고 결과를 다시 읽는 작업이 각각 실행됐다. 종료 시각이 지난 그룹을 정리하는 경로에서는 참가자와 시험 상태를 확인하는 호출까지 겹쳤다.

일반 연습 답안 저장은 D1 호출 3회, 전체 모의고사 시작은 5회였다. 전체 요청 조건을 비교하면 최대 28회까지 호출됐다. 같은 Worker 안에서 SQL을 순차 실행해도 first()·all()·run()·batch()를 다시 호출할 때마다 D1 경계를 한 번 더 넘는다. batch() 안에 SQL 13개를 넣은 경우는 왕복 1회와 SQL 13문장으로 따로 센다.

수정 전 코드

mutate() · commitMutation()

readMutation()으로 받은 상태를 검사한 뒤 commitMutation()에서 다시 배치를 실행했다. 아래 두 호출 사이에 인증·점검·문항·revision 판정이 놓였다.

const { accountRow, siteRows, snapshot, appendSelection, repeatPolicy } =
  await readMutation(key, id, action === "append", appendCount);

const authorization = await deniedLearner(request, email, key, accountRow);
if (!authorization.ok) return authorization.response;
const maintenance = maintenanceResponse(request, siteRows);
if (maintenance) return maintenance;

const result = await commitMutation({
  action: action as "focus" | "pause" | "answer" | "save" | "submit",
  key, id, revision, position, choice, operationId,
  operationDigest: opDigest, mode: attempt.mode,
  continuousPractice: [2, 3].includes(Number(body.practiceFlowVersion)),
});

조회 횟수만 줄이면 앞선 조회와 실제 쓰기 사이에 계정 차단·점검 모드·응시 revision이 바뀌는 문제가 남는다. 쓰기 SQL 자체에도 같은 조건을 적용했다.

수정 후 코드

readAndCommitOrdinaryMutation()

계정·점검 상태, 변경 전 스냅샷, 조건부 변경, 변경 후 스냅샷을 하나의 트랜잭션 배치에 넣었다. mutation_input CTE로 사용자·응시 ID·revision·작업 ID를 바인딩하고, 소유권·진행 상태·차단 상태·점검 모드를 변경문의 조건으로 다시 판정한다.

const statements = mutationStatements(input, controlled);
const before = [
  ...context.statements,
  ...snapshotStatements(input.id, input.key),
];
const results = await commitPersonalBatch(db, [
  ...before,
  ...statements,
  ...snapshotStatements(input.id, input.key),
], metrics);

성공한 변경 뒤에만 후속 처리가 이어지도록 changes()=1 조건을 유지했다. 같은 작업 ID의 재요청은 이미 완료한 작업으로 읽고, 다른 요청이 revision을 먼저 올렸으면 변경하지 않는다. 그룹의 만료·준비 복구 경로도 필요한 상태를 묶어 읽고 제한된 배치로 정리했다.

검증 결과

수정 전후 패키지 Worker의 fetch()를 로컬 SQLite와 같은 합성 문제은행에서 실행했다. 인증·비인증, 정상·거절·재요청, 개인·그룹·관리자·SSR을 포함한 HTTP 조건 264개를 양쪽에 동일하게 적용했다. 응답 본문을 끝까지 읽고 waitUntil 작업도 모두 완료한 뒤 호출 수를 집계했다.

각 요청에서 실제 D1 메서드가 호출된 횟수와 배치에 들어간 SQL 문장 수를 별도로 기록했다. 다음은 그 요청별 기록을 DBeaver에서 SQL로 집계한 결과다.

이미지 확대

동일한 264개 요청에서 D1 호출 748→292회, SQL 3777→4026문장

닫기

화면 너비에 맞춤원본 크기−

100%

+원본 보기 (새 창)

확대한 이미지는 상하좌우로 이동할 수 있습니다. 키보드는 이미지 영역에 초점을 두고 방향키를 사용하세요. Esc로 닫습니다.

전체 SQL 문장 수는 조건부 검사와 상태 정리를 배치 안으로 옮기면서 늘었다. 감소한 것은 Worker와 D1 사이의 왕복 횟수다. 권한 거절, 점검 모드, 같은 작업의 재요청과 revision 충돌을 포함한 요청 결과는 모두 통과했다.