MySQL8.0 SKIP LOCKED / NOWAIT 활용하기

MySQL 8.0 버전부터는 읽기 일관성을 위한 read lock 에 대해 두가지 옵션이 추가되었습니다.

  • NOWAIT : 쿼리를 실행 후 읽으려는 row에 lock이 걸려있으면 바로 트랜잭션 실패 처리 (innodb_lock_wait_timeout 만큼 기다리지 않고 바로)
  • SKIP LOCKED : 쿼리를 실행 후 읽으려는 row에 lock 이 걸려있으면 해당 row skip 하고 resultset return

동시성 이슈를 해결하기 위해 select ~ for update , select ~ for shared mode 같은 쿼리를 수행할 때 위 옵션을 줄 수 있는데 언제 사용하면 좋을까요?

  • batch 작업 등으로 테이블 내 데이터를 일괄 변경할 때
  • 티켓 예매 서비스 처럼 사용자들이 동시에 몰리는 데이터를 다룰 때

이 두가지 예제로 살펴보겠습니다.

예제 1 - batch 작업

  • 아래 작업을 여러 thread에서 병렬로 수행
* progress='PREPARE' 인 데이터를 정렬하여 3건씩 가져옴
* 한건씩 progress='DONE' 으로 update
* 다음 처리 대상 3건 가져오기
  • test data
CREATE TABLE progress (
  seq INT PRIMARY KEY,
  progress ENUM('PREPARE', 'DONE') DEFAULT 'PREPARE'
);


INSERT INTO progress (seq)
WITH RECURSIVE my_cte AS
(
SELECT 1 AS n
UNION ALL
SELECT 1+n FROM my_cte WHERE n<10
)
SELECT * FROM my_cte;
  • skip locked 없이 수행했을 때
### session 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql>  select * from progress where progress='PREPARE' order by seq limit 3 for update ;
+-----+----------+
| seq | progress |
+-----+----------+
|   1 | PREPARE  |
|   2 | PREPARE  |
|   3 | PREPARE  |
+-----+----------+
3 rows in set (0.01 sec)


### session 2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql>  select * from progress where progress='PREPARE' order by seq limit 3 for update ;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

=> select for update로 인해 row lock이 걸려 다른 세션에서는 데이터를 가져올 수가 없습니다.
결국 lock 대기하게 되어 병렬 수행이 아무런 의미가 없어지게 됩니다.

  • skip locked
### session 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from progress where progress='PREPARE' order by seq limit 3 for update ;
+-----+----------+
| seq | progress |
+-----+----------+
|   1 | PREPARE  |
|   2 | PREPARE  |
|   3 | PREPARE  |
+-----+----------+
3 rows in set (0.00 sec)


### session 2 
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from progress where progress='PREPARE' order by seq limit 3 for update skip locked;
+-----+----------+
| seq | progress |
+-----+----------+
|   4 | PREPARE  |
|   5 | PREPARE  |
|   6 | PREPARE  |
+-----+----------+
3 rows in set (0.00 sec)

=> 위처럼 select for update 에 의해 row lock 걸린 row는 skip 하고 다음 row들을 읽어옵니다.
다른 세션에 의해 데이터 변경이 잦지 않고 데이터를 일괄로 변경하는 특성이 있는 batch 작업들은 skip locked 옵션을 통해 병렬로 수행하기가 굉장히 쉬워졌습니다.

예제 2 - 동시성 요구되는 데이터 화면 (티켓)

  • 시나리오
* 사용자가 티켓을 예매하려고함
* 여러 좌석 중에 앉을 좌석을 선택하고 결제 진행 중
* 예매 시작~결제성공 할 때까지 사용자가 선택한 좌석이 다른 사용자들에게는 사용불가능한 좌석으로 표시되어야함
  • test data
CREATE TABLE seats (
  seat_no INT PRIMARY KEY,
  booked ENUM('YES', 'NO') DEFAULT 'NO'
);
 

# generate 100 sample rows
INSERT INTO seats (seat_no)
WITH RECURSIVE my_cte AS
(
SELECT 1 AS n
UNION ALL
SELECT 1+n FROM my_cte WHERE n<100
)
SELECT * FROM my_cte;
  • skip locked 없이 사용했을 때 =>
위 seats 테이블에 status (sold, available, pending) 같은 컬럼과 데이터를 추가하여 
예매 중인 좌석은 따로 pending으로 update 수행하고  
화면단에는 pending 상태인 좌석은 표시하지 않는 로직이 필요할 것입니다. 
결제 중에 취소된 pending 상태의 좌석은 어플리케이션에서 설정한 timeout 후  
다시 available로 update 하는 로직도 필요하게 되는 등 생각해야 할 부분이 많을 것입니다.
  • skip locked 사용했을 때
### session 1  1~90번 좌석을 예매 중

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM seats WHERE seat_no BETWEEN 1 AND 90 AND booked = 'NO' FOR UPDATE ;

### session 2 
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM seats where booked = 'NO' for update skip locked;
+---------+--------+
| seat_no | booked |
+---------+--------+
|      92 | NO     |
|      93 | NO     |
|      94 | NO     |
|      95 | NO     |
|      96 | NO     |
|      97 | NO     |
|      98 | NO     |
|      99 | NO     |
|     100 | NO     |
+---------+--------+
9 rows in set (0.00 sec)

=> skip locked 옵션을 사용하면 status (sold, available, pending) 관리 같은 로직이 없어도
트랜잭션이 진행중인 (예매중인) row는 skip 해서 보여주기 때문에 훨씬 간단한 코드로 동일한 효과를 볼 수 있습니다.