[MySQL] Skip Locked For Session

💡 July님의 200억건의 데이터를 MySQL로 마이그레이션 할 때 고려했던 개념과 튜닝 방법 강의를 듣고 정리한 내용입니다.

 

목차

SELECT FOR UPDATE

읽기 작업(Read) 중 조회하는 레코드(Row)에 잠금(Lock)을 걸기 위해서 이 구문을 사용할 수 있다. 이 SELECT FOR UPDATE 구문을 사용하면 다른 트랜잭션에서 Lock을 건 Row에 대한 변경(Exclusive Lock)과 FOR UPDATE 조회를 방지할 수 있다.

 

하지만 변경과 조회에 대해 Lock을 걸기 때문에 함부로 사용하면 DeadLock이 발생할 수 있다. 반면 동시성 이슈가 발생하는 상황에서 유용하게 사용될 수 있다. 예를 들어 티켓팅을 하면 사용자가 동시에 접근한다. 이때 티켓 판매 상태에 대해 유용하게 샤용된다. 또는 일괄로 대용량의 데이터를 처리하는 배치 작업에서도 유용하게 사용될 수 있다.

Skip Locked

읽기 작업에 대해 일관성을 보장하는 옵션 중 하나로 Lock이 걸려있는 Row는 조회 결과에 반환하지 않는다.

No Wait

읽기 작업에 대해 일관성을 보장하는 또 다른 옵션 중 하나, 쿼리 실행시 Lock이 걸려있다면 대기하지 않고 바로 실패처리한다.

 

예제 1 - Batch 시스템

다음과 같이 멀티 스레드 환경에서 여러개의 DB Connection을 맺고 작업을 처리하는 시스템이 있다고 가정하자.

 

1. name이 july인 테이블을 정렬해서 3건을 조회한다.
2. 이때 한 건씩 name을 june으로 업데이트한다.
3. 이 후 다음 처리 대상 3건을 가져온다.

먼저 다음과 같이 여러 세션에서 row lock을 거는 조회 쿼리가 여러 개 수행되면 다른 세션에서는 Lock을 대기하다가 Lock wait timeout exceeded; try restarting transaction 에러가 발생한다.

SELECT * FROM test WHERE name = "july" ORDER BY id LIMIT 3 FOR UPDATE;

이때 다음과 같이 skipped lock을 두면 Lock이 걸려있는 로우는 반환하면서 에러를 방지할 수 있다.

SELECT * FROM test WHERE name = "july" ORDER BY id LIMIT 3 FOR UPDATE SKIP LOCKED;

 

 

예제 2 - 티켓팅 시스템

다음과 같은 작업이 진행되는 티켓팅 시스템을 가정하자.

 

1. 사용자들이 티켓을 예매한다.
2. 좌석을 선택하고 결제를 진행한다.
3. 예매를 시작하고 완료할 때까지 다른 사용자들은 해당 좌석을 선택할 수 없다. (이미 선택된 좌석입니다.)

만약 Skiped Lock이 없는 경우 어떻게 처리할지 생각해보자. 우선 티켓 테이블에 티켓 판매 상태를 나타내는 컬럼을 추가해주어야한다. (e.g. 빈 좌석, 결제 진행 중인 좌석, 판매 완료 좌석) 또한 화면에서는 빈 좌석만 노출될 수 있도록 별도의 로직을 구성해야한다. 그리고 결제 진행중인 상태인 좌석에서 결제 완료로 이어지지 않을 경우 빈 좌석으로 돌리는 타임아웃 정책도 필요하다. 또한 결제 진행 중 상태를 표현하기 위해 별도의 트랜잭션을 시작하고 관리해주어야한다. (간단하게만 생각해도 매우 복잡해진다.) 

 

하지만 Skiped Lock을 사용하면 매우 간단하게 개선할 수 있다. 티켓팅 결제를 진행할때 Skiped Lock을 건다면, 화면에서는 Lock이 걸려있는 row는 조회 결과에 반환하지 않으므로 조회 자체가 이뤄지지 않는다.

 

SELECT FOR DUPATE와 Skiped Lock을 테스트해보자. 먼저 다음과 같이 ticket 테이블을 생성한다.

-- 티켓팅 테이블 생성
CREATE TABLE ticket (
  seq BIGINT,
  name VARCHAR(255)
);

-- 테스트를 위한 row 생성
INSERT ticket(seq, name)
VALUES (1, "ax");

여러 세션에서 SELECT FOR UPDATE 구문을 호출

먼저 한 세션에서 SELECT FOR DUPATE 구문을 호출한다. 이후 다른 세션에서도 동일하게 SELECT FOR DUPATE 구문을 호출한다. 이때 이후에 호출한 세션은 이미 먼저 다른 세션이 SELECT FOR UPDATE를 호출하고 해당 row에 대해 Lock을 획득하고 있으므로 아래와 같이 대기하게 된다.

 

 

시간이 지나도 Lock 획득에 실패하면 다음과 같은 에러를 반환한다.

 

 

실행 도중에 다른 세션이 트랜잭션을 종료하고 Lock을 반환하면 Lock을 획득하고 해당 row를 조회할 수 있다.

 

 

물론 SELECT FOR UPDATE가 아닌 일반적인 SELECT (Lock을 획득하지 않음) 구문이라면 문제없이 조회할 수 있다.

SELECT FOR UPDATE SKIP LOCKED

한 세션에서  SELECT FOR UPDATE 구문을 호출한다. 이번에는 다른 세션에서 SELECT FOR UPDATE SKIP LOCKED 구문을 호출한다.

-- 세션 시작
BEGIN;
SELECT * FROM ticket WHERE name = "ax" ORDER BY seq LIMIT 3 FOR UPDATE;

 

이미 이전 세션에서 Lock을 걸고있으므로 조회 결과게 포함되지 않고 Empty set을 반환하는 것을 볼 수 있다.

 

정리

SELECT ... FOR UPDATE는 레코드에 Exclusive Lock을 걸어 다른 세션에서 해당 레코드를 수정하거나 FOR UPDATE로 다시 조회하는 것을 차단한다.

반면 단순 SELECT는 읽기 전용으로 잠금을 방해하지 않으므로 여러 세션에서 동시에 조회할 수 있다. 혹은 SELECT IN SHARE MODE를 사용하는 경우에도 Shared Lock을 걸어 다른 세션에서 동시에 조회할 수 있다. 하지만 이 경우 데이터를 수정하거나 FOR UPDATE로 다시 조회하는 요청은 대기해야한다. (특정 Row에 대해서 Shared Lock이 획득이 되어있다면 Exclusive Lock은 대기한다는 것을 기억하자)