title profile picture

무지성 개발 탈출기

MySQL의 엔진 InnoDB를 알아보자.(1/2)

Jul 13, 2021
·

  스타트업 지원사업을 통해 데이터베이스 설계 및 유지보수 컨설팅을 받은 적이 있습니다. 멘토님과 이야기를 나누면서 DBA는 기본적으로 MySQL, MariaDB의 기본 엔진인 InnoDB에 대해 알아야 한다고 조언해주셨습니다. 엔진의 동작 원리에 따라 최적화 및 유지보수가 가능하기 때문인데요. 그래서 이번 포스팅은 MySQL docs에서 소개하고 있는 InnoDB의 장점을 자세히 설명하면서 필요한 주변 개념들도 함께 정리하고자 합니다.

InnoDB 주요 장점

  InnoDB는 MySQL 기본 저장 엔진입니다. MySQL docs에서 소개하고 있는 InnoDB의 주요 장점을 살펴보면,

  1. 동작을 수행하는 Commit, 동작 수행 이전 상태로 돌아가는 Rollback과 같은 Transaction 기능을 지원한다.
  2. Row-level locking과 Consistent reads를 통해 다중 사용자 환경에서 동시 수행이 가능하다.
  3. 물리적 저장 기준인 PRIMARY KEY를 통한 Clustered index 기능을 제공한다.
  4. 데이터 무결성을 보장하기 위한 FOREIGN KEY 기능을 제공한다.

  이외에도 여러 가지 기능을 제공하지만 이번 포스팅은 주요 장점 1, 2번에 대해 다룰 것입니다. 1번부터 하나씩 살펴보겠습니다.

1. Transaction 기능

   Transaction(트랜잭션)이란, 데이터베이스에서 수행하는 작업의 가장 작은 단위를 말합니다. InnoDB는 모든 데이터베이스의 작업을 트랜잭션 단위로 묶어 수행하며, 트랜잭션이 ACID라고 불리는 특성을 만족할 수 있도록 기능을 제공합니다. ACID의 개념을 짧게 정리하면,

ACID

  • A : Atomicity (원자성)

    트랜잭션의 작업은 분리될 수 없습니다. 만약 트랜잭션 내의 모두 작업이 수행되지 않으면 트랜잭션의 수행 이전 상태로 돌아가야 합니다.

  • C : Consistency (일관성)

    트랜잭션의 작업 이전, 이후 모두 데이터베이스의 무결성 제약조건을 만족해야합니다.

  • I : Isolation (독립성)

    트랜잭션이 동시에 수행될 때, 각 트랜잭션 간의 수행이 서로에게 영향을 주지 않고 독립적으로 수행하게 됩니다. 해당 특성은 자원 점유에 대한 문제로, 성능을 위해 격리 수준을 조절할 수 있습니다.

  • D : Durability(지속성)

    데이터 베이스의 무결성 제약을 위해 트랜잭션의 결과는 영속적으로 반영되어야 합니다.

  이러한 특성을 만족할 수 있도록 트랜잭션의 COMMIT, ROLLBACK 기능을 제공합니다. 

// autocommit은 세션 내에서 지정할 수 있습니다.
SET autocommit = 0;

// autocommit mode에서도 트랜잭션을 명시하여 사용할 수 있습니다.
START TRANSACTION 
// 여러가지 SQL 문.
COMMIT

  또한 SQL 쿼리문 각각이 하나의 트랜잭션으로 수행되는 autocommit mode를 제공합니다. 해당 모드에서는 사용자의 커넥션이 발생하였을 때, 새로운 세션을 시작하게 되고 각 쿼리문의 트랜잭션이 자동으로 커밋됩니다. 하지만 세션 내에서 트랜잭션의 에러가 발생하게 되면 해당 트랜잭션의 쿼리문의 수행이 ROLLBACK됩니다.



2. Consistent reads & Row-level locking

  데이터베이스의 트랜잭션을 동시에 수행하기 위해서는 동일한 데이터를 동시에 조회하거나 수정/삭제하는 방식을 정의해야 합니다. 이러한 동시성 제어를 위해 Consistent reads와 Row-level locking를 사용합니다.

MVCC(Multi-Version Concurrency Control) 다중 버전 동시성 제어

  기본적으로 InnoDB는 여러 시점에 버저닝된 스냅샷을 통해 동시성 제어를 수행합니다. 스냅샷이란, 특정 시점의 데이터의 상태를 말합니다. 데이터를 조회하는 시점에 해당 데이터의 현재 상태를 미리 저장하여 두고 작업을 수행하는 방식으로, 여러 트랜잭션이 동시에 수행되더라도 각각 특정 시점의 데이터 상태를 기준으로 수정/삭제하기 때문에 동시성 제어가 가능하게 됩니다. 이후 각 스냅샷들 간의 변경을 데이터베이스에 반영하는 과정이 내부적으로 이루어집니다. 하지만 문제는 트랜잭션 내에서 여러번의 조회가 발생하는 경우입니다. 설명을 위해 MySQL docs의 예제를 가져왔습니다.

               Session A              Session B

           SET autocommit=0;      SET autocommit=0;
time
|          SELECT * FROM t;
|          #empty set
|                                 INSERT INTO t VALUES (1, 2);
|
v          SELECT * FROM t;
           #empty set
                                  COMMIT;

           SELECT * FROM t;
           # (X)

           COMMIT;

           SELECT * FROM t;
		    # (Y)

  예제는 두 개의 세션이 동일한 데이터에 대한 트랜잭션을 수행하고 있습니다. 세션 A가 3번째로 조회하는 시점에 세션 B가 트랜잭션이 COMMIT됨에 따라 데이터베이스에 반영되었습니다. 세션 A에서 3번째 조회 결과인 (X)는 두 가지가 될 수 있습니다.

  1. 트랜잭션 내부에서 사용되는 데이터베이스의 상태인 'empty set' → Repeatable Read
  2. 세션 B가 수행한 트랜잭션이 반영된 현재 데이터베이스의 상태인 (1, 2) → READ COMMITTED

이러한 단일 트랜잭션 내의 여러 번의 조회 결과를 정의하는 방식을 isolation level(격리 수준)로 정할 수 있는데요. 두 격리 수준에서 동시성 제어는 어떻게 이루어지는지 확인해보겠습니다.

Repeatable Read

  innoDB의 격리 수준 중에서 기본설정에 해당하는 수준입니다. 번역하면 '반복 가능한 읽기'인데, 말 그대로 동일 트랜잭션 내에서는 동일한 상태를 반복적으로 읽을 수 있다는 것입니다.

1) Consistent read

  트랜잭션 내에서 모든 작업은 최초로 읽은 데이터로 구성된 스냅샷을 사용하며, 트랜잭션이 커밋된 이후에만 새로운 스냅샷을 생성하여 사용합니다. 이를 통해 동일 트랜잭션에서의 모든 조회가 동일한 스냅샷을 보기 때문에 반복적인 읽기가 가능하게 됩니다. 그러므로 트랜잭션 내에서 여러 번의 조회를 수행하더라도 다른 트랜잭션이 삽입, 수정, 삭제한 작업의 결과가 데이터에 반영되지 않습니다. 위의 예제에서는 세션 A의 스냅샷을 사용하고 있기 때문에 세 번째 조회 결과 (X) 또한 세션 B의 수정이 반영되지 않은 'empty set'이 반환됩니다.

  • 트랜잭션의 시작 시점이 아닌 최초 SELECT 문을 통한 조회 시점에 스냅샷이 생성됩니다.
     그러므로 최초의 SELECT 문을 수행하기 전 커밋된 다른 트랜잭션의 데이터 수정이 스냅샷에 반영됩니다.

2) Row-level locking

  해당 격리 수준에서는 UPDATE, DELETE문을 위한 조회(수정하거나 삭제할 대상을 위한 조회)를 수행할 때 조회하는 조건 방식에 따라 다른 수정 / 삭제 작업인 UPDATE, DELETE문에 대한 잠금이 발생합니다. 모든 잠금은 트랜잭션이 커밋되는 시점에 해제됩니다.

  • UNIQUE Index를 사용할 수 있는 단일 값에 대한 조건을 가진 쿼리에 대해서는 해당 행에 대한 lock을 수행합니다.(Row-level locking)
     쿼리문의 대상이 된 행에 대한 lock으로 인해 동시에 수정 / 삭제 작업이 수행되지 못하고 해제될 때까지 지연됩니다.
  • 이외에 방식의 조건의 경우, 조회에 사용된 모든 범위에 대한 lock을 수행합니다.(Gap locking)
     조회 범위에 해당하는 행에 대한 잠금으로 인해 동시에 수정 / 삭제 작업이 수행되지 못하고 해제될 때까지 지연됩니다.

READ COMMITTED

  격리 수준 중, Repeatable Read 다음 수준으로, 조회를 수행하는 시점에 커밋된 것을 읽는다는 것을 의미합니다.

1) Consistent read

  조회 시점마다 새로운 스냅샷을 찍기 때문에 동일 트랜잭션 내에서도 다른 트랜잭션의 수정이 반영된 데이터를 조회할 수 있게 됩니다. 위의 예제에서 매번 조회시마다 해당 시점의 최신 데이터로 스냅샷을 생성합니다. 세번째 조회 시점에는 이전에 커밋된 세션 B의 결과가 반영된 결과인 (1, 2) 행이 결과로 반환됩니다.

2) Row-level locking

  Repeatable Read 격리 수준과 동일하게 UPDATE, DELETE문을 위한 조회(수정하거나 삭제할 대상을 위한 조회)를 수행할 때 조회하는 조건 방식에 따라 다른 수정 / 삭제 작업인 UPDATE, DELETE문에 대한 잠금이 발생합니다. 모든 잠금은 트랜잭션이 커밋되는 시점에 해제됩니다.

  • 하지만 다른 점은 조회 범위에 대한 잠금이 아닌 수정을 수행할 행에 대해서만 잠금이 발생하게 됩니다.(Row-level locking)
     조건으로 사용되지 않은 행에 대해서는 lock이 존재하지 않기 때문에 다른 트랜잭션의 UPDATE, DELETE가 수행되어 값이 즉시 반영되지만, 조건으로 사용된 행에 대해서는 lock이 해제되고 난 후에 수행됩니다. 이러한 잠금 방식은 동시성을 높이고 교착 상태를 피할 수 있게 합니다. 하지만 Phantom Rows가 발생할 수 있습니다. Phantom Rows에 대한 설명은 해당 링크를 참고하세요.


+) 격리 수준별 Row-level Locking

   각 격리 수준의 행 수준의 잠금 차이를 이해하기 위해 MySQL doc의 예제를 설명하고자 합니다.

  • 1. 인덱스를 사용하지 않는 UPDATE / DELETE

    CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;

# Session A
START TRANSACTION;
UPDATE t SET b = 5 WHERE b = 3;

# Session B
UPDATE t SET b = 4 WHERE b = 2;


EXPLAIN UPDATE t SET b = 5 WHERE b = 3;
# +------------------------------+ 
# |      type     |      rows    |
# |-------------------------------
# |       ALL     |       5      |
# +------------------------------+

      예제에는 인덱스를 정의하지 않은 테이블 t가 존재합니다. 세션 A 쿼리문의 실행계획를 통해 조회한 행의 수는 5개로, 수정 작업을 위해 모든 행에 대한 조회가 발생했다는 것을 알 수 있습니다. 세션 A의 트랜잭션 수정 작업이 커밋되지 않은 시점에 동시에 세션 B의 수정 작업을 수행하고자 하는 상황입니다. 각 격리 수준에 따라 수행 결과를 설명해보겠습니다.


    Repeatable Read

      세션 A 쿼리문의 실행계획에서처럼 수정 작업의 조건을 만족하는 행을 찾기 위해서 모든 행을 조회하였습니다. 이때, 트랜잭션이 커밋되지 않았기 때문에 조회를 사용한 모든 행에 대한 잠금이 발생합니다. 이전 세션 A의 모든 행에 대한 잠금으로 인해 세션 B의 업데이트는 세션 A의 트랜잭션이 커밋될 때까지 지연됩니다.

    READ COMMITTED

      해당 격리 수준에서는 수정 작업의 조건을 만족하는 행을 찾기 위해서 조회한 행이 아닌 실제 수정 작업을 수행하는 행에 대해서만 잠금이 발생합니다. 세션 A를 통해서 UPDATE하지 않은 행 (1, 2) (5, 2)에 대한 잠금이 발생하지 않으므로, 세션 A의 트랜잭션이 커밋되지 않았음에도 세션 B의 수정이 즉시 이루어질 수 있습니다.



  • 2. 인덱스를 사용하는 UPDATE / DELETE

    CREATE TABLE t (a INT NOT NULL, b INT, c INT, INDEX (b)) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2,3),(2,2,4);
COMMIT;

# Session A
START TRANSACTION;
UPDATE t SET b = 3 WHERE b = 2 AND c = 3;

# Session B
UPDATE t SET b = 4 WHERE b = 2 AND c = 4;


EXPLAIN UPDATE t SET b = 5 WHERE b = 3;
# +---------------------------------------------------------+ 
# |      type     |      possible_keys    |      rows       |
# |----------------------------------------------------------
# |     range     |            b          |        1        |
# +---------------------------------------------------------+

    Repeatable Read, READ COMMITTED
      인덱스를 사용하는 경우, 두 격리 수준 모두 수정 작업을 수행하는 행에 대해서만 잠금이 발생합니다. 사실, Repeatable Read 수준에서는 작업을 수행하는 행에 대한 잠금이 발생한다고 해석하는 것보다는 수정 작업의 조건을 만족하는 행을 찾기 위한 조회가 Unique Index로 인해 작업을 수행하는 행에 대해서만 조회가 발생하고, 조회된 행에 대한 잠금이 발생한다고 보는 것이 맞습니다. 실제 Index가 존재할 때 세션 A의 UPDATE문의 실행계획을 보면 인덱스 b를 사용하여 1개의 행에 대한 조회가 발생한 것을 확인할 수 있습니다.



정리

이번 포스팅에서는 InnoDB의 1, 2번 장점에 관해 설명하였습니다. 장점에 대해 아는 것도 중요하지만 각 장점을 충분히 활용할 수 있도록 기능 구현에서 적용할 수 있는 것들을 정리해보겠습니다.

1. Transaction 기능

  • 반드시 함께 수행되어야 하는 쿼리들에 대해서는 트랜잭션으로 묶어서 사용하도록 합니다. 예를 들어 송금에 대한 기능 구현 시, A의 계좌에서 송금할 금액 차감, B의 계좌에 송금된 금액 가산, A에서 B로 송금한 로그 기록 등 하나라도 누락되어서는 안 되는 일련의 작업을 하나의 트랜잭션화하여 일관된 데이터 삽입이 가능하도록 구현합니다.
  • 트랜잭션 화 하여 수행하는 경우, 트랜잭션을 구성하는 작업 중 하나라도 에러를 반환하면, 해당 트랜잭션이 ROLLBACK되기 때문에 반드시 수행되지 않는 경우에 대해 에러 핸들링이 고려하여야 합니다.

2. Row-level locking과 Consistent reads를 통해 다중 사용자 환경에 대한 동시성 제어

  • 수정 및 삭제 작업 시에 조회 조건을 인덱스를 기준으로 사용하여 행별 잠금으로 수행하도록 합니다. 이를 통해 해당 작업을 수행하지 않는 행에 대해서는 동시에 접근할 수 있도록 합니다.

다음 포스팅에서는 InnoDB의 나머지 3, 4번 장점에 관해 설명하도록 하겠습니다.



© 2021 Chanuuuu | Theme by JunhoBaik | In his heart a man plans his course, but the LORD determines his steps.