Database Lock으로 인한 Slow Query 제거하기

들어가며

안녕하세요, 숨고 Backend Engineer Terry입니다.

개발자라면 한 번쯤 고민해 봤을 동시성 처리 이슈는, 개발에서 매우 중요하면서도 다루기 까다로운 문제 중 하나입니다. 동시성 처리를 위해 가장 많이 사용하는 것은 Database의 Lock 기능입니다. 하지만, 이 기능을 사용하면 동시성 이슈는 해결할 수 있지만 Slow Query 이슈가 추가로 발생할 수 있습니다.

숨고에서도 이렇게 Lock으로 인한 Slow Query 이슈가 있었고, 이번 블로그 글에서는 이러한 문제들을 해결하기 위해 적용해 본 사례들을 공유하고자 합니다.

동시성 문제와 Lock의 역할

동시성 문제는 여러 스레드나 프로세스가 동일한 데이터 또는 리소스에 동시에 접근하려 할 때 발생합니다. 예를 들어, 여러 사용자가 동시에 하나의 데이터에 접근해 업데이트를 시도한다면 데이터의 일관성이 깨질 수 있습니다. 이를 방지하기 위해 주로 사용하는 것이 Lock입니다. Lock은 자원에 대한 독점을 보장하여 다른 스레드나 프로세스가 동시에 접근하지 못하도록 하는 방식을 말합니다.

하지만 Lock은 데이터에 동시에 접근하는 것을 막고 하나의 요청만 처리하므로 여러 요청이 몰릴 경우 대기하는 시간이 길어질 수 있습니다. 숨고에서도 데이터베이스의 일관성을 보장해야 하는 테이블 row에 Lock을 걸고 작업하도록 구현하였으나 이에 따라 대기시간이 길어지면서 Slow Query가 발생하였습니다.

숨고에서 발생한 문제

숨고에서는 1초 이상 소요되는 쿼리를 Slack을 통해 자동으로 공유받는 방식으로 Slow Query를 모니터링하고 있습니다. 이를 바탕으로 Back-end Chapter에서는 개선이 필요한 쿼리를 리스트업하여 지속적으로 최적화하고 있는데요.

최근 주기적으로 올라오는 Slow Query 알림을 분석해 볼 필요성을 느껴, 문제의 원인을 자세히 살펴보기로 하였습니다.

문제가 발생한 쿼리는 숨고의 안심번호 매핑 기능에서 발생한 쿼리였습니다.

숨고에서는 유저의 연락처 보호를 위해 안심번호 기능을 제공하고 있습니다. 안심번호는 다음과 같은 과정으로 할당됩니다.

1. 사용 중이지 않은 안심번호를 조회합니다. (status = ‘A’)

2. 조회된 안심번호를 사용 중으로 업데이트합니다. (status = ‘L’)

3. 유저의 실제 번호와 안심번호를 매핑합니다.

이 과정에서 하나의 안심번호는 한 명의 유저와 매핑되어야 합니다. 데이터의 일관성을 유지하기 위해, SQLAlchemy의 with_for_update() 옵션을 사용하여 안심번호를 조회할 때 해당 row를 Lock으로 보호하고, 다른 트랜잭션이 동시에 접근하지 못하도록 합니다.

해결 방법 찾아보기

위의 문제를 해결하기 위한 방법으로 Lock을 제거하는 방향으로 검토하였습니다. 그러나 Lock을 제거하면 다른 유저에게 동일한 안심번호가 할당될 위험이 있기 때문에, Lock 없이도 동시성 이슈를 안전하게 처리할 방법이 필요했습니다. 이를 해결하기 위해 다음과 같은 방법들을 검토하였습니다.

1. with_for_update의 skip_locked 옵션 사용하기

첫 번째로 고안해 본 방법은 MySQL 8.0 이상의 버전에서 사용할 수 있는 FOR UPDATE SKIP LOCKED라는 기능을 활용하는 것이었습니다.

이는 SQLAlchemy에서 with_for_update(skip_locked=True)로 적용할 수 있습니다. 이 기능은 이미 Lock이 걸린 row를 제외하고 다른 row를 조회할 수 있어, 동시에 여러 요청이 들어와도 대기하지 않고 다른 데이터를 처리할 수 있습니다.

하지만 저희가 이 문제를 해결하려던 당시, 숨고의 MySQL 버전은 5.7이었기 때문에 이 기능을 사용할 수 없었습니다. (현재는 MySQL 8.0으로 업데이트가 되어 적용할 수 있지만, 당시에는 다른 방법을 찾아야 했습니다.)

2. Redis를 이용한 Lock 제어

다음으로 고려한 방법은 Redis를 이용해 동시성 문제를 해결하는 것이었습니다. Redis는 자원에 Lock을 걸고 해제하는 데 매우 빠르고 효과적인 툴입니다. Redis의 SETNX(Set if Not Exists) 명령어를 사용해, 이미 다른 프로세스에서 사용 중인 자원이 있으면 Lock을 걸고, Lock이 없는 자원만 접근할 수 있도록 처리할 수 있습니다.

Redis를 통해 안심번호 할당 로직을 구현할 때는 다음과 같은 흐름으로 처리할 수 있습니다.

• Redis에 Lock을 설정하고, 설정에 성공한 요청만 안심번호를 할당받습니다

• 만약 Lock 설정에 실패하면 다른 안심번호를 할당받을 때까지 반복합니다

Redis를 활용하는 것도 좋은 방법이지만, Redis 서버가 장애를 겪거나, 네트워크 문제가 생길 경우 Lock 제어에 실패할 위험이 있어 안정성 측면에서 우려가 있었습니다. 이에 Redis나 다른 툴을 사용하지 않고 해결할 방법이 없을지 다시 고민해 보았습니다.

3. 업데이트 조건을 통한 제어

깊은 고민 끝에 찾아낸 해결책은 특정 조건을 통한 업데이트였습니다. 저는 이 방법이 가장 간단하면서도 효과적이라고 생각했습니다. 이는 트랜잭션에서 동시에 같은 안심번호 row를 조회하더라도, 조건에 맞는 요청만 업데이트되기 때문에 먼저 들어온 요청으로 조건이 변경된다면 다음에 들어온 요청은 업데이트에 실패하게 되고 안심번호가 매핑되지 않게 됩니다.

안심번호 할당 시, 특정 조건으로 row를 조회하고, 해당 조건을 기반으로 업데이트를 시도합니다. 하나의 트랜잭션만 업데이트에 성공하게 되면, 나머지는 실패합니다. 실패한 트랜잭션은 다시 조회를 시도하고 새로운 row에 대해 업데이트를 시도하게 됩니다.

자세한 동작 로직은 아래와 같이 이루어집니다.

1. 할당할 수 있는 안심번호를 조회합니다. (status = 'A')

   • 이때 동시에 요청이 여러 건이 들어온다면 같은 안심번호(0505-1111-2222)를 조회하게 됩니다.

2. 안심번호를 할당받기 위해 UPDATE safe_number SET status='L' WHERE id=1 and status='A' 쿼리로 데이터베이스 테이블을 업데이트합니다.

3. 가장 먼저 성공한 쿼리만 안심번호 할당에 성공하고, 나머지 쿼리는 실패하게 됩니다.

4. 실패한 쿼리들은 1번 로직부터 다시 재시도 하게 됩니다.

이 방법의 장점은 간단하게 쿼리 수정을 통해 구현할 수 있다는 점입니다. 또한 데이터베이스 내부에서 모든 처리가 이루어지기 때문에 Redis와 같은 외부 시스템에 의존하지 않아도 됩니다.

실제로 구현해 보기

위에서 찾아낸 업데이트 조건으로 제어하는 방식을 적용해 보았습니다.

적용하는 방법은 간단합니다. 기존 코드의 with_for_update() 옵션을 제거하고, 매핑을 위해 업데이트하는 쿼리에 조건 필터를 추가하면 됩니다.

동시에 요청이 들어온다면 안심번호 매핑에 실패할 수 있기 때문에 다른 사용 가능한 안심번호를 조회하여 다시 시도할 수 있도록 처리 해두었습니다.

<이전 코드>

def get_available_safe_number():
 with transaction('master') as session:
   available_safe_numbers = (
       session.query(SafeNumberModel)
       .filter(SafeNumberModel.status == 'A')
       .with_for_update()
       .first()
   )
   if available_safe_numbers is None:
       raise Exception("할당 가능한 안심번호가 없습니다.")
   session.query(SafeNumber).filter(SafeNumberModel.id == available_safe_numbers.id).update({'status': 'L'})
   return available_safe_numbers.safe_number 

<변경된 코드>

def get_available_safe_number():
  max_count = 5
  safe_number = None
  for count in range(1, max_count + 1):
    with transaction('master') as session:
      available_safe_numbers = (
          session.query(SafeNumberModel)
          .filter(SafeNumberModel.status == 'A')
          .first()
      )
      if available_proxy_numbers is None:
          raise Exception("할당 가능한 안심번호가 없습니다.")
      update_result = (
          session.query(SafeNumberModel)
          .filter(
              SafeNumberModel.id == available_safe_numbers.id,
              SafeNumberModel.status == 'A',
          )
          .update({'status': 'L'})
        )
      if update_result:
          safe_number = available_safe_numbers.proxy_number
          break
      if count >= max_count:
        raise Exception("안심번호 할당에 실패 했습니다.")
  return safe_number

과연 잘 동작할까?

이 방식이 제대로 작동하는지 확인하기 위해 멀티 스레드를 통해 동시성 테스트를 진행했습니다. 새로 구현한 업데이트 조건을 통한 안심번호 할당 로직을 적용했을 때 각 스레드가 다른 안심번호를 할당받는 것을 확인할 수 있었습니다.

def worker(idx):
    safe_number = get_available_safe_number_with_lock('test', 'user')
    print(f'Thread Number {idx} Result :', safe_number)
def task_safe_number():
    print("안심번호 할당 테스트 시작")
    threads = []
    for i in range(3):
        # 스레드를 생성하고, worker 함수에 인자를 전달
        thread = threading.Thread(target=worker, args=(i,))
        threads.append(thread)
        thread.start()
    for thread in threads:
        thread.join()
    print("모든 스레드 작업 완료")

보너스. 다른 방법으로 적용해본 사례

1. SKIP_LOCKED를 활용한 방법

위와 같은 방법으로 문제를 처리하고 난 후 시간이 지나서 숨고의 데이터베이스는 Mysql 8.0으로 업그레이드되었습니다. 그래서 안심번호 할당 부분에도 위에서 설명해 드렸던 SKIP_LOCKED를 적용할 수 있게 되었고 Lock으로 인해 Slow Query가 발생하는 숨고의 캐쉬 관련 로직에도 SKIP_LOCKED를 적용하였습니다.

구현하는 방법은 간단했습니다. SQLAlchemy의 ORM 구문에 .with_for_update(skip_locked=True) 만 추가하면 간단하게 구현할 수 있었습니다.

def get_available_safe_number():
  with transaction('master') as session:
    available_safe_numbers = (
      session.query(SafeNumberModel)
      .filter(SafeNumberModel.status == 'A')
      .with_for_update(skip_locked=True)
      .first()
    )
    if available_safe_numbers is None:
      raise Exception('할당 가능한 안심번호가 없습니다.')
    session.query(SafeNumber).filter(SafeNumberModel.id == available_safe_numbers.id).update({'status': 'L'})
  return available_safe_numbers.safe_number

2. Redis Lock을 활용한 방법

이번 사례는 Slow Query가 발생하진 않았지만, Redis를 이용해 동시성 문제를 해결한 다른 사례입니다. Redis의 SETNX(Set if Not Exists) 명령어를 사용해, 이미 다른 프로세스에서 사용 중인 자원이 있으면 Lock을 걸고, Lock이 없는 자원만 접근할 수 있도록 처리할 수 있습니다.

캐시를 처리하는 API에 같은 요청이 짧은 시간 내로 동시에 들어오면 중복으로 처리될 수 있기 때문에 Redis를 이용한 Lock을 적용하였습니다.

Redis Lock은 다양한 상황에 유연하게 적용할 수 있어서 클라이언트와 주고받는 캐시 관련 API와 숨고의 MSA내에서 Service들 간에 서로 요청하는 캐시 관련 API에도 적용할 수 있었습니다.

def lock_with_redis(key: str):
  redis = redis_client.connect('cash')
  response = redis.set(name=key, value=1, nx=True, ex=timedelta(seconds=5))
  if not bool(response):
    raise Exception('5초 내에 여러번 호출되었습니다.')

결론 및 앞으로

위의 로직을 적용한 결과 Slack에서 올라오던 Slow Query 알림은 더 이상 올라오지 않게 되었습니다. 또한 Slow Query가 제거된 덕분에 API 평균 응답 속도는 800ms에서 200ms로 약 75% 감소하였고, Slow Query 평균 응답 속도는 18초 이상에서 400ms대로 약 97% 감소하였습니다.

동시성 처리가 필요한 상황들은 다양하므로 각 상황에 맞는 방법을 적용하는 것이 중요합니다. 저희가 적용했던 특정 업데이트 조건을 통한 동시성 처리 방법은 안심번호 매핑 기능과 같이 한 번의 업데이트가 필요한 상황에 적용하시는 것을 추천해 드립니다.

숨고에는 앞서 설명한 안심번호 기능 외에도, 캐시나 견적 발송처럼 실시간으로 동작하는 기능들이 많습니다. 이러한 기능들은 동시성 처리를 위해 Lock을 사용하고 있어, Slow Query가 발생할 가능성이 큽니다. 앞으로 이러한 문제를 지속적으로 해결해 나가는 것이 목표입니다.

이 글을 읽는 분들이 동시성 처리 문제를 해결하실 때 도움이 되시길 바라며, 제가 소개한 방법 이외에도 더 좋은 방법이 있다면 같이 공유하고 소통하면 좋겠습니다.

긴 글 읽어주셔서 감사드립니다.