비관적 동시성 제어를 적용할 경우 락이 걸린 row는 조회가 안 되는 걸까?

✔️ 결론

👀 문제 정의

❓ 분석&시도

시도1

🔎 원인

🔜 나가며

📖 Ref

✔️ 결론

Repeatable Read를 사용 중이라면 MVCC 덕분에 row에 X-Lock을 걸어도 가장 최근 커밋된 데이터를 읽어와서 Lock이 걸린 같은 row라도 조회가 가능하다.

👀 문제 정의

상황: 주문재고가 마이너스가 되는 문제를 해결하려고 비관적 동시성 제어(select for update)를 적용함

밥 먹다가 문득 생각난 게 비관락이 락을 너무 오래 잡고 있어서 비효율적이라는데.. 어디가 비효율적이라는 거지? 비관락으로 X-Lock을 걸어서 조회도 안 된다는 건가?

조회에 락을 걸긴하지만 재고수량 검증 후에 바로 update 쿼리를 수행하기 때문에 어차피 X-Lock이 걸리고 그 텀이 짧지 않아서 어차피 락을 거나 안 거나 성능차이는 그렇게 크지 않을 것 같다는 생각을 했다.

예를들어 내가 상품코드 10001번의 컨버스를 주문하려고 하는데, 재고수량을 조회를 해오면서 해당 row에 락을 걸고, 주문을 하는 동안 다른 사용자가 10001번의 상품을 주문하려고하면(이때 재고수량도 조회해옴) X-Lock이 걸려있어서 조회도 안 되어서 성능이 안 좋다는 건가?

그럼 엄청난 성능이 안 좋은 게 맞다… 주문할 때마다 락이 걸릴테고 그 상품을 조회하는 사용자는 엄청난 불편함을 겪을테니까

❓ 분석&시도

세션1: opt_comb_no인 row에 배타락을 건다.

start TRANSACTION;

SELECT PO.PROD_ID, PO.OPT_COMB_NO, PO.INV_QTY
FROM PROD_OPT PO
WHERE OPT_COMB_NO = 1
FOR UPDATE;

세션2: 세션1에서 락을 건 row를 조회를 하려고 한다.

start TRANSACTION;

select * from prod_opt where OPT_COMB_NO = 1;

위의 가정대로라면 세션1에서 같은 row를 이미 락을 걸었으니까 락이 풀릴 때까지 조회가 안 될 줄 알았으나 세션2에서 조회가 너무 잘 됨

시도1

락이 제대로 안 걸린건가? 싶어서 락을 조회해보았다.

# 현재 발생 중인 모든 데이터 락에 대한 정보를 제공한다. 락을 잡고 있는 트랜잭션, 락 타입, 락 모드 등 상세한 정보를 확인할 수 있다.
SELECT * FROM performance_schema.data_locks;
# 락으로 인해 대기 중인 트랜잭션들에 대한 정보를 제공한다. 어떤 트랜잭션이 어떤 락 때문에 대기 중인지, 대기 중인 트랜잭션과 락을 보유한 트랜잭션의 정보를 확인할 수 있다.
SELECT * FROM performance_schema.data_lock_waits;

아 왜 락이 2개인가 했더니…. 그 테이블락 건다는 게 그말이었구나? 친절한 SQL 튜닝에 나온

record 락이 걸린 부분을 확인해보면(LOCK_TYPE에 RECORD), LOCK_MODE에 ‘X’라고 젹혀져있는 것을 확인할 수 있다.

X = X-Lock = Exclusive Lock = 배타락 = 쓰기락

락은 제대로 걸려있는 것을 확인할 수 있다. 그럼 왜 배타락인 X락이 걸려있는데 어떻게 조회가 가능한 것일까?

🔎 원인

GPT에게 물어봐서 힌트를 얻을 수 있었다.

MVCC라는 개념은 알고 있었는데,

MySQL의 기본 MVCC를 사용한다는 것과 락의 상관관계?를 잘 매칭을 못 시켰던 것 같다.

MySQL의 기본 Isolation Level은 Repeatable Read이다. Repeatable Read는 MVCC를 사용하기 때문에 비록 X-Lock(배타락)이 걸린 행이 있더라도, 가장 최근 커밋된 버전을 읽어올 수 있다.

이래서 row에 X락이 걸려있는 상태에서도 다른 트랜잭션에서 락을 건 행 조회가 가능한 것이다.

🔜 나가며

읽기락/배타락/낙관락은 알고 있었는데 Exclusive Lock, S-Lock, X-Lock이라는 용어는 생소해서 조금 해멨었다.

이번 기회로 락 종류에 대해 알아보고 공부하는 계기가 되었다.

📖 Ref

• 친절한 SQL 튜닝

  예스24

  YOUR EVERY STORY 문화 콘텐츠 플랫폼, 예스24

  https://www.yes24.com/Product/Search?query=친절한sql튜닝&domain=ALL&pid=123487&cosemkid=go16844732698259627

  

• 오라클 성능 고도화 원리와 해법1

  오라클 성능 고도화 원리와 해법 1 - 예스24

  초 대용량 데이터베이스 환경의 여러 차세대 시스템 구축 프로젝트에서 얻은 소중한 경험과 기술 노하우를 널리 전파하기 위해 만든 책이다. 데이터베이스 성능 최적화 전문가가 되기를 꿈꾸는 이들에게 최고의 지침서가 될 이 책은, 고성능 데이터베이스 개발 및 운영 환...

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

  

• MySQL 성능 최적화

  MySQL 성능 최적화 - 예스24

  MySQL을 사용하는 모든 DBA와 개발자를 위한 솔루션 가이드!트위터나 페이스북과 같은 최근의 서비스는 대용량의 실시간 데이터베이스 처리가 필수다. 데이터베이스 모델링과 최적의 운영 환경을 위한 DBMS 구성, 최적화된 쿼리는 이러한 서비스 품질에 가장 많은...

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

  

• Real MySQL 8.0 (2권)

  Real MySQL 8.0 (2권) - 예스24

  『Real MySQL 8.0』은 『Real MySQL』을 정제해서 꼭 필요한 내용으로 압축하고, MySQL 8.0의 GTID와 InnoDB 클러스터 기능들과 소프트웨어 업계 트렌드를 반영한 GIS 및 전문 검색 등의 확장 기능들을 추가로 수록했다. 또한 『Re...

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

  

Uploaded by N2T