🔐 동시성 문제 해결을 위해 어떤 Lock을 사용해야 할 까? - (2) DB Lock의 Shared Lock, Exclusive Lock Deep Dive 🔓

👀 Overview

Shared Lock과 Exclusive Lock은 S-Lock, X-Lock이라고도 하며, (읽기 락, 쓰기 락), (공유 락, 배타 락)이라고 불리기도 한다.

하지만 이 lock에 대한 내용을 학습하면서 여러 블로그들을 찾아봤는데, 잘못된 내용들이 정말 많았다!

 

그래서 그 내용들을 실제 내가 MySQL 쿼리를 통해 비교하면서 바로잡고, 파헤쳐 보기 위해서 글을 써야겠다는 결심을 했다.

 

🔐Shared Lock과 Exclusive Lock

먼저 앞 글에서도 언급했지만, InnoDB 스토리지 엔진 레벨에서의 Lock을 설명하면서 Shared Lock, Exclusive Lock의 개념이 나왔다.

특정 row에 lock을 걸면 레코드락, 여러 row에 range로 lock을 걸면 넥스크 키 락, 그리고 그 row들 중간에 걸리는 갭 락 이 있었다.

InnoDB 스토리지 엔진이 제공하는 락을 취득하는 방법에는 1️⃣ UPDATE 쿼리, 2️⃣ DELETE 쿼리, 3️⃣ Shared Lock / Exclusive Lock 획득 시에 발생한다고 언급했었다.

 

그 중, Shared Lock과 Exclusive Lock에 대해서 알아보자.

 

1. Shared Lock (공유 락)

Shared Lock은 MySQL 8.0 버전 부터 SELECT .. FOR SHARE 을 통해 획득할 수 있다.

Shared Lock은 읽기 잠금이라고 말한다. 그래서 리소스를 여러 사용자가 동시에 레코드를 읽을 수는 있지만, 다른 사용자가 변경 하는 것은 차단한다고 여러 블로그들에서 말한다.

하지만 Exclusive Lock에서도 동일하게 리소스를 여러 사용자가 동시에 레코드를 읽을 수는 있지만, 변경하는 것을 차단한다.

뿐만 아니라, shared, exclusive lock이 아닌 update, delete 쿼리에서도 동일하게 다른 트랜잭션에서 동시에 변경하는 것은 차단한다.

 

무슨말이냐면, 사원번호가 10001번이고 연봉이 60117인 row에 레코드 락, 그 중 shared lock을 한 트랜잭션 내에서 걸었다. (예시는 업무에 바로쓰는 SQL 튜닝 책의 데이터를 사용했다)

autocommit을 끄고, 트랜잭션을 시작했다. 그리고 shared lock을 for share 키워드를 통해 획득했다.

transaction 1번 - shared lock 획득

shared lock을 위에서 얻고, 다른 창을 열어 다른 트랜잭션에서 해당 row를 update해 보았다.

transaction 2번 - update 통한 row 변경 시도

그러니 무한 대기 상태에 빠졌다. spin lock형태로 계속 시도하는 것 같다.

그러고 transaction 1번에서 rollback 시켰더니, 대기중이던 transaction 2번에서 update 쿼리가 정상 실행되었다.

transaction 2번 - transaction 1번에서 rollback 후 실행 (44초 걸림)

그리고 SHOW PROCESSLIST;로 다른 창을 하나 더 열어 프로세스가 어떤 상태인지 비교를 해 보았다.

먼저 Transaction 1번이 rollback을 통해 lock 반환 전 모습이다.

Transaction 3 - 프로세스 상태 확인

Id가 77번인 프로세스에서 update 쿼리를 계속해서 실행하고 있다.

 

아래는 Transaction 1번이 rollback 후 lock을 반환 하고 난 모습이다.

Transaction 3 - 프로세스 상태 확인

Id가 77번인 프로세스에서 update를 마치고 state가 바뀌었다.

 

그런데 이렇게 lock을 획득하고 다른 트랜잭션에서 update 쿼리 실행 시 대기하는 현상은 shared lock, exclusive lock이 아닌 update나 delete에서도 동일하다.

Trasaction 1번 - delete 쿼리 실행

Transaction 2번 - delete 쿼리 실행

위 쿼리를 보면, 결국 transaction 1번이 rollback을 하고 나서야 16초 만에 delete 쿼리가 실행되는 것을 확인할 수 있었다.

이건 

 

동시에 레코드를 읽을 수는 있지만, 다른 사용자가 변경 하는 것은 차단하는 것이 shared lock의 특징이라고 나오는데, 이는 shared lock에서의 특징이라기 보다는 InnoDB 스토리지 엔진 상에서 특정 row에 레코드락이 걸리기 때문이라는 것을 알 수 있었다.

 

따라서 개인적으로 shared lock에 대해서 학습하다 헷갈린 부분이 아래 부분인데, 특정 row에 대해서 shared lock을 얻은 트랜잭션내에서 update를 통한 row에 대한 변경이 가능하다! shared lock이라고 무조건 lock이 걸린 row에 대해 쓰기작업이 불가능한것 아니라, 동일한 트랜잭션이면 가능하다.

동일한 트랜잭션일 경우 shared lock 획득 후 데이터 변경 가능

그러면 Shard Lock의 진짜 특징은 뭘까?

 

아래에서 볼 수 있듯, 한 트랜잭션에서 공유 락을 획득하면, 해당 트랜잭션이 커밋 / 롤백을 통해 락을 반환할 때 까지 다른 트랜잭션에서 획득 할 수 있는 락을 말한다. 단, 배타 락은 획득 불가능 하다. 따라서 다른 트랜잭션에서 배타 락 을 획득하려 하면 대기 상태에 빠진다.

Transaction 1번 - 트랜잭션 시작 후 공유락 획득

Transaction 2번 - 트랜잭션 시작 후 공유락 획득 가능 but 배타 락 획득 불가

 

2) Exclusive Lock (배타 락) 

Exclusive Lock은 이미 나왔지만 SELECT .. FOR UPDATE 을 통해 획득할 수 있다.

배타 락의 특징 또한 다른 트랜잭션에서 읽기가 불가능하다고 알고 있었는데, 이는 잘못되었다고 생각한다.

Transaction 1번 - Exclusive Lock 획득

위 Transaction 1번에서 배타락을 획득하면 다른 트랜잭션에서 읽기가 안될것 같지만 아래와 같이 가능하다.

Transaction 2번 - 동일한 row에 대해서 select 가능

위와 같은 select 쿼리를 실행하면 대기 상태에 빠질것 같았지만 바로 조회가 되었다.

왜그럴까?🤔

 

배타락의 특징을 잘못 이해하고 있어서 위와 같은 오해를 했는데,

배타락의 특징은 한 트랜잭션에서 배타락을 획득하면 커밋 or 롤백을 통해 배타락을 반납하기 전 까지 다른 트랜잭션에서 배타락 AND 공유락 을 취득 할 수 없다.

Transaction 1번 - 배타락 획득

Transaction 2번 - 배타락 획득 실패

배타락을 Transaction 2번에서 획득 하려고 하면 대기상태에 빠진다. (Transaction 1번이 배타락을 커밋 / 롤백을 통해 반납해야 획득한다)

이때는 Transaction 2번에서 공유락을 획득 하려고 해도 대기상태에 빠진다

Transaction 2번 - 공유락 획득 실패 (대기상태)

 

 

정리하자면, 공유락은 한 트랜잭션에서 획득 시, 다른 트랜잭션에서도 획득 가능하다. 이는 다른 트랜잭션에서 배타락의 획득을 막고, 다른 트랜잭션에서의 update, delete 쿼리 실행을 막는다.

배타 락은 한 트랜잭션에서 획득 시, 다른 트랜잭션에서 공유락, 배타락 획득 모두를 막는다. 이는 다른 트랜잭션에서의 updaet, delete 쿼리 실행을 막는다. 

 

후기

보통 후기를 잘 쓰지는 않는데, 여러 블로그를 통해 학습을 하는 것도 중요하지만, 직접 실습을 통해 결과를 확인해 보는 과정도 중요하구나..! 라는 것을 느꼈다.

직접 sql 쿼리를 날려보면서, 기존의 내가 가지고 있던 생각들,

- shared lock == 읽기 락 -> 다른 곳에서 읽는 것은 가능하고 쓰는 것만 막는 구나

- exclusive lock == 쓰기 락 -> 다른 곳에서 읽는 것도 안되고 쓰는것도 안되구나

라는 잘못된 인식에서 해방시켜 주었다.

 

 

[Lock 시리즈]

동시성 제어를 위한 Lock (1) - DB Lock

동시성 제어를 위한 Lock (2) - Shard Lock, Exclusive Lock Deep Dive

 

 

Reference

Real MySQL 8.0 Chapter 5

https://velog.io/@soongjamm/Select-%EC%BF%BC%EB%A6%AC%EB%8A%94-S%EB%9D%BD%EC%9D%B4-%EC%95%84%EB%8B%88%EB%8B%A4.-X%EB%9D%BD%EA%B3%BC-S%EB%9D%BD%EC%9D%98-%EC%B0%A8%EC%9D%B4

Select 쿼리는 S락이 아니다. (X락과 S락의 차이)