Ray`s Tech Lab

들어가며

BE 개발자라면 DB를 공부하면서 트랜잭션 격리 수준(Transaction Isolation Level)을 접한 적이 있을 겁니다. 저 역시 취준 시절 기술 면접을 준비하면서, 그리고 DB를 공부하면서 트랜잭션 격리 수준을 공부한 적이 있는데요. 사실 공부한 적만 있지 사이드 프로젝트 또는 실무에서 트랜잭션 격리 수준으로 인한 문제를 마주쳤던 적은 없었습니다. 하지만 이번에 사내에서 낙관적 락으로 동시성 문제를 방지하는 기능을 개발하는 과정에서 트랜잭션 격리 수준에 대해 어설프게 알고 있던 덕에 뜻밖의 곤욕을 치르게 됐는데요. 아무래도 저처럼 이론으로만 트랜잭션 격리 수준을 공부했던 분들에게 도움이 될 수도 있는 내용인 것 같아 공유하고자 이 글을 쓰게 됐습니다.

※ 하기 내용은 트랜잭션 격리 수준, 그 중에서도 REPEATABLE_READ에 대한 지식이 있음을 바탕으로 작성했습니다.

※ 보안 문제 등으로 제가 담당한 시스템의 정확한 명칭과 세부 업무, 기능은 언급하지 않고자 했습니다. 이 점 양해 부탁드립니다.

개발 과정 & 고민했던 점

제가 이번에 개발한 기능은 "통보서 중복 작성 방지" 기능이었습니다. 기존 통보서 작성 프로세스는 다음과 같았습니다.

1. 통보서를 보낼 업무(BUSINESS_INFO 테이블의 레코드)에 대해 특정 프로세스 진행
2. 고객에게 보낼 통보서 내용을 DB에 INSERT ( = 통보서 작성)
3. 해당 업무의 통보상태를 NOT_REGISTER(통보 내용 미작성)에서 REGISTER(통보 내용 작성)으로 UPDATE

해당 비즈니스 로직은 코드 상에서는 다음처럼 구현되어 있었습니다.

@Transactional
public void processNotice(String bizSeqNbr) {
    // 통보서를 보낼 업무 정보 조회
    BusinessInfo businessInfo = readBusinessInfo(bizSeqNbr);

    // .. 생략 (특정 프로세스 진행)

    // 고객에게 보낼 통보서 내용 INSERT
    insertNotice(notice);

    // 해당 업무의 통보상태를 UPDATE
    updateBusinessNoticeStatus(bizSeqNbr, NoticeStatus.REGISTER);
}

-- updateBusinessNoticeStatus

UPDATE BUSINESS_INFO
SET NOTICE_STATUS    = #{BUSINESS_NOTICE_STATUS}
  , LAST_CHANGE_ID   = #{EMPNO}
  , LAST_CHANGE_DTTM = NOW()
WHERE BUSINESS_SEQUENCE_NUMBER = #{BIZ_SEQ_NBR};

통보서 작성 후 해당 업무의 통보상태(BUSINESS_INFO.NOTICE_STATUS)를 REGISTER로 갱신하므로, 중복 작성을 방지하는 가장 쉬운 방법은 해당 업무의 통보상태 값이 REGISTER라면  통보서를 작성하지 않도록 하는 것이라고 생각했습니다. 업무 정보를 조회하는 시점부터 비관적 락을 적용하는 방법도 있었으나, 이 시스템은 소수의 직원이 사용하는 사내 시스템이었기 때문에 중복 작성이 흔하게 일어나진 않을 거라 판단하여 다음과 같은 낙관적 락 형태를 적용한 쿼리를 활용하기로 했습니다. 

-- updateBusinessNoticeStatusToRegister

UPDATE BUSINESS_INFO
SET NOTICE_STATUS    = #{BUSINESS_NOTICE_STATUS}
  , LAST_CHANGE_ID   = #{EMPNO}
  , LAST_CHANGE_DTTM = NOW()
WHERE BUSINESS_SEQUENCE_NUMBER = #{BIZ_SEQ_NBR}
AND NOTICE_STATUS = 'NOT_REGISTER'; 

-- version 등의 컬럼을 추가할 수 없던 상황이라.. 통보서 작성 전의 통보상태는 NOT_REGISTER임을 활용
-- 갱신된 레코드가 0개라면 예외를 일으켜 롤백

그러나 사용 중인 DB(=MySQL)은 트랜잭션 격리 수준으로 디폴트인 REPEATABLE_READ를 사용중이었습니다. 동일 트랜잭션에서는 여러 번 SELECT를 해도 항상 같은 결과가 나오도록 보장하는 격리 수준으로, 트랜잭션 시작 후 최초로 읽기 시작한 시점의 스냅샷을 참조하는 원리인데요. 저는 이 스냅샷을 UPDATE 시에도 동일하게 활용할 것이라고 생각해서, 다음처럼 낙관적 락을 적용해도 통보서를 중복으로 작성되는 문제가 생길 수 있다는 생각이 들었습니다.

하지만 여러 번의 테스트 결과, 중복 갱신은 발생하지 않았습니다. 벌어지지 않을 일에 대해 "헉 이거 문제 생기는 거 아냐?"라고 생각하고 있던 거죠.. 근데 왜 발생하지 않았을까요? 저는 트랜잭션 안에서 SELECT와 UPDATE가 같은 시점의 데이터(스냅샷)를 바라보고 수행된다고 생각했지만, 실제로는 SELECT는 과거의 스냅샷을 바라보고 UPDATE는 현재를 바라보고 수행되기 때문이었습니다.

어설프게 알면 당하는 MySQL REPEATABLE_READ의 함정

이런 현상은 MySQL 공식 문서에서도 찾아볼 수 있었습니다.

A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by transactions that committed before that point in time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by earlier statements within the same transaction. This exception causes the following anomaly: If you update some rows in a table, a SELECT sees the latest version of the updated rows, but it might also see older versions of any rows. If other sessions simultaneously update the same table, the anomaly means that you might see the table in a state that never existed in the database.

• 일관된 읽기(Consistent Read)란 InnoDB가 MVCC을 사용해, 쿼리에 특정 시점의 DB 상태를 스냅샷으로 제공하는 것.
• 쿼리는 해당 시점 이전에 커밋된 트랜잭션의 변경분은 볼 수 있지만, 그 이후에 커밋되었거나 아직 커밋되지 않은 트랜잭션의 변경분은 볼 수 없다. (= REPEATABLE_READ의 원리)
• 단, 같은 트랜잭션 내에서 수행된 DML(INSERT, UPDATE 등)로 인한 변경분은 볼 수 있다.
• 이 때문에 아래와 같은 비일관성(anomaly) 이 발생 가능하다.
  1. 같은 트랜잭션 내에서 일부 레코드만 UPDATE한 후 SELECT를 실행하면, 업데이트된 레코드만 최신 버전으로 보인다.
  2. 여러 트랜잭션이 동시에 동일 테이블에 대해 업데이트를 하고 있다면, 트랜잭션 내에서 SELECT로 조회되는 결과는 실제 데이터베이스에 존재한 적 없던 상태를 보여줄 수도 있다. (트랜잭션 내에서는 동일 트랜잭션의 DML로 인한 변경분만 보이므로)

The snapshot of the database state applies to SELECT statements within a transaction, not necessarily to DML statements. If you insert or modify some rows and then commit that transaction, a DELETE or UPDATE statement issued from another concurrent REPEATABLE READ transaction could affect those just-committed rows, even though the session could not query them. If a transaction does update or delete rows committed by a different transaction, those changes do become visible to the current transaction.

• 스냅샷은 트랜잭션 내의 SELECT 문에만 적용되며, DML(INSERT, UPDATE 등)에도 적용되어야 하는 건 아니다.
• A 트랜잭션이 일부 레코드를 INSERT 또는 UPDATE한 뒤 커밋했을 때, 동시에 실행 중이던 REPEATABLE_READ 수준의 B 트랜잭션에서 실행되는 DELETE나 UPDATE 문은 A 트랜잭션이 방금 커밋한 행에도 영향을 줄 수 있다(B 트랜잭션에서는 그 레코드들을 SELECT로 조회할 수 없더라도)
  • >> UPDATE / DELETE는 특정 시점의 스냅샷이 아닌 현재를 바라보고 수행되기 때문으로 이해할 수 있습니다.
• 한 트랜잭션이 다른 트랜잭션에서 커밋한 레코드를 UPDATE하거나 DELETE할 경우에는 그로 인한 변경사항이 현재 트랜잭션 내에서 즉시 반영되어 보이게 된다. (하단 예제 이미지 참고)

이를 토대로 정리해보겠습니다. UPDATE / DELETE는 특정 시점의 스냅샷이 아닌 현재를 바라보고 수행됩니다. 따라서 하나의 트랜잭션 안에서 SELECT한 결과를 조건으로 활용해 UPDATE / DELETE 등을 해줄 때, 이런 트랜잭션이 여러 개가 동시에 실행된다면 내가 예상한 레코드가 UPDATE / DELETE되지 않거나 내가 예상하지 않은 레코드가 UPDATE / DELETE되는, 즉 예상치 못한 동작이 발생할 수 있습니다. 

결국 트랜잭션 격리 수준은 읽기와 관련된 문제들(Dirty Read, Non-Repeatable Read 등)들에 대한 해결책은 될 수 있으나, 쓰기에 대한 문제들(Lost Update 등)은 여전히 발생 가능합니다. 이를 위해 낙관적 락, 비관적 락(사실 끝나서 얘기하지만 저는 낙관적 동시성 제어 등으로 부르는게 더 맞다고 생각하는 편)을 적절히 적용해서 쓰기와 관련된 문제들을 해결할 수 있어야 하죠. 다행히 제가 이번 기능 개발에서 적용한 낙관적 락 형태의 쿼리는 시스템 특성상 해당 쿼리를 활용하는 코드가 제한되어 있어 동시성 문제가 나지 않았지만, 이번 삽질을 통해 낙관적 락 적용시에는 version등의 컬럼을 활용해 UPDATE 조건을 정확하고 유일하게 적용해야 쓰기 관련된 동시성 문제들을 깔끔하게 해결할 수 있다는 인사이트를 얻었습니다. 단순한 CRUD를 넘어 격리 수준 등을 토대로 나올 수 있는 복합적인 동시성 문제들까지 고려하는 습관을 잡아야 할 것 같습니다 :)

Reference

https://dev.mysql.com/doc/refman/8.4/en/innodb-consistent-read.html

https://stackoverflow.com/questions/59287861/repeatable-read-isolation-level-select-vs-update-where 

1. 트랜잭션이란?

데이터베이스 관리 시스템 내의 논리적인 작업 단위를 말합니다. 10,000원 단위로 돈을 내라고 하는데 5,000원을 내면 안되는 것처럼, 작업 단위라는 말은 이 작업이 반만 실행되는 일은 없어야 한다는 것, 다시 말하면  전체적으로 실행되거나 전혀 실행되지 않아야 한다는 것을 뜻합니다. 즉 트랜잭션은 데이터베이스가 처리해야 하는 일종의 작업 시나리오인데, 데이터베이스의 일관성과 신뢰성을 보장하는 All or Nothing의 시나리오를 말하는 것으로 이해할 수 있습니다.

• 일관성 : 트랜잭션 전후로 데이터베이스 내 데이터는 무결해야 한다는 것, 즉 데이터베이스 내 데이터들은 어떤 순간에도 정확하고 완전해야 하며 이 데이터를 신뢰할 수 있어야 한다는 것을 말합니다. 예를 들어 같은 DB에 있는 A계좌에서 B계좌로 돈을 보냈는데, A계좌에 출금 기록은 있지만 B계좌에 입금 기록이 없다면 이 DB는 일관된 상태로 볼 수 없습니다.(참고로 이 케이스는 명시되지 않은 제약 조건으로 볼 수 있으며, 반대로 명시적인 제약 조건으로는 외래 키 제약 등이 있습니다.)
• 신뢰성 : 데이터베이스 내 데이터가 안전하게 저장되고 정확하게 관리되어, 시스템 장애나 오류 상황에서도 데이터가 손실되거나 손상되지 않도록 보장하는 특성을 말합니다.

트랜잭션은 보통 다음과 같은 과정으로 진행됩니다.

1. 트랜잭션 시작(BEGIN)
2. 작업 수행(데이터 read/write 등)
3. 일련의 과정들이 문제없이 수행됐다면 COMMIT (작업 내용이 DB에 영구저장됨)
4. 중간에 문제가 발생했다면 ROLLBACK (트랜잭션 시작 전으로 되돌아감)

ex)

-- 트랜잭션 시작
BEGIN;

-- 작업 1
UPDATE people
SET name = 'Ray'
WHERE id = 1;

-- 작업 2
UPDATE people
SET lastname = 'Cho'
WHERE id = 10;

-- 일련의 작업이 문제없이 진행되면 DB에 반영
COMMIT;

2. 트랜잭션의 ACID란?

데이터베이스의 일관성과 신뢰성을 위해, 앞서 소개한 All or Nothing을 포함해 트랜잭션은 ACID라고 불리는 4가지 성질을 가져야 합니다.

1) Atomicity (원자성)

트랜잭션으로 처리할 작업 시나리오는 모두 처리되든지 아니면 하나도 처리되지 말아야 하든지에 대한 것으로, 앞서 설명한 All or Nothing에 해당합니다. COMMIT 실행 시 작업 내용을 DB에 쓰는 것과 ROLLBACK 실행시 DB를 되돌리는 것은 DBMS가 해주는 부분이므로, 개발자는 트랜잭션을 만들면서 언제 COMMIT을 하고 어떤 경우에 ROLLBACK을 할 지를 잘 챙겨야 합니다.

2) Consistency (일관성)

트랜잭션 처리 전후로 DB는 일관된 상태를 유지해야 한다는 것을 말합니다. 물론 뜯어보면 트랜잭션 처리 전과 후는 데이터베이스가 다른 상태이겠지만, 어쨌든 둘 다 똑같이 일관된 상태를 유지해야 한다는 뜻입니다. 외래 키 또는 CHECK문을 통한 명시적인 제약을 위반했는지는 DBMS가 COMMIT 전에 확인하고 알려주지만, Application 관점에서 트랜잭션이 일관성을 보장할 수 있도록 하기 위해서는 개발자가 트랜잭션을 잘 정의할 필요가 있습니다.

3) Isolation (독립성)

데이터베이스에서 여러 트랜잭션이 동시에 수행되어도 각 트랜잭션들이 서로 영향을 주지 않아야 함을 말합니다. Atomicity(원자성)는 어떤 트랜잭션을 처리하기 전까지 다른 트랜잭션을 처리하지 않는다는 개념이 아니라 단순히 "작업"이라는 관점에서 이 작업이 모두 수행되냐 아니냐의 개념입니다. 따라서 여러 트랜잭션이 동시에 실행될 수 있고, 이로 인한 동시성 문제들이 발생할 수 있기 때문에 트랜잭션들을 각각 따로따로 실행되는 것처럼 독립시켜 각 트랜잭션들이 서로 영향을 주지 않게 할 필요가 있습니다.

이를 만족하는 가장 쉽고 강력한 방법은 모든 트랜잭션을 직렬로 수행하는 것이나 이는 동시성이 감소하므로 DB 퍼포먼스에 심각한 악영향을 끼칩니다. 따라서 DBMS는 보통 여러 종류의 Isolation Level을 만들어 트랜잭션들이 서로 영향을 주는 정도를 설정할 수 있도록 제공합니다. 이를 통해 개발자는 어떤 Level로 트랜잭션을 동작시킬 지 설정할 수 있으며, 각 Level에서 발생 가능한 문제들을 인지하고 다룰 수 있어야 합니다. (아래에서 상세히 다뤄보겠습니다)

4) Durability (영속성)

트랜잭션이 완료되면 그 결과는 영구적으로 저장되어야 함을 말합니다. 시스템이 고장 나더라도 트랜잭션의 결과는 손실되지 않아야 하며, 데이터베이스에 영구적으로 반영되어야 함을 뜻합니다. 기본적으로 DBMS가 보장합니다.

3. 트랜잭션 동시 실행으로 발생 가능한 대표적인 문제들

1) Dirty Read

COMMIT되지 않은 변화분을 읽는 문제를 말합니다.

가령 x에 10, y에 20이 저장되어 있을 때

• Tx A : X에 Y를 더함
• Tx B : Y에 10을 더함

을 한다고 가정해보겠습니다. 어떠한 사유로 인해 Tx B가 롤백되는 케이스 중 다음과 같은 케이스가 발생 가능합니다.

Tx A 입장에선 y값을 20으로 읽어서 최종적으로 x에 30을 write했습니다. 그러나 Tx B가 마지막에 롤백되어 Tx A는 유효하지 않은 y값 20을 읽었던 셈이 됐고 결과적으로 x에 write했던 30도 유효하지 않은 값이 됐습니다. 이 현상을 Dirty Read라고 부르며 일관되지 않은 결과를 초래하는 원인이 될 수 있습니다.

2) Non-Repeatable Read

한 트랜잭션에서 어떠한 데이터를 두 번 이상 읽을 때각 읽기 결과가 달라지는 것을 말합니다. 

가령 x에 10이 있을 때

• Tx A : x를 한 번 읽고 어떤 로직을 처리하다가 x를 한 번 더 읽음
• Tx B : x에 20을 더함

을 한다고 가정해보겠습니다. Tx A에서 진행되는 두 번의 읽기 사이에 Tx B가 실행&COMMIT되면 다음 현상이 발생 가능합니다.

Tx A 입장에선 같은 데이터인 x를 한 트랜잭션에서 두 번 읽었는데 결과가 달라졌습니다. DB 관점에서 보면 Tx B가 실행되기 전후에 맞춰 데이터를 잘 읽은 것으로 볼 수도 있습니다. 그러나 Application 관점에서 보면 첫 번째 읽기를 통해 처리한 로직이 왜곡되든가 중복 처리가 되는 현상 등이 발생 가능합니다. 즉 "두 번의 읽기 결과값이 달라지는 것이 문제"라기보다는 "트랜잭션이 시작된 후 읽은 데이터를 신뢰할 수 없게 된다는 것이 문제"라고 봐야 합니다.

3) Phantom Read

한 트랜잭션에서 같은 조건의 데이터를 여러 번 읽을 때 각 읽기 결과가 달라지는 것(읽었던 행이 없어지거나 새 행이 읽히는)을 말합니다.

가령 v라는 컬럼값을 각각 10, 20으로 갖는 x1과 x2라는 레코드가 있을 때

• Tx A : v=10인 레코드를 한 번 읽고 어떤 로직을 처리하다가 같은 조건으로 레코드를 한 번 더 읽음
• Tx B : x2의 v를 10으로 바꿈

을 한다고 가정해보겠습니다. Tx A에서 진행되는 두 번의 읽기 사이에 Tx B가 실행&COMMIT되면 다음 현상이 발생 가능합니다.

한 트랜잭션에서 같은 조건으로 데이터를 읽었는데 첫 번째 읽기에선 없던 레코드가 두 번째 읽기에서 생겼습니다. 만약 Tx B에서 x1을 지웠다면 Tx A의 두 번째 읽기에선 어떤 레코드도 잡히지 않을 텐데 이것도 Phantom Read입니다. Non-Repeatable Read와 비슷하게 DB 관점에서 보면 Tx B가 실행되기 전후에 맞춰 데이터를 잘 읽은 것으로 볼 수 있으나, Application 관점에서 보면 첫 번째 읽기를 통해 처리한 로직이 왜곡되는 현상 등이 발생 가능합니다. 이 역시도 "두 번째 읽기에서 새로운 행이 추가되거나 읽었던 행이 삭제되는 게 문제"라기보다는 "트랜잭션이 시작된 후 특정 조건으로 읽은 결과를 신뢰할 수 없게 되는게 문제"라고 봐야 합니다.

4. 트랜잭션 격리 수준 (Transaction Isolation Level)

위에서 살펴봤던 동시성에 관련된 문제들을 방지하려면 각 트랜잭션들이 서로에게 영향을 주지 않도록 격리해야 하나, 그 정도를 강하게 할 경우 동시성이 낮아지게 됩니다. 즉 동시에 처리 가능한 트랜잭션 수가 줄어드는 것이고 이는 곧 DB 퍼포먼스 저하로 이어집니다. 그래서 DBMS는 보통 여러 Isolation Level을 만들어 트랜잭션이 서로 영향을 주는 정도를 개발자들이 선택할 수 있도록 해뒀습니다. SQL 표준에서 정의한 Isolation Level에선 가장 낮은 레벨이 Read Uncommitted, 가장 높은 레벨이 Serializable로 레벨이 높아질수록 동시성이 낮아지나 격리 정도가 높아지고, 레벨이 낮아질수록 동시성이 높아지나 격리 정도가 낮아집니다.

1) Read Uncommitted

트랜잭션 실행 중에 다른 트랜잭션에서 커밋되지 않은 데이터도 읽을 수 있도록 하는 레벨입니다. 사실상 트랜잭션 격리를 하지 않는 레벨로 Dirty Read, Non-Repeatable Read, Phantom Read를 비롯한 모든 동시성 문제가 발생 가능합니다. 다만 동시성은 가장 높은 레벨입니다.

2) Read Committed

트랜잭션 실행 중엔 다른 트랜잭션에서 커밋된 데이터만 읽을 수 있도록 하는 레벨입니다. Dirty Read 문제가 발생되지 않으나 Non-Repeatable Read, Phantom Read 문제는 여전히 발생 가능합니다.

3) Repeatable Read

SELECT로 읽은 레코드에 락을 거는 방법 등을 통해 동일 트랜잭션 실행 중 한 레코드를 여러 번 읽어도 항상 같은 결과가 나오도록 보장하는 레벨입니다. Dirty Read, Non-Repeatable Read 문제는 발생되지 않으나 Phantom Read 문제는 여전히 발생 가능합니다.

4) Serializable

트랜잭션이 읽거나 쓰는 모든 데이터에 락을 거는 방법 등을 통해 트랜잭션들을 직렬로 실행하는 것처럼 보이게 하는 레벨입니다. Dirty Read, Non-Repeatable Read, Phantom Read를 비롯한 모든 동시성 문제가 발생되지 않는 레벨입니다. 다만 동시성이 가장 낮습니다.

표로 정리하면 다음과 같습니다.

5. 사실 다른 문제들도 있습니다

널리 알려진 문제가 Read Uncommitted, Non-Repeatable Read, Phantom Read일 뿐 DB에서 발생 가능한 동시성 관련 문제들은 더 많습니다. 대표적으로 Lost Update, Write Skew 등이 있습니다.

1) Lost Update

두 트랜잭션이 동시에 같은 데이터를 업데이트하는 경우, 한 트랜잭션의 변경분이 다른 트랜잭션에 의해 덮어쓰여지는 것을 말합니다.

가령 x에 10이 있을 때

• Tx A : X에 20을 더함
• Tx B : X에 10을 더함

을 한다고 가정해보겠습니다. x에는 총 30이 더해질 것으로 예상되나, Tx A가 진행되는 사이 Tx B가 실행되면 다음 문제가 발생 가능합니다.

Tx B가 실행되어 x에 20이 저장됐으나, Tx A는 본인이 원래 읽었던 10에 20을 더한 30을 write하게 되어 결론적으로 x값은 30이 아닌 20만 증가됐습니다. 즉 Tx B가 수행한 변경분이 Tx A에 의해 덮어씌워진 셈이며, 이는 일관되지 않은 결과를 초래할 수 있습니다.

2) Write Skew

두 트랜잭션이 서로 독립적으로 데이터를 읽고 검증한 후 업데이트할 때 데이터 무결성이 깨지는 것을 말합니다.

가령 x, y가 각각 1이고 x + y >= 1이어야 한다는 제약이 있다고 할 때

• Tx A : x에서 1을 뺌
• Tx B : y에서 1을 뺌

을 한다고 가정해보겠습니다. 현실 세계에선 현재 병원에 의사가 두 명 재직 중인데, 뭐가 됐든 의사 한 명은 재직 중이어야 하는 제약이 있는 상황에서 두 의사가 동시에 휴직 신청을 한 경우로 빗댈 수 있습니다.

Tx A에서 먼저 x를 0으로 업데이트했습니다. 만약 트랜잭션이 직렬로 수행됐다면 Tx B는 실행되지 않았어야 했을 것입니다. 그러나 Tx B가 데이터를 읽은 시점에서는 x, y 모두 1이었으니 Tx B도 y를 0으로 업데이트해줬습니다. 결국 각 트랜잭션이 서로 다른 데이터를 업데이트한 것이지만 결과적으론 x, y가 모두 0이 되어 데이터 무결성이 깨지게 된 셈입니다.

이 문제들 외에도 여러 문제가 존재합니다. 물론 어떤 문제들은 적절히 격리 수준을 설정하는 것으로 막아줄 수 있고, DB 엔진들은 저마다 각 격리 수준을 구현하는 방법이 다르기 때문에 생각보다 더 넓은 범위의 문제들을 방지해주기도 합니다. 예를 들어 MySQL InnoDB는 Repeatable Read 수준을 사용해도 Next-Key Lock 등을 통해 Phantom Read 문제를 방지해주고, PostgreSQL은 Snaption Isolation이란 형태로 Repeatable Read를 구현하여 Phantom Read 문제를 방지해줍니다.

그러나 DB에서 제공하는 격리 수준으로 방지할 수 없는 문제들도 있습니다. 그리고 격리 수준은 데이터를 읽거나 쓰는 시점에서의 충돌을 막아주는 것으로 처리된 결과가 논리적으로 무결한 상태임을 보장하진 않습니다. Write Skew같은 문제는 Repeatable Read같은 격리 수준에서도 발생 가능한 것을 예시로 들 수 있습니다.

즉 개발자는 시스템의 요구사항에 맞는 적절한 격리 수준을 선택하는 것도 중요하나, 다른 전략을 추가적으로 활용해서 DB에서 발생되는 동시성 문제들을 해결할 수 있어야 합니다. 이를 위해 낙관적 동시성 제어, 비관적 동시성 제어로 대표되는 동시성 제어 전략을 사용할 수 있습니다.

TMI : 두 동시성 제어 전략은 낙관적 락, 비관적 락이라는 이름으로 더 많이 알려져 있는 것으로 보입니다. 그러나 둘 다 락을 사용하는 전략은 아니기 때문에 동시성 제어가 더 정확한 표현이라고 생각되어 이 글을 쓰면서 동시성 제어라는 이름으로 작성했습니다.

6. 동시성 제어 전략 : 낙관적 동시성 제어와 비관적 동시성 제어

이름에서 알 수 있듯이 낙관적 동시성 제어는 "충돌이 나지 않을 거야"라고 가정하여 최대한 동시성을 확보한 뒤 커밋 직전에 충돌 여부를 확인하는 방법이고, 비관적 동시성 제어는 "충돌이 분명 날 거야"라고 가정하여 처음부터 충돌 자체를 방지하는 메커니즘입니다. 자세히 살펴보면 다음과 같습니다.

1) 낙관적 동시성 제어 (Optimistic Concurrency Control)

자원에 락을 걸지 않고 작업을 수행하다가 커밋 시점에 다른 트랜잭션에 의해 데이터가 변경됐는지 확인하는 전략으로, 충돌이 나지 않을 것이라 가정하고 자원에 대한 잠금 없이 높은 동시성을 유지하면서 작업을 처리하는 개념입니다. 보통 데이터를 읽을 때 그 시점의 상태 정보(버전 정보 등)을 저장해뒀다가 데이터 변경 시점에 상태 정보를 비교하여 변화가 있다면 다른 트랜잭션이 데이터를 변경한 것으로 간주하고 갱신을 취소하거나 롤백하는 형태로 구현됩니다. 만약 상태 정보가 같다면 상태 정보 갱신도 함께 수행됩니다. 락을 사용하지 않아 높은 동시성을 제공할 수 있으므로 데이터 충돌이 적은 환경에서 사용한다면 성능 최적화를 기대할 수 있다는 장점이 있으나, 충돌이 자주 발생하는 환경이라면 갱신 실패 또는 롤백이 반복되며 성능 저하의 주범이 될 수 있다는 단점이 있습니다.

2) 비관적 동시성 제어 (Pessimistic Concurrency Control)

자원(테이블, 레코드 등)에 락을 걸고 작업을 수행하여 다른 트랜잭션의 접근을 차단하는 전략으로, 충돌이 발생함을 가정하고 리소스에 대한 락을 미리 획득하여 특정 기간 동안 해당 리소스에 대한 독점적인 액세스를 유지하는 개념입니다. 보통 SELECT FOR UPDATE를 사용해 데이터를 읽는 시점부터 배타 락을 설정하는 형태 등으로 구현됩니다. 충돌 가능성을 사전에 차단하는 만큼 데이터 무결성을 강력히 보장하고 충돌이 자주 발생하는 상황에선 낙관적 락보다 좀 더 나은 성능을 기대할 수 있다는 장점이 있으나, 락으로 인한 동시성 저하 및 락 획득을 위한 대기 시간 증가, 데드락 발생 가능성이 존재한다는 단점이 있습니다.

• 공유 락(Shared Lock) : Read Lock으로도 불리며 자원에 대한 쓰기는 안 되지만 자원을 읽는 동안 다른 트랜잭션에서 데이터를 읽는 것은 허용하는 개념의 락입니다.
• 배타 락(Exclusive Lock) : Write Lock으로도 불리며 자원에 대해 다른 트랜잭션의 읽기와 쓰기를 모두 막는 개념의 락입니다.

참고

https://cybernerdie.medium.com/database-transactions-explained-a-deep-dive-into-reliability-17ab4e17117a

https://d2.naver.com/helloworld/407507

https://www.youtube.com/watch?v=sLJ8ypeHGlM&t=804s

https://www.youtube.com/watch?v=bLLarZTrebU&list=LL&index=9

https://medium.com/@iamssrofficial/concurrency-in-databases-database-isolation-levels-dirty-read-phantom-read-non-repeatable-read-320ff3553d6d

https://medium.com/@abhirup.acharya009/managing-concurrent-access-optimistic-locking-vs-pessimistic-locking-0f6a64294db7

Subquery란

SQL 쿼리 내에서 또다른 SELECT절을 사용하는 문법을 쓸 때, 내부에 포함되는 SELECT절을 서브쿼리(Subquery)라 부른다. SELECT 절에서 사용되는 서브쿼리를 스칼라 서브쿼리(Scala Subquery), FROM절에서 사용되는 서브쿼리를 인라인뷰(Inline View), WHERE절에서 사용되는 서브쿼리를 중첩 서브쿼리(Nested Subquery)라고 부른다.

Scala Subquery란

SELECT절에서 사용되는 서브쿼리로, 반드시 하나의 record(행)을 리턴해야하며 그 행도 하나의 컬럼값만 가지고 있어야 한다. (즉 단일행, 단일 컬럼을 반환해야 한다)

예제로, 다음과 같은 두 개의 테이블이 있다고 하자. DEPARTMENTS는 회사에 있는 부서들의 부서id와 부서명을, EMPLOYEES는 임직원들의 이름과 소속 부서id 등을 가진다.

이 때, 임직원들의 이름과 그들이 속한 부서의 이름을 함께 조회하고 싶다고 하고, 스칼라 서브쿼리를 사용해본다고 하자. 이때, 스칼라 서브쿼리의 FROM절에서 DEPARTMENTS를 참조하는가, EMPLOYEES를 참조하는가를 참조하는가를 주의해야 한다. 

스칼라 서브쿼리의 FROM절에서 DEPARTMENTS를 참조할 경우의 쿼리와 결과는 다음과 같다.

SELECT EMP_NAME, (
    SELECT DEPT_NAME
    FROM DEPARTMENTS d
    WHERE d.DEPT_ID = e.DEPT_ID) 
FROM 
    EMPLOYEES e;

스칼라 서브쿼리의 FROM절에서 EMPLOYEES를 참조할 경우의 쿼리와 결과는 다음과 같다.

SELECT (
        SELECT EMP_NAME
        FROM EMPLOYEES e
        WHERE d.DEPT_ID = e.DEPT_ID
    ), DEPT_NAME 
FROM 
    DEPARTMENTS d;

서브쿼리의 FROM절에서 DEPARTMENTS를 참조하는 경우, d.DEPT_ID = e.DEPT_ID를 만족하는 레코드가 하나만 나오기 때문에 문제가 되지 않는다. 하지만 서브쿼리의 FROM절에서 EMPLOYEES를 참조하는 경우, d.DEPT_ID = e.DEPT_ID를 만족하는 레코드가 다수가 나오기 때문에 문제가 되고, 위와 같은 오류가 생기는 것이다.

즉, 스칼라 서브쿼리는 반드시 하나의 레코드와 하나의 컬럼값을 리턴해야 한다.

스칼라 서브쿼리의  성능 측면에서의 문제점 & LEFT OUTER JOIN으로 변환

쿼리는 FROM(과 JOIN) → WHERE → GROUP BY → HAVING → SELECT → ORDER BY 순으로 실행된다. 즉 특정 테이블에서 특정 조건에 맞는 레코들들을 모두 뽑아오고, 해당 레코드들에 대해 select절에서 특정 컬럼들을 뽑아내는 식으로 동작한다.  스칼라 서브쿼리는 건수만큼 반복해서 수행된다. 데이터가 많아질수록 성능 저하의 주범이 될 수 있는 것이다.

따라서, LEFT OUTER JOIN과 Inline View를 활용하는 식으로 쿼리를 바꿔 성능을 개선시킬 수 있다. 다음 쿼리를 살펴보자.

SELECT DEPT_NAME, (
    SELECT COUNT(*)
    FROM EMPLOYEES e
    WHERE e.DEPT_ID = d.DEPT_ID
) NUMS
FROM DEPARTMENTS d;

부서별로 부서명과 소속 인원들의 이름을 보는, 스칼라 서브쿼리를 쓰는 쿼리다. 이를 LEFT OUTER JOIN과 Inline View을 활용하는 형태로 다음과 같이 바꿔쓸 수 있다.

SELECT DEPT_NAME, c.NUMS
FROM DEPARTMENTS d
LEFT OUTER JOIN (
    SELECT e.DEPT_ID, COUNT(*) AS NUMS
    FROM EMPLOYEES e
    GROUP BY e.DEPT_ID
) c
ON d.DEPT_ID = c.DEPT_ID;

기존에는 해당 서브쿼리가 건수만큼 반복돼서 수행됐지만, 이렇게하면 한 번만 수행되고 끝난다. 

하지만 늘상 스칼라 서브쿼리를 LEFT OUTER JOIN으로 바꾼다고 성능 향상이 되는 건 아니다(그러면 스칼라 서브쿼리 아무도 안 쓰지..). 상황별로 스칼라 서브쿼리를 쓰는게 이득일 때도 있다. 다만 스칼라 서브쿼리를 쓸 때는 속도를 고려하는 습관을 가지자.

다음과 같은 테이블이 있다고 하자.

SALES_HISTORY 테이블은 특정 상품들과 해당 상품들이 판매된 시간, 판매된 가격 정보를 가진다.

1. GROUP BY를 통한 집계

GROUP BY를 통해 특정 컬럼(들)을 기준으로 그룹핑한 다음 집계함수를 활용한 합이나 평균값의 산출이 가능하다. 본 글에서는 SUM을 활용한 상품들의 일자별 매출액을 조회하는 상황을 가정한다.

SELECT
  PRODUCT_ID,
  DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DAY,
  SUM(PRICE) AS TOTAL_SALES
FROM
  SALES_HISTORY
GROUP BY
  PRODUCT_ID, SALES_DAY;

2. WITH ROLLUP을 가미한 집계

WITH ROLLUP을 가미하면, GROUP BY로 보여지는 결과물 뿐만 아니라 그룹핑한 각각의 소그룹별 집계와 전체 집계를 볼 수 있다.

SELECT
  PRODUCT_ID,
  DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DAY,
  SUM(PRICE) AS TOTAL_SALES
FROM
  SALES_HISTORY
GROUP BY
  PRODUCT_ID, SALES_DAY WITH ROLLUP;

NULL값으로 표기된 부분들이 WITH ROLLUP을 통해 산출된 집계값이다. 1000번 상품 그룹에 대한 집계와 1001번 상품에 대한 집계, 1002번 상품에 대한 집계와 더불어 전체 집계 또한 나온 것을 확인할 수 있다. 

MySQL, 그리고 InnoDB의 Index

인덱스(Index)는 DB에서 테이블의 데이터를 빠르게 검색하기 위해 사용되는 보조적인 자료구조로, 인덱스와 실제 데이터는 따로 보관 및 관리된다. 전통적으로 인덱스를 위해 가장 많이 사용되는 자료구조는 B-tree (Balanced Tree)로, Leaf Node는 실제 데이터 레코드를 찾아갈 수 있는 주솟값을 가지고 있다.

MySQL 역시 B-tree 기반 인덱스를 사용하며, InnoDB 엔진의 경우 클러스터링 인덱스(클러스터링 테이블이라고도 부름)를 사용하는데 이는 테이블에서 PK(Primary Key)값이 비슷한 레코드들끼리 묶어 저장하는 걸 의미한다. 즉 스토리지 엔진으로 InnoDB를 사용한다면 아무 것도 하지 않아도 알아서 PK값들에 의해 B-tree 구조로 테이블의 데이터가 저장된다(각 Leaf Node가 레코드의 모든 컬럼 값을 가짐). 즉 PK값에 따라 레코드의 저장 위치가 결정되는 것이며, 만약 PK값이 변경된다면 해당 레코드가 저장되는 물리적 위치 또한 바뀌게 됨을 의미한다. 따라서 이렇게 클러스터링 인덱스로 저장되는 테이블은 PK 기반의 검색이 매우 빠르나 레코드의 저장이나 PK 변경은 상대적으로 느리다.

이 특성 때문에, 세컨더리 인덱스에 대한 B-tree를 기준으로 볼 때 스토리지 엔진으로 MyISAM을 사용할 경우 Leaf Node가 실제 레코드의 물리적인 주소들을 가지는 반면 InnoDB는 Leaf Node가 PK를 가진다. 즉 InnoDB를 사용할 경우 세컨더리 인덱스를 통해 데이터를 읽을 때 데이터를 바로 찾아가는 게 아니라, Leaf Node에 저장된 PK를 이용해 클러스터링 인덱스에서 찾아가게 된다.

본 포스트에서는 Index를 사용하지 않는 Full Table Scan방법과 Index를 사용하는 4가지 Scan 방법에 대해 다룬다.

Full Table Scan (=Table Full Scan)

말 그대로 테이블 전체 데이터를 순차적으로 scan하는 방식이다(배열에서 모든 데이터를 선형탐색으로 읽는 것에 비유할 수 있다). InnoDB가 데이터(인덱스도 포함)들을 저장하는 기본적인 단위는 "페이지"인데, Full Table Scan은 테이블의 데이터가 담긴 페이지들을 모두 읽어와서 읽게 된다. 레코드가 많은 테이블의 경우, Full Table Scan은 성능 저하의 주범이 되는 경우가 많다. 일반적으로 인덱스가 없거나, 또는 인덱스를 활용할 수 없거나, 아니면 테이블의 레코드가 너무 적어서 인덱스를 사용하지 않고 테이블 전체를 읽는 게 더 빠를 경우 이 방법이 사용된다.

Index Full Scan

인덱스의 Leaf 노드 전체(즉 인덱스의 처음부터 끝까지)를 순차적으로 scan하지만 실제 테이블을 참조하지는 않는 방식을 말한다. 쿼리가 인덱스에 명시된 컬럼들만으로 조건을 처리가능한 경우에 주로 이 방법이 사용된다.(B-tree의 각 노드가 인덱스로 명시된 컬럼값과 child node의 위치를 키-값 형태로 가지고 있음을 상기할 것) 인덱스 뿐만이 아닌 레코드까지 읽어야 한다면 절대 이 방식을 사용하지 않으며, 실제 테이블을 참조하지는 않으니 Full Table Scan보다는 효율적(왜냐면 인덱스의 전체 크기가 테이블 자체의 사이즈보다 작아서 Disk I/O가 줄어들기 때문)이다.

참고로 이렇게 쿼리가 인덱스에 존재하는 컬럼만으로 처리 가능할 때 "커버링 인덱스"라고 부른다.

Index Range Scan

특정 범위만큼의 인덱스를 스캔한 뒤 이에 대응하는 레코드들을 읽는 방식이다. 쿼리에서 BETWEEN이나 부등호 등이 사용되어 검색해야 할 인덱스의 범위(range)가 결정됐을 때 사용된다. 스캔의 시작점이 되는 리프 노드만 찾으면 그때부터는 종료 지점까지의 리프 노드들을 쫙 스캔한 후(B+ tree는 리프 노드끼리도 서로 연결돼있음을 상기), 필요하다면 읽은 리프 노드들이 가리키는 레코드들을 읽어온다(따라서 Random I/O가 수반되게 된다). 쿼리가 인덱스에 명시된 컬럼들만으로 조건을 처리가능하다면(즉 커버링 인덱스) Index Full Scan처럼 테이블 레코드들을 읽어오는 과정은 없다. 

Loose Index Scan 

인덱스에서 필요한 부분만 선택적으로 스캔한 뒤 이에 대응하는 레코드들을 읽는 방식이다. Index Range Scan과 비슷하게 동작하나, 필요한 인덱스 키와 불필요한 인덱스 키를 구분한 뒤 불필요한 인덱스 키는 무시하는 식으로 동작한다. 보통 GROUP BY, MAX, MIN 등이 사용된 쿼리를 최적화할 때 사용된다.

Index Skip Scan

인덱스는 두 개 이상의 컬럼에 대해서도 만들어줄 수 있는데(멀티 컬럼 인덱스 등으로 부름), 이 때 컬럼 순서에 따라 인덱스가 어떤 기준으로 정렬되는지가 결정된다.(예를 들어 A, B 순서로 인덱스를 만들었다면 A에 대해 먼저 정렬된 다음 B에 대해 정렬됨) 이때 WHERE절에 멀티 컬럼 인덱스의 첫 컬럼이 없으면 원래 인덱스를 사용할 수 없으나, 첫 컬럼이 없음에도 불구하고 인덱스를 사용할 수 있는 스캔 방식이 이 Index Skip Scan이다. 

예로, student테이블에 대해 다음과 같이 gender, address에 대해 인덱스가 걸려있다고 해보자.

ALTER TABLE students ADD INDEX ix_gender_address (gender, address);

이때다음 쿼리는 원래는 Full Table Scan 또는 Index Full Scan(커버링 인덱스인 경우)으로 수행될 것이다.

SELECT gender, address
FROM students
WHERE address LIKE "경기도%";

하지만 Index Skip Scan을 사용하면 WHERE절에서 쓰이지 않은 인덱스의 선행 컬럼에 대해 가능한 값들을 구한 다음, 해당 컬럼의 조건을 추가해 쿼리를 다시 실행하는 형태로 처리하게 된다. 즉 여기서는 WHERE절에서 쓰이지 않은 gender라는 컬럼에 대해, 해당 컬럼에서 유니크한 값들을 모두 뽑은 다음 다음과 같은 형태의 쿼리를 다시 실행하게 된다.

SELECT gender, address
FROM students
WHERE gender = "M" AND address LIKE "경기도%";

SELECT gender, address
FROM students
WHERE gender = "F" AND address LIKE "경기도%";

참고로, Index Skip Scan은 WHERE절에서 쓰이지 않는 선행컬럼의 유니크한 값의 개수가 적어야 하고(많으면 오히려 쿼리를 처리하는 속도가 더 느려질 수 있기 때문), 커버링 인덱스를 만족해야 한다는 단점이 있다.