[DB] READ_UNCOMMITTED에서 DELETE가 타임아웃 나는 이유

문제 상황

 특정 사용자의 데이터를 일괄 삭제하는 배치에서 두 종류의 타임아웃이 발생했다.

Lock wait timeout exceeded; try restarting transaction   ← 서브쿼리 DELETE 단계
Query execution was interrupted (max_statement_time exceeded)  ← PK 기반 SELECT+DELETE 단계

 

 

 두 에러는 동시에 발생한 것이 아니다. 서브쿼리 DELETE를 사용할 때 Lock wait timeout이 발생했고, PK 기반 SELECT+DELETE로 전환한 뒤 max_statement_time exceeded가 발생했다. 타임아웃 종류가 바뀐 것 자체가 분석 포인트다.

 

 

 초기 코드는 서브쿼리 DELETE 구조였다.

DELETE FROM products
WHERE id IN (
    SELECT id FROM (
        SELECT id FROM products
        WHERE user_id = #{userId}
        ORDER BY id
        LIMIT 5000
    ) t
)

 

 

 처음에는 서브쿼리가 타임아웃의 원인이라고 생각했다. 하지만 쿼리 형태가 아니라 시스템의 구조적 문제에 의해 발생한 충돌이었다.

 같은 테이블에 실시간 서비스가 ON DUPLICATE KEY UPDATE 벌크 UPSERT를 지속적으로 실행 중이었다.

 

 

 

 

 

 

 

---

실시간 서비스의 락 획득

 충돌 원인을 이해하려면 실시간 서비스의 락 획득 방식을 먼저 파악해야 한다.

@Transactional
public void saveProductData(List<Product> productList) {
    sqlSession.insert("productMapper.saveProductData", productList);
}

 

 sqlSession은 MyBatis SqlSessionTemplate으로 Spring 트랜잭션에 자동 참여한다. @Transactional 범위 전체 동안 커넥션과 락이 유지된다.

 

 

 실제 실행 쿼리는 <foreach> 벌크 INSERT + ON DUPLICATE KEY UPDATE다.

<insert id="saveProductData" parameterType="list">
    INSERT INTO products (user_id, name, code, price, ...)
    VALUES
    <foreach item="p" collection="list" separator=",">
        (#{p.userId}, #{p.name}, #{p.code}, #{p.price}, ...)
    </foreach>
    ON DUPLICATE KEY UPDATE
        name   = IF(status='N', name,   VALUES(name))
      , price  = IF(status='N', price,  VALUES(price))
      , ...
</insert>

 

 

ON DUPLICATE KEY UPDATE는 내부적으로 두 경로로 분기한다.

 

 

 

X lock은 INSERT/UPDATE 완료 시점이 아니라 트랜잭션 COMMIT 시점까지 유지된다. N건 벌크라면 X lock이 최대 N개 동시에 쌓인 채로 COMMIT까지 유지된다.

 

 

 

 

 

---

충돌 원인 분석

1. Dirty Read - 미커밋 신규 row

 DB 격리 수준이 READ_UNCOMMITTED이므로 배치 SELECT는 아직 커밋되지 않은 row도 읽는다.

 실시간 서비스가 신규 row를 INSERT 중인 트랜잭션(미커밋)이 X lock을 보유한 상태일 때, 배치 SELECT는 그 row를 Dirty Read로 읽고 DELETE 대상에 포함시킨다.

 

 

 

 격리 수준이 READ_COMMITTED였다면 커밋된 row만 읽히므로 미커밋 row는 애초에 SELECT에 잡히지 않았을 것이다.

 READ_UNCOMMITTED이기 때문에 아직 존재하지 않아야 할 row를 읽어서 DELETE까지 시도하게 된 것이다.

 

 

 

2. UPDATE 경로 X lock - 기존 row

 실시간 서비스의 UPSERT는 기존 데이터가 있는 경우 UPDATE 경로를 탄다. 이 경우 충돌하는 row는 커밋된 상태다.

 

 

 기존 데이터가 많을수록 UPDATE 경로 비율이 높아지고, 그만큼 X lock이 많이 걸려 DELETE와의 충돌 가능성이 올라간다.

 

 

 

 

3. 갭 락(Gap Lock) — 범위 스캔

 기존 서브쿼리 DELETE는 쿼리 플래너가 user_id 인덱스 범위 스캔을 선택할 가능성이 높다. 이 과정에서 InnoDB가 Next-Key Lock(record + gap)을 설정한다.

 

 

락 종류	대상	설명
Record Lock	레코드 자체	특정 row에 대한 X/S 락
Gap Lock	레코드 사이 간격	해당 간격의 INSERT 차단
Next-Key Lock	레코드 + 앞 간격	Record Lock + Gap Lock 조합 (InnoDB 기본)

 

 INSERT 실행 시 InnoDB는 실제 레코드 락 전에 INSERT INTENTION LOCK을 해당 간격에 건다. INSERT INTENTION LOCK끼리는 호환되지만, 해당 간격에 GAP LOCK이 걸려 있으면 블로킹된다.

 

 

 

 

 

 

 

---

해결 과정

1. SELECT+DELETE 분리 + id(PK) 기반 삭제

 서브쿼리 DELETE를 SELECT와 DELETE로 분리하고, id(PK)로 삭제 대상을 지정한다.

<select id="selectProductIds" parameterType="map" resultType="long">
    SELECT id FROM products
    WHERE user_id = #{userId}
    LIMIT 5000
</select>

<delete id="deleteProductsBatch" parameterType="list">
    DELETE FROM products
    WHERE id IN
    <foreach collection="list" item="id" open="(" separator="," close=")">#{id}</foreach>
</delete>

 

 이 방식에서 갭 락은 발생하지 않는다. InnoDB는 유니크 인덱스(PK 포함) 단건 조회에서 레코드 락만 걸고 갭 락은 설정하지 않는다. 갭 락은 범위 스캔이나 비고유 인덱스 스캔에서 사이에 새 row가 끼어들 수 없도록 막기 위해 설정하는 것인데, PK 포인트 룩업은 구간을 스캔하지 않기 때문이다.

 

 에러 종류가 바뀌었다. 갭 락은 제거됐지만, Dirty Read와 UPDATE 경로 X lock 충돌은 여전히 남는다. 그런데 이 단계에서 에러 종류가 Lock wait timeout에서 max_statement_time exceeded로 바뀐다.

 

 

왜 타임아웃 종류가 바뀌었나

 서브쿼리 DELETE는 user_id 범위 스캔 중 GAP LOCK이 첫 번째 충돌 지점에서 즉시 블로킹을 걸었다. 단 한 번의 락 대기가 innodb_lock_wait_timeout을 넘으면서 Lock wait timeout이 발생했다.

서브쿼리 DELETE:
    범위 스캔 → GAP LOCK → 실시간 INSERT와 충돌
    → 단일 대기 → 50초 초과 → Lock wait timeout exceeded

 

 

 PK 기반 DELETE WHERE id IN (id_1, ..., id_5000)로 바꾸면 갭 락이 사라진다. 하지만 Dirty Read로 읽힌 미커밋 row들이 id 리스트에 포함된 상태이므로, DELETE는 5000개 row에 X lock을 하나씩 획득 시도한다.

 

 실시간 서비스가 각 row를 짧게 잡고 빨리 커밋한다면 개별 락 대기는 40초에 못 미친다. 그러나 5000개 row를 처리하는 동안 짧은 대기가 수십~수백 번 누적되면 statement 전체 실행 시간이 max_statement_time을 초과한다.

 

PK 기반 DELETE:
    row 1:   락 획득 (즉시)
    row 2:   2초 대기 → 획득
    row 3:   락 획득 (즉시)
    ...
    row 847: 5초 대기 → 획득
    ...
    → 개별 대기는 40초 미만
    → 누적 실행 시간이 max_statement_time 초과
    → Query execution was interrupted (max_statement_time exceeded)

 

 갭 락 제거로 빠른 단일 블로킹은 사라졌지만, Dirty Read row들에 대한 반복 락 시도가 statement 실행 시간 자체를 길게 늘린 것이다.

 

 

 

2.  updated_at < baseTime 조건 추가

 배치 시작 시점을 baseTime으로 고정하고, SELECT 시 해당 시각 이전 데이터만 조회한다.

<select id="selectProductIds" parameterType="map" resultType="long">
    SELECT id FROM products
    WHERE user_id = #{userId}
    AND updated_at &lt; #{baseTime}
    LIMIT 5000
</select>

 

String baseTime = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
Map<String, Object> param = new HashMap<>();
param.put("userId", userId);
param.put("baseTime", baseTime);

List<Long> ids;
do {
    ids = sqlSession.selectList("productMapper.selectProductIds", param);
    if (!ids.isEmpty()) {
        sqlSession.delete("productMapper.deleteProductsBatch", ids);
    }
} while (!ids.isEmpty());

 

 

 

Dirty Read (미커밋 신규 row) → 해소

 신규 INSERT row의 updated_at은 baseTime 이후다. Dirty Read로 읽혀도 updated_at < baseTime 조건에서 걸러지므로 DELETE 대상에 포함되지 않는다.

 

 

 

UPDATE 경로 X lock (기존 row) → 완전 해소 불가

 기존 row는 updated_at < baseTime이므로 SELECT에 포함된다. SELECT와 DELETE 사이 타이밍에 실시간 서비스가 같은 row의 X lock을 획득하면 충돌이 발생한다.

 

 baseTime 필터는 이러한 타이밍 경합을 차단하지 못한다. Dirty Read 케이스는 '읽히면 안 될 row를 읽는' 문제였지만, 이 케이스는 '읽혀야 할 row인데 DELETE 직전에 락이 걸린' 타이밍 문제이기 때문이다.

 

 아직 이 케이스에서 문제가 발생하지 않은 것은 SELECT → DELETE 사이 틈이 수 밀리초로 매우 짧고, INSERT보다 UPDATE 경로가 적게 발생하기 때문으로 보인다. 하지만 트래픽이 늘거나 배치 대상과 실시간 서비스가 겹치는 user_id가 많아지면 언제든 문제가 생길 수 있다.

 

 

 

 

 

---

마치며

 이번 트러블슈팅의 핵심은 하나의 타임아웃이 아니라 세 가지 락 충돌 경로가 동시에 작동했다는 점이다.

 

 갭 락은 PK 기반 WHERE id IN (...) 으로 전환해 완전히 제거했고, Dirty Read는 updated_at < baseTime 필터로 DELETE 시도 자체를 막았다. UPDATE 경로 X lock 충돌만이 남는다. 격리 수준 변경, 실행 시간 분리 등 여러 방법이 있지만 현재 시스템 환경에서는 적용할 수 없어 이 기능은 여기서 마무리한다.