낙관적 동시성 제어를 적용하면서 마주친 문제: 재고수량 비교

👀 의문점

❓ 원인 추론 & 시도

1) 일단 Repeatable Read로 시나리오 짜보고 테스트

2) 책, 자료 찾아보기

3) 헷갈렸던 부분2

🔜 나가며

더 알아볼 것

Ref

아래는 제 삽질 과정을 기록한 글이고 틀린 내용이 포함되어있을 수 있습니다…!

👀 의문점

낙관적 동시성 제어를 적용하면서 마주친 문제들: 타임스탬프 삽질

결론문제 정의분석&시도시도1) 안 되는 이유를 나름 분석해봄 ㅋ..시도2) 3) 위 글을 통해잘못된 개념 바로잡고🔍 원인✅ 해결방법 결론문제 정의처음에 동시성 문제를 해결하려고 하였을 때, 처음에 가정을 Isolation Level이 Repeatable Read로 하면 안 된다는 잘못된 개념을 가지고 있었어서 Read Committed로 내려야 되는 줄 알고 얘를 위해 Read Committed로 내리는 건 말이 안 되는 것 같아 비관적 동시성 제어로 해결하는 방법을 선택하였는데 ㅎ,,잘못된 지식으로 잘못된 판단을 내리고 있었다 ㅠㅠㅠ JPA를 사용한다면 Version으로 깔끔하게 구현이 가능하던데, 나는 Spring + MyBatis여서 쿼리문에서 CAS(compare-and-set) 방식으로 낙관적 ..

https://beppp.tistory.com/127

위 게시글에서 정의했던 이상한 가정으로 낙관적 동시성 제어를 적용하는 과정에서 굉장히 삽질을 하고 있었다.

일반적으로 동시성 문제를 해결하는 방법은 크게 두 가지다.

1. 비관적 동시성 제어(Pessimistic Concurrency Control)

2. 낙관적 동시성 제어(Optimistic Concurrency Control)

여러 책에서는 낙관적 동시성 제어를 타임스탬프를 활용하는 방법으로 소개하고 있었다. 하지만 실제로 적용해 보았는데 동시성 문제가 해결되지 않았고, 이유를 분석하려다 오히려 문제 해결과는 점점 멀어지는 이상한 가정을 하게 되었다.

구글링을 통해 다양한 해결책을 찾아봤지만, 대부분 JPA의 @Version으로 해결하고 있었고 MyBatis 사용 사례는 찾기 어려웠다. 그러다 찾다찾다 드디어 우연히 한 블로그에서 JPA가 아니라 내가 책에서 본 쿼리를 사용해 동시성 문제를 해결한 글을 발견하게되었다..!

[DB] 동시성 문제 해결방법

동시성 문제가 무엇이고 해결방법에 대하여 알아보도록 한다.

https://chrisjune-13837.medium.com/db-동시성-문제-해결방법-f5e52e2e3

나는 이미 잘못된 가정에 깊이 빠져 있어서 "저 방법으로 해결될 리가 없다"고 생각하며 반신반의하며 속는셈치고 시도를 해보았다.

재고수량 감소쿼리에 INV_QTY >= #{qty} 조건을 추가해 실제 재고 수량이 충분할 때만 업데이트하도록 했다. 변경 후 격리 수준에 상관없이 Repeatable Read와 Read Committed 모두 문제없이 작동하는 것을 확인할 수 있었다. 이 결과는 내가 세운 가정과 전혀 달랐고.. 예상치 못한 결과였다. 내 예상으로는 -1로 동시성 문제가 해결이 안 되었어야 했는데 실제로는 0으로 동시성 문제가 해결되었다..

기존

<update id="decreaseProdQty" parameterType="Map">
    UPDATE prod_opt
    SET INV_QTY = INV_QTY - #{qty}
      , UPD_DTTM = now()
    WHERE OPT_COMB_NO = #{optCombNo}
    AND UPD_DTTM = #{updDttm}
</update>

변경

<update id="decreaseProdQty" parameterType="Map">
    UPDATE prod_opt
    SET INV_QTY = INV_QTY - #{qty}
      , UPD_DTTM = now()
    WHERE OPT_COMB_NO = #{optCombNo}
    AND INV_QTY >= #{qty}
</update>

❓ 원인 추론 & 시도

1) 일단 Repeatable Read로 시나리오 짜보고 테스트

문제상황: 재고수량 검증 로직이 있음에도 불구하고 재고가 1개 남은 상품을 두 사용자가 동시에 주문했을 때 재고수량이 마이너스가 되는 현상

위와 같이 쿼리를 변경했을 때 내가 예상한 시나리오는 다음과 같다. 이 시나리오대로라면 MVCC를 사용하기 때문에 낙관적 동시성 제어가 제대로 적용되지 않아 결과가 -1이 되어야 한다. 그런데 왜 통과되는 것일까?

시간 순서	트랜잭션 1 상태	트랜잭션 2 상태
1	<재고수량 검증을 위한 읽기> 재고수량: 1개
2	<재고수량 검증> 구매하려는 개수: 1개 재고수량: 1개 ⇒ 통과	<재고수량 검증을 위한 읽기> 재고수량: 1개
3	<재고수량 감소> decreaseProdQty를 실행 INV_QTY(1) >= #{qty}(1) ⇒ 참이라 재고수량이 감소된다. 재고수량: 0개	<재고수량 검증> 구매하려는 개수: 1개 재고수량: 1개 ⇒ 통과
4	그 뒤로 주문 로직 생성	<재고수량 감소> decreaseProdQty를 실행 트랜잭션 1에서 재고수량을 변경시켰지만 isolation이 Repeatable Read이므로 트랜잭션 시작 시의 스냅샷을 기준으로 일관된 데이터를 읽어오기 때문에 INV_QTY(1) >= #{qty}(1) ⇒ 참이라 트랜잭션2도 마찬가지로 재고수량이 감소된다. 재고수량: -1개
5	커밋	그 뒤로 주문 로직 생성
6	결과는 재고수량 0	커밋
7		결과는 재고수량 -1

2) 책, 자료 찾아보기

MVCC에 대해 대충 알고 있었어서 update 시에는 Consistent 모드가 아닌 Current 모드로 값을 수정한다는 것을 알고 있었지만, where 절에 해당하는 데이터를 읽어올 때도 Current 모드로 읽어와서 값을 비교한 다음 조건이 만족하면 update를 한다는 사실을 몰랐다.

나는 데이터를 업데이트할 때만 Current 모드로 업데이트하고, where 절의 데이터는 Consistent 모드로 읽어오는 줄 알았다.

AND INV_QTY >= #{qty} 조건은 쿼리 실행 시점의 현재 데이터 상태를 반영하여 평가된다. 이는 UPDATE 작업이 Current 모드로 데이터를 읽기 때문이다. MVCC는 데이터 변경 시 데이터의 일관성을 유지하도록 설계된 방식이다. 데이터 변경 작업은 최신의 데이터 상태를 반영해야 하며, 이를 통해 데이터의 무결성과 일관성을 보장한다.

결과론적으로보면 where 절에 명시된 조건을 평가할 때 Current 모드로 읽어와야 위 결과가 납득이 되는데, 이후에 ‘친절한 SQL 튜닝’책을 찾아보니 update 문에서 where 절에 명시된 조건을 평가할 때 스냅샷 기준으로 데이터를 읽는 것처럼 설명하는데, 뭐가 맞는 건지 혼란스럽다.. 이 부분은 더 조사가 필요하다.

시간 순서	트랜잭션 1 상태	트랜잭션 2 상태
1	<재고수량 검증을 위한 읽기> 재고수량: 1개
2	<재고수량 검증> 구매하려는 개수: 1개 재고수량: 1개 ⇒ 통과	<재고수량 검증을 위한 읽기> 재고수량: 1개
3	<재고수량 감소> decreaseProdQty를 실행 INV_QTY(1) >= #{qty}(1) ⇒ 참이라 재고수량이 감소된다. 재고수량: 0개	<재고수량 검증> 구매하려는 개수: 1개 재고수량: 1개 ⇒ 통과
4	그 뒤로 주문 로직 생성	<재고수량 감소> decreaseProdQty를 실행 트랜잭션 1에서 재고수량을 변경시켜 0이되었다. isolation이 Repeatable Read이므로 트랜잭션 시작 시의 스냅샷을 기준으로 일관된 데이터를 읽어오지만, update므로 Consistent가 아닌 Current 모드로 값을 읽어오기 때문에 스냅샷에서 읽어온 값인 1이 아닌 최근 블록에서 읽어온 데이터. 0을 읽어와 조건 충족이 안 되어 update 수행X INV_QTY(0) >= #{qty}(1) 재고수량: 0개
5	커밋	그 뒤로 주문 로직 생성
6	결과는 재고수량 0	커밋
7		결과는 재고수량 0

3) 헷갈렸던 부분2

시간 순서	트랜잭션 1 상태	트랜잭션 2 상태
1	<재고수량 검증을 위한 읽기> 재고수량: 1개
2	<재고수량 검증> 구매하려는 개수: 1개 재고수량: 1개 ⇒ 통과	<재고수량 검증을 위한 읽기> 재고수량: 1개
3	<재고수량 감소> decreaseProdQty를 실행 INV_QTY(1) >= #{qty}(1) ⇒ 참이라 재고수량이 감소된다. 재고수량: 0개	<재고수량 검증> 구매하려는 개수: 1개 재고수량: 1개 ⇒ 통과
4	그 뒤로 주문 로직 생성	<재고수량 감소> decreaseProdQty를 실행 트랜잭션 1에서 재고수량을 변경시켜 0이되었다. isolation이 Repeatable Read이므로 트랜잭션 시작 시의 스냅샷을 기준으로 일관된 데이터를 읽어오지만, update므로 Consistent가 아닌 Current 모드로 값을 읽어오기 때문에 스냅샷에서 읽어온 값인 1이 아닌 최근 블록에서 읽어온 데이터. 0을 읽어와 조건 충족이 안 되어 update 수행X INV_QTY(0) >= #{qty}(1) 재고수량: 0개
5	커밋	그 뒤로 주문 로직 생성
6	결과는 재고수량 0	커밋
7		결과는 재고수량 0

여기서 내가 잘못 이해했던 부분이 하나 또 있었다. 트랜잭션 1이 시간 3번에서 재고 수량을 감소시킨 후, 트랜잭션2의 시간순서 4부분에서 바로 또 데이터가 변경이 가능할 줄 알았는데 이부분도 틀렸다.

트랜잭션1이 update를 하려고 하면 해당 row에 대한 X-Lock(배타락)을 얻어오고, 이 락은 트랜잭션이 종료되어야 해제된다. 그러므로 트랜잭션2는 트랜잭션1이 커밋할 때까지 대기해야 한다. DB가 락을 사용하여 데이터의 일관성과 격리를 유지하기 때문이다.

시간 순서	트랜잭션 1 상태	트랜잭션 2 상태
1	<재고수량 검증을 위한 읽기> 재고수량: 1개
2	<재고수량 검증> 구매하려는 개수: 1개 재고수량: 1개 ⇒ 통과	<재고수량 검증을 위한 읽기> 재고수량: 1개
3	<재고수량 감소> decreaseProdQty를 실행 INV_QTY(1) >= #{qty}(1) ⇒ 참이라 재고수량이 감소된다. 재고수량: 0개	<재고수량 검증> 구매하려는 개수: 1개 재고수량: 1개 ⇒ 통과
4	그 뒤로 주문 로직 생성	대기
5	커밋	대기
6	결과는 재고수량 0	<재고수량 감소> decreaseProdQty를 실행 트랜잭션 1에서 재고수량을 변경시켜 0이되었다. isolation이 Repeatable Read이므로 트랜잭션 시작 시의 스냅샷을 기준으로 일관된 데이터를 읽어오지만, update므로 Consistent가 아닌 Current 모드로 값을 읽어오기 때문에 스냅샷에서 읽어온 값인 1이 아닌 최근 블록에서 읽어온 데이터. 0을 읽어와 조건 충족이 안 되어 update 수행X INV_QTY(0) >= #{qty}(1) 재고수량: 0개
7		그 뒤로 주문 로직 생성
8		커밋
9		결과는 재고수량 0

어쩐지 두 개의 쓰레드가 메서드를 실행할 때마다 쓰레드의 이름을 출력하도록 해뒀었는데, update를 실행하기 전까지는 두 개의 쓰레드가 거의 동시에 로그가 찍혔으나, 한 쓰레드가 update를 수행하자 두 트랜잭션 사이의 약간 시간 간격이 생겼었다. 이런 현상이 약간 의아했었는데 업데이트 시 X-Lock을 걸고, 그 Lock은 트랜잭션이 커밋될 때까지 지속되기 때문에 트랜잭션 2가 대기 상태에 들어가면서 시간 간격이 생긴 것이었다.

친절한 SQL 튜닝 p.467

🔜 나가며

꼬리에 꼬리를 무는 학습을 하며 조각난 지식들이 서로 연결되는 경험을 했다. 이 과정에서 완전히 이해하고 실제로 적용해봄으로써 경험을 통해 배우는 것의 중요성을 깨달았다. 개념을 제대로 이해하지도 못했는데 빨리 해결하려고, 빠른 길만을 찾으려 하다 보면 오히려 시간이 더 걸린다는 걸 실감했다..

제대로 알지도 못하는 상태에서 내 경험 관련해 면접에서 관련 질문을 받았다면 완전 탈수기처럼 탈탈탈 털렸을 것 같다..^^

더 알아볼 것

Repeatable Read 격리 수준이고, 낙관적 동시성 제어로 재고수량이 마이너스되는 문제를 해결하려면 update 시 where 절에 명시된 조건을 평가할 Consistent가 아닌 Current 모드로 데이터를 읽어오는 게 맞는 것 같은데..

친절한 SQL 튜닝책에서는 다르게 설명하고 있어 뭐가 맞는 건지 혼란스럽다. 이 부분에 대해서는 좀 더 자료를 찾아봐야 할 것 같다.

Ref

친절한 SQL 튜닝 - 예스24

책 제목은 필자가 애청하는 라디오 프로그램 ‘손에 잡히는 경제’ 중 ‘친절한 경제’라는 코너에서 착안했다. 어려운 경제 이슈를 일반인 눈높이에 맞게 풀어서 설명해 주는 진행자를 보면서 “나도 SQL 성능 문제를 저렇게 쉽게 풀어 설명하는 책을 써야겠다”는 다짐...

https://www.yes24.com/Product/Goods/61254539

Uploaded by N2T