답변.

대량 업데이트는 연결된 테이블에서 많은 행을 변경할 때 중요한 절차입니다. SQL의 전형적인 구현은 서브쿼리 또는 JOIN을 사용한 UPDATE입니다. 문제: 실행 순서에 대한 통제 없이 대량 업데이트 작업은 많은 행을 잠그고 잠금 에스컬레이션을 일으키며, 여러 업데이트가 있는 경우 데드록을 초래할 수 있습니다.

해결책:

항상 UPDATE를 작은 배치로 나누세요(예: 기본 키 또는 날짜 범위 기준).
JOIN을 통한 SET 지향 접근 방식을 사용하되, 제한 없이 대량 업데이트는 피하세요.
필터를 적절히 설정하고 WHERE 조건에 따라 필드를 인덱싱하며, 연결된 테이블의 작업 순서를 고려하세요.

코드 예시 (PostgreSQL):

UPDATE Orders o
SET status = 'archived'
FROM Customers c
WHERE o.customer_id = c.id
  AND c.closed = TRUE
  AND o.status != 'archived';

또는 배치로:

WITH upd AS (
   SELECT o.id
   FROM Orders o
   JOIN Customers c ON o.customer_id = c.id
   WHERE c.closed = TRUE
   AND o.status != 'archived'
   LIMIT 10000
)
UPDATE Orders
SET status = 'archived'
WHERE id IN (SELECT id FROM upd);

주요 특징:

"모든 테이블을 한 번에 업데이트하는 것"을 피하고 항상 배치로 처리하세요.
업데이트 및 필터링 필드에 대한 인덱스를 사용하세요.
필요 없는 행의 대량 업데이트를 피하면서 명확한 선택 조건을 설정하세요.

함정 질문.

범위 또는 대처 필터 없이 유사한 테이블에 대해 UPDATE를 동시에 실행하면 어떻게 됩니까?

데드록이 발생할 가능성이 있습니다: 프로세스는 서로를 기다리며 동일한 행을 잠금합니다. 이를 피하기 위해서는 패킷이 겹치지 않거나 반드시 순차적으로 실행되어야 합니다.

대량 상태 변경에 있어서 JOIN을 통한 UPDATE와 서브쿼리의 차이점은 무엇인가요?

적절한 인덱스가 있다면, 주요 차이는 오직 가독성과 특정 DBMS에서의 성능 차이에 있습니다. JOIN은 일반적으로 더 빠르며, 옵티마이저가 더 나은 계획을 세우게 합니다.

UPDATE 대신 TRUNCATE/DELETE를 사용하는 것이 적절한 경우는 언제인가요?

비즈니스 로직이 허용하는 경우에, 예를 들어 아카이브된 레코드를 물리적으로 삭제하거나 테이블을 초기화해야 할 때입니다. 그러나 상태의 대량 업데이트에는 항상 UPDATE만 사용해야 합니다.

일반적인 실수와 안티 패턴

"필터 없이 대량 UPDATE": 잠금, 롤백, 데드록 발생.
인덱스 없음 — 테이블을 완전히 스캔함.
키에 따라 범위를 나누지 않고 UPDATE를 병렬로 실행.

실제 사례

부정적인 케이스

대규모 인터넷 쇼핑몰에서 주문 및 고객의 상태 변경을 동시에 업데이트하여 여러 UPDATE를 실행했지만, 인터벌을 구분하지 않았습니다. 결과: 상호 잠금으로 인해 여러 번 강제 롤백이 필요했으며, 작성되지 않은 데이터가 롤백되었습니다.

장점:

한 번의 요청으로 모든 것을 처리.

단점:

데드록 가능성, 성능 저하, 작은 오류로도 대량의 데이터가 롤백될 수 있습니다.

긍정적인 케이스

대규모 샘플을 배치로 나누어 엄격히 순차적으로 실행하며 필터를 통해 필요한 행만 처리했습니다.