- 분야: DB
- 기술 스택: TypeScript · SQLite · Cloudflare D1
- 관련 기술: 불변 트리거 · 부분집합 검사 · 조건부 upsert · 트랜잭션 롤백
문제와 원인
불변 원본의 복원 전 검사가 기존 DB와 백업의 전체 행 수를 비교했다. 기존 [A]에 정상 백업 [A,B]를 넣어도 행 수가 달라 누락된 B의 복원이 막혔다.
사전 조회 뒤 같은 PK의 다른 본문이 삽입되면 DO NOTHING이 충돌을 건너뛰었다. 백업에 없는 행이 추가돼도 최종 집합을 다시 검사하지 않아, 같은 batch의 설정 변경까지 성공할 수 있었다.
수정 전 코드
verifyImmutablePersonalRows()
if (fullReplace && existingCount !== 0
&& (existingCount !== incoming.length || matchedCount !== incoming.length)) {
throw new Error(`${table} 불변 원본 범위가 백업과 달라 전체 교체를 중단했습니다.`);
}
restoreStatement()
ON CONFLICT(${conflictTarget}) ${IMMUTABLE_PERSONAL_TABLES.has(table)
? "DO NOTHING" : `DO UPDATE SET ${updates}`}
restoreBackup()
if (allowed.includes("study_groups")) statements.push(...GROUP_OWNER_SLOT_RECOVERY_SQL.map(sql => ({sql})));
if (!statements.length) throw new Error("복원할 데이터가 없습니다.");
await adminRepository.batch(statements);
수정 후 코드
기존 행 모두가 백업과 내용까지 같은 부분집합이면 누락분을 복원하도록 바꿨다. 보호 대상은 릴리스·공개 원본·비공개 원본의 3개 불변 테이블이다.
verifyImmutablePersonalRows()
// matchedCount: 백업과 내용까지 일치한 기존 행 수
if (fullReplace && matchedCount !== existingCount) {
throw new Error(`${table} 불변 원본 범위가 백업과 달라 전체 교체를 중단했습니다.`);
}
restoreStatement()
ON CONFLICT(${conflictTarget}) ${IMMUTABLE_PERSONAL_TABLES.has(table) ? immutablePersonalConflictAction(table) : `DO UPDATE SET ${updates}`}
immutablePersonalConflictAction()
같은 행의 중복은 유지하고, 다른 내용의 PK 충돌은 보호 컬럼에 UPDATE를 시도해 불변 트리거로 중단한다.
const differs = spec.columns
.map(column => `\`${table}\`.\`${column}\` IS NOT excluded.\`${column}\``)
.join(" OR ");
return `DO UPDATE SET \`${guardedColumn}\` = excluded.\`${guardedColumn}\` WHERE ${differs}`;
immutablePersonalCountGuards()
백업에 없는 추가 행은 같은 batch 끝의 행 수 guard로 거부한다. SQL 오류가 발생하면 D1 batch() 트랜잭션의 복원 행과 설정 변경을 함께 롤백한다.
SELECT CASE
WHEN (SELECT COUNT(*) FROM `skct_personal_releases`) = ? THEN 1
ELSE abs(-9223372036854775808)
END AS restored_count;
restoreBackup()
if (allowed.includes("study_groups")) statements.push(...GROUP_OWNER_SLOT_RECOVERY_SQL.map(sql => ({sql})));
if (mode === "full-replace") statements.push(...immutablePersonalCountGuards(immutableRows));
if (!statements.length) throw new Error("복원할 데이터가 없습니다.");
await adminRepository.batch(statements);
검증 결과
부분 손실·충돌·경쟁
Node.js·SQLite의 격리 메모리 DB와 D1 대체 batch에서 합성 릴리스 A·B·C를 사용했다. 6조건을 전후 각각 1회, 총 12회 실행했고, 경쟁 입력은 사전 검사 뒤 삽입한 다음 복원 batch에 site_notice 변경을 함께 넣었다.
| 조건 | 수정 전 | 수정 후 |
|---|---|---|
빈 DB ← [A,B] | 2행 복원 | 2행 복원 |
기존 [A] ← [A,B] | 거부·1행 유지 | B 복원·2행 |
기존 [A,C] ← [A,B] | 사전 거부 | 사전 거부 |
| 같은 PK A에 다른 본문 존재 | 사전 거부 | 사전 거부 |
| 검사 뒤 다른 본문의 A 삽입 | 복원 성공·설정 changed | 불변 트리거 오류·A 보존·설정 original |
| 검사 뒤 백업에 없는 C 삽입 | 2행 유지·설정 changed | integer overflow·C 보존·복원 A 롤백·설정 original |
두 경쟁 조건에서 설정 변경이 남는 경우는 2/2 → 0/2였다. 검사 뒤 삽입한 A·C는 복원 batch 밖의 행이며, 롤백은 batch가 시도한 쓰기에 적용된다.
SQLite 실행 화면
SQLite Fiddle의 별도 합성 DB에서 부분 손실 1조건·경쟁 2조건을 실행했다. 부분 손실의 사전 조회는 기존 1행·백업 2행·내용 일치 1행·충돌 0행으로, 기존 조건은 reject, 수정 조건은 allow였다.
복원 SQL을 1회 실행한 뒤 release_rows=2, release_ids=A,B가 됐다. 설정은 original을 유지했다.
Fiddle 경쟁 조건은 오류 뒤 같은 DB 연결에서 명시적 ROLLBACK을 실행한 결과다. 일반 SQLite의 ABORT는 현재 문장을 중단하므로 전체 복원 트랜잭션 취소에는 이 롤백이 필요하다.
같은 PK에 다른 본문이 들어온 경우
검사 뒤 같은 PK의 다른 본문 A를 삽입하면 복원 SQL의 UPDATE가 불변 트리거에서 중단됐다.
ROLLBACK 뒤 다시 조회한 결과는 A 1행·설정 original이었다.
백업에 없는 행이 추가된 경우
검사 뒤 백업에 없는 C를 삽입하면 batch 끝의 행 수 guard에서 integer overflow가 발생했다.
ROLLBACK 뒤에는 C 1행만 남았고 복원이 시도한 A는 제거됐다. 설정도 original을 유지했다.