MySQL의 Transaction Isolation Levels
사실 이 글의 목적은 데이터베이스의 Transaction Isolation Levels에 대해 공부한 내용을 쓰기 위함이었는데, MySQL을 예로 사용하며 여러가지 실습해보며 MySQL에서만 적용되는 몇가지 특성이 있음을 알게 되었고, 제목에다가 "MySQL"을 붙이게 되었다.
아래 4가지 Transaction Isolation Levels에 대해 공부한 내용을 각각 나누어 적어보았다.
실습에서는 MySQL 14.14 버젼, Storage Engine은 InnoDB를 사용하였습니다.
우선 실습하기 전에 미리 실습용 테이블을 하나 생성하였다.
CREATE TABLE user (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
테이블에 아래와 같이 데이터도 추가하였다.
mysql> INSERT INTO user (name) VALUES ("jupiny");
mysql> INSERT INTO user (name) VALUES ("jupiny2");
mysql> INSERT INTO user (name) VALUES ("jupiny3");
mysql> SELECT * FROM user;
+----+---------+
| id | name |
+----+---------+
| 1 | jupiny |
| 2 | jupiny2 |
| 3 | jupiny3 |
+----+---------+
READ UNCOMMITTED
READ UNCOMMITTED 은 이름 그대로 아직 다른 트랜잭션에서 COMMIT되지 않은 데이터들을 읽어올 수 있는 level이다. 이 때 읽어온 데이터들이 결국 COMMIT 된다면 더 빨리 읽어오는게 무슨 문제가 있겠냐 생각할 수도 있다. 하지만 만약 해당 트랜잭션이 COMMIT 되지 않고 ROLLBACK 된다면 결국 세상에 존재해서는 안 될 데이터를 읽어온 게 되어버린다.(이렇게 신뢰할 수 없는 데이터를 읽어오는 것을 dirty read 라고 부른다.)
[TRANSACTION-1]
mysql> SET SESSION transaction isolation level READ UNCOMMITTED;
mysql> START TRANSACTION;
mysql> SELECT * FROM user;
+----+---------+
| id | name |
+----+---------+
| 1 | jupiny |
| 2 | jupiny2 |
| 3 | jupiny3 |
+----+---------+
[TRANSACTION-2]
mysql> START TRANSACTION;
mysql> UPDATE user SET name = "updated" WHERE id = 1;
mysql> INSERT INTO user (name) VALUES ("inserted");
[TRANSACTION-1]
mysql> SELECT * FROM user;
+----+----------+
| id | name |
+----+----------+
| 1 | updated |
| 2 | jupiny2 |
| 3 | jupiny3 |
| 4 | inserted |
+----+----------+
결과를 보면 알 수 있듯이, TRANSACTION-2 에서 아직 COMMIT 하지 않았음에도 불구하고, TRANSACTION-1 에서는 그 데이터들을 읽어옴을 볼 수 있다.
READ UNCOMMITTED level에서는 아래 세 가지 현상이 모두 발생함을 알 수 있다.
- 아직
COMMIT되지 않은 신뢰할 수 없는 데이터를 읽어옴(dirty read) - 한 트랜잭션에서 동일한
SELECT쿼리의 결과가 다름(non-repeatable read) - 이전의
SELECT쿼리의 결과에 없던 row가 생김(phantom read)
READ COMMITTED
READ COMMITTED 는 다른 트랜잭션에서 COMMIT 된 데이터만 읽어올 수 있는 level이다.
[TRANSACTION-1]
mysql> SET SESSION transaction isolation level READ COMMITTED;
mysql> START TRANSACTION;
mysql> SELECT * FROM user;
+----+---------+
| id | name |
+----+---------+
| 1 | jupiny |
| 2 | jupiny2 |
| 3 | jupiny3 |
+----+---------+
[TRANSACTION-2]
mysql> START TRANSACTION;
mysql> UPDATE user SET name = "updated" WHERE id = 1;
mysql> INSERT INTO user (name) VALUES ("inserted");
[TRANSACTION-1]
mysql> SELECT * FROM user;
+----+---------+
| id | name |
+----+---------+
| 1 | jupiny |
| 2 | jupiny2 |
| 3 | jupiny3 |
+----+---------+
앞서 본 READ UNCOMMITTED 의 경우와 다르게 TRANSACTION-2 에서 아직 COMMIT 하지 않았기 때문에 SELECT 쿼리의 결과가 이전과 동일함을 볼 수 있다.
- 아직
COMMIT되지 않은 신뢰할 수 없는 데이터는 읽어올 수 없음(dirty read)
여기서 TRANSACTION-2 을 COMMIT하면,
[TRANSACTION-2]
mysql> COMMIT;
[TRANSACTION-1]
mysql> SELECT * FROM user;
+----+----------+
| id | name |
+----+----------+
| 1 | updated |
| 2 | jupiny2 |
| 3 | jupiny3 |
| 4 | inserted |
+----+----------+
이제 TRANSACTION-1 에서 읽어올 수 있게 된다.
- 한 트랜잭션에서 동일한
SELECT쿼리의 결과가 다름(non-repeatable read) - 이전의
SELECT쿼리의 결과에 없던 row가 생김(phantom read)
REPEATABLE READ
REPEATABLE READ 은 MySQL InnoDB의 기본 isolation level이다. MySQL에서 지정한 기본 level인만큼 동시성과 안정성의 균형을 가장 잘 갖춘 level이 아닐까 생각한다.
실습을 하기에 앞서, 먼저 내가 알고 있던 REPEATABLE READ 에 대해 기대했던 결과를 먼저 적어보았다.
- 아직
COMMIT되지 않은 신뢰할 수 없는 데이터는 읽어올 수 없음(dirty read) - 한 트랜잭션에서 동일한
SELECT쿼리의 결과가 동일(non-repeatable read) - 이전의
SELECT쿼리의 결과에 없던 row가 생김(phantom read)
하지만 실제 MySQL에서는 이 결과와 달랐다.(사실 이 부분에서 많이 놀랐다.)
앞서 실습했던 것과 동일한 순서로 테스트해보면,
[TRANSACTION-1]
mysql> SET SESSION transaction isolation level REPEATABLE READ;
mysql> START TRANSACTION;
mysql> SELECT * FROM user;
+----+---------+
| id | name |
+----+---------+
| 1 | jupiny |
| 2 | jupiny2 |
| 3 | jupiny3 |
+----+---------+
[TRANSACTION-2]
mysql> START TRANSACTION;
mysql> UPDATE user SET name = "updated" WHERE id = 1;
mysql> INSERT INTO user (name) VALUES ("inserted");
[TRANSACTION-1]
mysql> SELECT * FROM user;
+----+---------+
| id | name |
+----+---------+
| 1 | jupiny |
| 2 | jupiny2 |
| 3 | jupiny3 |
+----+---------+
- 아직
COMMIT되지 않은 신뢰할 수 없는 데이터는 읽어올 수 없음(dirty read)
[TRANSACTION-2]
mysql> COMMIT;
[TRANSACTION-1]
mysql> SELECT * FROM user;
+----+---------+
| id | name |
+----+---------+
| 1 | jupiny |
| 2 | jupiny2 |
| 3 | jupiny3 |
+----+---------+
- 한 트랜잭션에서 동일한
SELECT쿼리의 결과가 동일(non-repeatable read) - 이전의
SELECT쿼리의 결과에 없던 row가 생기지 않음(phantom read)
기대했던 것과 다르게 non-repeatable read는 물론, phantom read도 발생하지 않았다.
이 이유는 MySQL 공식 문서에 자세히 설명되어있다.
READ UNCOMMITTED
Each consistent read, even within the same transaction, sets and reads its own fresh snapshot.
REPEATABLE READ
This is the default isolation level for InnoDB. Consistent reads within the same transaction read the snapshot established by the first read.
MySQL에서는 REPEATABLE READ 와 READ COMMITTED level에 대해서 한 트랜잭션에서 SELECT 쿼리로 데이터를 읽어올 때 테이블에 lock을 걸지 않고, 해당 시점의 데이터 상태를 의미하는 snapshot 를 구축하여 거기서 데이터를 읽어온다.
앞에서 다루지 않았지만, READ COMMITTED 에서 각각의 SELECT 쿼리는 그때그때 최신의 snapshot을 구축하여 데이터를 읽어온다. 따라서 한 트랜잭션이지만 SELECT 쿼리의 결과가 다르기도 했다.
이와 다르게 REPEATABLE READ 는 한 트랜잭션에서 처음 데이터를 읽어올 때 구축한 snapshot에서 모두 데이터를 읽어온다. 따라서 매번 SELECT 쿼리의 결과들이 항상 처음과 동일했던 것이고, 이로 인해 phantom read도 발생하지 않았던 것이다.
또 한가지 흥미로운 점은, 비록 한 트랜잭션에서 SELECT 쿼리는 항상 동일하더라도, 다른 트랜잭션에서 건드린 row에 대한 UPDATE, DELETE 의 결과는 출력될 수 있다.
예를 들어 아까 [TRANSACTION-1] 을 아직 COMMIT 하지 않은 상황에서 아래와 같이 UPDATE 쿼리를 실행하면,
[TRANSACTION-1]
mysql> UPDATE user SET name = "upserted" WHERE name = "inserted";
-- Query OK, 1 row affected (0.01 sec)
-- Rows matched: 1 Changed: 1 Warnings: 0
SELECT 쿼리 결과에는 분명 name = "inserted"인 row가 없었지만, 1개의 row가 변경됬다는 문구가 출력되었다.
그리고 다시 SELECT 쿼리를 실행해보면,
[TRANSACTION-1]
mysql> SELECT * FROM user;
+----+----------+
| id | name |
+----+----------+
| 1 | updated |
| 2 | jupiny2 |
| 3 | jupiny3 |
| 4 | upserted |
+----+----------+
새로 갱신된 최신의 snapshot에서 데이터를 읽어옴을 볼 수 있다.
SERIALIZABLE
앞서 REPEATABLE READ 가 동시성과 안정성의 균형을 가장 잘 갖춘 isolation level였다면, SERIALIZABLE 는 동시성을 상당 부분 포기하고 안정성에 큰 비중을 둔 isolation level이다. SERIALIZABLE 은 한 트랜잭션 안에서 단순 SELECT 쿼리를 사용하더라도, 모두 SELECT ... FOR SHARE 으로 변환한다.
SELECT ... FOR SHARE 은 읽어온 row들에 shared lock(또는 read lock)을 거는 쿼리이다.
[TRANSACTION-1]
mysql> SET SESSION transaction isolation level SERIALIZABLE;
mysql> START TRANSACTION;
mysql> SELECT * FROM user;
+----+---------+
| id | name |
+----+---------+
| 1 | jupiny |
| 2 | jupiny2 |
| 3 | jupiny3 |
+----+---------+
[TRANSACTION-2]
mysql> START TRANSACTION;
mysql> SELECT * FROM user;
+----+---------+
| id | name |
+----+---------+
| 1 | jupiny |
| 2 | jupiny2 |
| 3 | jupiny3 |
+----+---------+
row들에 shared lock이 걸려있으므로 SELECT 쿼리로 데이터는 읽어올 수 있지만,
[TRANSACTION-2]
mysql> UPDATE user SET name = "updated" WHERE id = 1;
UPDATE 나 INSERT 쿼리로는 해당 row의 lock이 풀리기 전까지 수정이나 추가할 수 없다. 따라서 위에서처럼 UPDATE 쿼리를 실행하면 결과가 출력되지 않고 계속 대기 상태에 있게 된다 .
물론 한없이 기다리진 않고, 설정된 timeout이 지나면 아래와 같이 에러를 출력할 것이다.
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
만약 timeout 되기 전에 TRANSACTION-1 이 COMMIT 한다면, 걸려있던 lock이 풀려나 UPDATE 쿼리가 정상적으로 실행될 것이다.
이렇듯 SERIALIZABLE 에서는, 한 트랜잭션에서 SELECT 쿼리를 실행하면 그 트랜잭션이 COMMIT 되기 전까지 다른 트랜잭션에서는 수정, 추가, 삭제 등의 작업조차 할 수 없으므로 아래 3가지 현상 모두 발생할 일이 없다.
- 아직
COMMIT되지 않은 신뢰할 수 없는 데이터를 읽어올 수 없음(dirty read) - 한 트랜잭션에서 동일한
SELECT쿼리의 결과가 항상 동일(non-repeatable read) - 이전의
SELECT쿼리의 결과에 없던 row가 생기지 않음(phantom read)