ON DUPLICATE KEY UPDATE 로 신규/중복을 갈랐는데 항상 신규로 읽혔다
Connector/J 의 useAffectedRows 기본값이 false 라, 아무것도 안 바꾼 UPDATE 도 1 을 돌려줍니다
외부 시스템에서 받은 메시지를 저장하는 에이전트를 만들면서, 같은 메시지가 다시 들어와도 한 번만 적재되게 해야 했습니다. 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 에 명시합니다.
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 세션은 폐기해야 하는 상태가 됩니다. 같은 트랜잭션에서 잡아 "중복이니 정상" 으로 넘기면 커밋 시점에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(); // 값이 그대로면 여기로 빠집니다
이렇게 "대상이 존재했는가" 를 반환값으로 판단하는 코드가 있으면 오판합니다. MyBatis 든 JPA 든 기존 코드가 많은 데이터소스에 나중에 끼워 넣을 값은 아닙니다. 새 데이터소스에서 처음부터 켜는 쪽이 안전합니다.
그 밖에 걸린 것들입니다.
- 이 값을 바꾼 뒤 테스트가 그대로 통과하면, 테스트가 이 경로를 안 보고 있다는 뜻입니다. 멱등 판별에 의존하는 코드라면 "같은 입력 두 번" 케이스가 실제 MySQL 에 대고 돌아야 합니다.
ON DUPLICATE KEY UPDATE절에updated_at = NOW()같은 실제 변경을 넣으면 affected rows 가2가 되어1 = 신규판별식이 다시 틀립니다.- ODKU 는 중복일 때도 AUTO_INCREMENT 를 소비합니다.
id에 구멍이 생기는 건 정상입니다.