사용자의 웹 활동 기록을 저장하려면 먼저 URL에서 웹사이트와 페이지를 찾아야 합니다.
이미 존재하면 기존 행을 사용하고, 처음 방문한 주소라면 새로 저장합니다.

초기 구현은 전형적인 조회 후 저장 패턴이었습니다.
핵심 흐름을 단순화하면 다음과 같습니다.

fun upsertWebsite(command: UpsertWebsiteCommand): Website {
    val website = websiteRepository.findByDomain(command.domain)
 
    if (website != null) {
        return website
    }
 
    return try {
        websiteRepository.save(
            Website(
                domain = command.domain,
                faviconUrl = command.faviconUrl
            )
        )
    } catch (exception: DataIntegrityViolationException) {
        websiteRepository.getByDomain(command.domain)
    }
}

CREATE UNIQUE INDEX uk_website_domain ON website (domain);

domain에는 유니크 인덱스가 있으므로 동시에 같은 인덱스로 INSERT가 수행되면 하나만 성공합니다.
나머지 요청은 유니크 제약조건 예외를 잡은 뒤 먼저 저장된 웹사이트를 다시 조회하면 될 거라 생각했는데요.
하지만 여러 사용자가 동시에 같은 신규 웹사이트를 처음 방문하는 부하 테스트에서 첫 요청을 제외한 다수의 요청이 500으로 실패했습니다.

재현 흐름

두 트랜잭션이 같은 도메인(example.com)을 처리한다고 가정하겠습니다.
참고로 현재 MySQL의 격리 수준은 REPEATABLE READ입니다.

두 요청의 첫 일반 SELECT 시점에는 행이 없었습니다.
트랜잭션 A가 행을 저장하고 커밋한 뒤 트랜잭션 B의 INSERT는 유니크 인덱스의 현재 상태를 검사하므로 중복을 감지합니다.

문제는 예외 이후 B가 수행한 일반 SELECT였습니다.
InnoDB의 일관된 읽기(Consistent Read)는 같은 트랜잭션에서 처음 만들어진 읽기 뷰(Read View)를 재사용합니다.
따라서 유니크 검사는 A가 커밋한 행을 알고 있지만, 일반 조회는 트랜잭션 시작 시점의 스냅샷을 보아 행을 찾지 못할 수 있습니다.

한 트랜잭션 안에서 다음 두 결과가 동시에 나타난 것입니다.

• INSERT: 이미 존재하는 domain이다.
• SELECT: 이 트랜잭션의 스냅샷에는 존재하지 않는다.

유니크 제약조건이나 예외 처리 자체가 잘못된 것이 아니라, 쓰기의 중복 검사와 일반 조회가 같은 가시성 규칙을 사용하지 않는 것이 원인이었습니다.

대안 1: 잠금 읽기

예외 이후 SELECT ... FOR UPDATE 같은 잠금 읽기를 사용하면 최신 커밋 데이터를 읽는 최신 읽기(current read)가 됩니다.
이를 이용해 먼저 커밋된 행을 다시 찾을 수 있습니다.

하지만 사전 조회부터 FOR UPDATE로 바꾸는 방식은 선택하지 않았습니다.

SELECT *
FROM website
WHERE domain = :domain
FOR UPDATE;

존재하는 행에는 레코드 락이 걸리고, 존재하지 않는 키를 조회할 때는 격리 수준과 검색 조건에 따라 갭 락(gap lock)이나 넥스트 키 락(next-key lock)이 생길 수 있습니다.
웹사이트가 아직 없다는 사실을 잠그기 위해 인접한 인덱스 범위까지 경쟁하게 만들 수 있고, 결국 INSERT 전에 조회와 락이라는 단계도 그대로 남습니다.

필요한 동작은 “없으면 넣고, 있으면 안전하게 기존 행을 사용한다”였습니다.
이를 애플리케이션의 조회-분기-저장으로 조립하는 대신 데이터베이스의 단일 원자적 업데이트 문으로 표현하는 편이 더 직접적이었습니다.

대안 2: ON DUPLICATE KEY UPDATE

웹사이트 저장을 다음 쿼리로 변경했습니다.

INSERT INTO website (id, domain, favicon_url)
VALUES (:id, :domain, :faviconUrl)
ON DUPLICATE KEY UPDATE
    favicon_url = COALESCE(website.favicon_url, VALUES(favicon_url));

현재 구현에서는 jOOQ를 사용했습니다.

override fun upsertByDomain(website: Website): Boolean {
    val websiteId = website.id ?: createUuid()
 
    dsl
        .insertInto(WEBSITE)
        .columns(WEBSITE.ID, WEBSITE.DOMAIN, WEBSITE.FAVICON_URL)
        .values(websiteId, website.domain, website.faviconUrl)
        .onDuplicateKeyUpdate()
        .set(
            WEBSITE.FAVICON_URL,
            coalesce(WEBSITE.FAVICON_URL, website.faviconUrl)
        )
        .execute()
 
    val persistedId =
        dsl
            .select(WEBSITE.ID)
            .from(WEBSITE)
            .where(WEBSITE.DOMAIN.equal(website.domain))
            .fetchOne(WEBSITE.ID)
 
    return persistedId == websiteId
}

COALESCE(existing, incoming)은 기존 파비콘(favicon)이 있으면 보존하고, 기존 값이 NULL일 때만 새 값으로 채웁니다.
동시에 들어온 요청들이 서로 다른 파비콘을 보내더라도 이미 확보한 값을 불필요하게 덮어쓰지 않습니다.

이 쿼리도 내부적으로 유니크 인덱스 충돌을 직렬화하기 위한 락을 사용합니다.
차이는 애플리케이션이 “먼저 읽고 나중에 쓰는” 두 단계를 경쟁시키는 것이 아니라, 충돌 감지와 INSERT/UPDATE 선택을 하나의 DB 문장 안에서 수행한다는 점입니다.

트랜잭션 경계 분리

업서트만 적용하고 recordHistory() 전체를 하나의 트랜잭션으로 유지하면 이후 조회가 외부 트랜잭션의 기존 읽기 뷰를 재사용할 여지가 남습니다.
또 웹사이트, 페이지, 중복 기록 검사, 기록 저장을 모두 한 트랜잭션으로 묶으면 앞 단계에서 얻은 락과 커넥션을 전체 흐름이 끝날 때까지 보유합니다.

그래서 기록 생성 전체에는 트랜잭션을 열지 않고, 웹사이트와 페이지 저장처럼 원자성이 필요한 작업에 각각 트랜잭션 경계를 두었습니다.

실제 HistoryService.recordHistory()는 외부 트랜잭션을 만들지 않고, WebsiteService.upsertWebsite()와 PageService.upsertPage()처럼 필요한 저장 작업이 각자 트랜잭션을 갖게 했습니다.

@Transactional(propagation = Propagation.NOT_SUPPORTED)
fun recordHistory(userId: UUID, command: RecordHistoryCommand): RecordHistoryResult {
    val website =
        websiteService.upsertWebsite(
            UpsertWebsiteCommand(
                domain = domain,
                faviconUrl = command.faviconUrl
            )
        )
    val page =
        pageService.upsertPage(
            UpsertPageCommand(
                websiteId = website.id!!,
                url = command.url
            )
        )
    ...
}

WebsiteService.upsertWebsite()의 트랜잭션이 끝난 뒤 반환되므로, 이어지는 작업은 웹사이트 저장 전에 만들어진 외부 트랜잭션의 스냅샷을 공유하지 않습니다.
또한 웹사이트 저장 중 획득한 락은 해당 트랜잭션이 끝날 때 해제됩니다.

이 구조는 기록 생성 전체의 원자성을 포기하는 선택이기도 합니다.
웹사이트 저장 후 기록 저장이 실패하면 사용되지 않은 웹사이트 행이 남을 수 있습니다.
하지만 웹사이트와 페이지는 여러 사용자 기록이 공유하는 독립 애그리거트이며, 기록이 없는 웹사이트가 잠시 남는 것이 비즈니스 정합성을 깨뜨리지 않는다고 판단했습니다.

왜 업서트 이후에도 SELECT가 필요한가

ON DUPLICATE KEY UPDATE는 저장과 충돌 처리를 원자적으로 수행하지만, 현재 구현에는 최종 웹사이트 ID와 신규 등록 여부가 필요합니다.

• 신규 웹사이트라면 생성한 UUID가 저장됩니다.
• 기존 웹사이트라면 이미 저장된 UUID를 사용해야 합니다.
• 신규 웹사이트일 때만 카테고리 분류 아웃박스를 생성해야 합니다.

따라서 업서트 이후 domain으로 ID를 조회하고, 미리 생성한 ID와 비교해 INSERT 여부를 판별합니다.
이 SELECT는 사전 조회가 아니라 쓰기 충돌이 정리된 뒤 최종 상태를 읽는 조회입니다.

“업서트도 결국 조회 후 저장 아닌가?”라는 질문에 대한 차이도 여기에 있습니다.
애플리케이션의 조회 후 저장은 두 SQL 사이에 다른 트랜잭션이 끼어들 수 있지만, ON DUPLICATE KEY UPDATE의 충돌 판단과 쓰기 분기는 하나의 SQL 실행 안에서 InnoDB가 처리합니다.

검증

같은 웹사이트와 페이지에 대해 100명의 사용자가 동시에 기록 생성 요청을 보내는 시나리오로 검증했습니다.

확인한 조건은 다음과 같습니다.

1. 웹사이트 행이 한 개만 생성된다.
2. 페이지 행이 한 개만 생성된다.
3. 모든 사용자의 기록은 정상 저장된다.
4. 웹사이트 분류 아웃박스도 한 개만 생성된다.
5. 유니크 제약조건 충돌이 500 응답으로 노출되지 않는다.

전체 recordHistory() 트랜잭션을 유지한 버전과 세부 트랜잭션으로 분리한 버전도 같은 업서트 조건에서 비교했습니다.
트랜잭션을 분리한 버전은 웹사이트와 페이지 저장의 락 보유 시간이 짧아져 100명 동시 요청의 P95 응답 시간이 943ms에서 667ms로 감소했습니다.

이 수치는 트랜잭션을 작게 만들면 항상 29% 빨라진다는 의미는 아닙니다.
같은 유니크 키에 쓰기 경합이 집중된 이 시나리오에서 긴 외부 트랜잭션이 락과 커넥션 보유 시간을 늘리고 있었다는 근거로 사용했습니다.

정리

이 문제를 단순히 “동시 INSERT에서 유니크 예외가 발생했다”로 보면 예외를 잡아 재조회하는 것으로 끝나기 쉽습니다.
실제 원인은 REPEATABLE READ에서 사전 조회로 만들어진 읽기 뷰와, 최신 인덱스 상태를 확인하는 INSERT의 가시성이 달랐던 점이었습니다.

해결은 두 부분으로 나뉩니다.

1. 조회 후 조건부 INSERT를 ON DUPLICATE KEY UPDATE 기반의 원자적 업데이트로 바꿨습니다.
2. 웹사이트 저장을 별도 트랜잭션으로 분리해 외부 트랜잭션의 오래된 스냅샷과 긴 락 보유를 제거했습니다.

락을 직접 넓게 잡아 모든 경쟁을 막기보다, 충돌이 실제로 발생하는 INSERT 시점에 데이터베이스가 유니크 키 단위로 조정하게 만든 선택이었습니다.