삭제된 지 30일 지난 댓글을 지우는 DELETE가 Table is specified twice로 거부된 이유

문제 발생

소프트 삭제된 댓글을 30일 뒤 완전히 지우는 정리 작업을 만들었습니다. 답글이 남아 있는 댓글은 자리(tombstone)를 지켜야 하므로, 자식이 없는 것만 고르는 조건을 넣었습니다.

DELETE FROM comments
WHERE deleted_at < NOW() - INTERVAL 30 DAY
  AND NOT EXISTS (
    SELECT 1 FROM comments AS reply WHERE reply.parent_id = comments.id
  );

ERROR 1093 (HY000): Table 'comments' is specified twice, both as a target for 'DELETE' and as a separate source for data

원인 분석

같은 테이블을 고치면서 읽을 수 없습니다. MariaDB 문서의 서브쿼리 제한 항목이 그대로 말합니다 — 서브쿼리에서 같은 테이블을 수정하면서 동시에 선택하는 것은 불가능하다고요. 문서의 예시도 정확히 이 모양입니다.

DELETE FROM staff WHERE name = (SELECT name FROM staff WHERE age=61);
ERROR 1093 (HY000): Table 'staff' is specified twice

문법 오류가 아니라 실행 모델의 제약입니다. 지우는 도중에 같은 테이블을 읽으면 어느 시점의 행을 보는지가 정의되지 않기 때문에 아예 거부합니다. UPDATE에서도 같습니다.

해결 방안

1. 먼저 고르고, 그다음 지웁니다. 두 문장으로 나누면 서브쿼리가 아닙니다.

SELECT id FROM comments AS c
WHERE c.deleted_at < NOW() - INTERVAL 30 DAY
  AND NOT EXISTS (SELECT 1 FROM comments AS reply WHERE reply.parent_id = c.id);

DELETE FROM comments WHERE id IN (1, 2, 3);

애플리케이션 코드에서는 id 배열을 받아 IN으로 지웁니다. 목록이 길면 잘라서 여러 번 보냅니다.

2. 답글이 있는 댓글은 두 번 돌려서 처리합니다. 첫 번째 실행에서 자식 없는 답글이 지워지면, 두 번째 실행에서 그 부모가 자식 없는 댓글이 되어 지워집니다. 한 번에 끝내려 하지 않아도 됩니다.

3. 다중 테이블 DELETE로 자기 자신을 조인할 수도 있습니다. MariaDB의 DELETE는 여러 테이블을 조인해 그중 한 테이블의 행을 지우는 문법을 갖고 있고, 여기서는 같은 테이블에 다른 별칭을 붙일 수 있습니다.

DELETE c FROM comments AS c
LEFT JOIN comments AS reply ON reply.parent_id = c.id
WHERE c.deleted_at < NOW() - INTERVAL 30 DAY
  AND reply.id IS NULL;

다만 문서에 따르면 이 문법에서 ORDER BY와 LIMIT는 MariaDB 11.8.1부터만 쓸 수 있습니다. 배치를 잘라 지우려면 1번이 어느 버전에서든 같게 동작합니다.

4. 신고된 글처럼 지우면 안 되는 것은 NOT EXISTS로 함께 뺍니다. 신고 테이블은 다른 테이블이라 이 제약과 무관하게 서브쿼리로 읽을 수 있습니다.

5. 되돌릴 수 없는 작업이니 테스트로 지킵니다. 30일 지난 것은 사라지고, 최근 것과 답글이 남은 것은 살아 있는지를 실제 DB에 넣어 확인합니다.

공식 문서

• MariaDB: Subquery Limitations — Modifying and Selecting from the Same Table
• MariaDB: DELETE — Multiple-Table Syntax