MySQL의 SELECT ... FOR UPDATE와 TypeORM queryRunner를 이용한 SELECT ... FOR UPDATE 쿼리 실행

MVCC와 Non-Locking Consistent Read

MySQL의 InnoDB 스토리지 엔진은 MVCC를 기반으로 동시성 제어를 처리한다. Non-Locking Consistent Read는 MVCC의 핵심 기능으로, 트랜잭션에서 SELECT 쿼리를 실행할 때 별도의 잠금 없이 데이터 읽기가 가능하다. 이는 동시성 성능을 높이고 여러 사용자가 동시에 데이터를 읽고 쓸 수 있도록 돕는다.

• 일반적인 SELECT 쿼리는 Non-Locking Consistent Read 방식으로 실행되어, 트랜잭션이 시작된 시점 이전에 커밋된 데이터를 읽는다.
• 변경 작업이 일어나면, InnoDB는 변경 전 데이터를 Undo 로그에 백업하고, 읽기 요청이 오면 이 Undo 영역에서 데이터를 반환하여 트랜잭션 일관성을 유지한다.

 

트랜잭션 격리 수준과 일관성

• Read Committed: 트랜잭션 중 가장 최근에 커밋된 데이터를 반환한다. 각 SELECT 쿼리마다 최신 커밋된 데이터를 읽는다.
• Repeatable Read: 트랜잭션이 시작된 시점 직전에 커밋된 데이터를 반환한다. 같은 트랜잭션 내에서 여러 번 데이터를 읽어도 동일한 결과를 반환한다. 이는 Undo 영역의 데이터를 사용하여 일관성을 유지한다.

 

SELECT ... FOR UPDATE

SELECT ... FOR UPDATE는 트랜잭션 내에서 특정 레코드에 Exclusive Lock (X-lock)을 설정하여, 다른 트랜잭션이 해당 레코드에 대한 쓰기 작업을 하지 못하도록 막는다.

• SELECT ... FOR UPDATE는 항상 최신 커밋된 데이터를 반환한다. 이는 트랜잭션의 격리 수준과 상관없이 적용된다.
• Exclusive Lock이 설정된 레코드는 다른 트랜잭션에서 읽기와 쓰기 모두 불가능하며, 잠금을 해제할 때까지 대기 상태가 된다.
• 이 구문은 반드시 트랜잭션 내에서 실행되어야 효과가 있다. 트랜잭션이 종료될 때까지 해당 레코드는 잠긴 상태로 유지된다.

 

SELECT ... FOR SHARE

SELECT ... FOR SHARE는 레코드에 Shared Lock (S-lock)을 설정한다.

• SELECT ... FOR SHARE는 다른 트랜잭션이 해당 레코드를 읽는 것은 허용하지만, 쓰기 작업은 불가능하게 만든다.
• 이 구문은 동시에 여러 트랜잭션이 데이터를 읽을 수 있도록 하되, 해당 데이터에 변경을 가하는 것을 막고자 할 때 유용하다.

 

성능과 동시성 문제

SELECT ... FOR UPDATE나 SELECT ... FOR SHARE는 데이터 일관성을 보장하는 데 매우 유용하지만, 성능 측면에서는 신중히 사용해야 한다.

• Exclusive Lock이 걸리면 다른 트랜잭션에서 해당 레코드에 접근하지 못하고 대기하게 된다. 특히, 잠금 상태에서 외부 서버나 Redis 큐를 통해 데이터를 처리하는 경우, 외부 처리 시간이 오래 걸리면 MySQL의 트랜잭션이 계속 Active 상태로 유지되며, 잠금이 걸린 레코드에 다른 트랜잭션이 접근할 수 없는 상태가 오래 지속될 수 있다. 이는 데드락 또는 성능 저하로 이어질 수 있다.
• 잠금 충돌을 최소화하기 위해, 업데이트 대상이 명확할 때만 SELECT ... FOR UPDATE를 사용하도록 WHERE 조건을 신중하게 설정하는 것이 중요하다.

SELECT ... FOR UPDATE는 성능을 저하시킬 수 있으므로, 다음과 같은 상황에서는 이를 대신할 방법을 고려할 수 있다.

• 업데이트가 필요한 경우에만 잠금을 거는 전략
  • 트랜잭션 내에서 데이터를 읽을 때 반드시 SELECT ... FOR UPDATE가 필요하지 않다면, 일반 SELECT 구문을 사용하고 실제 업데이트 시점에 UPDATE 구문에서 자동으로 잠금을 거는 방식으로 성능을 최적화할 수 있다.
• 격리 수준 및 로그 형식 설정
  • MySQL에서는 트랜잭션 격리 수준을 READ-COMMITTED로 설정하고, 바이너리 로그 형식을 MIXED나 ROW로 설정하는 것이 성능 최적화에 도움이 될 수 있다. 이는 불필요한 잠금 충돌을 줄이고 동시성 성능을 향상시킨다.

 

TypeORM queryRunner를 이용한 SELECT ... FOR UPDATE 쿼리 실행

수업 예약 및 정산 관련 api를 만들며 정산 과정에서 동시성 문제를 막기 위해 select ... for update 구문을 사용해 보았다.

사용한 프레임워크는 NestJS이며, 아래는 포인트 정산 api의 Service 부분 예시이다.

참고로 TypeORM의 queryRunner는 트랜잭션 단위로 데이터베이스 작업을 수행할 수 있는 객체이다.

import { HttpException, HttpStatus, Injectable } from '@nestjs/common';
import { DataSource } from 'typeorm';
import { Teacher } from 'src/entities/users.entity';

@Injectable()
export class TeachersService {
  constructor(
    private readonly dataSource: DataSource,
  ) {}

  async pointPayout(teacherId: number, point: number): Promise<void> {
    const queryRunner = this.dataSource.createQueryRunner(); // queryRunner 생성
    await queryRunner.connect(); // 데이터베이스와 연결 설정
    await queryRunner.startTransaction(); // 트랜잭션 시작

    try {
      const teacher = await queryRunner.manager.findOne(Teacher, {
        where: { id: teacherId },
        lock: { mode: 'pessimistic_write' }, // SELECT ... FOR UPDATE
      });

      if (!teacher)
        throw new HttpException(
          '선생님을 찾을 수 없습니다.',
          HttpStatus.NOT_FOUND,
        );

      if (teacher.point < point)
        throw new HttpException('포인트가 부족합니다.', HttpStatus.BAD_REQUEST);

      await queryRunner.manager.decrement(
        Teacher,
        { id: teacherId },
        'point',
        point,
      );

      await queryRunner.commitTransaction(); // 트랜잭션 내 모든 작업이 성공적으로 완료되면 트랜잭션을 커밋
      // 커밋이 되어야 모든 작업이 데이터베이스에 영구적으로 적용됨
    } catch (e) {
      await queryRunner.rollbackTransaction(); // 트랜잭션 중 예외가 발생하면 트랜잭션을 롤백
      throw e;
    } finally {
      await queryRunner.release(); // 커밋 또는 롤백이 이뤄지면 마지막으로 데이터베이스 연결을 해제
    }
  }
}

 

 

 

선생님의 포인트 정보를 확인하기 위해 findOne을 할 때, lock 옵션에 비관적 잠금 모드인 pessimistic_write를 사용하여 SELECT ... FOR UPDATE 구문을 실행하였다

SELECT ... FOR UPDATE 구문이 실행되면서 현재 트랜잭션이 완료될 때까지 해당 행을 잠그고, 다른 트랜잭션이 이 데이터를 수정하거나 삭제하는 것을 방지한다

즉, 포인트 정보를 조회하는 동안 다른 트랜잭션이 동일한 teacherId를 대상으로 포인트를 수정하지 못한다

그리고 포인트 차감을 위한 decrement 메서드도 트랜잭션 내에서 실행되기 때문에, 트랜잭션이 성공적으로 커밋되기 전까지는 실제로 데이터베이스에 반영되지 않는다

query: START TRANSACTION
query: SELECT "Teacher"."id" AS "Teacher_id", "Teacher"."createdAt" AS "Teacher_createdAt", "Teacher"."updatedAt" AS "Teacher_updatedAt", "Teacher"."deletedAt" AS "Teacher_deletedAt", "Teacher"."username" AS "Teacher_username", "Teacher"."email" AS "Teacher_email", "Teacher"."password" AS "Teacher_password", "Teacher"."point" AS "Teacher_point", "Teacher"."role" AS "Teacher_role" FROM "users" "Teacher" WHERE ( (("Teacher"."id" = $1)) ) AND ( "Teacher"."deletedAt" IS NULL ) AND ( "Teacher"."role" IN ($2) ) LIMIT 1 FOR UPDATE -- PARAMETERS: [1,1]
query: UPDATE "users" SET "point" = "point" - 10, "updatedAt" = CURRENT_TIMESTAMP WHERE ( "id" = $1 ) AND ( "role" IN ($2) ) -- PARAMETERS: [1,1]
query: COMMIT

 

쿼리 실행 로그를 확인해 보면, 두 번째 query에 SELECT ... FOR UPDATE 구문이 실행된 걸 확인할 수 있다.

 

 

 

 

 

SELECT ... FOR UPDATE 구문이 실행될 때 일반 SELECT 구문을 실행하거나 변경하는 등의 테스트도 해보고 싶은데 이런 테스트는 어떻게 할 수 있으려나🤔

찾아보고 괜찮은 방법이 있다면 다음 포스팅으로 돌아오겠습니다...!