Project : RainMind 개발일지 - 13 [schedule 생성 시 lock 확인 at MySQL 콘솔]

성능 최적화를 하며 이런 쿼리를 쓴 적이 있다.

@Modifying
@Transactional
@Query(value = """INSERT INTO schedules (user_id, title, location_id, start_at, end_at) 
  SELECT :userId, :title, :locationId, :start_at, :end_at
  WHERE (SELECT COUNT(*) FROM schedules WHERE user_id = :userId) <= 29
""")  

Where 구문 내부의 select 절은 당연히 MVCC에 의해 lock을 잡지 않을 것으로 (처음에)예상되었으나, 실제로 k6 스크립트를 이용해 서버 API를 호출하고, mysql 콘솔에 쿼리를 날려보면

mysql> SELECT NOW() ts, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_MODE, LOCK_STATUS, COUNT(*) cnt FROM performance_schema.data_locks WHERE ENGINE='INNODB' AND (LOCK_MODE LIKE '%GAP%' OR LOCK_MODE LIKE '%NEXT-KEY%') GROUP BY OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_MODE, LOCK_STATUS ORDER BY cnt DESC;
+---------------------+---------------+-------------+----------------------+---------------+-------------+-----+
| ts                  | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME           | LOCK_MODE     | LOCK_STATUS | cnt |
+---------------------+---------------+-------------+----------------------+---------------+-------------+-----+
| 2026-02-23 11:17:37 | rainmind      | users       | PRIMARY              | S,REC_NOT_GAP | GRANTED     |   1 |
| 2026-02-23 11:17:37 | rainmind      | schedules   | schedule__fk_user_id | S,GAP         | GRANTED     |   1 |
| 2026-02-23 11:17:37 | rainmind      | location    | PRIMARY              | S,REC_NOT_GAP | GRANTED     |   1 |
+---------------------+---------------+-------------+----------------------+---------------+-------------+-----+
3 rows in set (0.01 sec)  
  
// 이후 추가로 다시 날려봄  
+---------------------+---------------+--------------+------------+---------------+-------------+-----+
| ts                  | OBJECT_SCHEMA | OBJECT_NAME  | INDEX_NAME | LOCK_MODE     | LOCK_STATUS | cnt |
+---------------------+---------------+--------------+------------+---------------+-------------+-----+
| 2026-02-23 11:17:31 | rainmind      | alarm_outbox | PRIMARY    | X,REC_NOT_GAP | GRANTED     |   1 |
+---------------------+---------------+--------------+------------+---------------+-------------+-----+

Gap lock이 관찰된다. 무엇인가 했더니, INSERT INTO … SELECT는 ‘수정/삽입 중에 read를 수행하므로 gap lock(과 해당하는 레코드에 S-lock)을 건단다.

Gap lock은 서로 중첩 가능하기에 S/X 구분이 무의미한데, ‘S,GAP’이라고 표시되는거 보면 SELECT와 같은 조회 구문의 성격이 Read라서 S로 표시되는듯 하다.

alarm_outbox는 내부적으로 save 로직이 있으니, insert intention lock으로 인해 X-lock이 걸리는 것은 알겠다. 근데 users랑 location은 대체 왜 걸리는건지 의문스러웠다. 아니 findBy…로 SELECT만 수행해서 아무런 락이 걸리지 않을텐데…

라고 생각하여 찾아보니, schedules에는 FK가 걸려있다. users와 location의 키를 빌려오는데, 참조한 레코드 즉 user_id = 3, location_id = 5를 참조했다면 해당 레코드에만 S-lock을 건다고 한다. 그래서 S,REC_NOT_GAP이라고 표현된 듯 하다.

Gap lock의 조건이, PK/UK가 아니거나 또는 Where 구문으로 탐색 시 동일 조회가 아니면 걸리는데, 빌려온 FK 모두 PK라서, 해당 레코드에만 S-lock이 걸린듯 하다.

이렇게 FK를 빌려준 상위 테이블에 참조 무결성을 위한 락을 Intention Shared Lock이라고 하며, 정확히는

1) Intention Shared Lock이 테이블 수준으로 먼저 걸린다. 따라서 모든 행을 다 뒤지지 않더라도 S-lock이 걸린 행이 있는지 파악이 가능하다. 이 lock은 ALTER와 같은, 테이블의 Schema를 바꾸는 행위를 막는다.

2) 해당하는 행(또는 Gap)에 S-lock이 걸린다(또는 Gap lock).

즉 읽는 도중에 스키마 변경과 같은 변동이 큰 작업은 막고, 최대한 효율적으로 S-lock이 있는지 파악하면서 작업의 유연성을 제공하는 것이다.

추가로, INSERT시에도 insert intention lock이 걸리는데, 이때도 테이블 수준으로 Intention eXclusive Lock이 걸려 마찬가지 원리로 Schema를 바꾸는 행위를 막는다.

따라서, 기존 서비스 로직에
1) 데드락 예외 발생 시, try-catch로 삼켜서 사용자가 재시도 하도록
2) 또는, for문을 통해 일정 횟수 정도 재시도 하는 로직(Thread sleep도 고려 가능)

그러나 @Transactional이 붙어있을 때, try-catch로 삼켜도 해당 트랜잭션이 롤백 처리가 되는지 통과 처리가 되는지에 대해서 좀 더 알아봐야 할 것 같고, 롤백 처리가 된다면 서비스 로직을 새로운 트랜잭션 단위로 분리하거나 그런 방식으로 해야할 것 같아 당장 수정하지 말고, 이 부분은 좀 더 고민해 보아야겠다.