Backend Developer

2 분 소요

단순한 UPDATE 쿼리였다. sort_no를 1 증가시키고, sort_no_desc를 그에 맞춰 업데이트하는 것.
근데 결과값이 예상과 달랐다. sort_no_desc가 -3이 나왔다. 왜 -2가 아니라 -3일까?

이 글은 MySQL UPDATE문의 특이한 동작 방식과 그로 인해 겪은 실무 경험을 공유한다.

문제 상황

우리 팀은 상품 정렬 순서를 관리하는 테이블을 운영하고 있었다.
오름차순과 내림차순 정렬을 빠르게 처리하기 위해 두 개의 컬럼을 사용했다.

CREATE TABLE prod (
    id BIGINT PRIMARY KEY,
    sort_no INT,           -- 오름차순 정렬용
    sort_no_desc INT,      -- 내림차순 정렬용 (sort_no * -1)
);

특정 범위의 상품들의 정렬 번호를 일괄적으로 증가시켜야 하는 요구사항이 들어왔다.
TypeORM으로 간단하게 구현했다.

첫 번째 시도 - TypeORM Raw Expression

  await queryRunner.manager
    .createQueryBuilder()
    .set({
      sortNo: () => `sort_no + ${value}`,
      sortNoDesc: () => `(sort_no + ${value}) * -1 `,
    })
    .where(/* 조건 */)
    .execute();

예상했던 결과는 이거였다:

  • 현재 sort_no = 1, sort_no_desc = -1
  • 증가값 value = 1
  • 기대 결과: sort_no = 2, sort_no_desc = -2

실제 결과는 이거였다:

  • 실제 결과: sort_no = 2, sort_no_desc = -3

왜 -3이 나왔을까?

MySQL UPDATE문의 특이한 동작 방식

생성된 SQL을 확인해보니 이런 쿼리가 실행되고 있었다.

UPDATE `prod` 
SET 
  `sort_no` = `sort_no` + 1,
  `sort_no_desc` = (`sort_no` + 1) * -1
WHERE `site_code` = ? AND `sort_no` <= ? AND `sort_no` >= ? AND `deleted` = ?

언뜻 보면 문제없어 보인다. 하지만 MySQL은 다른 데이터베이스와 다르게 동작한다.

핵심 원인 - MySQL의 순차적 실행

MySQL 공식 문서를 찾아보니 답이 있었다.

“Single-table UPDATE assignments are generally evaluated from left to right.”

MySQL은 UPDATE문의 SET 절을 왼쪽에서 오른쪽으로 순차적으로 실행한다. 그리고 더 중요한 건, 이미 변경된 값을 다음 할당에서 참조한다는 것이다.

실행 과정을 다시 보자:

  1. sort_no = 1 + 1 = 2 (sort_no가 2로 변경됨)
  2. sort_no_desc = ( 2 + 1 ) * -1 = -3 (변경된 sort_no 값 2를 사용)

아, 그래서 -3이 나왔구나.

표준 SQL과의 차이

그런데 찾아보니까 이 동작이 표준 SQL과 다르다는 점이다.

PostgreSQL 공식 문서에 따르면, PostgreSQL 같은 데이터베이스는 모든 SET 절을 병렬로 실행하고, 항상 원본 값을 참조한다. 그래서 같은 쿼리를 PostgreSQL에서 실행하면 정상적으로 sort_no_desc = -2가 나온다.

-- MySQL: 두 컬럼이 모두 col2 값을 가지게 됨
UPDATE test_table SET col1 = col2, col2 = col1;

-- PostgreSQL: 정상적으로 값이 교환됨
UPDATE test_table SET col1 = col2, col2 = col1;

해결 방법

첫 시도 - 쿼리를 두 단계로 나눠 해결

// 1단계: sort_no만 업데이트
await queryRunner.manager
  .createQueryBuilder()
  .set({ sortNo: () => `sort_no + ${value}` })
  .where(/* 조건 */)
  .execute();

// 2단계: sort_no_desc 업데이트
await queryRunner.manager
  .createQueryBuilder()
  .set({ sortNoDesc: () => `(sort_no + ${value}) * -1` })
  .where(/* 조건 */)
  .execute();

두 번의 쿼리가 실행되는 단점이 있지만, 100% 예측 가능한 결과를 보장한다.
처음은 이 구조로 처리하려고했으나, 팀원분들과 이 문제를 논의하다 근본적인 해결법을 찾았다.

좀 더 근본적인 해결법 - (이랬으면 애초에 문제도 없었겠는걸?..)

sortNoDesc는 무조건 (sortNo * -1)를 보장한다.
따라서 아래처럼 하면 다중 컬럼 업데이트시 MYSQL이 순차 SET반영을 한다 하더라도 안전한다.


await queryRunner.manager
  .createQueryBuilder()
  .set({
    sortNo: () => `sort_no + ${value}`,
    sortNoDesc: () => `sort_no_desc + ${value * -1}`,
  })
  .where(/* 조건 */)
  .execute();

즉, 문제는 다중 컬럼 업데이트보다 직접 자기 참조가 아닌 (sortNo값을 활용하여 sortNo에 할당)
간접 자기 참조(sortNo를 활용하여 sortNoDesc에 할당)하는 방식이 위험성이 있었던것에 가깝다고 볼 수 있다.

마무리하며

이번 경험을 통해 몇 가지 중요한 교훈을 얻었다.

첫째, 데이터베이스마다 고유한 특성이 있다. MySQL의 UPDATE 순차 실행은 표준 SQL과 다르고, 이를 모르면 예상치 못한 버그를 만들 수 있다.

둘째, 생성되는 SQL을 항상 확인하자. ORM이 어떤 SQL을 생성하는지 모른다면, 언젠가는 문제가 생긴다.

개인적으로는 이런 함정에 빠져서 당황했지만, 덕분에 MySQL을 더 깊이 이해하게 되었다. 앞으로는 데이터베이스별 특성을 더 꼼꼼히 확인하고 테스트해야겠다.

혹시 비슷한 문제를 겪고 있다면, 이 글이 도움이 되길 바란다.

댓글남기기

참고