Transaction Isolation Level

대부분의 RDBMS에서 표준으로 사용하는 개념으로, Database에 대한 트랜잭션간 고립성 정도를 나타내는 개념이다. Isolation Level은 주요 데이터베이스 시스템에서 비슷한 이름과 기능으로 구현되어 있다. 총 4개의 단계로 구성되어 있으며, 각 단계는 아래와 같다.

1. READ UNCOMMITTED
2. READ COMMITTED
3. REPEATABLE READ
4. SERIALIZABLE

물론 각 데이터베이스 시스템마다 각 Isolation Level의 세부 구현이 다를 수 있지만, 기본적인 개념과 목적은 같다. 이러한 Isolation level은 데이터베이스가 transaction을 처리하는 방식과 다른 transaction의 영향을 받는 정도를 결정한다. 이를 통해서 동시성과 일관성 사이의 균형을 맞추는 것을 목적으로 한다.

Read operation에서 발생할 수 있는 문제

1. Dirty Read
   아직 COMMIT이 이루어지지 않은 트랜잭션 A의 데이터에 대해서, 다른 트랜잭션 B가 SELECT 쿼리를 통해 해당 데이터를 읽을 수 있는 것을 말한다. 물론 트랜잭션 A에서 해당 데이터가 정상적으로 COMMIT이 이루어진다면 아무런 문제가 발생하지 않을 수 있지만, 여러 이슈로 인해 rollback이 발생하게 되면 트랜잭션 B는 존재하지 않는 데이터를 다루게 되는 문제가 발생한다.
   
   
2. Non-repeatable Read
   한 트랜잭션에서 같은 SELECT 쿼리에 대해서 2회 이상 실행했을 때, 쿼리의 결과가 다른 상황을 말한다. 예를 들어, 트랜잭션 A가 SELECT 쿼리를 실행한 결과 (1)이 존재하고, 그 이후 트랜잭션 B가 데이터에 대한 UPDATE/DELETE를 수행했을 때, 그 이후 발생한 트랜잭션 A의 SELECT 쿼리의 실행 결과 (2)가 존재한다고 하자. 이 때 (1)과 (2)가 다른 경우를 non-repeatable Read라고 한다.
   
   
3. Phantom Read
   한 트랜잭션 내에서 같은 SELECT 쿼리를 2회 실행했는데, 첫 번째 결과에서 존재하지 않았던 record가 두 번째 결과에서 나타나는 현상을 말한다.

Isolation Level

원활한 설명을 위해, 아래와 같이 테이블이 정의되어 있고, 약간의 데이터가 존재한다고 해보자.

CREATE TABLE user (  
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30)
);

INSERT INTO user (name) VALUES ("charlie");  
INSERT INTO user (name) VALUES ("peter");  
INSERT INTO user (name) VALUES ("tom");  
SELECT * FROM user;
+----+---------+
| id | name    |
+----+---------+
| 1  | charlie |
| 2  | peter   |
| 3  | tom     |
+----+---------+

 

1. READ UNCOMMITTED

말 그대로, 다른 트랜잭션에서 COMMIT이 이루어지지 않은 데이터들을 다른 트랜잭션이 읽어올 수 있는 상태이다. uncommitted data를 읽어온 후에 정상적으로 commit이 이루어진다면 문제가 발생하지 않지만, 해당 data에 대해 rollback이 발생하게 된다면 database에 존재하지 않는 data를 읽어오게 된다. Rollback 여부와 관계 없이, 이러한 uncommitted data를 다른 트랜잭션이 읽는 상황을 dirty read라고 한다. 아래 예시를 참고해보자.

 

우선 Database Session의 transaction isolation level을 READ UNCOMMITTED로설정하고 transaction을 시작하자.

# Transaction A
SET SESSION transaction isolation level READ UNCOMMITTED;  
START TRANSACTION;  
SELECT * FROM user;  
+----+---------+
| id | name    |
+----+---------+
| 1  | charlie |
| 2  | peter   |
| 3  | tom     |
+----+---------+

 

새로운 transaction을 생성하여 값을 수정해보자. 물론 COMMIT은 이루어지지 않았다.

# Transaction B
START TRANSACTION;  
UPDATE user SET name = "ana" WHERE id = 1;  
INSERT INTO user (name) VALUES ("alice");

 

기존 transaction A에 대해서 SELECT문을 실행했을 때 결과이다.

SELECT * FROM user;  
+----+----------+
| id | name     |
+----+----------+
| 1  | ana      |
| 2  | peter    |
| 3  | tom      |
| 4  | alice    |
+----+----------+

 

위 결과에서 볼 수 있다시피, transaction B에서 아직 COMMIT이 이루어지지 않았음에도 transaction A에서 해당 data들을 읽어올 수 있다. 이로 인해 한 트랜잭션 안에서 같은 SELECT문에 대해 다른 결과가 나타날 수 있고, rollback이 발생할 경우 존재하지 않는 data를 사용하는 문제가 발생할 수 있다.

1. Dirty Read → 발생
2. Non-repeatable Read → 발생
3. Phantom Read → 발생

2. READ COMMITTED

READ UNCOMMITTED와는 반대로 READ COMMITTED는 한 transaction에서 COMMIT이 발생하지 않았다면, 다른 transaction에서는 해당 data에 대해서 읽을 수 없는 isolation level을 말한다. 아래 예시를 확인해보자.

# Transaction A
SET SESSION transaction isolation level READ COMMITTED;  
START TRANSACTION;  
SELECT * FROM user;  
+----+---------+
| id | name    |
+----+---------+
| 1  | charlie |
| 2  | peter   |
| 3  | tom     |
+----+---------+

# Transaction B
START TRANSACTION;  
UPDATE user SET name = "ana" WHERE id = 1;  
INSERT INTO user (name) VALUES ("alice");
# COMMIT이 이루어지지 않음

# Transaction A
SELECT * FROM user;  
+----+---------+
| id | name    |
+----+---------+
| 1  | charlie |
| 2  | peter   |
| 3  | tom     |
+----+---------+

만약 transaction B에 대해서 아래와 같이 COMMIT이 이루어진다면

# Transaction B
COMMIT;

transaction A에서도 해당 data들을 읽어올 수 있다.

# Transaction A
SELECT * FROM user;  
+----+----------+
| id | name     |
+----+----------+
| 1  | ana      |
| 2  | peter    |
| 3  | tom      |
| 4  | alice    |
+----+----------+

 

하지만 여전히 한 transaction 내에서 같은 SELECT 쿼리에 대한 2회 이상의 실행에서 결과가 다르게 나타날 수 있다는 문제가 있다. 이를 non-repeatable read라고 한다. 즉, COMMIT 여부와 관계 없이 한 트랜잭션 내에서 발생하는 동일한 SELECT 쿼리에 대해서는 같은 결과가 발생해야함을 충족하지 못한다.

참고로, PostgreSQL이 기본적으로 READ COMMITTED를 사용한다고 한다.

1. Dirty Read → 발생하지 않음
2. Non-repeatable Read → 발생
3. Phantom Read → 발생

3. REPEATABLE READ

READ COMMITTED와 REPEATABLE READ는 모두 한 transaction에서 SELECT 쿼리로 data를 읽어올 경우, 테이블에 lock을 걸지 않고 해당 시점의 data의 상태를 의미하는 snapshot를 생성하고, 그 snapshot에서 data를 읽어온다.

READ COMMITTED는 SELECT 쿼리를 실행할 때마다 snapshot을 새로 생성하여, 다른 트랜잭션에서 COMMIT이 발생할 경우 SELECT문의 결과가 달라질 수 있다. 하지만 REPEATABLE READ는 처음 data를 읽어올 때 생성한 snapshot에서 계속해서 data를 읽어오기 때문에 다른 트랜잭션에서 데이터를 UPDATE/DELETE 하더라도 이러한 변경사항이 현재 트랜잭션에 영향을 주지 않는다.

참고로 REPEATABLE READ는 MySQL InnoDB의 기본 Isolation level이라고 한다.

아래 예시를 참고하자.

# Transaction A
SET SESSION transaction isolation level REPEATABLE READ;
START TRANSACTION;
SELECT * FROM user;
+----+---------+
| id | name    |
+----+---------+
| 1  | charlie |
| 2  | peter   |
| 3  | tom     |
+----+---------+

# Transaction B
START TRANSACTION;  
UPDATE user SET name = "ana" WHERE id = 1;  
INSERT INTO user (name) VALUES ("alice");
COMMIT;

# Transaction A
SELECT * FROM user;  
+----+---------+
| id | name    |
+----+---------+
| 1  | charlie |
| 2  | peter   |
| 3  | tom     |
+----+---------+
# tranaction B에서 발생한 UPDATE, INSERT가 영향을 주지 않는다.

 

하지만, 데이터 변경(UPDATE, DELETE)가 아닌 삽입(INSERT)이 다른 트랜잭션에서 발생하는 경우, 기존에 존재하지 않았던 record가 현재 트랜잭션의 SELECT 쿼리의 결과에 나타날 수 있다. 이를 phantom read라고 한다. 하지만 MySQL과 PostgreSQL에서의 실제 결과는 phantom read가 발생하지 않으며, SQL server에서는 발생한다. Database System마다 REPEATABLE READ에 대한 세부 구현이 다른 것으로 보인다.

참고로, MySQL이 기본적으로 REPEATABLE READ를 사용한다고 한다.

1. Dirty Read → 발생하지 않음
2. Non-repeatable Read → 발생하지 않음
3. Phantom Read → DB system의 세부 구현에 따라 발생 여부 다름

4. SERIALIZABLE

가장 엄격한 Isolation level으로, 동시성을 약화시키고 안정성에 큰 비중을 둔 level이라고 한다. SELECT 쿼리를 수행할 때 자동으로 해당 Data에 대한 Shared lock(data lock)를 획득하고, 다른 트랜잭션에서의 INSERT, UPDATE, DELETE 작업을 방지한다. 물론 읽기 작업은 가능하다. 하지만 동시성이 크게 제한되면 시스템의 성능에 영향을 줄 수 있다. 만약 shared lock이 걸린 data에 대해서 다른 트랜잭션이 INSERT, UPDATE, DELETE 작업을 수행하려고 하면, 해당 트랜잭션은 대기상태가 되고, shared lock을 건 트랜잭션에서 COMMIT이 발생하면 대기상태가 풀려 작업이 해당 작업이 실행된다. 아래 예시를 확인해보자.

# Transaction A
SET SESSION transaction isolation level SERIALIZABLE;  
START TRANSACTION;  
SELECT * FROM user;  
+----+---------+
| id | name    |
+----+---------+
| 1  | charlie |
| 2  | peter   |
| 3  | tom     |
+----+---------+
# SELECT 쿼리를 실행했기 때문에, 해당 data에 대한 shared lock이 걸리게 된다.

# Transaction B
START TRANSACTION;  
SELECT * FROM user;  
+----+---------+
| id | name    |
+----+---------+
| 1  | charlie |
| 2  | peter   |
| 3  | tom     |
+----+---------+
# shared lock이 걸려있지만, read 작업은 가능하다.

# Transaction B
UPDATE user SET name = "updated" WHERE id = 1;
# data lock이 걸려있기 때문에 위 명령어를 실행해도 반응없이 대기하게 된다.

# timeout에 설정된 시간이 지나면 아래와 같은 에러 메시지가 발생한다.
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction  

 

만약 Transaction A에서 COMMIT이 발생하면, 쿼리문UPDATE user SET name = "updated" WHERE id = 1;에 대한 대기가 풀려 해당 쿼리문이 실행되게 된다.

요약하자면, SERIALIZABLE isolation level은 한 트랜잭션에서 SELECT문이 실행되면 해당 트랜잭션에서 COMMIT이 발생하기 전까지 data lock이 걸려 해당 data에 대해 UPDATE, INSERT, DELETE 작업을 수행할 수 없다.

1. Dirty Read → 발생하지 않음
2. Non-repeatable Read → 발생하지 않음
3. Phantom Read → 발생하지 않음

 

추후에 data lock과 관련지어서 다시 공부해보면 좋을 것 같다.

 

[References]

https://jupiny.com/2018/11/30/mysql-transaction-isolation-levels/

https://mangkyu.tistory.com/300

https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

https://www.postgresql.org/docs/current/transaction-iso.html