답변.

질문 배경:

대량 데이터 업데이트는 데이터 전송, 마이그레이션, 비즈니스 로직 수정 시 요구됩니다. 전형적인 예: 수억 개의 행에서 상태를 변경해야 하는 경우, 서비스를 중단하지 않고 가용성과 성능을 유지해야 합니다.

문제:

제한 없이 일반적인 UPDATE는 오래 걸리며, 잠금 에스컬레이션으로 이어지고, 테이블을 잠그고, 오류 발생 시 집합 롤백이 발생할 수 있습니다. 사용자에게 미치는 영향을 최소화하고 트랜잭션성을 보장하는 접근 방식이 필요합니다.

해결책:

WHERE 및 LIMIT/TOP을 사용하여 작업을 배치로 나누기.
윈도우 함수, 임시 테이블, 임시 타임스탬프 사용.
때때로 인덱스를 일시적으로 제거하고, 저장 지점(SAVEPOINT)을 고정하며, 더 낮은 수준의 격리를 사용할 수 있습니다.

코드 예:

-- 10,000 행 배치 업데이트 예제
WHILE 1 = 1
BEGIN
  UPDATE TOP (10000) mytable
  SET status = 'archived', updated = GETDATE()
  WHERE status = 'active';
  IF @@ROWCOUNT = 0 BREAK;
END

주요 특징:

배치 처리는 잠금 보유 기간을 줄입니다.
원자성은 각 미니 트랜잭션 범위 내에서만 보장됩니다.
일부 DBMS에는 대량 작업을 가속화하는 특별한 대량 연산자가 있습니다.

난해한 질문.

하나의 트랜잭션으로 대량 UPDATE를 수행하고 테이블을 잠그지 않을 수 있습니까?

일반적으로는 불가능합니다. 대규모 트랜잭션은 테이블/페이지를 잠그고, 잠금 및 시간 초과 위험을 증가시킵니다. 배치로 작업하는 것이 좋습니다.

인덱스의 존재가 대량 업데이트 속도에 영향을 미칩니까?

네. 인덱싱된 필드를 업데이트하면 각 행에 대해 인덱스를 재구성해야 합니다. 때때로 인덱스를 임시로 제거하는 것이 합리적일 수 있지만, 이는 깊은 분석이 필요합니다.

배치 업데이트 시 모든 행이 원자적으로 업데이트됩니까?

아니요, 원자성은 한 배치(행/트랜잭션 한도) 내에서만 보장됩니다. 배치가 중단되면 일부 행은 업데이트되지만 일부는 업데이트되지 않습니다. 진정한 원자성을 위해서는 하나의 트랜잭션 내에서 전체 UPDATE가 필요하지만, 이는 대량 데이터에서 위험할 수 있습니다.

일반적인 오류 및 안티 패턴

제한 없이 단일 UPDATE, 잠금의 에스컬레이션을 유발함
인덱스 미고려 - 인덱스가 있는 열에서 높은 성능 기대
저장 지점(SAVEPOINT) 미사용

실제 사례

부정적인 사례

기술 엔지니어가 프로덕션 DB에서 단일 쿼리로 1천만 개의 행을 업데이트 하려 했습니다: UPDATE mytable SET status = 'archived'. 사이트는 "응답 없음" 상태가 되었고, 롤백은 수십 분이 걸렸으며, 성능이 저하되었습니다.

장점:

명령의 간단함, 최소한의 코드 단점:
프로덕션 서비스가 중단됨
오류 발생 시 대규모 트랜잭션 롤백 가능성

긍정적인 사례

쿼리를 10,000 행씩 배치로 나누어 짧은 트랜잭션으로 업데이트하여, 가동 시간 중에 정지 없이 진행됩니다.