UPDATE와 DELETE 안전성

UPDATE와 DELETE의 문법 오류보다 더 위험한 오류는 SQL이 정확히 실행돼 너무 많은 행을 바꾸는 경우입니다.

WHERE를 썼다는 사실만으로 안전하지 않고 조건식이 업무 대상과 정확히 일치해야 합니다.

변경 전·후 행 집합을 같은 키로 확인하고 예상 건수를 넘으면 커밋하지 않는 절차가 필요합니다.

회원 게시판에서 특정 초안 게시글 하나를 게시하고 오래된 보관 게시글을 정리합니다.

먼저 실수 UPDATE를 트랜잭션 안에서 재현해 ROW_COUNT를 확인한 뒤 롤백하고, PK와 현재 상태를 함께 조건으로 둔 안전한 변경을 실행합니다.

트랜잭션 최소 개념

트랜잭션은 여러 SQL 변경을 하나의 확정 단위로 묶는 경계입니다.

START TRANSACTION으로 시작하고, 검토한 변경을 남길 때 COMMIT, 취소할 때 ROLLBACK을 실행합니다.

MySQL의 기본 autocommit=1에서는 트랜잭션을 직접 시작하지 않은 DML 한 문장이 실행 직후 확정될 수 있습니다.

따라서 아래 오류 실습은 반드시 START TRANSACTION과 ROLLBACK을 한 연결에서 순서대로 실행합니다.

8장에서 원자성·격리·잠금을 더 깊게 다룹니다.

조건 없는 UPDATE의 위험

한 초안 게시글을 게시하려다 WHERE를 빠뜨리면 모든 게시글 상태가 PUBLISHED로 바뀝니다.

게시 상태 검사에 맞춰 published_at까지 채우면 제약 오류 없이 모든 행이 잘못 공개될 수 있습니다.

이번에는 트랜잭션 안에서 실행해 피해 규모를 보고 즉시 롤백합니다.

START TRANSACTION;

UPDATE posts
SET status = 'PUBLISHED',
    published_at = COALESCE(published_at, updated_at),
    version = version + 1;

SELECT ROW_COUNT() AS changed_rows;
SELECT status, COUNT(*) AS rows_per_status
FROM posts
GROUP BY status;

ROLLBACK;

SELECT status, COUNT(*) AS rows_per_status
FROM posts
GROUP BY status
ORDER BY status;

version까지 증가시켰으므로 이미 게시된 행도 실제 변경되고, changed_rows는 목표 1건이 아니라 실행 직전 전체 행 수가 됩니다.

선택 연습을 실행했다면 행 수는 달라지지만 오류의 의미는 같습니다.

트랜잭션 안에서는 모든 상태가 PUBLISHED로 보이고 롤백 뒤 원래 분포로 복원됩니다.

changed_rows
N  -- 실행 직전 전체 행 수와 같음

status    | rows_per_status
PUBLISHED | N

ROLLBACK 후 status별 건수
= UPDATE 전 분포

SQL_SAFE_UPDATES는 일부 클라이언트 실수를 막지만 업무 조건식의 정확성을 보장하지 않습니다.

키 조건이 있어도 잘못된 키 범위면 데이터는 바뀝니다.

변경 전 SELECT와 변경 DML이 동일 조건식을 공유하고 예상 키·건수를 사람이 확인해야 합니다.

트랜잭션은 커밋 전 복구 기회를 주지만 DDL·외부 호출과 섞이면 같은 롤백을 기대할 수 없습니다.

안전한 변경 절차

먼저 SELECT로 대상 PK와 현재값을 고정합니다.

UPDATE WHERE에는 식별 키뿐 아니라 기대한 현재 상태를 넣어 사이에 상태가 달라졌으면 0건으로 오류가 발생하게 합니다.

ROW_COUNT가 예상과 같은지 보고 같은 트랜잭션에서 변경 후 행을 다시 읽습니다.

DELETE도 대상 스냅샷·참조 관계·보존 정책을 확인한 뒤 수행합니다.

최종 커밋은 확인 이후에만 합니다.

UPDATE·DELETE·트랜잭션은 표준 개념이고 ROW_COUNT(), SQL_SAFE_UPDATES 세부는 MySQL 기능입니다.

기본값 자동 커밋이 1이면 각 DML이 즉시 커밋될 수 있습니다.

실습 클라이언트가 구문을 자동 커밋하는지 @@autocommit을 확인합니다.

트랜잭션 격리와 동시 갱신 충돌은 ch8에서 더 깊게 다룹니다.

기본 키를 이용한 변경 보호

게시할 초안 게시글을 이메일·제목으로 미리 찾되 실제 UPDATE는 얻은 id와 DRAFT 현재 상태를 함께 조건으로 둡니다.

published_at은 이미 NULL이어야 하며 변경 뒤 건수와 행을 확인합니다.

SET @target_id = (
  SELECT s.id
  FROM posts AS s
  JOIN members AS m ON m.id = s.author_id
  WHERE m.email = 'sora@example.com'
    AND s.title = 'JOIN 예습'
    AND s.status = 'DRAFT'
);

SELECT id, status, published_at, title
FROM posts
WHERE id = @target_id;

START TRANSACTION;

UPDATE posts
SET status = 'PUBLISHED',
    published_at = '2026-07-05 01:30:00',
    updated_at = '2026-07-05 01:30:00',
    version = version + 1
WHERE id = @target_id
  AND status = 'DRAFT';

SELECT ROW_COUNT() AS changed_rows;
SELECT id, title, status, published_at
FROM posts
WHERE id = @target_id;

COMMIT;

@target_id에 사전 조회에서 확인한 한 ID를 넣었을 때 changed_rows가 정확히 1이고 상태·게시 시각·버전만 바뀝니다.

이미 다른 상태면 0건이라 덮어쓰지 않습니다.

preview rows  | 1
changed_rows | 1
after state  | PUBLISHED
published_at | 2026-07-05 01:30:00
commit       | allowed only after row and count match

사용자 변수에 ID를 수동으로 넣는 예제는 절차를 보여 주기 위한 것입니다.

애플리케이션에서는 같은 트랜잭션에서 버전 열이나 현재 상태 조건식으로 낙관적 동시성을 구현합니다.

0건은 ‘없는 ID’, ‘이미 처리됨’, ‘다른 사용자가 변경함’을 구분해 후속 SELECT와 도메인 결과로 변환합니다.

변경 전후 무결성 확인

한 행 확인만으로 끝내지 않고 전체 상태 건수, 잘못된 게시 상태·시각 조합, 대상 이메일의 게시글을 다시 봅니다.

삭제라면 보존해야 할 자식·감사 건수도 포함합니다.

SELECT status, COUNT(*) AS rows_per_status
FROM posts
GROUP BY status
ORDER BY status;

SELECT COUNT(*) AS broken_publication_pairs
FROM posts
WHERE (status = 'PUBLISHED' AND published_at IS NULL)
   OR (status = 'DRAFT' AND published_at IS NOT NULL);

SELECT p.id, p.title, p.status
FROM posts AS p
JOIN members AS m ON m.id = p.author_id
WHERE m.email = 'sora@example.com'
ORDER BY p.id;

PUBLISHED가 정확히 한 건 늘고 DRAFT가 한 건 줄며 broken_publication_pairs는 0이어야 합니다.

다른 회원·공개 게시글은 바뀌지 않아야 합니다.

DELETE는 행을 제거하므로 ‘없다’는 결과만 보고 어떤 값이 사라졌는지 복구하기 어렵습니다.

소프트 삭제가 필요한지, 법적 삭제로 실제 제거해야 하는지, 이력이 필요한지 먼저 정합니다.

무조건 deleted_at을 추가하면 모든 쿼리가 필터를 잊을 위험이 있고 UNIQUE 재사용 규칙도 복잡해집니다.

데이터 수명주기 장에서 물리 삭제·소프트 삭제·익명화를 다시 비교합니다.

나쁜 UPDATE를 트랜잭션 안에서 실행하고 롤백 전후 상태 분포를 캡처하세요.

안전 UPDATE의 id를 맞게 두되 기대 상태를 PUBLISHED로 바꾸어 changed_rows 0을 확인합니다.

WHERE에 제목만 사용했을 때 동명 게시글 여러 개가 바뀔 수 있음을 예제로 재현합니다.

DELETE도 먼저 SELECT 건수·PK 목록을 확인하고 SAVEPOINT 뒤 롤백하는 연습을 합니다.

대량 변경은 한 트랜잭션이 언두 로그·잠금·복제 지연을 키울 수 있어 PK 범위 배치, 비율 제한, 체크포인트가 필요합니다.

영향 행 수 상한을 애플리케이션과 실행 절차에 두고 예상보다 크면 자동 중단합니다.

백업은 실시간 언두 로그가 아니며 복구는 다른 정상 변경까지 되돌릴 수 있습니다.

고위험 SQL은 상대 검토, 유지보수 시간 창, 감사 로그와 함께 실행합니다.

조건식에 !=를 쓰면 NULL 행이 빠질 수 있고 시간대 날짜 범위가 어긋나면 하루 밖의 행을 바꿀 수 있습니다.

LIMIT을 UPDATE 안전장치로 쓰려면 ORDER BY와 유일한 대상 기준이 있어야 하며 근본 조건식 검증을 대신하지 않습니다.

DELETE 후 AUTO_INCREMENT 누락 구간은 오류가 아니며 ID 연속성을 업무 의미로 쓰지 않습니다.

클라이언트가 중간 SELECT 뒤 자동 커밋하는지 확인합니다.

사전 조회와 보호 조건이 있는 상태 조건식은 ch8 트랜잭션·잠금에서 동시 쓰기 주체 시나리오로 확장됩니다.

ch15 이력과 ch16 삭제에서는 변경 전 값·행위자·사유·보존 기간을 함께 기록합니다.

지금부터 모든 DML 예제는 변경 전 SELECT, 영향 행 수, 사후 일치 검사를 기본 형식으로 유지합니다.

UPDATE·DELETE 실행 기준

WHERE 유무가 아니라 대상 집합 증거로 승인합니다.

변경할 PK 목록·예상 건수·변경 전 상태·변경 후 불변식을 설명할 수 없으면 실행하지 않습니다.

연습 문제

2026-07-01 이전에 작성된 DELETED 게시글만 물리 삭제하는 절차를 작성하세요.

먼저 ID와 건수를 보고 트랜잭션 안에서 DELETE한 뒤 ROW_COUNT가 사전 조회 건수와 같을 때만 커밋한다고 가정합니다.

SELECT id, title, created_at
FROM posts
WHERE status = 'DELETED'
  AND created_at < '2026-07-01 00:00:00'
ORDER BY id;

START TRANSACTION;

DELETE FROM posts
WHERE status = 'DELETED'
  AND created_at < '2026-07-01 00:00:00';

SELECT ROW_COUNT() AS deleted_rows;

-- preview count와 같을 때만 COMMIT, 다르면 ROLLBACK한다.
COMMIT;

핵심 정리

• 변경 전 SELECT와 DML은 동일 조건식을 공유해야 합니다.
• PK·기대 상태·버전으로 오래된 변경을 0건 오류로 만듭니다.
• ROW_COUNT와 사후 불변식을 확인한 뒤 커밋합니다.
• 트랜잭션은 검증 기회를 주지만 범위 확인과 운영 복구를 대신하지 않습니다.

다음 문서에서는 NOT NULL·UNIQUE·FK·검사 오류를 고쳐 쓰기 순서로 진단합니다.