SELECT FOR UPDATE

Jake·2023년 7월 7일

동시성

목록 보기

1/1

SELECT ... FOR UPDATE; 로 Query를 실행하게 되면 해당하는 레코드에 배타 락 (Exclusive Lock)이 잡히게 됩니다. (MySQL의 경우 Index)

배타 락은 쓰기 락(Write Lock)이라고도 불립니다. 데이터에 대해 배타 락을 획득한 트랜잭션은, 읽기 연산과 쓰기 연산을 모두 실행할 수 있습니다. 다른 트랜잭션은 배타 락이 걸린 데이터에 대해 읽기 작업도, 쓰기 작업도 수행할 수 없습니다. 즉, 배타 락이 걸려있다면 다른 트랜잭션은 공유 락, 배타 락 둘 다 획득 할 수 없다. 배타 락을 획득한 트랜잭션은 해당 데이터에 대한 독점권을 갖는 것입니다.

배타 락

아래와 같이 SELECT FOR UPDATE 를 사용하여 특정 데이터로부터 배 락 을 획득할 수 있습니다.

SELECT * FROM table_name WHERE id = 1 FOR UPDATE;

SELECT FOR UPDATE 쿼리는 가장 먼저 LOCK을 획득한 SESSION의 SELECT 된 ROW 들이 UPDATE 쿼리 후 COMMIT이 되기 이전까지 다른 SESSION들은 해당 ROW들을 수정하지 못하도록 하는 기능입니다

즉, 쉽게 말해 데이터를 수정하려고 SELECT 하는 중이니 다른 사람들은 해당 데이터에 접근할 수 없어 라고 할 수 있습니다

즉 해당 row 들 간에 동시성을 제어하기 위하여 LOCK을 거는 기능입니다

여기서 LOCK이란 뜻 그대로 잠금이라는 의미이며 LOCK을 누군가가 가지고 있으면 해제하기 전까지 접근할 수 없도록 되어있습니다.

예제

아래 예시로 id = 2399 의 name을 TongHae → JONGHUN 으로 변경하는 과정 중 다른 session에서 해당 row 에 접근하는 상황을 살펴보겠습니다

위 콘솔을 A, 아래 콘솔을 B 로 가정하겠습니다

전체적인 과정은 다음과 같습니다

A가 transaction을 시작한다
B가 transation을 시작한다
A가 id=2399 record에 LOCK을 얻는다
B가 id=2399 record에 대해 접근을 시도한다
B가 Lock wait timeout exceeded; try restarting transaction 에러를 받는다
A가 정보를 수정한다
A가 commit을 진행한다
B가 id=2399 record에 대해 접근을 시도 -> 성공한다

각 색깔별로 동일한(아주 비슷한) 시간대에 사용자 2명이 transaction을 진행했다고 가정합니다

A와 B 사용자의 파란색 영역은 transaction을 시작하는 내용입니다

A의 첫번째 빨간색 영역은 id=2399 row에 대한 for update 키워드를 통해 LOCK을 얻는 과정이며 이때 다른 사용자는 해당 row에 대해 접근할 수 없습니다

이떄 B가 id=2399에 대해 접근을 시도하였으며 Lock wait timeout exceeded; try restarting transaction 에러를 반환받고 LOCK을 얻지 못하였으므로 대기중에 timeout이 발생하는 내용입니다

이후 A는 노란색 영역에서 commit 완료 시점에 LOCK 권한을 반납하고 이후 B가 id=2399에 접근을 시도할 경우 LOCK 권한을 얻을 수 있으며 B의 노란색 영역에서 select에 대한 결과가 화면에 보이는 것을 볼 수 있었습니다

위 사항을 실무에서 어떻게 사용되고 활용될까요 ?

아래와 같이 가정해보도록 하겠습니다

가정

예제 1

영화 티켓 예매 시스템

사용자 A가 select update api 호출 시

혹은 해당 좌석에 대한 표시를 LOCK을 걸어놓음
다른 사용자 B는 계속 대기 상태

실제 진행 상황 가정

사용자 A 가 e-2열 선택 후 결제 진행중
선택 후 결제를 위해 결제 페이지로 넘어감
a. 이 시점에 해당 row에 대해 LOCK을 걸어 놓음으로써 다른 사용자가 접근할 수 없도록 설계
사용자 B 화면에서는 e-2열 좌석이 예매 불가(회색처리)로 표기되어 선택 불가

와 같이 진행될 것 같습니다

동시성 제어

예제 2

도서 구매 시스템

주문 로직은 복잡하여 0.5초가 소요
각 사용자는 주문을 0.001초 차이로 진행 완료

재고 = 5
여러개 구매 가능
책의 종류는 동일

각 사용자가 구매할 책의 개수

사용자 A = 3권
사용자 B = 3권
사용자 C = 2권

1 동시성 제어를 하지 않을 경우

A 주문완료
B 주문완료 (이 시점에 B는 아직 재고 수량이 5이므로 주문 가능)
C 주문완료 (이 시점에 C는 아직 재고 수량이 5이므로 주문 가능)

즉 모든 주문 성립

0.5초뒤

a 주문 성공 / 재고 2
b 주문 성공? / 재고 -1 ERROR!!
c 주문 성공? / 재고 -3 ERROR!!

결과 : 재고 = -3 ???

시간순으로 다시 나타내보면 현재 시간을 15:00:00.000 이라고 가정

- 15:00:00.000 A 주문 완료
- 15:00:00.001 B 주문 완료
- 15:00:00.002 C 주문 완료

시간 + 0.5초

- 15:00:00.500 A 주문 성공
- 15:00:00.501 B 주문 실패 / 재고 -1
- 15:00:00.502 C 주문 실패 / 재고 -3

으로 볼 수 있습니다

A사용자는 주문 성공을 하였지만 주문을 완료한 B, C 입장에서는 화면상 주문완료가 되었는데 시스템상에서는 주문 실패가 된 것입니다

결국 주문이 완료되어 결재는 되었는데 시스템 오류로 인해 주문은 실패하게된 케이스입니다

동시성을 제어하지 않으면 이러한 상황이 발생할 수 있으니 동시성 제어는 필수입니다

2 동시성 제어를 할 경우, 주문 후 DB 변경 로직까지 처리시간을 보장

A 주문완료
B 주문 시도시 id = 100 인 ROW에 LOCK이 걸려있으므로 접근 불가
C 주문 시도시 id = 100 인 ROW에 LOCK이 걸려있으므로 접근 불가

A 주문 성공 id = 100 인 ROW에 LOCK 해제
B 주문 시도시 id = 100 인 ROW에 LOCK 획득
B 주문 시도시 주문 수량 초과로 주문 실패
B 는 id = 100 인 ROW에 LOCK 해제
C 주문 시도시 id = 100 인 ROW에 LOCK 획득
C 주문 성공 id = 100 인 ROW에 LOCK 해제

시간순으로 다시 나타내보면 현재 시간을 21:00:00.000 이라고 가정

- 21:00:00.000 A 주문 완료 / id = 100 LOCK 획득
- 15:00:00.001 B 주문 시도시 실패 / id = 100에 접근 불가 
- 15:00:00.002 C 주문 시도시 실패 / id = 100에 접근 불가

시간 + 0.5초

- 15:00:00.500 A 주문 성공 / id = 100 LOCK 반환, 재고 2
- 15:00:00.501 B 주문 시도시 실패 / id = 100 LOCK 획득, 재고 초과 ERROR, id = 100 LOCK 반환
- 15:00:00.502 C 주문 완료 / id = 100 LOCK 획득

시간 + 0.5초

- 15:00:01.002 C 주문 성공 / id = 100 LOCK 반환, 재고 0

LOCK을 통해 동시성을 제어하여 모든 주문들은 성공적으로 진행됩니다

결과 : A와 C는 성공적으로 주문 진행 완료, B는 주문 불가, 재고 = 0

즉, 데이터베이스 내에서 특정 행에 동시성 제어를 위해 LOCK을 사용하는 것입니다

Jake