[Database] Isolation level에 따른 동시성 문제 분석

데이터베이스 트랜잭션은 설정된 isolation level에 따라 방지할 수 있거나 방지할 수 없는 동시성 문제가 존재합니다. ANSI SQL(미국 표준 협회의 SQL 표준)은 isolation level에 따라 방지해야 하는 최소한의 동시성 문제를 다음과 같이 정의합니다. 

Isolation level	Dirty Read	Nonrepeatable Read	Phantom Read	Serialization Anomaly
Read uncommitted	O	O	O	O
Read committed	X	O	O	O
Repeatable read	X	X	O	O
Serializable	X	X	X	X

데이터베이스 구현체(MySQL, PostgreSQL 등)는 위 ANSI SQL을 따르기도 하지만 각각의 구현 방법에 따라 방지할 수 있는 동시성 문제가 달라질 수 있습니다(PostgreSQL은 repeatable read에서 phantom read를 방지합니다). 이번 포스팅에서는 동시성 문제가 실제로 어떻게 발생하는지 SQL을 직접 사용해 보면서 살펴보겠습니다. 

 

데이터베이스 스키마 

데이터베이스는 MySQL과 PostgreSQL을 사용하겠습니다. Isolation level에 따라 방지할 수 있는 동시성 문제의 종류가 다르기 때문에 함께 살펴보면 이해하는데 도움이 될 수 있습니다. 

위 스키마는 예시 스키마이며 이번 포스팅에서 모든 테이블을 사용하지는 않습니다. 

MySQL DDL/DML 

create table if not exists example.admin
(
    id         bigint auto_increment
        primary key,
    name       varchar(50)          not null,
    go_to_work tinyint(1) default 0 null
);

create table if not exists example.room
(
    id   bigint auto_increment
        primary key,
    name varchar(50) not null
);

create table if not exists example.tickets
(
    id    bigint auto_increment
        primary key,
    name  varchar(50)      not null,
    count bigint default 0 null
);

create table if not exists example.user
(
    id   bigint auto_increment
        primary key,
    name varchar(50) not null
);

create table if not exists example.booking
(
    id         bigint auto_increment
        primary key,
    room_id    bigint not null,
    user_id    bigint not null,
    start_date date   null,
    end_date   date   null,
    constraint booking_room_id_fk
        foreign key (room_id) references example.room (id),
    constraint booking_user_id_fk
        foreign key (user_id) references example.user (id)
);

create index booking_end_date_index
    on example.booking (end_date);

create index booking_start_date_index
    on example.booking (start_date);

insert into example.admin (id, name, go_to_work)
values (1, 'Devvy', false);

insert into example.room (id, name)
values (1, 'room1');

insert into example.tickets (name, count)
values ('free-ticket', 1);

insert into example.user (id, name)
values (1, 'user1'),
       (2, 'user2');

 

PostgreSQL DDL/DML 

CREATE TABLE IF NOT EXISTS example.admin
(
    id         SERIAL PRIMARY KEY,
    name       VARCHAR(50) NOT NULL,
    go_to_work BOOLEAN DEFAULT FALSE NULL
);

CREATE TABLE IF NOT EXISTS example.room
(
    id   SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE IF NOT EXISTS example.tickets
(
    id    SERIAL PRIMARY KEY,
    name  VARCHAR(50) NOT NULL,
    count BIGINT DEFAULT 0 NULL
);

CREATE TABLE IF NOT EXISTS example.user
(
    id   SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE IF NOT EXISTS example.booking
(
    id         SERIAL PRIMARY KEY,
    room_id    BIGINT NOT NULL,
    user_id    BIGINT NOT NULL,
    start_date DATE NULL,
    end_date   DATE NULL,
    CONSTRAINT booking_room_id_fk
        FOREIGN KEY (room_id) REFERENCES example.room (id),
    CONSTRAINT booking_user_id_fk
        FOREIGN KEY (user_id) REFERENCES example.user (id)
);

CREATE INDEX booking_end_date_index
    ON example.booking (end_date);

CREATE INDEX booking_start_date_index
    ON example.booking (start_date);

INSERT INTO example.admin (name, go_to_work)
VALUES ('Devvy', FALSE);

INSERT INTO example.room (name)
VALUES ('room1');

INSERT INTO example.tickets (name, count)
VALUES ('free-ticket', 1);

INSERT INTO example.user (name)
VALUES ('user1'),
       ('user2');

 

Dirty Read 

처음으로 살펴볼 동시성 문제인 dirty read는 read uncommitted isolation level이 설정됐을 때 발생할 수 있습니다. Dirty read가 어떻게 발생할 수 있는지 단계별로 살펴보겠습니다. 

Isolation level to read uncommitted

데이터 조회 

특정 room을 예약(booking) 하기 위해서 room이 이미 예약됐는지 확인하는 쿼리를 실행하겠습니다. 아래 쿼리의 결과는 해당 room이 예약가능하다는 것을 표시합니다. 

예약 

Room이 예약 가능함을 확인했으므로 booking 테이블에 데이터를 저장함으로써 예약을 진행합니다. 해당 트랜잭션은 아직 커밋되지 않은 상태입니다. 

Dirty read 

다른 트랜잭션에서 동일한 room을 동일한 시간대에 예약 가능한지 확인해 보겠습니다. 커밋되지 않은 데이터를 읽을 수 있기 때문에 결과는 "예약 불가"로 표시됩니다. 

따라서 애플리케이션은 이를 예약 불가능한 상태로 보고 유저에게 해당 room을 예약할 수 없다고 알립니다. 하지만 첫 번째 트랜잭션에서 rollback이 수행되면 어떻게 될까요? booking 데이터가 insert 되지 않기 때문에 해당 시간대의 room은 예약이 가능한 상태가 됩니다.

위와 같이 dirty read는 commit 되지 않은 데이터를 읽을 수 있습니다. 이를 방지하기 위해서는 isolation level을 read committed로 변경해야 합니다. 

Isolation level을 read committed로 변경한 후 booking 테이블을 조회하면 해당 데이터가 "예약 가능"으로 표시됩니다.

추가적으로  알면 좋은 개념으로는 dirty write가 있습니다. 이는 커밋되지 않은 데이터를 다른 트랜잭션에서 update 하는 현상을 의미합니다. 

 

Non-repeatable Read 

다음으로는 하나의 트랜잭션에서 동일한 쿼리를 여러 번 수행했을 때 결과가 달라지는 현상인 non-repeatable read에 대해 살펴보겠습니다. 

Isolation level to read committed 

현상을 재현하기 위해 isolation level을 read committed로 설정합니다. 각각의 트랜잭션을 실행합니다. 

데이터 조회 

사용가능한 티켓이 얼마나 남아있는지 확인하기 위해 tickets 테이블을 조회합니다. 아래 쿼리의 결과로 확인할 수 있는 사용 가능한 티켓은 1개입니다. 

두 번째 트랜잭션에서도 사용 가능한 티켓을 조회하기 위한 쿼리를 수행합니다.

데이터 수정 

티켓을 사용하기 위해 사용 가능한 티켓 수를 업데이트합니다. 업데이트를 완료한 트랜잭션은 커밋을 수행합니다. 

데이터를 다시 조회 

첫 번째 트랜잭션에서 티켓 수량을 확인하기 위해 다시 쿼리를 수행합니다. 티켓 수량을 update 하는 쿼리는 이미 커밋이 완료됐기 때문에 count가 0으로 수정된 것을 확인할 수 있습니다. 즉, 하나의 트랜잭션에서 동일한 SELECT 쿼리를 여러 번 수행했을 때 결과가 달라지는 non-repeatable read 현상이 발생했습니다. 

만약 트랜잭션의 isolation level을 repeatable read로 설정하면 어떻게 될까요? 

트랜잭션의 isolation level을 repeatable read로 수정 후 결과를 보면 count가 1로 설정됩니다. 이는 하나의 트랜잭션에서 일관된 snapshot을 읽을 수 있도록 다른 트랜잭션의 영향을 격리시킬 수 있는 데이터베이스의 기능 덕분입니다. 대표적인 구현 방법으로는 MVCC(Multi Version Concurrency Control)입니다. MVCC에 대해 궁금하시면 아래 포스팅을 참고해 주세요. 

[Database] DBA급 개발자로 - #18 Mutli-Version Concurrency Control

 

Lost Update

다음으로는 lost update에 대해 살펴보겠습니다. 각각의 트랜잭션이 read -> update 순서로 동작하고 동시에 하나의 데이터를 update 했을 때 먼저 수행된 update가 overwrite 됨으로써 그 결과가 반영되지 않는 현상을 의미합니다. Lost update는 데이터베이스에 따라 repeatable read에서 발생하거나 발생하지 않을 수 있습니다(MySQL은 발생하지만 PostgreSQL은 발생하지 않습니다). 

MySQL 

Isolation level to repeatable read 

현상을 재현하기 위해 트랜잭션 isolation level을 repeatable read로 설정합니다. 

데이터 조회 

각각의 트랜잭션에서는 사용 가능한 티켓을 확인하기 위한 쿼리를 수행합니다. 두 트랜잭션의 쿼리 결과는 count = 1로 동일합니다. 

데이터 수정 

각각의 트랜잭션은 사용 가능한 티켓이 1개씩 남아있음을 확인합니다. 애플리케이션은 해당 값을 활용해서 티켓을 사용하는 로직을 수행합니다(티켓 사용을 나타내기 위해 티켓의 수 -1). 애플리케이션의 동작을 모방하기 위해서 다음과 같은 쿼리(set count = 1 - 1)를 사용합니다. 

각각의 트랜잭션은 update이후 커밋을 수행합니다. 

데이터 조회 

양쪽 커밋이 모두 완료된 이후 데이터를 다시 조회합니다. 

분명 티켓을 2번 활용했기 때문에 count는 -1이 돼야 합니다. 하지만 한쪽의 update가 overwrite 되면서 lost update가 발생합니다. 이로 인해 count는 0으로 설정됩니다. Commit 된 정보가 덮어씌워지는 현상은 비즈니스적으로도 큰 문제가 될 수 있기 때문에 주의해야 합니다(위 시나리오에는 티켓이 2번 사용됐지만 1번만 사용된 것처럼 보입니다). 

PostgreSQL 

PostgreSQL은 MySQL과 다르게 lost update를 미연에 방지합니다. 

Isolation level to repeatable read

데이터 조회 

데이터 수정 

각각의 트랜잭션에서 count 값을 업데이트하겠습니다. 

두 번째 트랜잭션의 업데이트가 block 되는 것을 확인할 수 있습니다. 이는 lock에 의해 업데이트가 수행되지 못하는 상태입니다. 여기서 첫 번째 트랜잭션이 커밋 또는 롤백의 수행 여부에 따라 두 번째 트랜잭션의 결과가 달라집니다. 

첫 번째 트랜잭션이 커밋되면 두 번째 트랜잭션은 lost update가 발생할 수 있음을 인지하고 두 번째 트랜잭션을 실패처리합니다. 하지만 첫 번째 트랜잭션이 rollback을 수행하면 두 번째 트랜잭션은 정상적으로 업데이트를 수행할 수 있습니다. 

위 작동방식은 postgreSQL의 문서에 명시돼 있습니다. 

13.2. Transaction Isolation

 

Phantom 

Phantom은 repeatable read에서 발생할 수 있는 동시성 문제 중 하나입니다. 동시에 수행되는 트랜잭션 A와 B가 있다고 가정하겠습니다. A와 B는 데이터를 조회하고 조회된 데이터를 바탕으로 새로운 데이터를 저장하거나 기존 데이터를 변경합니다(insert, update or delete). A와 B에 의해 변경된 데이터가 이전에 수행했던 조회 쿼리에 영향을 준다면 어떻게 될까요?(phantom 현상이 발생합니다) 시나리오를 통해 단계별로 살펴보겠습니다. 

MySQL 

Isolation level to Repeatable Read

현상을 재현하기 위해서 isolation level을 repeatable read로 설정하겠습니다.

데이터 조회 

첫 번째 트랜잭션에서는 booking 테이블을 조회하여 "2023-01-01 ~ 2023-01-03" 사이에 1번 room이 예약 가능 여부를 확인하는 쿼리를 수행합니다. 해당 room은 예약이 가능한 것으로 판단할 수 있습니다. 

두 번째 트랜잭션에서는 "2023-01-02 ~ 2023-01-04" 사이에 1번 room의 예약 가능 여부를 확인하는 쿼리를 수행합니다. 결과적으로 두 쿼리 모두 1번 room이 트랜잭션에서 명시한 기간에 예약이 가능한 것으로 결과를 반환합니다. 

데이터 저장 

결과를 기반으로 각각의 트랜잭션은 예약을 위한 데이터를 저장합니다(저장 후 커밋을 수행합니다). 

결과 조회 

각각의 트랜잭션은 1번 room에 대해 예약이 완료됐음을 나타낼 수 있는 데이터를 저장합니다. 데이터가 일관된 상태라면 해당 room은 동일한 시간대에 1번의 예약만 존재해야 합니다. 결과를 살펴보겠습니다. 

결과를 보니 "2023-01-01 ~ 2023-01-03"과 "2023-01-02 ~ 2023-01-04"에 1번 room을 예약하는 데이터가 저장됐습니다. 해당 데이터는 기간이 겹치기 때문에 데이터의 일관성이 깨진 것이나 다름이 없습니다. 이처럼 phantom은 서로 다른 트랜잭션이 서로의 조회 쿼리에 영향을 주는 데이터 변경을 수행했을 때 발생할 수 있습니다. 

Serializable 

Serializable로 isolation level을 올리면 phantom 현상이 발생하지 않습니다. 

MySQL Locks 

Serializable isolation level의 트랜잭션이 진행될 때 lock을 어떻게 사용하는지 살펴보겠습니다. 

Start transaction

트랜잭션을 시작한 시점에는 별도의 lock이 존재하지 않습니다. 

데이터 조회 

데이터를 조회하는 시점에는 테이블 전체에 IS(Intention Shared lock - shared lock을 획득할 의도가 있음을 나타냄)을 획득하고 booking 테이블을 booking_room_id_fk 외래키에 대해 shared lock을 획득합니다. 

데이터 저장 

데이터를 저장하는 시점에 IX(Intention Exclusive lock - exclusive lock을 획득할 의도가 있음을 나타냄)을 획득하고 booking 테이블에는 booking_room_id_fk 외래키에 대해 shared lock과 gap lock을 획득합니다. 

그럼 위에서 serializable isolation level에서 동시에 예약 데이터를 저장했을 때 왜 하나의 트랜잭션은 실패처리가 된 것일까요? 

두 트랜잭션에서 동시에 booking 테이블을 조회할 때 트랜잭션이 획득한 lock은 아래와 같습니다. 

booking_room_id_fk 외래키 인덱스에 "supremum pseudo-record"에 대해 shared lock이 획득된 것을 확인할 수 있습니다. MySQL 문서에는 "supremum" pseudo-record를 다음과 같이 정의합니다.

For the last interval, the next-key lock locks the gap above the largest value in the index and the “supremum” pseudo-record having a value higher than any value actually in the index. The supremum is not a real index record, so, in effect, this next-key lock locks only the gap following the largest index value.

booking_room_id_fk에는 1번 room의 room_id 값만 저장됐기에 supremum pseudo-record는 1보다 큰 값을 나타냅니다. 만약 트랜잭션에서 아래와 같이 room_id가 1인 데이터를 insert 하려고 하면 어떻게 될까요? 

결과는 booking_room_id_fk에 쓰기를 위한 lock을 획득하지 못해 대기하다 설정된 timeout 시간이 지나면 트랜잭션이 실패합니다.

이러한 원리를(gap lock) 활용해서 MySQL은 serializable isolation level에서 phantom 현상을 방지할 수 있습니다. Gap lock에 대한 정보는 아래 문서에 자세히 설명돼 있습니다. 

MySQL :: MySQL 8.0 Reference Manual :: 15.7.1 InnoDB Locking

마무리 

트랜잭션의 isolation level에서 발생하는 현상은 개념적으로 이해하고 있었지만 직접 경험해 보니 더욱 명확하게 파악할 수 있었습니다. 데이터베이스의 isolation level 별로 발생하는 현상은 비즈니스에 심각한 영향을 미칠 수 있으므로, 언제 어떤 현상이 발생하는지 파악할 수 있고 이를 예방할 수 있는 능력은 개발자로서 중요한 역량이라 생각합니다.