MySQL SELECT FOR UPDATE 락 대기 해결: Lock Escalation 오해와 1205 타임아웃 분석
주문 한 건을 SELECT … FOR UPDATE로 조회했는데, 다른 주문의 UPDATE까지 멈춘다면 무엇부터 확인해야 합니까? 결과가 한 행이라는 이유만으로 잠금 범위도 한 행이라고 판단하면 원인을 놓칠 수 있습니다.
이번 글에서는 MySQL 8.4.11에서 직접 수행한 재현 실험을 바탕으로 실행계획, 실제 잠금, 차단 세션을 연결해 분석합니다. 인덱스 추가 전에는 다른 행의 UPDATE가 1205 오류로 종료되었으며, 추가 후에는 같은 UPDATE가 정상 처리되었습니다.
실무 문제 상황: 500번 주문을 조회했는데 900번 수정이 멈춥니다
실험 조건입니다
| 항목 | 실험 조건 |
|---|---|
| MySQL 버전 | 8.4.11 |
| 스토리지 엔진 | InnoDB |
| 격리 수준 | REPEATABLE READ |
| 데이터 | 1,000행이며 id가 기본키입니다. |
| 초기 인덱스 | order_no에는 인덱스가 없습니다. |
| 초기 설정 | autocommit=ON, innodb_lock_wait_timeout=50입니다. |
| 재현 설정 | 세션 B의 락 대기 제한을 30초로 변경합니다. |
| 타임아웃 처리 | innodb_rollback_on_timeout=OFF입니다. |
autocommit이 ON이어도 START TRANSACTION으로 명시적 트랜잭션을 시작하면 COMMIT 또는 ROLLBACK까지 트랜잭션을 유지할 수 있습니다. 실험에서는 A가 잠금을 보유하고, B가 UPDATE하며, C가 잠금 상태를 관찰하도록 연결을 분리했습니다.
세션 A에서 다음 SQL을 실행한 뒤 트랜잭션을 종료하지 않습니다.
START TRANSACTION;
SELECT id, order_no, quantity
FROM dba_for_update_lab
WHERE order_no = 'ORD-000500'
FOR UPDATE;세션 B에서는 다른 행인 900번을 수정합니다.
SET SESSION innodb_lock_wait_timeout = 30;
START TRANSACTION;
UPDATE dba_for_update_lab
SET quantity = quantity - 1
WHERE id = 900;업무 식별자는 서로 다르지만 B의 UPDATE는 대기합니다. 이 상황에서는 대기 중인 UPDATE의 실행계획뿐 아니라, 잠금을 먼저 획득한 A의 조회 경로를 함께 확인해야 합니다.
원인 분석: 조회 결과보다 넓은 범위에 잠금이 설정됩니다
실행계획에서 전체 스캔을 확인합니다
EXPLAIN
SELECT id, order_no, quantity
FROM dba_for_update_lab
WHERE order_no = 'ORD-000500'
FOR UPDATE;
실제 결과는 type=ALL, possible_keys=NULL, key=NULL, rows=1000, filtered=10, Extra=Using where입니다.
order_no 조건을 좁혀 탐색할 인덱스가 없으므로 전체를 스캔하는 계획입니다. rows와 filtered는 옵티마이저 추정치입니다. filtered=10을 보고 실제로 100행만 잠겼다고 계산해서는 안 됩니다.
이 실험의 REPEATABLE READ 조건에서 잠금 읽기는 검색 과정의 인덱스 레코드에 잠금을 설정합니다. 적절한 인덱스가 없어 전체를 탐색하면 최종 결과에 포함되지 않는 행까지 다른 트랜잭션의 수정을 막을 수 있습니다.
잠금 항목 1,005개는 업무 데이터 1,005행이라는 뜻이 아닙니다
세션 C에서 다음 SQL로 잠금을 집계했습니다.
SELECT
t.PROCESSLIST_ID AS connection_id,
dl.INDEX_NAME AS index_name,
dl.LOCK_TYPE AS lock_type,
dl.LOCK_MODE AS lock_mode,
dl.LOCK_STATUS AS lock_status,
COUNT(*) AS lock_entries
FROM performance_schema.data_locks AS dl
LEFT JOIN performance_schema.threads AS t
ON t.THREAD_ID = dl.THREAD_ID
WHERE dl.OBJECT_SCHEMA = DATABASE()
AND dl.OBJECT_NAME = 'dba_for_update_lab'
GROUP BY
t.PROCESSLIST_ID, dl.INDEX_NAME,
dl.LOCK_TYPE, dl.LOCK_MODE, dl.LOCK_STATUS
ORDER BY connection_id, lock_type, index_name, lock_mode;DATABASE()로 필터링하므로 관찰 연결에서도 테스트 DB를 선택해야 합니다.
한 건을 찾는 쿼리였지만 PRIMARY에 다수의 잠금 항목이 관측되었습니다. 다만 data_locks는 업무 행 수를 세는 테이블이 아닙니다. supremum 같은 내부 의사 레코드의 잠금도 표시될 수 있습니다.
따라서 1,005개를 “서로 다른 업무 데이터 1,005행”으로 해석하지 않습니다. 초과한 5개 항목의 정확한 구성은 이 집계 화면만으로 확정할 수 없습니다.
TABLE/IX는 Lock Escalation의 증거가 아닙니다
InnoDB는 행 잠금이 많아졌다는 이유로 테이블 잠금으로 자동 승격하는 Lock Escalation을 사용하지 않습니다.
캡처의 TABLE/IX는 하위 레코드에 배타 잠금을 설정하려는 의도 잠금입니다. 테이블 전체에 대한 배타 잠금인 TABLE/X와 다릅니다. 여러 트랜잭션의 IX는 서로 호환되므로 IX가 있다는 사실만으로 이번 대기의 원인을 설명할 수 없습니다.
이번 현상은 전체 스캔에 따라 넓게 설정된 레코드 잠금으로 해석해야 합니다. 일반적인 비잠금 SELECT까지 모두 차단된다는 의미도 아닙니다.
대기 관계로 실제 충돌 지점을 확인합니다
| 항목 | 관측값 |
|---|---|
| 대기 연결 | 11 |
| 차단 연결 | 9 |
| 대기 인덱스 | PRIMARY |
| 대기 잠금 모드 | X,REC_NOT_GAP |
| 대기 상태 | WAITING |
| 대기·차단 LOCK_DATA | 모두 900 |
| 차단 잠금 모드 | X |
X,REC_NOT_GAP은 갭을 제외한 인덱스 레코드 자체에 대한 배타 잠금 요청입니다. 이 화면은 900번 레코드에서 잠금 충돌이 발생했다는 근거입니다. 갭 락이라는 설명만으로 이번 UPDATE 대기를 설명하면 실제 관측과 어긋납니다.
해결책: 타임아웃 처리와 인덱스 개선을 구분합니다
1205 오류가 발생해도 트랜잭션 종료를 확인합니다
MySQL Error (1205):
Lock wait timeout exceeded; try restarting transactioninnodb_lock_wait_timeout은 InnoDB 행 잠금을 기다리는 제한입니다. SQL 전체 실행 시간이나 애플리케이션 요청 시간을 일괄 제한하는 설정이 아닙니다.
이번 환경은 innodb_rollback_on_timeout=OFF입니다. 이때 락 대기 타임아웃은 기본적으로 실패한 문장을 롤백하며, 트랜잭션 전체를 자동 종료하지 않습니다. 같은 트랜잭션에서 앞서 수행한 변경까지 사라졌다고 가정하면 안 됩니다.
따라서 업무 단위 전체를 실패로 처리할 정책이라면 명시적으로 ROLLBACK하고, 재시도 가능 여부를 판단해야 합니다.
ROLLBACK;프레임워크가 자동으로 롤백하는지는 예외 처리와 트랜잭션 경계 설정에 따라 확인해야 합니다. 이번 캡처는 타임아웃 발생을 확인한 결과이며, 이전 변경의 잔존 여부를 별도로 재현한 실험은 아닙니다.
주문번호 인덱스로 검색 범위를 줄입니다
실험에서는 A와 B의 트랜잭션을 모두 ROLLBACK한 뒤 인덱스를 추가했습니다.
ALTER TABLE dba_for_update_lab
ADD UNIQUE INDEX uk_order_no (order_no);
EXPLAIN
SELECT id, order_no, quantity
FROM dba_for_update_lab
WHERE order_no = 'ORD-000500'
FOR UPDATE;
| 항목 | 인덱스 추가 전 | 인덱스 추가 후 |
|---|---|---|
| type | ALL | const |
| key | NULL | uk_order_no |
| 추정 rows | 1000 | 1 |
| filtered | 10 | 100 |
조회 조건에 맞는 유일 인덱스로 접근 경로가 바뀌었습니다. 유일 키 전체를 동등 조건으로 검색하여 존재하는 한 행을 찾는 경우에는 범위 검색과 잠금 범위가 달라집니다. 다만 보조 인덱스와 대응하는 클러스터드 인덱스에 잠금이 설정될 수 있으므로 잠금 항목이 반드시 1개라는 뜻은 아닙니다.
UNIQUE 인덱스는 주문번호가 실제 업무에서도 유일하다는 전제에서 선택합니다. 중복이 허용되는 컬럼에 이번 DDL을 그대로 적용해서는 안 됩니다.
동일한 UPDATE를 다시 실행하여 개선을 확인합니다
인덱스 추가 후 A에서 500번 주문을 다시 FOR UPDATE로 조회하고 트랜잭션을 유지하는 순서로 실험을 반복합니다. B에서는 다음 SQL을 실행합니다.
START TRANSACTION;
UPDATE dba_for_update_lab
SET quantity = quantity - 1
WHERE id = 900;
SELECT id, order_no, quantity
FROM dba_for_update_lab
WHERE id = 900;
제공된 실행 로그에서 UPDATE는 1행을 수정했고 도구 표시 시간은 0.997ms입니다. 별도의 결과 화면에서도 id=900, order_no=ORD-000900, quantity=99를 확인했습니다.
0.997ms는 이번 단일 실행의 관측값입니다. 스크립트 전체 시간인 148ms와 구분해야 하며, 이를 일반적인 성능 보장이나 처리량 향상 비율로 확대하지 않습니다.
또한 UPDATE 성공과 같은 세션의 조회 결과는 COMMIT 완료를 의미하지 않습니다. 실험 종료 시 A와 B에서 각각 ROLLBACK하여 잠금과 테스트 변경을 정리합니다.
DBA 운영 노하우: 잠금 범위와 보유 시간을 함께 줄입니다
대기 SQL보다 먼저 실행된 트랜잭션을 추적합니다
900번 UPDATE는 기본키로 한 행을 찾는 SQL입니다. 그런데도 앞선 트랜잭션이 해당 레코드까지 잠그면 대기합니다. 대기 SQL에만 인덱스를 추가하려 하지 말고, 차단 세션이 어떤 SQL로 잠금을 획득했는지 확인해야 합니다.
차단 세션은 현재 SQL을 실행하지 않는 Sleep 상태에서도 미종료 트랜잭션을 보유할 수 있습니다. 운영에서는 다음 조회로 열린 트랜잭션과 현재 연결 상태를 함께 확인할 수 있습니다. 다른 연결을 보려면 필요한 모니터링 권한이 있어야 합니다.
SELECT
trx.trx_mysql_thread_id AS connection_id,
trx.trx_started,
TIMESTAMPDIFF(
SECOND, trx.trx_started, NOW()
) AS trx_age_seconds,
trx.trx_state,
trx.trx_rows_locked,
trx.trx_query,
p.COMMAND,
p.TIME AS process_state_seconds
FROM information_schema.innodb_trx AS trx
LEFT JOIN information_schema.processlist AS p
ON p.ID = trx.trx_mysql_thread_id
ORDER BY trx.trx_started;trx_query가 NULL이어도 열린 트랜잭션일 수 있습니다. 또한 processlist의 TIME은 현재 상태의 경과 시간이므로 전체 트랜잭션 나이는 trx_started와 구분해서 읽습니다.
비관적 락을 잡은 채 외부 응답을 기다리지 않습니다
FOR UPDATE 이후 외부 API 호출이나 사용자 입력을 기다리면 잠금 보유 시간이 길어집니다. 가능한 사전 작업을 트랜잭션 밖에서 처리하고, 트랜잭션 안에서는 필요한 데이터를 다시 검증한 뒤 짧게 변경하고 종료하는 구조를 검토합니다.
여러 행을 잠그는 업무에서는 접근 순서를 통일하고, 처리량뿐 아니라 트랜잭션 지속 시간과 동시 접근 빈도도 함께 확인합니다.
타임아웃 증가는 원인 해결과 구분합니다
대기 한도를 늘려도 불필요하게 넓은 잠금 범위는 그대로입니다. 오히려 대기 연결이 더 오래 누적될 수 있으므로 타임아웃은 서비스 응답 목표와 복구 정책에 맞춰 정해야 합니다.
애플리케이션의 요청·드라이버 타임아웃도 함께 점검합니다. 클라이언트가 먼저 요청을 포기했을 때 서버 작업과 트랜잭션을 어떻게 정리하는지 확인해야 합니다.
재시도에는 횟수 제한과 지연을 두고 중복 처리를 방지해야 합니다. 특히 외부 결제나 메시지 발행이 섞인 업무는 DB 롤백만으로 외부 작업까지 취소되지 않으므로 멱등성을 함께 설계해야 합니다.
단순 수량 차감은 조건부 UPDATE도 검토합니다
단순히 수량이 남아 있을 때 1개 차감하는 업무라면 다음과 같은 조건부 UPDATE를 검토할 수 있습니다.
UPDATE dba_for_update_lab
SET quantity = quantity - 1
WHERE id = 900
AND quantity > 0;영향받은 행이 1개면 변경 성공이며, 0개면 대상 부재 또는 조건 불충족입니다. 이 방식도 행 잠금을 사용하므로 동일 행의 경합은 발생할 수 있습니다. 여러 테이블의 상태를 함께 검증해야 하는 업무까지 한 문장으로 대체할 수 있다는 뜻은 아닙니다.
마무리: 반환 행 수와 잠금 범위를 구분해야 합니다
이번 실험에서는 한 건을 찾는 FOR UPDATE가 전체 스캔으로 실행되면서, 다른 행을 수정하는 세션까지 차단했습니다. 잠금 집계와 대기 관계를 통해 9번 연결이 900번 레코드에서 11번 연결을 막는 상황을 확인했습니다.
주문번호 인덱스 추가 후 실행계획은 ALL에서 const로 바뀌었고, 재실행한 UPDATE는 정상 처리되었습니다. InnoDB 락 장애에서는 Lock Escalation을 가정하기보다 검색 경로, 충돌 레코드, 트랜잭션 보유 시간, 타임아웃 후 정리 정책을 순서대로 확인하는 것이 중요합니다.
참고 자료입니다
댓글 0
첫 댓글을 남겨보세요.