SQL 사용 시 주의할 점 : 읽기-수정-쓰기

RDBMS로 개발할 때 버그를 만들기 쉬운 사용 패턴을 살펴본다. RDBMS가 안전한 데이터 연산 기능을 산더미처럼 제공해도, 개발자가 잘못 구현하면 오류가 발생하는 경우다. 재현 케이스를 만들기도 어렵다. 미리 알고 있으면 원인을 알 수 없는 오류를 미연에 방지한다. 외부 시스템(DB 등)을 자세히 이해하는 것이 단단한(robust) 시스템을 구현하기 위해 중요하다는 작은 교훈도 얻을 수 있다.

예시

데이터 저장소 동시성에 대해 이야기할 때 지겹도록 등장하는 은행 잔고에 대한 예로 시작해 보자.

사용자 A에게 X 코인 입금

A의 잔고 관리 구현을 자연어로 풀어 쓰면

  1. A의 잔고를 DB 에서 가져와 애플리케이션 변수 b에 저장. (읽기)
  2. 변수 b에 X를 더한 값을 다시 저장. (수정)
  3. DB 에 변수 b 값을 쓴다. (쓰기)

이를 SQL 및 애플리케이션 로직으로 다시 쓰면 아래와 같을 것이다. (애플리케이션 로직은 SQL 주석으로 표시하였다. <...> 는 애플리케이션 로직에서 SQL 문을 생성할 때 변수 값으로 치환해 주어야 하는 부분이다)

/* b = \ */
SELECT amount
  FROM balance
 WHERE user_id = <A.id&gt;; 

/* b = b + X */

UPDATE balance
   SET amount = <b&gt;
 WHERE user_id = <A.id&gt;;

이 단순한 로직으로 입금 요청이 여러 곳에서 동시(concurrent)에 발생하면, 의도대로 동작하지 않을 수도 있다. 이를 설명하기 위해 A 의 현재 잔고(balance)가 0 코인이라고 가정하자. 이 상태에서 두 곳으로 부터 동시에 A에게 각각 300 코인, 700 코인을 입금하려고 한다. 입금 후 A 의 잔고는 당연히 300 + 700 = 1000이 되어야 한다.

그러나 만약 두 입금 요청이 아래와 같은 순서로 실행되면 기대했던 것과는 다른 결과가 나온다.

300 코인 입금 요청 (T1)700 코인 입금 요청 (T2)
/* b = */ SELECT ...; : b = 0
/* b = */ SELECT ...; : b = 0
/* b = b + 300 */ : b = 300
/* b = b + 700 */ : b = 700
UPDATE ...; : DB 상 A 잔고 = 300
UPDATE ...; : DB 상 A 잔고 = 700

DB 의 A 잔고는 최종적으로 700 코인이다. 300 코인 입금 분이 사라져 버렸다!

원자성(atomicity)

이런 결과가 발생한 원인은 입금 로직이 원자성(atomic)이 없기 때문이다. 원자성은 간단히 말하자면 해당 연산이 쪼개 질수 없다는 의미이다. 하나의 논리적인 단위로 실행되면 정확한 결과가 나온다. 앞 서 예로 든 로직은 겉보기에도 3 단계로 나누어져 실행된다. 예를 들어 우연히 위의 예가 아래와 같은 순서로 실행되었다면 원했던 결과가 나왔을 것이다.

300 코인 입금 요청 (T1)700 코인 입금 요청 (T2)
/* b = */ SELECT ...; : b = 0
/* b = b + 300 */ : b = 300
UPDATE ...; : DB 상 A 잔고 = 300
/* b = */ SELECT ...; : b = 300
/* b = b + 700 */ : b = 1000
UPDATE ...; : DB 상 A 잔고 = 1000

그러나 이런 순서대로 실행된다는 보장이 전혀 없다. 얼마든지 제일 처음 제시한 실행 순서처럼 뒤섞일 수 있기 때문에 문제가 발생한다.

Lock

쉽게 떠올릴 수 있는 원자성 확보 기법은 공유 메모리를 이용한 락(lock, mutex)일 것이다. 입금 로직을 아래처럼 변경하면 문제 없이 동작한다.

/* l = lock() */

// 기존 입금 로직

/* l.release() */

이제 다시 위의 예를 실행해 보자.

300 코인 입금 요청 (T1)700 코인 입금 요청 (T2)
/* l = lock() */
: 아무도 lock 을 잡고 있지 않으므로 통과
/* b = */ SELECT ...; : b = 0
ㅣ = lock()
: T1이 l 을 잡고 있으므로, 풀릴 때까지 대기(block)
/* b = b + 300 */ : b = 300
UPDATE ...; : DB 상 A 잔고 = 300
/* l.release() */
: lock이 풀렸기 때문에, lock 함수가 반환되어 실행 재개
/* b = */ SELECT ...; : b = 300
/* b = b + 700 */ : b = 1000
UPDATE ...; : DB 상 A 잔고 = 1000
/* l.release() */

lock이 읽기 – 수정 – 쓰기 연산에 대한 원자성을 보장해 주기 때문에 기대했던대로 동작한다.

이렇게 락을 통해 문제를 간단히 해결할 수 있을까? 일반적으로 다수의 애플리케이션 서버가 하나의 RDBMS를 공유해서 사용도록 구성한다. 공유 메모리를 이용한 락은 서버 한 대 안에서 실행되는 서로 다른 프로세스 간에서는 사용할 수 있지만, 여러 서버에서 실행되는 서로 다른 프로세스 간에는 사용이 불가능하다. 오히려 RDBMS를 여러 애플리케이션 서버간 동기화 및 원자성을 확보하기 위한 수단으로 사용하는 경우가 많다.

트랜잭션(transaction)

일반적으로 RDBMS에서 원자성을 보장하기 위해 트랜잭션을 사용한다. 여러 DB 연산을 하나의 논리적인 단위로 묶을 수 있고, 그 연산들을 한 번에 반영하거나(commit), 불가능하다면 전부 없었던 일로 무효화(rollback)하는 방식으로 동작한다. 해당 루틴을 트랜잭션으로 묶으면 입금 루틴은 아래처럼 된다.

BEGIN;

// 기존 입금 로직

COMMIT;

연산들을 트랜잭션으로 묶는 것만으로 문제를 해결할 수 있을까? 이 예제부터는 RDBMS 종류와 설정에 따라서 동작이 달라질 수 있다. 특히 isolation level 설정에 따라 동작이 크게 달라진다. 여기서는 MySQL 8(InnoDB) 기본 설정(isolation level: REPEATABLE READ)을 가정하고 설명한다.

300 코인 입금 요청 (T1)700 코인 입금 요청 (T2)
BEGIN
/* b = */ SELECT ...; : b = 0
BEGIN
/* b = */ SELECT ...; : b = 0
/* b = b + 300 */ : b = 300
UPDATE ...;
: 최종적으로 COMMIT 이후에 DB에 반영된다.
/* b = b + 700 */ : b = 700
UPDATE ...;
: T1에서 트랜잭션 도중에 UPDATE를 실행하였기
: 때문에, 해당 트랜잭션이 종료될 때까지
: 대기(block)한다.
COMMIT; : DB 상 A 잔고 = 300
: 대기중이던 트랜잭션이 commit 되었으므로 진행.
: 최종적으로 COMMIT 이후에 DB에 반영된다.

COMMIT; : DB 상 A 잔고 = 700

RDBMS 트랜잭션 기능을 사용했음에도 여전히 결과가 이상하다.

NoSQL 열풍이 불었던 시기에도 결제 DB만큼은 RDBMS 를 사용해야 한다는 말이 공공연했었다. 그 정도로 트랜잭션은 데이터 처리의 정확성을 확보해 주는 RDBMS 만의 큰 장점 중 하나이다 . 그러나 단순히 트랜잭션으로 묶는다고 해서 문제가 쉽게 풀리는 것은 아니다.

읽기 – 수정 – 쓰기 동작의 원자성

트랜잭션을 이용해도 문제를 해결할 수 없는 이유는 RDBMS의 트랜잭션은 DB 내 연산에 대한 원자성만을 보장하기 때문이다. 그런데 예로 든 입금 로직은 DB 내 연산 이외에 애플리케이션에서 연산을 하고 있고, 해당 결과를 DB에 반영한다. 애플리케이션 연산은 DB 외부의 일이기 때문에 DB 내부 트랜잭션만으로는 해결이 불가능하다.

읽기와 쓰기는 DB에서 이루어지기 때문에 원자적이다. 수정도 평범하게 지역 변수로 업데이트 한다면 원자적이다. 그러나 읽기 – 수정 – 쓰기 3개를 합친 연산이 논리적인 한 단위로 동작하지 않는게 문제이다. 이것을 하나의 논리적인 단위로 묶을 수 있다면 문제를 해결할 수 있다.

해결책 1 – 단일 SQL 쿼리로 변환

예제처럼 연산이 간단한 경우 SQL 자체의 연산 기능을 사용해 쿼리 하나로 묶는 것이 가장 간단한 해결책이다.

UPDATE balance SET amount = amount + <X&gt; WHERE user_id = <A.id&gt;;

데이터를 읽고 수정하고 쓰는 과정을 하나의 쿼리로 합쳐 원자성을 확보한다. 단일 SQL 문은 RDBMS 상에서 원자성을 가지므로 위에서 언급한 동시성 문제가 발생하지 않는다.

300 코인 입금 요청 (T1)700 코인 입금 요청 (T2)
UPDATE ... + 300 ...;
: DB 상 A 잔고 = 300
UPDATE ... + 700 ...;
: DB 상 A 잔고 = 1000

추가로 설명이 필요 없을 정도로 간단하게 해결된다.

이렇게 간단하게 해결할 수 있음에도, 굳이 컬럼까지 써 가며 이 문제를 다루는 이유가 더 있다. 여기서 든 입금 예제는 문제제기를 위해 최대한 간단하게 만들었다. 대부분 SQL 문 한 줄로 해결할 수 없는 경우가 더 많을 것이다.

  • 입금하기 전 해당 유저가 존재하는지 확인 후 입금 진행 : SQL SELECT 로 데이터를 가져와 애플리케이션에서 확인해야 함
  • 정밀도나 크기 때문에 DB 에 VARCHAR 등으로 숫자가 아닌 타입으로 저장하는 경우 : SQL SELECT 문으로 데이터를 가져와 애플리케이션에서 변환해 계산해야 함
  • RDBMS 가 지원하지 않는 연산이 필요한 경우 : SQL SELECT 문으로 데이터를 가져와 애플리케이션에서 처리해야 함

해결책 2 – Locking Read

MySQL(InnoDB) 기본 설정(isolation level: REPEATABLE READ)에서 트랜잭션 안에서 SELECT 같은 읽기 연산은 DB 내부 락을 잡지 않는다. 따라서 일반적인 SELECT 는 대기(block) 없이 바로 실행된다(
Consistent Nonlocking Reads). 반면 UPDATE 같은 쓰기 연산은 수정한 로우(row)들에 대해 트랜잭션이 끝날 때까지 다른 트랜잭션 실행을 방지하는 배타적인 락을 건다. 예를 들어 트랜잭션 T1에서 로우 R1을 업데이트했다고 하자. T1이 끝날 때까지, T1 이외의 다른 트랜잭션들이 R1을 UPDATE하면 대기(block)하게 된다. 로우 수준에서 락을 거는 방식(row level locking)을 이용해 RDBMS 내부적으로 원자성을 지키는 것이다.

쓰기 시에 사용되는 배타적인 로우 기반 락을 SELECT 시에도 잡도록 SQL 수준에서 지시할 수 있다. SELECT ... FOR UPDATE 구문을 사용하면 된다. 해당 SQL 문에 의해 SELECT 되는 로우들은 UPDATE 시 사용되는 락과 동일한 배타적인 락을 잡도록 한다(Locking Read).

우리가 사용한 입금 로직에서 잔고를 가져올 때 일반적인 non-locking read 대신 locking read 를 사용해보자. 트랜잭션이 끝날 때까지 해당 row에 대해서는 다른 트랜잭션에서 읽기와 쓰기 모두 불가능해진다. 원자성을 지킬 수 있다. 입금 로직을 SELECT ... FOR UPDATE구문을 사용하도록 변경해 보자. SELECT 문에 FOR UPDATE 추가한 것만 달라졌다.

 BEGIN;

/* b = \ */
SELECT amount
  FROM balance
 WHERE user_id = <A.id&gt;
   FOR UPDATE; 

/* b = b + X */

UPDATE balance
   SET amount = <b&gt;
 WHERE user_id = <A.id&gt;;

COMMIT;

이제 위와 동일한 방식으로 실행해 보자.

300 코 입금 요청 (T1)700 코인 입금 요청 (T2)
BEGIN
/* b = */ SELECT ... FOR UPDATE;
: b = 0
: SELECT 한 row 에 배타적 lock 을 잡는다.
BEGIN
/* b = */ SELECT ... FOR UPDATE;
: T1에서 SELECT한 row에 배타적 lock 을 잡으려고
: 시도. 이미 T1이 잡고 있기 때문에, T1이 끝날 때 까지
: 대기 (block)
/* b = b + 300 */ : b = 300
UPDATE ...;
COMMIT;: DB 상 A 잔고 = 300
: T1이 종료되면서 lock을 release하므로 실행 재개.
: b = 300
/* b = b + 700 */ : b = 700
UPDATE ...;
COMMIT; : DB 상 A 잔고 1000

입금 로직이 기대했던 대로 동작하는 것을 알 수 있다.

마치며

RDBMS 를 사용할 때 동시성으로 인해 발생하는 문제와 그 해결책을 알아 보았다. DB에서 읽은 값을 애플리케이션에서 수정하고 다시 DB에 쓰는 경우 단일 SQL 쿼리를 사용하거나, SELECT ... FOR UPDATE 를 이용하면 문제를 해결할 수 있다.

동시성 관련 버그는 테스트를 통해 발견하기 어렵다. 쿼리 실행이 특정한 순서에 의해 실행되는 경우에만 발견할 수 있기 때문이다. 특히 단순한 unit test 같은 방법으로 문제 되는 케이스를 생성하는 것이 쉽지 않다.

문제점과 해결책만 간결하게 나열하지 않고, 어디서 어떻게 문제가 되는지 순차적으로 보여준 것은, 필자가 해당 내용을 처음 인지하고, 파고 들었을 때 얻은 작은 깨달음을 조금이나마 전달하고 싶었기 때문이다.

  • 자신이 사용하고 있는 외부 시스템(e.g. RDBMS) 이 지원하는 기능과 한계를 정확하게 파악하고 사용해야 한다. “RDBMS 트랜잭션 기능을 쓰면 데이터 처리 문제는 신경 쓸 필요 없다” 와 같은 태도에 대한 주의가 필요하다.
  • 테스트 케이스도 중요하지만 로직을 자신의 머리 속에서 실행했을 때 미심쩍은 부분이 없어야 한다. 테스트 케이스에서 결과가 올바르다고, 그냥 넘어가면 차후 발견하기 힘든 버그를 만들 수도 있다.

참고

답글 남기기

댓글을 게시하려면 다음의 방법 중 하나를 사용하여 로그인 하세요:

WordPress.com 로고

WordPress.com의 계정을 사용하여 댓글을 남깁니다. 로그아웃 /  변경 )

Google photo

Google의 계정을 사용하여 댓글을 남깁니다. 로그아웃 /  변경 )

Twitter 사진

Twitter의 계정을 사용하여 댓글을 남깁니다. 로그아웃 /  변경 )

Facebook 사진

Facebook의 계정을 사용하여 댓글을 남깁니다. 로그아웃 /  변경 )

%s에 연결하는 중

This site uses Akismet to reduce spam. Learn how your comment data is processed.