SNUXI 개발일지 - 5 [Lock 매커니즘 확인]

MySQL 연산시 어떠한 Lock이 동작하는지에 관해서는 이전에 총 정리한적 있다(next-key lock, gap lock, IX, IS, Redo/Undo 등등 최대한 많은 내용들을 이해하고 정리하였다). 아래 링크 참조

• 링크 : MySQL InnoDB의 동작

이를 바탕으로, 팀 프로젝트로 개발한 SNUXI 서비스의 MySQL에는 어떤 락이 걸리는지 확인해보려 한다.

규칙

우선 하나의 채팅방에 입장할 수 있는 인원은 최소 2명, 최대 4명뿐이다.

채팅방 하나 당 인원수는 많지 않지만, 서비스 사용자가 많아지면 채팅방의 수가 많아지므로 피크타임에 채팅방 입장 HTTP 요청이 많이 올 것이다. 따라서 특정 채팅방 입장 시도가 다른 채팅방의 입장을 방해하지 않았으면 좋겠다.

현재 상황

엔티티간의 관계(Participants 테이블 - User 테이블 등 연관관계가 많음)가 중요하고, 1달이라는 짧은 완성 기간 안에 새로운 NoSQL 사용을 익혀 사용하기에는 무리가 있다고 판단하여 INSERT/DELETE 연산이 많은 환경일 것임에도 MySQL을 선택하였다.

Record에 접근할 때, Gap lock 또는 Record lock을 사용하게 되는데 위의 규칙과 맞물려 될 수 있으면 Record lock을 사용하는 방향으로 SQL 쿼리를 작성하는 것이 좋을 듯 하다.

때마침 채팅방 입장 API는 potId, 즉 Pots 테이블의 Primary Key를 input으로 받으므로 이를 이용하면 아래와 같은 Record Lock만 존재할 것으로 기대하는 쿼리를 짜볼 수 있을 것이다.

UPDATE Pots p
SET p.status = CASE 
    WHEN p.currentCount + 1 >= p.minCapacity THEN :successStatus 
    ELSE p.status
  END,
  p.currentCount = p.currentCount + 1
WHERE p.id = :potId
AND p.currentCount < p.maxCapacity

쿼리는 다 짰고, 실제로 Record Lock만 걸리는지 (아니면 Gap lock이 같이 걸리는지) 확인하고 싶다. 어떤 시나리오를 짜야할까?

Lock 확인 시나리오

딱 3개의 콘솔로 확인할 수 있다. 락 거는 콘솔(A), 락에 막힌 콘솔(B), 락에 막힌걸 확인하는 콘솔(C) 이렇게 3개만 있으면 된다.

조금 더 구체화해서 적어보면 이렇게 되겠다.

• A : autocommit = 0으로 설정한 후, 트랜잭션을 열어 UPDATE 쿼리를 날린다.
• B : autocommit = 0으로 설정한 후, 트랜잭션을 열어 UPDATE 쿼리를 날린다. A가 락을 잡고 있기 때문에, B가 쿼리를 날리면 콘솔이 멈춘다(정확히는 락에 의해 대기중).
• C : 현재 Pots 테이블에 존재하는 lock의 종류를 파악하기 위해, 아래를 콘솔에 입력한다.

  SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    LOCK_TYPE,
    LOCK_MODE,
    LOCK_STATUS,
    LOCK_DATA
  FROM performance_schema.data_locks
  WHERE OBJECT_NAME = 'pots';
  

결과

콘솔 출력 결과의 맨 아래에 index_name = PRIMARY에 의해 lock_mode = X,REC_NOT_GAP이 출력됨을 확인, 즉 gap lock이 아니라 record lock이 걸림을 확인했다.

즉, 위의 UPDATE 쿼리는 id가 다른 방에 입장하고자 하는 요청들을 가로막지 않는 쿼리이다. Gap lock이었으면 서버 처리성능이 lock에 의해 크게 감소했을텐데, record lock만을 거는 쿼리를 통해 잠재적인 성능 저하 요인을 차단했다.