UPDATE와 DELETE 안전성

UPDATE와 DELETE에서 문법 오류보다 위험한 것은, SQL이 오류 없이 실행되어 의도보다 많은 행을 바꾸는 경우입니다. 문법 오류는 서버가 막아 주지만, 조건이 틀린 UPDATE는 서버가 보기에 아무 문제가 없습니다. WHERE를 썼다는 것만으로는 안전하지 않고, 조건식이 바꾸려는 행의 집합과 정확히 맞아야 합니다.

이 글에서는 sora의 초안 하나를 공개하는 작업을 예로 듭니다. 먼저 WHERE를 빠뜨린 UPDATE를 트랜잭션 안에서 실행해 피해의 규모를 보고 되돌린 다음, 기본 키와 현재 상태를 함께 조건으로 둔 UPDATE로 한 행만 바꿉니다. 바꾸기 전에 같은 조건으로 조회하고, 바꾼 뒤에 영향받은 행의 수와 전체 상태를 확인한 다음에만 COMMIT합니다.

트랜잭션의 최소 개념

트랜잭션(transaction)은 여러 변경을 하나로 묶어, 모두 확정하거나 모두 취소하게 하는 단위입니다. START TRANSACTION으로 시작하고, 검토를 마친 변경은 COMMIT으로 확정하며, 취소할 때는 ROLLBACK을 실행합니다. 커밋하지 않은 채로 연결이 끊기면 그 트랜잭션의 변경은 롤백됩니다.

MySQL은 기본으로 자동 커밋(autocommit)이 켜져 있어서, 트랜잭션을 직접 시작하지 않고 실행한 DML은 한 문장마다 곧바로 확정됩니다. 현재 연결의 설정은 다음처럼 확인합니다.

SELECT @@autocommit;

@@autocommit
1

그래서 아래의 실습은 반드시 한 연결 안에서 START TRANSACTION부터 ROLLBACK까지 순서대로 실행합니다. 원자성과 격리, 잠금은 8장에서 자세히 다루고, 여기서는 확정하기 전에 결과를 확인할 기회를 얻는 용도로만 씁니다.

조건을 빠뜨린 UPDATE

초안 하나를 공개하려다 WHERE를 빠뜨리면 모든 게시글이 PUBLISHED가 됩니다. 이때 published_at까지 채우면 1장의 CHECK도 통과하므로, 오류 없이 모든 행이 잘못 공개됩니다. 이번에는 트랜잭션 안에서 실행해 결과를 보고 곧바로 되돌립니다.

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;

본문의 순서대로만 실습했다면 게시글은 1장의 'SELECT 기초'와 앞 글의 세 행을 더한 네 행이고, 그 가운데 초안은 'JOIN 예습' 하나입니다. 세 SELECT의 결과는 차례로 다음과 같습니다.

changed_rows
4

status    | rows_per_status
PUBLISHED | 4

status    | rows_per_status
DRAFT     | 1
PUBLISHED | 3

목표는 한 행이었는데 changed_rows는 전체 행 수인 4입니다. ROW_COUNT()는 바로 앞 문장이 실제로 바꾼 행의 수를 돌려주는데, version을 모든 행에서 1씩 올렸으므로 이미 공개된 행도 바뀐 행으로 셉니다. 트랜잭션 안에서는 네 행이 모두 PUBLISHED로 보이고, ROLLBACK 뒤에는 원래의 분포로 돌아옵니다. 연습 문제의 INSERT를 실행했다면 수는 다르지만 의미는 같습니다.

SQL_SAFE_UPDATES를 켠 세션에서는 이 UPDATE가 실행되기 전에 오류 1175로 거부됩니다. 키 열을 쓰는 WHERE나 LIMIT이 없는 UPDATE와 DELETE를 막는 설정이며, 위의 결과는 이 설정이 꺼진 기본 상태를 기준으로 했습니다. 다만 이 설정은 WHERE를 빠뜨린 실수만 막을 뿐, 조건식이 바꾸려는 대상과 맞는지는 확인해 주지 않습니다.

키 조건이 있어도 키의 범위가 틀리면 데이터는 바뀝니다. 그래서 변경 전의 SELECT와 변경하는 DML이 같은 조건식을 쓰고, 예상한 키와 건수를 사람이 확인해야 합니다. 트랜잭션은 커밋하기 전에 되돌릴 기회를 주지만, 그 안에서 DDL을 실행하면 2장 1편에서 본 것처럼 암묵적으로 커밋되고, 메일 발송 같은 외부 호출은 롤백으로 되돌릴 수 없습니다.

더 깊이: 안전 갱신 모드가 실제로 검사하는 것

mysql 클라이언트를 --safe-updates나 같은 뜻의 --i-am-a-dummy로 시작하면, 접속할 때 sql_safe_updates=1과 함께 sql_select_limit=1000, max_join_size=1000000을 세션에 설정합니다. 그래서 이 모드에서는 LIMIT이 없는 SELECT가 1,000행까지만 돌려주고, 서버가 100만 개보다 많은 행 조합을 검사해야 한다고 추정하는 여러 테이블의 SELECT는 오류가 됩니다. 여러 테이블을 바꾸는 UPDATE와 DELETE는 대상 테이블 가운데 하나라도 테이블 스캔을 쓸 때만 거부됩니다.

WHERE에 키 열을 적었는데도 거부되는 경우가 있습니다. MySQL 8.4 매뉴얼에 따르면 옵티마이저가 그 키의 인덱스를 쓰지 않기로 하면 오류가 나는데, 범위 최적화의 메모리가 range_optimizer_max_mem_size를 넘어 테이블 스캔으로 돌아가는 경우가 하나입니다. 인덱스가 있는 문자열 열을 WHERE c1 = 2222처럼 숫자와 비교해, 형 변환 때문에 인덱스를 쓰지 못하는 경우가 다른 하나입니다. 즉 이 모드는 키 열을 적었는지가 아니라, 실행 계획이 키로 대상 행을 정하는지를 기준으로 판단합니다.

이유를 찾을 때는 같은 UPDATE나 DELETE 앞에 EXPLAIN을 붙입니다. EXPLAIN은 안전 갱신 모드의 오류를 내지 않으므로, 이어서 SHOW WARNINGS를 실행하면 인덱스를 쓰지 못한 원인을 볼 수 있습니다. 오류가 났을 때의 메시지에도 처음 생긴 진단 내용이 함께 들어갑니다.

바꾸기 전에 보고, 바꾼 뒤에 센다

안전한 변경은 같은 조건식으로 대상을 먼저 조회하는 데서 시작합니다. 사전 조회로 대상의 기본 키와 현재 값을 확인하되, 이 조회만으로 행이 잠기거나 값이 고정되지는 않습니다. 조회와 UPDATE 사이에 다른 연결이 행을 바꿀 수 있으며, 이를 막는 잠금 읽기는 8장 3편에서 다룹니다.

UPDATE의 조건이 어떤 행과도 맞지 않으면 SQL은 오류 없이 성공하고, 영향받은 행의 수만 0이 됩니다. 그러니 0은 애플리케이션이나 실행하는 사람이 따로 처리해야 하는 결과입니다. 영향받은 행의 수가 예상과 같은지 보고, 같은 트랜잭션 안에서 바뀐 행을 다시 읽은 뒤에야 COMMIT합니다.

UPDATE와 DELETE, 트랜잭션은 표준 SQL의 개념이지만, ROW_COUNT()와 SQL_SAFE_UPDATES는 MySQL의 기능입니다. mysql 클라이언트는 UPDATE 뒤에 조건에 맞은 행(Rows matched)과 실제로 바뀐 행(Changed)을 함께 보여 주고, ROW_COUNT()는 그 가운데 바뀐 행의 수를 돌려줍니다. 애플리케이션의 드라이버는 연결 설정(CLIENT_FOUND_ROWS)에 따라 조건에 맞은 행의 수를 돌려주기도 하므로, 쓰는 드라이버가 어느 쪽인지 확인해 둡니다.

기본 키와 현재 상태로 한 행만 바꾼다

공개할 초안은 이메일과 제목으로 찾되, 실제 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 = '[email protected]'
    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;

결과의 id는 앞에서 연습 문제를 얼마나 실행했는지에 따라 달라지므로, 아래에는 나머지 열만 적었습니다. 사전 조회는 초안 한 행을 보여 주고, UPDATE는 한 행을 바꾸며, 다시 읽은 행은 공개 상태와 게시 시각을 갖습니다.

status | published_at | title
DRAFT  | NULL         | JOIN 예습

changed_rows
1

title     | status    | published_at
JOIN 예습 | PUBLISHED | 2026-07-05 01:30:00.000000

changed_rows가 1이고 다시 읽은 행이 예상과 같을 때만 COMMIT합니다. 코드 끝의 COMMIT은 조건문이 아니므로, 실제로는 문장을 나누어 실행하면서 결과가 예상과 다르면 COMMIT 대신 ROLLBACK을 실행합니다. 이 UPDATE로 이 행의 version도 0에서 1이 됩니다.

@target_id에는 스칼라 서브쿼리의 결과가 들어갑니다. 이메일과 제목, 초안 조건에 맞는 행이 없으면 NULL이 들어가 UPDATE는 0행을 바꾸고, 제목은 UNIQUE가 아니어서 맞는 행이 둘 이상이면 오류 1242(Subquery returns more than 1 row)가 납니다. 이 오류로 SET이 실패하면 변수는 바뀌지 않으므로, 같은 연결에서 앞서 넣어 둔 값이 있다면 그 값이 그대로 남습니다. SET이 오류를 냈다면 다음 문장으로 넘어가지 않고, 사전 조회의 결과가 정확히 한 행인지 확인한 뒤에 UPDATE를 실행합니다.

애플리케이션에서는 같은 방법을 낙관적 동시성 제어(optimistic concurrency control)로 씁니다. 읽을 때의 version을 기억해 두었다가 WHERE id = ? AND version = ?로 UPDATE하면, 그사이 다른 사용자가 바꾼 행은 0행이라는 결과로 드러납니다. 0행에는 없는 id, 이미 처리된 요청, 다른 사용자의 변경이라는 세 경우가 섞여 있으므로, 다시 조회해 어느 경우인지 가린 뒤 그에 맞는 응답으로 바꿉니다.

바꾼 뒤 전체를 다시 확인한다

한 행을 확인하는 것으로 끝내지 않고, 상태별 건수와 잘못된 상태와 시각의 조합, 대상 회원의 게시글을 다시 봅니다. 삭제라면 남아 있어야 할 자식 행과 감사 기록의 수도 함께 셉니다.

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 = '[email protected]'
ORDER BY p.id;

본문의 순서대로 실습했다면 결과는 다음과 같습니다. 마지막 결과도 id 열은 뺐습니다.

status    | rows_per_status
PUBLISHED | 4

broken_publication_pairs
0

title       | status
SELECT 실습 | PUBLISHED
JOIN 예습   | PUBLISHED

PUBLISHED가 한 건 늘고 DRAFT가 한 건 줄었는데, 결과에는 DRAFT의 행이 아예 없습니다. GROUP BY는 실제로 있는 행으로만 그룹을 만들기 때문에, 초안이 0건이 되면 0이라는 행이 나오는 대신 그 그룹이 사라집니다. 상태마다 0까지 보려면 상태의 목록을 따로 두고 OUTER JOIN해야 하며, 이 방법은 5장 3편에서 다룹니다. 연습 문제의 초안이 남아 있다면 DRAFT 행이 그 수만큼 보이고, 어느 경우든 broken_publication_pairs는 0이어야 하며 다른 회원의 글은 바뀌지 않아야 합니다.

DELETE 전에 정할 것

DELETE는 행을 없애므로, 지운 뒤에는 "없다"는 결과만 남고 어떤 값이 사라졌는지 되살리기 어렵습니다. 그래서 지우기 전에 대상의 기본 키 목록과 건수를 같은 조건으로 조회하고, 그 행을 가리키는 자식 행과 보존해야 할 기록이 있는지 확인합니다. 지울 행이 많다면 트랜잭션 안에 SAVEPOINT를 두고 지운 뒤, 결과를 보고 ROLLBACK TO SAVEPOINT로 되돌려 보는 연습도 도움이 됩니다.

무엇을 지울지보다 먼저 정말 지워야 하는지를 정합니다. 화면에서만 숨기면 되는 소프트 삭제(soft delete)인지, 법적인 요구로 실제로 지워야 하는지, 지운 사실의 이력이 필요한지에 따라 방법이 다릅니다. 모든 테이블에 무작정 deleted_at 열을 더하면 모든 조회가 그 조건을 잊지 않아야 하고, 지운 회원의 이메일을 다시 쓸 수 있는지 같은 UNIQUE의 규칙도 복잡해집니다. 물리 삭제와 소프트 삭제, 익명화는 16장에서 비교합니다.

DELETE 뒤에 AUTO_INCREMENT의 번호가 비는 것은 오류가 아닙니다. 번호는 행을 구별하는 데만 쓰고, 번호가 연속인지에 업무의 의미를 두지 않습니다.

많은 행을 바꿀 때

수십만 행을 한 트랜잭션으로 바꾸면 언두 로그와 잠금이 커지고, 복제 서버가 따라잡는 데도 오래 걸립니다. 이런 작업은 기본 키의 범위로 나누어 배치마다 커밋하고, 실행 속도를 제한하며, 어디까지 끝났는지 기록해 중간에 멈춰도 이어서 할 수 있게 합니다. 영향받는 행의 수에 상한을 두고 예상보다 크면 자동으로 멈추게 하는 것도 같은 목적입니다.

백업은 실시간으로 되돌리는 장치가 아니며, 백업에서 복구하면 그 뒤의 정상적인 변경까지 함께 되돌아갈 수 있습니다. 그래서 위험한 SQL은 동료의 검토를 받고, 정해 둔 작업 시간에, 누가 무엇을 실행했는지 기록하면서 실행합니다.

조건식 자체의 함정도 있습니다. published_at <> '2026-07-04 01:45:00'처럼 NULL을 허용하는 열에 부정 조건을 쓰면 그 열이 NULL인 초안은 결과에서 빠지고(3장 4편), 시간대를 잘못 맞춘 날짜 범위는 하루 밖의 행을 바꿀 수 있습니다. UPDATE에 LIMIT을 안전장치로 붙이려면 ORDER BY로 유일한 순서를 정해야 하며, LIMIT이 조건식의 검증을 대신하지는 않습니다.

평범한 SELECT는 시작한 트랜잭션을 커밋하지 않습니다. 다만 쓰는 클라이언트가 문장마다 COMMIT을 보내도록 설정되어 있지 않은지, 확인 쿼리를 다른 연결에서 실행하고 있지 않은지는 살펴야 합니다. 다른 연결에서는 커밋하지 않은 변경이 보이지 않으므로, 확인은 변경한 그 연결에서 합니다.

건수가 같아도 대상이 다를 수 있다

사전 조회에서 지울 대상이 id 10과 11이었는데, 조회한 뒤 다른 작업이 데이터를 바꾸어 실제 DELETE의 대상이 10과 12가 되었다고 해 봅시다. 예상한 건수도 2이고 ROW_COUNT()도 2이지만, 지우려던 11은 남고 12가 지워졌습니다. 건수가 같다는 것만으로는 성공이라고 할 수 없는 경우입니다.

이를 막으려면 사전 조회에서 얻은 id 목록을 조건에 함께 둡니다. WHERE id IN (10, 11) AND status = 'DELETED'처럼 쓰면 그사이 11의 상태가 바뀌었을 때 영향받은 행이 1이 되어 차이가 드러나고, 12는 처음부터 대상이 아닙니다. 아래의 연습 문제는 다른 연결이 함께 쓰지 않는 실습 환경을 전제로 하며, 운영 환경에서는 대상의 id와 기대하는 상태를 함께 조건으로 두거나 8장 3편의 잠금으로 경쟁을 막은 뒤 결과까지 확인합니다.

바꿀 기본 키의 목록, 예상 건수, 바꾸기 전의 상태, 바꾼 뒤에도 지켜야 할 조건을 설명할 수 없다면 실행하지 않습니다. 이 글부터 모든 DML 예제는 사전 조회, 영향받은 행의 수, 사후 확인을 기본 형식으로 따릅니다. 바꾸기 전의 값과 실행한 사람, 사유, 보존 기간을 함께 남기는 방법은 15장의 변경 이력에서 이어집니다.

정리

• 변경 전의 SELECT와 DML은 같은 조건식을 쓰고, 예상한 키와 건수를 확인한 뒤 실행합니다.
• 자동 커밋이 켜져 있으면 DML이 문장마다 확정되므로, 확인이 필요한 변경은 트랜잭션 안에서 실행하고 결과를 본 뒤 COMMIT합니다.
• 기본 키와 기대하는 상태나 버전을 함께 조건에 두면, 이미 처리되었거나 다른 사람이 바꾼 행은 오류 대신 0행으로 드러납니다.
• ROW_COUNT()는 바로 앞 문장이 실제로 바꾼 행의 수이며, 건수가 같아도 대상이 다를 수 있으므로 바뀐 행과 전체 분포를 다시 확인합니다.
• DELETE 전에는 물리 삭제와 소프트 삭제 가운데 무엇이 맞는지부터 정하고, 대상의 키 목록과 자식 행을 확인합니다.

연습 문제

1. 같은 UPDATE를 다시 실행하기

본문의 보호 조건이 있는 UPDATE가 커밋된 뒤, 같은 연결에서 같은 @target_id와 status = 'DRAFT' 조건으로 UPDATE만 다시 실행하면 영향받은 행은 몇 개인가요? 조건을 status = 'PUBLISHED'로 바꿔 실행하면 어떻게 되나요? 두 실험은 START TRANSACTION 안에서 실행하고 ROLLBACK으로 되돌려, 뒤의 장에서 쓰는 데이터를 그대로 둡니다.

2. 제목은 행을 하나로 정하지 않는다

제목만으로 게시글을 찾는 UPDATE가 왜 위험한지 확인해 보세요. 다음 SQL을 실행하면, 1장 3편의 연습 문제를 실행했는지에 따라 changed_rows가 어떻게 달라지나요?

START TRANSACTION;

UPDATE posts
SET view_count = view_count + 1
WHERE title = 'JOIN 예습';

SELECT ROW_COUNT() AS changed_rows;

ROLLBACK;

3. 오래된 삭제 글을 지우는 절차

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;

-- 사전 조회의 건수와 같을 때만 COMMIT하고, 다르면 ROLLBACK한다.
COMMIT;

다음 글 제약 조건 오류 진단에서는 NOT NULL과 UNIQUE, 외래 키, CHECK가 낸 오류를 읽고, 어느 규칙이 왜 거부했는지 차례로 진단하는 방법을 다룹니다.