이 노트는 대량 변경 때 함께 커지는 세 가지를 분리해 읽는 방법을 설명합니다. ‘몇 행을 바꿨는가’는 영향량이고, ‘어떤 자원을 얼마나 오래 잠갔는가’는 경합의 범위이며, ‘로그와 flush가 얼마나 발생했는가’는 내구·복구 자원입니다. 실행 계획과 엔진별 관측값을 연결한 뒤, batch 분할이 원자성·checkpoint 의미를 어떻게 바꾸는지까지 추적합니다.
변경 행 수와 실제 잠금 범위
조건에 맞는 10행을 찾으려고 넓은 범위를 읽거나 인덱스·외래키·메타데이터를 검사할 수 있습니다. 잠그는 자원과 시점은 엔진·격리·계획에 달렸습니다. 최종 영향 행 수만으로 실제 잠금 범위를 추측하면 blocker를 놓칠 수 있습니다.
쿼리 자체가 5ms여도 그 거래 안에서 외부 API를 10초 기다리면 연결·잠금이 오래 남을 수 있습니다. 실행 CPU·I/O 시간과 잠금 대기·거래 보유 시간을 나눕니다. 한 방향 blocking과 순환 대기인 deadlock도 구분합니다.
엔진별 잠금 메커니즘과 관측 범위
| 엔진 | 관찰 예 | 구분할 계약 |
|---|---|---|
| SQL Server | lock DMV·waiting task·deadlock graph | key·page·object·HoBT, escalation |
| InnoDB | performance_schema data_locks·data_lock_waits | record·gap·next-key·격리 |
| PostgreSQL | pg_locks·pg_stat_activity·blocking 관계 | row/transaction wait·relation·SSI predicate |
예를 들어 SQL Server에서 한 요청이 세부 잠금을 많이 보유하면 escalation으로 table 또는 설정에 따른 HoBT 수준 잠금으로 바뀔 수 있지만, 항상 row→page→table 순서로 승격되는 것은 아닙니다. InnoDB에서 잠금 읽기나 변경 중 관찰되는 gap·next-key 잠금은 삽입과 범위 경쟁에 관여하며, 유일 동등 조건과 격리 수준에 따라 실제 범위가 달라집니다.
PostgreSQL SSI에서 보이는 predicate 정보는 일반적인 blocking range lock과 같은 것으로 세지 말고 별도 종류로 관찰합니다.
각 view의 지원 버전·권한·관측 범위를 확인합니다. row lock이 언제나 모든 행마다 view에 같은 형식으로 나타난다고 가정하지 않습니다. 내부 page latch와 transaction lock도 별도 wait 원인입니다.
그림을 글로 읽기
- 사용자 요청 지연
- 대기 세션·요청 자원
- 잠금 소유 transaction
- 시작·마지막 SQL·외부 대기
- 사용자 요청 지연 → 대기 세션·요청 자원: trace·session 연결
- 대기 세션·요청 자원 → 잠금 소유 transaction: blocker 추적
- 잠금 소유 transaction → 시작·마지막 SQL·외부 대기: 보유 시간 원인
로그 압력과 잠금 압력의 자원 분리
대량 갱신은 많은 로그 바이트·flush·복제 replay를 만들고 긴 잠금도 유지할 수 있습니다. 로그 flush 대기·디스크 여유·replica lag와 lock wait·blocker 수명을 각각 계측합니다. 작은 batch가 둘을 개선했다고 원인이 하나였다고 결론 내리지 않습니다.
인덱스 추가는 탐색·잠금 범위를 줄일 수 있지만 쓰기마다 인덱스 로그를 늘릴 수 있습니다. 반대로 transaction의 외부 대기를 제거하면 잠금 보유는 줄지만 행당 로그량은 비슷할 수 있습니다. 한 변수씩 바꿔 생성 로그·읽기량·commit 지연·p99를 비교합니다. durability를 약화해 수치만 낮춘 것을 같은 정확성의 최적화로 보고하지 않습니다.
Batch 분할과 전체 원자성·부분 완료
100만 행을 한 transaction 대신 1천 행씩 커밋하면 잠금 보유·복구 부담을 줄일 수 있지만 사용자가 중간 상태를 볼 수 있습니다. 이를 허용하는지 먼저 정합니다. 전체 전환이 필요하면 새 버전 데이터에 구성·검증한 뒤 읽기 포인터를 바꾸는 다른 모델이 필요할 수 있으며 동시 쓰기·복구는 추가됩니다.
WHERE status='pending' 목록을 OFFSET 1000씩 늘리며 갱신하면 앞의 완료 행이 조건에서 빠져 다음 pending 행을 건너뛸 수 있습니다. 안정된 키 범위·기준 입력·조건부 갱신과 완료 checkpoint를 사용합니다. checkpoint를 실제 반영보다 먼저 전진시키지 않습니다.
read next bounded key range after checkpoint
begin transaction
update rows only if expected source version still matches
record conflicts for later recalculation
persist completed range and conflict set consistently
commit
충돌 행을 건너뛰고 고수위만 올리면 영구 누락될 수 있어 내구 보류 집합·재조사 규칙이 필요합니다. 특정 행 오류가 전체 batch를 무한히 막지 않게 실패 분류·격리·한도를 둡니다. 처리·실패·보류 수를 전체 완료와 구분합니다.
접근 경로·거래 범위와 잠금 힌트의 우선순위
적합한 인덱스·검색 가능한 조건·짧은 거래·일관된 다중 키 잠금 순서를 검토합니다. row lock hint나 낮은 격리를 무조건 적용하면 lock 메모리·부정확한 읽기·새 경합이 생길 수 있습니다. 강한 격리도 deadlock·serialization 실패를 없애지 않으므로 전체 논리 거래를 제한적으로 재시도합니다.
테스트에서는 넓은 scan·긴 외부 대기·대량 로그·작은 batch를 각각 대조합니다. 취소·예외 뒤 rollback·연결 반환과 부분 진행 복구를 확인합니다. 현재 작업에서는 제품별 lock·log 부하 실험을 실행하지 않았습니다. 본문은 진단과 안전한 배치 설계입니다.
재현 연습은 같은 입력을 넓은 scan과 검색 가능한 인덱스 경로로 각각 실행하고, blocker의 자원 종류·거래 시작 시각·보유 시간을 기록하는 것으로 시작합니다. 그 다음 외부 대기를 거래 안에 넣은 경우와 제거한 경우를 비교합니다. 예상 결과는 외부 대기 제거가 잠금 보유 시간은 줄여도 행당 로그 바이트를 자동으로 줄이지 않는 것이며, 1천 행 batch는 중간 commit과 부분 완료를 만들어 전체 원자성 확인 항목을 추가한다는 것입니다.