Mysql, MariaDB 그리고 MVCC의 쓰기 충돌 감지

진행하고 있는 사이트 프로젝트에서는 애플리케이션 로직을 통해 DB 데이터를 업데이트 하는 과정에서 동시성 문제가 발생하는 것을 방지하기 위해 Redis 분산락, DB 비관적락, Atomic Update를 사용하고 있습니다.

흔히 접할 수 있는 예시로 상품 재고 차감, A계좌에서 B계좌로의 이체 상황 등이 있습니다.

 fun registerCoupon(userId: Long, couponCode: String) {
    lockManager.executeWithLock(
        generateRegisterCouponKey(userId, couponCode)
    ) { userCouponRegister.registerCoupon(userId, couponCode) }
}

 

기존에는 대부분 위 코드처럼 Redis 분산락으로 처리하였으나 이 사이드 프로젝트가 추후에 분산 DB를 사용할 확률도 없고

코드가 복잡해지는 것 같아서 대부분 분산락에서 DB 비관적락으로 변경하고 있습니다.

다만 쿠폰 발급처럼 특정 컬럼의 데이터를 동시에 수정하는 것이 아닌 If not exists -> insert의 경우는 Redis 분산락을 통해

처리하고 있습니다.

문제 상황

@SqsListener("update-creator-sales-statistics.fifo")
fun updateCreatorStatistics(@Payload event: QuestionPaymentEvent) {
    val questions = questionRepository.getQuestionsByQuestionIds(event.questionPayment.order.questionIds)
    val countQuestionByCreator = questions
        .stream()
        .collect(Collectors.groupingBy(Question::creatorId, Collectors.counting()))

    countQuestionByCreator
        .forEach { (creatorId: Long, count: Long) ->
            creatorStatisticsRepository.addSalesCount(
                creatorId,
                count.toInt()
            )
        }
}

 

위 코드는 문제 결제 이벤트를 통한 후처리 작업 중 하나입니다. 크리에이터의 문제 판매 통계를 업데이트 해주는 로직입니다.

유저가 문제를 구매하면 (크리에이터: 문제 개수)로 그룹화해서 addSalesCount 메서드를 호출하여 판매 통계를 업데이트 합니다.

@Transactional
override fun addSalesCount(creatorId: Long, count: Int) {
    jpaQueryFactory.update(creatorStatisticsEntity)
        .set(
            creatorStatisticsEntity.salesCount,
            creatorStatisticsEntity.salesCount.add(count)
        )
        .where(creatorStatisticsEntity.creatorId.eq(creatorId))
        .execute()
}

// UPDATE creator_statistics 
// SET sales_count = sales_count + {count} 
// where creator_statistics.creator_id = {creatorId}

 

addSalesCount는 querydsl로 작성되었는데 위 querydsl 코드는 DB에서 Atomic Update SQL로 처리되게 됩니다.

Atomic Update라는 것은 기존 값을 조회 후 조회 된 값에서 + count를 해주는 것이 아닌

sales_count = sales_count + {count} 형태로 DB 작업 연산 자체에서 값을 참조해서 참조 값에 count가 더해집니다.

특정 레코드에 대한 update 연산은 순차적으로 하나의 트랜잭션씩 처리되기 때문에 동시성 문제는 발생하지 않게 됩니다.

판매 통계의 경우 단순히 판매 수량 만큼 기존 값에서 +를 해주면 되기 때문에 Atomic Update 처리하였습니다.

따라서 기존에는 동시성 문제를 위와 같은 방식으로 방지하고 있었습니다.

 

그런데 우연히 문제 구매 이벤트 발행을 비동기 방식으로 변경하면서 부하 테스트를 주고 있었는데 갑자기 위 부분에서 오류가

발생하였습니다.

Caused by: org.hibernate.exception.GenericJDBCException: JDBC exception executing SQL [update creator_statistics cse1_0 set sales_count=(cse1_0.sales_count+?) where cse1_0.creator_id=?] [(conn=683) Record has changed since last read in table 'creator_statistics'] [n/a]
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:63)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:94)
	at org.hibernate.sql.exec.internal.StandardJdbcMutationExecutor.execute(StandardJdbcMutationExecutor.java:104)
	at org.hibernate.query.sqm.internal.SimpleUpdateQueryPlan.executeUpdate(SimpleUpdateQueryPlan.java:89)
	at org.hibernate.query.sqm.internal.QuerySqmImpl.doExecuteUpdate(QuerySqmImpl.java:666)
	at org.hibernate.query.sqm.internal.QuerySqmImpl.executeUpdate(QuerySqmImpl.java:639)
	at com.querydsl.jpa.impl.JPAUpdateClause.execute(JPAUpdateClause.java:76)
	at com.eager.questioncloud.core.domain.creator.infrastructure.repository.CreatorStatisticsRepositoryImpl.addSalesCount(CreatorStatisticsRepositoryImpl.kt:54)
	at jdk.internal.reflect.GeneratedMethodAccessor125.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:354)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:768)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:138)
	... 58 common frames omitted
    
Caused by: java.sql.SQLException: (conn=683) Record has changed since last read in table 'creator_statistics'
	at org.mariadb.jdbc.export.ExceptionFactory.createException(ExceptionFactory.java:306)
	at org.mariadb.jdbc.export.ExceptionFactory.create(ExceptionFactory.java:378)
	at org.mariadb.jdbc.message.ClientMessage.readPacket(ClientMessage.java:172)
	at org.mariadb.jdbc.client.impl.StandardClient.readPacket(StandardClient.java:915)
	at org.mariadb.jdbc.client.impl.StandardClient.readResults(StandardClient.java:854)
	at org.mariadb.jdbc.client.impl.StandardClient.readResponse(StandardClient.java:773)
	at org.mariadb.jdbc.client.impl.StandardClient.execute(StandardClient.java:697)
	at org.mariadb.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:93)
	at org.mariadb.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:342)
	at org.mariadb.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:319)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
	at com.p6spy.engine.wrapper.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:94)
	at org.hibernate.sql.exec.internal.StandardJdbcMutationExecutor.execute(StandardJdbcMutationExecutor.java:90)
	... 71 common frames omitted

 

예외 원인은 "Record has changed since last read in table"으로 요약하자면

creator_statistics를 업데이트 하려했지만 다른 트랜잭션에서 먼저 값을 수정했기에 롤백되었다는 뜻입니다.

아무래도 InnoDB MVCC에 의해 발생한 오류인 것으로 예상했습니다.

MVCC

Mysql, MariaDB는 별도의 스토리지 엔진을 변경하지 않으면 InnoDB엔진이 사용되게 됩니다.

저는 이번 프로젝트에서 MariaDB를 사용하고 있었습니다.

 

InnoDB엔진은 트랜잭션간 동시성 문제, 트랜잭션 격리 수준, 트랜젝션 롤백 처리 등을 쉽게 처리하기 위해 MVCC를 이용합니다.

MVCC는 다중 버전 동시성 제어의 줄임말인데, 핵심은 트랜잭션이 시작되면 작업을 시작하는 시점의 스냅샷을 보관하여 해당 시점으로
롤백을 할 수도 있고, 다른 트랜잭션이 값을 바꾸어도 스냅샷의 데이터를 참조하여 일관된 읽기를 처리할 수 있습니다.

 

MySQL :: MySQL 8.4 Reference Manual :: 17.3 InnoDB Multi-Versioning

 

MVCC (Multi-Version Concurrency Control) with MariaDB Xpand — MariaDB Documentation

MVCC는 분량이 많은 내용이라 공식문서를 참조하셔도 되고 다른 분들이 잘 정리해주셨으니 MVCC 관련 포스팅을 읽어보시는 것도
좋다고 생각합니다.

MVCC 트랜잭션 격리 수준 - REPEATABLE READ

MVCC는 트랜잭션 격리 수준에 따라 유연한? DB 작업을 처리할 수 있습니다.

Mysql, MariaDB는 InnoDB를 사용한다고 가정할 때 별도로 격리 수준을 변경하지 않으면 REPEATABLE READ를 사용하게 됩니다.

REPEATABLE READ의 경우 Mysql, MariaDB + InnoDB 구성에서는 팬텀리드, 더티리드가 발생하지 않습니다.

 

 

Transaction A에서 문제 ID가 428인 리뷰를 조회하였을 때 1개의 리뷰만 조회되었습니다.

이후 Trasaction B에서 문제 ID가 428인 새로운 리뷰를 작성하였습니다.

 

실제 DB 상에도 리뷰가 정상적으로 저장되었습니다.

 

하지만 여전히 Transaction A에서는 1개의 리뷰만 보여집니다.

이러한 것은 Transacion 격리 수준 REPEATABLE READ에 의한 MVCC 동작이기 때문입니다.

 

이처럼 다른 트랜잭션에서 새로운 값을 추가하여도, 혹은 기존 값을 업데이트 하여도 실제 테이블에서 데이터를 조회하는 것이 아닌

트랜잭션이 작업을 시작한 시점에 생성한 스냅샷으로부터 데이터를 조회합니다.

 

따라서 위 상황의 경우도 Transaction A가 ID가 1인 리뷰를 읽었을 때에는 평점이 2점이지만

Transaction B에서 평점을 1로 업데이트하더라도 Transacion A에서는 여전히 평점이 2점으로 조회되게 됩니다.

REPEATABLE READ - 갱신 손실

REPEATABLE READ 격리 수준의 경우 팬텀리드, 더티리드를 방지해준다는 장점이 있지만 Lock 없이 동시에 데이터를 수정하려는 경우 갱신 손실 문제가 발생할 수 있습니다.

 

 

약간 억지스러운 예시긴 하지만 위 상황의 경우 갱신 손실 문제가 발생합니다.

1. Transaction A에서 userId가 1인 유저의 포인트를 조회합니다. 또한 현재 시점의 스냅샷을 보관하게 됩니다.

    (REPEATABLE READ의 경우 트랜잭션의 첫번째 조회에서 스냅샷을 보관하게 됩니다.)

2. Transaction B에서 questionId가 1인 문제를 조회합니다. 트랜잭션 첫 조회이므로 현재 시점의 스냅샷을 보관하게 됩니다.

 

3. Transaction A에서 user_point를 500 포인트 사용처리 (감소)합니다.

 

4. Transaction B에서 userId가 1인 유저의 포인트를 조회합니다. 하지만 스냅샷으로 인해 1000포인트로 조회 됩니다.

 

5. Transaction B에서 user_point를 500포인트 충전처리하는데 스냅샷으로 인해 1000포인트로 조회되어 1500포인트로 변경 합니다.

 

REPEATABLE READ로 인해 Transaction A의 변경 사항은 손실되고 마지막 커밋을 한 Transaction B의 변경사항으로
적용되었습니다. 이와 같은 문제를 갱신 손실이라고 표현합니다.

 

많은 포스팅에서 InnoDB의 경우 갱신 손실 문제를 처리해주지 않기 때문에 Lock을 사용하거나 등등.. 추가적인 처리를

통해 갱신 손실 문제를 방지해야한다고 언급하기도 합니다.

MariaDB는 갱신 손실을 감지한다

사실 Record has changed since last read in table 오류 자체는 이전에도 몇번 만나보긴 했는데 전부 동시성 문제여서

어차피 해결해야 할 문제라 그러려니 하고 넘어갔었는데요. (그리고 Mysql도 원래 되는건지 알았습니다. ㄷㄷ)

 

[MDEV-35124] Set innodb_snapshot_isolation=ON by default - Jira

 

이번 오류 때문에 관련 내용을 찾아보다가 MariaDB의 jira 티켓을 보게 되었습니다.

처음부터 기본 InnoDB 트랜잭션 격리 수준인 REPEATABLE READ는 기존 정의에 부합하지 않았습니다. 가장 큰 문제는 쓰기/쓰기 충돌 감지 기능이 없다는 것입니다. 이 문제를 해결하고 REPEATABLE READ를 스냅샷 격리와 일치 시키기 위해 MariaDB Server 10.6.18, 10.11.8, 11.4.2에 부울 세션 변수 innodb_snapshot_isolation을 도입했습니다 . 이 변수는 사용자 애플리케이션의 작동을 방해하지 않도록 기본적으로 비활성화되어 있습니다.

 

MariaDB 개발진은 기존 InnoDB REPEATABLE READ은 갱신 손실과 같은 쓰기 충돌을 감지하지 못하는 것을 해결하기 위해

쓰기 작업 시 스냅샷 상태를 판단하도록 수정한 것 같습니다.

 

그리고 11.4.2 버전부터는 이 쓰기 충돌 감지를 Default로 On 되도록 수정되었다고 합니다.

Record has changed since last read in table 이 오류는 결국 MariaDB의 쓰기 충돌 감지로 인해 발생하는 오류였습니다.
제가 사용하고 있는 MariaDB 버전 역시 11.4.2임을 확인했습니다.

 

따라서 MariaDB는 Mysql과 달리 쓰기 충돌 감지를 한다고, 갱신 손실을 방지해준다고 보면 되겠습니다.

(Postgres과 유사한 방식이라고 합니다.)

문제 원인

@Around("@annotation(io.awspring.cloud.sqs.annotation.SqsListener)")
fun processingEventIdempotency(joinPoint: ProceedingJoinPoint) {
    val event = joinPoint.args.first { it is SQSEvent } as SQSEvent
    val idempotentKey = event.eventId + "-" + joinPoint.signature.name

    if (eventProcessLogRepository.existsByIdempotentKey(idempotentKey)) {
        return
    }

    transactionTemplate.execute {
        joinPoint.proceed()
        eventProcessLogRepository.save(EventProcessLog.create(idempotentKey))
    }
}

 

최근에 이벤트가 한번만 처리되는 것을 보장하기 위해서 멱등성 테이블 이용하는 로직을 AOP로 처리하였습니다.

또한 멱등성을 보장하도록 transactionTemplate로 이벤트 처리 로직과 멱등성 키 저장 로직을 하나의 트랜잭션으로 처리했습니다.

결론은 트랜잭션으로 묶인 것이 문제였습니다.

@SqsListener("update-creator-sales-statistics.fifo")
fun updateCreatorStatistics(@Payload event: QuestionPaymentEvent) {
    val questions = questionRepository.getQuestionsByQuestionIds(event.questionPayment.order.questionIds)
    val countQuestionByCreator = questions
        .stream()
        .collect(Collectors.groupingBy(Question::creatorId, Collectors.counting()))

    countQuestionByCreator
        .forEach { (creatorId: Long, count: Long) ->
            creatorStatisticsRepository.addSalesCount(
                creatorId,
                count.toInt()
            )
        }
}

 

결국 위 updateCreatorStatistics가 AOP 트랜잭션을 이어서 사용하게 된다는 것인데 따라서 위 로직은 이미 트랜잭션 내부에서

진행되고 있다는 뜻입니다.

 

그런데 questionRepository.getQuestionByQuestionIds 메서드는 DB로부터 데이터를 Select 하는 부분입니다.

이 부분이 트랜잭션 내에서 첫 조회 로직이기에 이 시점의 스냅샷을 저장하게 됩니다.

 

만약 트랜잭션 A와 트랜잭션 B가 거의 동일한 시점에 updateCreatorStatistics를 진입하였다면 두 트랜잭션 모두

동일한 상태의 스냅샷을 저장하게 될텐데요.

@Transactional
override fun addSalesCount(creatorId: Long, count: Int) {
    jpaQueryFactory.update(creatorStatisticsEntity)
        .set(
            creatorStatisticsEntity.salesCount,
            creatorStatisticsEntity.salesCount.add(count)
        )
        .where(creatorStatisticsEntity.creatorId.eq(creatorId))
        .execute()
}

 

만약 트랜잭션 A가 먼저 위 addSalesCount를 처리한 후 커밋하게 되면 트랜잭션 A에 의해 실제 salesCount가 변경되었을 겁니다.

 

이후 트랜잭션 B가 addSalesCount를 처리한 후 커밋하게 되면 트랜잭션 B는 먼저 커밋 된 트랜잭션 A로 인해 데이터 버전 불일치가 감지되어 쓰기 충돌 감지가 수행되고 Record has changed since last read in table가 발생하게 됩니다.

해결 방안

Lock을 사용하는 방법도 있고 그렇지만 저의 경우 Atomic Update이기 때문에 동시성 문제는 발생하지 않는다는 게 확신됩니다.

따라서 MariaDB의 쓰기 충돌 감지를 off 하였습니다.

 

콘솔창에서 set global innodb_snapshot_isolation = 0;를 입력해도 되고 conf 파일을 수정해도 될 것 같습니다.

 

위와 같이 global 변수를 변경해주면 쓰기 충돌 감지 기능이 off되어 더 이상 Record has changed since last read in table 오류는 발생하지 않았습니다.