2026.09.06·2분·#backend

ON DUPLICATE KEY UPDATE 로 신규/중복을 구분했는데 항상 신규로 읽혔다

Connector/J 의 useAffectedRows 기본값이 false 라, 아무것도 변경하지 않은 UPDATE 도 1 을 반환합니다

backend · cover image

외부 시스템에서 수신한 메시지를 저장하는 에이전트를 개발하면서, 같은 메시지가 다시 수신돼도 한 번만 적재되게 해야 했습니다. INSERT ... ON DUPLICATE KEY UPDATE 의 반환값으로 신규와 중복을 구분하는 방식을 사용했습니다.

@Modifying
@Query(value = """
        INSERT INTO messages (message_id, msg_type, ...)
        VALUES (:messageId, :msgType, ...)
        ON DUPLICATE KEY UPDATE id = id
        """, nativeQuery = true)
int insertIgnoringDuplicate(...);   // 의도: 1 = 신규, 0 = 이미 있던 행

그런데 이미 적재된 메시지가 재전송돼도 반환값이 계속 1 이었습니다. 판별 결과가 항상 "신규" 이니 뒤이어 자식 테이블(첨부)을 다시 INSERT 하다 UNIQUE 위반 예외가 발생했고, 그 예외 때문에 수신 확인을 전송하지 못했고, 확인을 수신하지 못한 상대 서버는 같은 메시지를 또 전송했습니다. 같은 메시지가 계속 재전송되고, 그 뒤에 누적된 메시지 전체가 이 한 건 때문에 처리되지 못했습니다.

로컬 테스트에서는 발견하지 못했습니다. Docker 를 사용할 수 없는 환경이라 Testcontainers 테스트가 생략됐고, 실제 DB 로 한 번 실행해 보고서야 문제가 확인됐습니다.

원인

MySQL 은 ODKU 의 affected rows 를 이렇게 반환합니다.

결과	affected rows
새로 INSERT	1
UPDATE 로 값이 변경됨	2
UPDATE 했지만 값이 그대로	0

ON DUPLICATE KEY UPDATE id = id 는 PK 를 자기 자신에 대입하는 관용구라 아무 컬럼도 변경하지 않습니다. 그래서 0 이 반환돼야 하고, 1 = 신규 / 0 = 중복 이 성립합니다.

문제는 Connector/J 의 useAffectedRows 기본값이 false 라는 점입니다. false 면 드라이버가 접속할 때 CLIENT_FOUND_ROWS 플래그를 활성화하고, 그러면 서버는 "변경된 행 수" 대신 "매칭된 행 수" 를 반환합니다. 아무것도 변경하지 않은 UPDATE 도 행을 발견하기는 했으므로 1 이 됩니다.

즉 신규와 중복이 둘 다 1 이 되어 판별식이 성립하지 않습니다. 예외나 경고 없이 반환값만 틀리게 나옵니다.

해결

JDBC URL 에 useAffectedRows=true 를 명시합니다.

spring.datasource.url=jdbc:mysql://${DB_HOST}:3306/app?useAffectedRows=true&serverTimezone=UTC

Testcontainers URL 에도 같은 값을 지정해야 합니다. 누락되면 테스트 환경이 운영과 다르게 동작해서, 이 결함이 있어도 테스트가 통과합니다.

new MySQLContainer<>("mysql:8.0")
        .withDatabaseName("app")
        .withInitScript("db/tables.sql")
        .withUrlParam("useAffectedRows", "true");

JPA `save()` 로 바꾸지 않은 이유

멱등 적재를 JPA 로 전환하는 방법을 먼저 검토했는데, 아래 세 가지 모두 사용할 수 없었습니다.

save() + 예외 캐치: 중복이면 DataIntegrityViolationException 이 발생하는데, 그 순간 Hibernate 세션은 폐기해야 하는 상태가 됩니다. 같은 트랜잭션에서 예외를 catch 해 "중복이니 정상" 으로 처리하면 커밋 시점에 UnexpectedRollbackException 이 발생합니다.
트랜잭션 분리: 부모(메시지)와 자식(첨부)이 한 커밋이어야 하는 조건을 충족할 수 없습니다.
미리 SELECT 해서 피하기: 대상 테이블 SELECT 권한이 필요해지고, 조회와 INSERT 사이 경쟁 조건도 남습니다.

ODKU + affected rows 는 예외 없이, SELECT 권한 없이, 한 트랜잭션 안에서 판별됩니다. 그래서 JPA 를 사용하면서 이 한 곳만 native 쿼리로 유지했습니다.

켜기 전에 확인할 것

useAffectedRows=true 는 커넥션 전역 동작을 변경합니다. 같은 데이터소스의 다른 UPDATE 도 "매칭된 행" 이 아니라 "변경된 행" 을 반환합니다.

int updated = repo.updateStatus(id, status);
if (updated == 0) throw new NotFoundException();   // 값이 그대로면 여기로 빠집니다

이렇게 "대상이 존재했는가" 를 반환값으로 판단하는 코드가 있으면, 값이 그대로인 UPDATE 를 대상이 없는 것으로 잘못 판단합니다. MyBatis 든 JPA 든 기존 코드가 많은 데이터소스에 나중에 추가할 설정은 아닙니다. 새 데이터소스에서 처음부터 활성화하는 쪽이 안전합니다.

그 밖에 확인한 내용입니다.

이 값을 변경한 뒤에도 테스트가 그대로 통과하면, 테스트가 이 경로를 검증하지 않고 있다는 뜻입니다. 멱등 판별에 의존하는 코드라면 "같은 입력 두 번" 케이스가 실제 MySQL 에서 실행되어야 합니다.
ON DUPLICATE KEY UPDATE 절에 updated_at = NOW() 같은 실제 변경을 추가하면 affected rows 가 2 가 되어 1 = 신규 판별식이 다시 틀립니다.
ODKU 는 중복일 때도 AUTO_INCREMENT 를 소비합니다. id 에 빈 번호가 발생하는 건 정상입니다.

#duplicate-key#idempotency#jdbc#mysql#testcontainers#troubleshooting

ON DUPLICATE KEY UPDATE 로 신규/중복을 구분했는데 항상 신규로 읽혔다

원인

해결

JPA save() 로 바꾸지 않은 이유

켜기 전에 확인할 것

JPA `save()` 로 바꾸지 않은 이유