티스토리 뷰

MariaDB에서 Transaction Isolation Level 적용하기.

DBMS마다 존재하는 Isolation Level(격리 레벨)에 대해 정리하고자 한다.

MariaDB를 예로 테스트를 진행하였으며, 다른 DBMS도 비슷한 격리레벨을 가지고 있다.


보통의 DBMS에서 공통으로 지원하는 격리 레벨은 다음과 같다.

READ UNCOMMITTED :  Shared Lock이 걸리지 않아 동시성 측면에서 높은 성능을 

보이지만, A라는 Transaction을 실행 중에 변경한 값을 B라는 Transaction에서 

접근이 가능 (Dirty Read)하기 때문에 일관성에 문제가 발생 하는등의 기회 비용이 

들어간다.


READ COMMITTED:  커밋된 Transaction에 대해서 다른 Transaction 내에서 

볼 수 있습니다. 다른 Session의 Transaction에 영향을 받기 때문에 동일 SQL

내에서 다른 결과값이 반환 될 수 있다. 따라서 데이터 일관성 문제 

(Non-Repeatable Read)가 발생 할 수 있다.


REPEATABLE READ: InnoDB table에서 기본으로 설정된 Level이다. 각기 다른 

Session내의 Transaction에대한 완벽한 독립성을 유지한다. 즉 A Transaction이 

Commit되어도 B Transaction에 영향을 미치지 않는다. 하지만 Transaction 진행 중에 

다른 Transaction이 입력하는 것을 허용하기 때문에 조회 시 없던 데이터가 나타 날 

가능성이 있다. (Phantom Read)


SERIALIZABLE: 가장 강력한 Isolation Level로 조회중인 모든 Record에 대한 Lock이 

발생하므로 Transaction 내에 조회중인 Record에 대한 어떠한 수정/삭제도 허용하지 

않는다. 동시성에 대한 성능저하가 발생하지만, 가장 완벽한 일관성 모드를 지원한다.




step 1. 현재 Isolation Level 확인

MariaDB > SELECT @@tx_isolation;

+-----------------+

| @@tx_isolation  |

+-----------------+

| REPEATABLE-READ |

+-----------------+



step 2. Isolation Level 별 테스트

- READ UNCOMMITTED LEVEL

MariaDB> SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;


seesion 1

MariaDB> SELECT * FROM test;

Empty set (0.02 sec)



session 2

트랙젝션을 시작 후 테이블에 Row를 추가한다.

MariaDB> BEGIN;

MariaDB> INSERT INTO test (column1) VALUES (1),(2);

MariaDB> select * from test;
+----+---------+---------+
| no | column1 | column2 |
+----+---------+---------+
|  1 | 1       | NULL    |
|  2 | 2       | NULL    |
+----+---------+---------+
2 rows in set (0.00 sec)


session 1

seesion2에서 Commit이 안된 Transaction에서 입력된 데이터 조회가 가능하다.

MariaDB> select * from test;
+----+---------+---------+
| no | column1 | column2 |
+----+---------+---------+
|  1 | 1       | NULL    |
|  2 | 2       | NULL    |
+----+---------+---------+
2 rows in set (0.00 sec)


session 2
rollback후 데이터가 원복이 되었음을 확인 할 수 있다.

MariaDB> ROLLBACK;

MariaDB> select * from test;
Empty set (0.00 sec)


seesion 1

session2 rollback 후 데이터가 원복이 되었음을 확인 할 수 있다.

MariaDB> SELECT * FROM test;

Empty set (0.02 sec)




- READ COMMITTED LEVEL

MariaDB> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;


session 1

MariaDB> BEGIN;

Query OK, 0 rows affected (0.00 sec)

MariaDBSELECT * FROM test;

Empty set (0.02 sec)


session 2

MariaDB> BEGIN;

MariaDB> INSERT INTO test (column1) VALUES (1),(2);

MariaDB> select * from test;

+----+---------+---------+

| no | column1 | column2 |

+----+---------+---------+

|  3 | 1       | NULL    |

|  4 | 2       | NULL    |

+----+---------+---------+

2 rows in set (0.00 sec)


session 1

session2 에서 commit이 안된 상태기 때문에 select 시 조회 되지 않는다.

MariaDB> SELECT * FROM test;

Empty set (0.02 sec)


session 2

MariaDB> commit;

Query OK, 0 rows affected (0.03 sec)


session 1

session1의 Transaction이 진행중인 상태라도 session2 commit 후 session1에 영향을 

미치기 때문에 조회가 가능하다. 

MariaDB> select * from test;

+----+---------+---------+

| no | column1 | column2 |

+----+---------+---------+

|  3 | 1       | NULL    |

|  4 | 2       | NULL    |

+----+---------+---------+

2 rows in set (0.00 sec)


MariaDB> commit;

Query OK, 0 rows affected (0.03 sec)


또 session1,2에서 동시에 Transaction이 진행 중  session2에서 데이터를 삭제 후

session1에서 이미 삭제된 데이터를 update 시 Lock이 발생하여 timeout이 발생한다.

session 2

MariaDB> BEGIN;

MariaDB> delete from test where no=3;

Query OK, 1 row affected (0.00 sec)


session 1

MariaDB> update test set column1=3 where no=3;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction




REPEATABLE READ LEVEL

MariaDB> SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;


session 1

MariaDB> BEGIN;

Query OK, 0 rows affected (0.00 sec)


MariaDB> SELECT * FROM test;

+----+---------+---------+

| no | column1 | column2 |

+----+---------+---------+

|  3 | 1       | NULL    |

|  4 | 2       | NULL    |

|  5 | 3       | NULL    |

|  6 | 4       | NULL    |

|  7 | 5       | NULL    |

|  8 | 6       | NULL    |

+----+---------+---------+

6 rows in set (0.00 sec)



session 2

MariaDB> BEGIN;

Query OK, 0 rows affected (0.00 sec)


MariaDB> INSERT INTO test (column1) VALUES (7);

Query OK, 1 row affected (0.03 sec)


MariaDB > SELECT * FROM test;

+----+---------+---------+

| no | column1 | column2 |

+----+---------+---------+

|  3 | 1       | NULL    |

|  4 | 2       | NULL    |

|  5 | 3       | NULL    |

|  6 | 4       | NULL    |

|  7 | 5       | NULL    |

|  8 | 6       | NULL    |

|  9 | 7       | NULL    |

+----+---------+---------+

7 rows in set (0.00 sec)


MariaDB> COMMIT;

Query OK, 0 rows affected (0.06 sec)


session 1

Read Commited와 다르게  Transaction이 진행 중일 경우 다른 Session의 영향을 받지않고

Commit을 해야 다른 Session에서 Commit한 결과가 반영된다. 

(Transaction의 일관성 보장)

MariaDB> SELECT * FROM test;

+----+---------+---------+

| no | column1 | column2 |

+----+---------+---------+

|  3 | 1       | NULL    |

|  4 | 2       | NULL    |

|  5 | 3       | NULL    |

|  6 | 4       | NULL    |

|  7 | 5       | NULL    |

|  8 | 6       | NULL    |

+----+---------+---------+

6 rows in set (0.00 sec)


MariaDB> commit;

Query OK, 0 rows affected (0.00 sec)


MariaDB> SELECT * FROM test;

+----+---------+---------+

| no | column1 | column2 |

+----+---------+---------+

|  3 | 1       | NULL    |

|  4 | 2       | NULL    |

|  5 | 3       | NULL    |

|  6 | 4       | NULL    |

|  7 | 5       | NULL    |

|  8 | 6       | NULL    |

|  9 | 7       | NULL    |

+----+---------+---------+

7 rows in set (0.00 sec)




- SERIALIZABLE LEVEL

MariaDB> SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;


session 1

MariaDB> BEGIN;

Query OK, 0 rows affected (0.00 sec)


MariaDB [iccs]> SELECT * FROM test;

+----+---------+---------+

| no | column1 | column2 |

+----+---------+---------+

|  3 | 1       | NULL    |

|  4 | 2       | NULL    |

|  5 | 3       | NULL    |

|  6 | 4       | NULL    |

|  7 | 5       | NULL    |

|  8 | 6       | NULL    |

|  9 | 7       | NULL    |

+----+---------+---------+

7 rows in set (0.00 sec)



session 2

session1이 Transaction을 시작 후 특정 테이블의 Record를 조회 중일 경우 

어떠한 다른 Session에서 Record 접근 시 Lock이 발생한다.

MariaDB> BEGIN;

Query OK, 0 rows affected (0.00 sec)


MariaDB> UPDATE test SET column1=77 where no = 9;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction


DBMS의 동시 성 측면에서는 느슨한 Isolation Level이 퍼포먼스가 높게 평가되지만

데이터 일관성 문제를 고려하지 않을 수 없기 때문에 기본적으로 

Repeatable Read Isolation Level (InnoDB 기본 격리레벨) 정도는 

유지해야 일관성 문제를 피해갈 수 있을 것이다.


참고 사이트

http://www.databasejournal.com/features/mysql/article.php/10897_3393161_2/MySQL-Transactions-Part-II---Transaction-Isolation-Levels.htm

댓글