MySQL - 트랜잭션 격리 수준 (transaction isolation level) 테스트

MySQL로 트랜잭션 격리 수준 동작 테스트를 하고, 결과를 정리해 놓는다.

트랜잭션 범위

MySQL에서 트랜잭션은 START TRANSACTION으로 시작하고, COMMIT으로 다음과 같이 실행된다.

START TRANSACTION
...
(트랜잭션)
...
COMMIT

 

트랜잭션 격리 수준 확인/ 변경

MySQL에서의 트랜잭션 격리수준 확인 및 변경은 아래 포스트를 참고한다.

MySQL 트랜잭션 격리수준 확인/변경

 

본 포스트에서는, 구문형으로 트랜잭션을 변경하는 대신, 시스템 변수를 사용하여 트랜잭션을 변경하는 방식을 사용하였으며, 테스트 환경이 MySQL 8.x 이므로, 변수명도 transaction_isolation으로 사용하였다. 구 버전이라면 tx_isolation으로 대체 사용하거나 원래의 구문형 문법을 사용하여 트랜잭션 격리수준을 변경 적용하면 동일한 결과를 기대할 수 있다.

 

테스트에 사용한 스키마

CREATE TABLE IF NOT EXISTS `students` (
    id int(6) AUTO_INCREMENT,
    name varchar(255) NOT NULL,
    major varchar(255) NOT NULL,
    PRIMARY KEY (id)
);

위와 같이 스키마를 생성하여 테스트에 사용한다.

 

트랜잭션 격리 수준별 동작 테스트

트랜잭션 격리 수준이 다를 때의 동작을 확인하기 위해, 두 개의 서로 다른 세션을 사용한다. 이를 위해, MySQL CLI를 활용한다.

$ mysql -u사용자ID -p

의 명령어를 사용하여 두 개의 세션으로 각각 접속한다.

 

0. Read Uncommitted

두 개의 세션으로 접속한 후, 격리 수준을 Read Uncommitted로 아래와 같이 변경한다.

SET @@session.transaction_isolation = 'READ-UNCOMMITTED'; 

테스트를 시작하기 전의 데이터 상태는 아래와 같다.

mysql> select * from students;
+----+-------+------------------+
| id | name  | major            |
+----+-------+------------------+
|  1 | John  | Music            |
|  2 | Sarah | Art              |
|  3 | Steve | Computer Science |
|  4 | Emma  | Movie            |
|  5 | Tom   | Chemistry        |
+----+-------+------------------+
5 rows in set (0.03 sec)

두 개의 서로 다른 트랜잭션을 각각 시작하고, 각 트랜잭션을 편의상 tx1, tx2로 부른다.

start transaction;

tx1과 tx2에서 sutdents 테이블을 위의 쿼리로 조회하면 동일하게 조회된다.
tx1의 데이터 중 Tom의 major를 Math로 변경해 본다. 단, tx2의 상태는 그대로 유지한다.

tx1에서 아래와 같이 데이터를 변경하되, commit은 실행하지 않는다.

mysql> update students set major = 'Math' where id = 5;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

<tx1의 상태>

mysql> select * from students;
+----+-------+------------------+
| id | name  | major            |
+----+-------+------------------+
|  1 | John  | Music            |
|  2 | Sarah | Art              |
|  3 | Steve | Computer Science |
|  4 | Emma  | Movie            |
|  5 | Tom   | Math             |
+----+-------+------------------+
5 rows in set (0.03 sec)

<tx2의 상태>

mysql> select * from students;
+----+-------+------------------+
| id | name  | major            |
+----+-------+------------------+
|  1 | John  | Music            |
|  2 | Sarah | Art              |
|  3 | Steve | Computer Science |
|  4 | Emma  | Movie            |
|  5 | Tom   | Math             |
+----+-------+------------------+
5 rows in set (0.00 sec)

이 격리 수준에서는, commit하지 않은 tx1에서의 데이터 변경 상태가 그대로 tx2에 노출되는 dirty read 상태가 관측된다. tx1에서 rollback을 하는 순간, tx2에서의 상태도 똑같이 rollback 상태로 반영이 되지만, 만약 tx2에서 데이터를 읽어서 어떤 비즈니스 로직을 처리하고자 했다면 문제를 일으켰을 것이다.

 

1. Read Committed

두 개의 세션으로 접속한 후, 격리 수준을 Read Committed로 아래와 같이 변경한다.

SET @@session.transaction_isolation = 'READ-COMMITTED'; 

테스트를 시작하기 전의 데이터 상태는 아래와 같다.

mysql> select * from students;
+----+-------+------------------+
| id | name  | major            |
+----+-------+------------------+
|  1 | John  | Music            |
|  2 | Sarah | Art              |
|  3 | Steve | Computer Science |
|  4 | Emma  | Movie            |
|  5 | Tom   | Chemistry        |
+----+-------+------------------+
5 rows in set (0.03 sec)

앞서의 테스트와 마찬가지로, 트랜잭션 내에서 tx1에서만 데이터를 변경하고, tx2에서는 어떻게 감지하는지 확인한다. tx1에서 데이터를 변경하고 commit은 하지 않은 상태에서 데이터를 tx1, tx2에서 각각 조회한다.

mysql> update students set major = 'Math' where id = 5;

<tx1의 상태>

mysql> select * from students;
+----+-------+------------------+
| id | name  | major            |
+----+-------+------------------+
|  1 | John  | Music            |
|  2 | Sarah | Art              |
|  3 | Steve | Computer Science |
|  4 | Emma  | Movie            |
|  5 | Tom   | Math             |
+----+-------+------------------+
5 rows in set (0.01 sec)

<tx2의 상태>

mysql> select * from students;
+----+-------+------------------+
| id | name  | major            |
+----+-------+------------------+
|  1 | John  | Music            |
|  2 | Sarah | Art              |
|  3 | Steve | Computer Science |
|  4 | Emma  | Movie            |
|  5 | Tom   | Chemistry        |
+----+-------+------------------+
5 rows in set (0.00 sec)

이 상태에서, tx1의 트랜잭션을 commit 해보자. tx1의 상태와 tx2의 상태가 똑같이 조회된다.

mysql> select * from students;
+----+-------+------------------+
| id | name  | major            |
+----+-------+------------------+
|  1 | John  | Music            |
|  2 | Sarah | Art              |
|  3 | Steve | Computer Science |
|  4 | Emma  | Movie            |
|  5 | Tom   | Math             |
+----+-------+------------------+
5 rows in set (0.01 sec)

적어도 commit한 후의 신뢰성이 확보된 데이터가 다른 트랜잭션에 보인다는 점에서 신뢰성은 높아졌다. 그러나, 트랜잭션이 실행중인 tx2에서 같은 SELECT가 다른 결과를 보여줄 수 있는 문제는 해결되지 않았다. 참고로, 오라클에서의 기본 트랜잭션 격리수준이기도 하다.

 

2. Repeatable Read

MySQL의 트랜잭션 격리수준의 디폴트값은 REPTEATABLE-READ이므로 새로 접속했다면 굳이 변경할 필요는 없다. 그러나, 위의 테스트를 거쳐왔다면 다음의 명령어를 실행하여, 현재 격리수준이 REPEATABLE-READ가 되도록 설정한다.

SET @@session.transaction_isolation = 'REPEATABLE-READ'; 

두 세션에서 각각 start transaction을 수행한다. 어떤 데이터 변경도 하기 전의 두 tx의 상태는 각각 아래와 같다.

<tx1의 상태>

mysql> select * from students;
+----+-------+------------------+
| id | name  | major            |
+----+-------+------------------+
|  1 | John  | Music            |
|  2 | Sarah | Art              |
|  3 | Steve | Computer Science |
|  4 | Emma  | Movie            |
|  5 | Tom   | Math             |
+----+-------+------------------+
5 rows in set (0.01 sec)

<tx2의 상태>

mysql> select * from students;
+----+-------+------------------+
| id | name  | major            |
+----+-------+------------------+
|  1 | John  | Music            |
|  2 | Sarah | Art              |
|  3 | Steve | Computer Science |
|  4 | Emma  | Movie            |
|  5 | Tom   | Math             |
+----+-------+------------------+
5 rows in set (0.05 sec)

tx1에서 하나의 레코드를 추가하고 commit을 하기 전이라면, tx1에만 해당 데이터가 나타난다.

<tx1: 레코드 하나 추가 직후>

mysql> insert into students(name, major) values ('Mika', 'Music');
Query OK, 1 row affected (0.08 sec)

mysql> select * from students;
+----+-------+------------------+
| id | name  | major            |
+----+-------+------------------+
|  1 | John  | Music            |
|  2 | Sarah | Art              |
|  3 | Steve | Computer Science |
|  4 | Emma  | Movie            |
|  5 | Tom   | Math             |
|  7 | Mika  | Music            |
+----+-------+------------------+
6 rows in set (0.00 sec)

tx1을 commit 한다. 그리고, tx2에서 데이터를 조회해 본다.

<tx2: tx1 commit 실행 후>

mysql> select * from students;
+----+-------+------------------+
| id | name  | major            |
+----+-------+------------------+
|  1 | John  | Music            |
|  2 | Sarah | Art              |
|  3 | Steve | Computer Science |
|  4 | Emma  | Movie            |
|  5 | Tom   | Math             |
+----+-------+------------------+
5 rows in set (0.03 sec)

Repeatable Read와 Read Committed의 차이는, 이렇게 반영된다. Read Committed라는 이름은 commit되면 나타난다. 그런데, Repeatable Read는 읽어도 같은 값을 보여주도록 보장해준다는 것을 의미한다. DB에 따라 구현 방식에 차이가 있을 수 있는데, 일부 DB에서는 이를 Lock을 통해 지원하고, MySQL은 이 문제를 Snapshot을 통해 지원한다.

 

3. Serializable

가장 높은 트랜잭션 격리 수준인 Serializable로 변경해 보자.

SET @@session.transaction_isolation = 'SERIALIZABLE'; 

마찬가지로 두 개의 세션을 열고, 각각 start transaction을 실행한다.

<tx1 상태>

mysql> select * from students;
+----+-------+------------------+
| id | name  | major            |
+----+-------+------------------+
|  1 | John  | English          |
|  2 | Sarah | Art              |
|  3 | Steve | Computer Science |
|  4 | Emma  | Movie            |
|  5 | Tom   | Math             |
|  7 | Mika  | Music            |
|  8 | Bob   | Art              |
+----+-------+------------------+
7 rows in set (0.05 sec)

<tx2 상태>

mysql> select * from students;
+----+-------+------------------+
| id | name  | major            |
+----+-------+------------------+
|  1 | John  | English          |
|  2 | Sarah | Art              |
|  3 | Steve | Computer Science |
|  4 | Emma  | Movie            |
|  5 | Tom   | Math             |
|  7 | Mika  | Music            |
|  8 | Bob   | Art              |
+----+-------+------------------+
7 rows in set (0.00 sec)

이 상태에서 tx2에서 임의의 레코드를 먼저 업데이트를 시도해 본다.

ysql> update students set major = 'Music' where id = 8;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

tx1에서 먼저 잡은 lock으로 인해 일정 시간이 경과한 후에, 에러가 발생해 버린다. 만약, tx2에서 timeout이 발생하기 전에 tx1에서 commit이나 rollback이 수행되었다면, tx2의 명령은 정상 실행되었을 것이다. 같은 명령을 tx1에서 실행해도 같은 현상을 확인할 수 있는데, tx2가 실행이 되고 있지 않았더라면 실행이 되고, tx1가 명시적으로 commit 또는 rollback으로 수행이 완료된 상태여야 실행이 보장된다. 즉, lock으로 데이터는 보호하지만, 동시에 많은 요청을 처리하는데는 지연을 발생시킬 수 밖에 없다.

 

lock 상태 확인하기

위와 같이 트랜잭션 격리수준을 변경하면서 테스트를 통해, 동시성을 제어하면서 데이터의 정합성을 보장하는 현상을 확인할 수 있다. 그런데, 이 테스트를 하면서 lock이 실제 어떻게 잡히는지도 함께 확인하면 이해하는데 더 도움이 될 것이다. Lock 정보를 조회하는 메타테이블도 8.x와 구버전의 경우 차이가 있는데 본인의 MySQL 버전에 맞게 정보 조회 명령어를 다르게 사용하여 확인할 수 있다.

• 구버전
  • information_schema.innodb_locks
  • information_schema.innodb_lock_waits
• 신버전
  • performance_schema.data_locks
  • performance_schema.data_lock_waits

  select * from performance_schema.data_locks

  와 같이 실행하면, 현재 lock이 어떻게 걸렸는지 세부 정보를 확인할 수 있다. 컬럼별 의미를 떠나서, 아무도 쓰지 않는 DB의 깨끗한 세션에 접속해서 위의 쿼리를 실행해 보면, 아무 것도 뜨지 않는다. 그러나, 어디선가 실행되고 있는 프로세스가 있다면 뭔가 노출될 것이다. (개인 테스트 환경의 장점인데, 신규로 설치하면 나밖에 없으므로 변화를 감지하기 좋다. 그러나, 운영중인 서비스라면 다른 프로세스들이 이미 실행 중일 것이라 파악하는데 좀더 어려울 수 밖에 없을 것이다.)

위의 테스트를 실행하면서, 언제 lock 이 생성되는지 위의 쿼리로 병행해서 확인해 보자. Read Committed, Repeatable Read의 경우, select 할때는 lock이 발견되지 않으나, update시 lock이 발견되는 것을 확인할 수 있다. 그러나, Serializable의 경우, select를 실행해도 lock이 발견된다.

 

참고

Docker 기반 MySQL 설치하기

DB 트랜잭션 - 격리수준(Transaction Isolation Level)