Mysql 트랜잭션 격레성 레벨 (Isolation Level)

Transaction

• 데이터의 정합성을 보장하기 위한 기능 (partial update 를 방지하는 기능)

• DBMS 커낵션의 수는 한정되어 있으므로, 트랜잭션 당 커낵션을 점유하는 시간을 최소화 해야 한다. → 트랜잭션 내에서 트랜잭션과 무관한 연산 및 network I/O 는 피해야 한다.

Isolation Level

• 여러 트랜잭션 간의 데이터 생성/변경/삭제를 어떻게 공유하고 차단할 것인지 결정한다.

• 격리성 레벨은 아래 4가지로 나뉜다. (아래로 갈수록 격리 정도는 높아지고 동시성은 떨어짐)
• Read Uncommitted
• Read Committed
• Repeatable Read
• Serializable

• Read Uncommitted 레벨은 격리 정도가 너무 낮고, Serializable 레벨은 동시성이 너무 낮아서 잘 사용하지 않는다.

부정합 (anomaly)

MVCC(Multi-Versioning Concurrency Control)

• Multi-Versioning 이란 하나의 레코드를 여러 버전으로 관리하는 것이다.

• InnoDB 는 언두(undo) 로그를 통해 Multi-Versioning 이 가능하다.

+------------+-----------+--------+
| first_name | last_name | emp_no |
+------------+-----------+--------+
| DaEun      | Kim       | A1234  |
+------------+-----------+--------+

• 격리성 레벨이 Read Uncommitted 인 경우, 버퍼 풀에서 데이터를 조회하므로 트랜잭션 A가 커밋하지 않은 데이터가 트랜잭션 B에서 보인다.

• 격리성 레벨이 Read Committed, Repeatable Read 인 경우에는 undo 영역에서 데이터를 조회하므로 트랜잭션 A 가 커밋하지 않은 데이터는 보이지 않는다.

Consistent Non-locking Read

• SELECT를 실행할 때는 락을 획득하거나 획득하기 위해 기다릴 필요가 없다. (락을 획득하려면 SELECT ~ LOCK IN SHARE MODE 와 같은 쿼리를 수행한다.)

• 다른 트랜잭션에서 UPDATE 를 수행한다고 해도 일관된 레코드를 조회하는 데 영향을 받지 않는다. (Read Committed 에서는 영향 받을 수 있음)

Read Uncommitted

• 가장 낮은 수준의 격리 단계로, 사실 상 트랜잭션이 서로 격리되지 않는 단계를 말한다.

• 다른 트랜잭션이 커밋하지 않은 데이터를 볼 수 있다. → insert/update 시도한 트랜잭션이 롤백할 경우 데이터가 생겼다 사라지게 되므로 혼란 초래.

Read Committed

• undo 로그를 통해 다른 트랜잭션이 커밋한 데이터만 볼 수 있기 때문에 Dirty Read 가 발생하지 않는다.

• 트랜잭션 내에서 레코드를 조회할 때 매번 새로운 undo 로그를 조회한다. → Non-Repeatable Read 가 발생한다.

mysql> SELECT * FROM employee WHERE emp_no = 'A1234';
+------------+-----------+--------+
| first_name | last_name | emp_no |
+------------+-----------+--------+
| DaEun      | Kim       | A1234  |
+------------+-----------+--------+
1 row in set (0.00 sec)

• 외래키 제약 조건을 검사하거나 duplicate-key 검사를 할 때를 제외하고 UPDATE / DELETE / SELECT FOR UPDATE 수행 시 gap lock 을 생성하지 않으므로, 다른 트랙잭션이 gap 에 INSERT 하는 것을 허용한다. → gap 대상으로 phantom read 가 발생한다.

mysql> select * from test_gap_lock;
+-------------------------+---------+
| cn_without_unique_index | cn_name |
+-------------------------+---------+
|                       2 | name222 |
|                       3 | name3   |
|                       4 | name4   |
+-------------------------+---------+
3 rows in set (0.01 sec)

mysql> show index from test_gap_lock;
+---------------+------------+--------------------------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table         | Non_unique | Key_name                       | Seq_in_index | Column_name             | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------------+------------+--------------------------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| test_gap_lock |          1 | idx_to_cn_without_unique_index |            1 | cn_without_unique_index | A         |           2 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------------+------------+--------------------------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)

Repeatable Read

• undo 로그를 통해 다른 트랜잭션이 커밋한 데이터만 볼 수 있기 때문에 Dirty Read 가 발생하지 않는다.

• 어떤 트랜잭션 내에서 레코드를 조회할 때, 해당 트랜잭션이 만들어지기 전에 가장 마지막에 생성된 undo 로그만을 참조하기 때문에 Non-Repeatable Read 가 발생하지 않는다.

• UPDATE / DELETE / SELECT FOR UPDATE 수행 시 gap lock 을 생성하므로, 다른 트랙잭션이 gap 에 INSERT/ UPDATE / DELETE 하는 것을 허용하지 않는다. → phantom read 가 발생하지 않는다.

MVCC 측면에서 트랜잭션을 빨리 종료해야 하는 이유

• 트랜잭션이 커밋되면 언두 로그를 조회하는 다른 트랜잭션이 종료될 때 까지 기다리다가, 언두 로그를 삭제한다.

• 트랜잭션이 롤백되면 언두 로그에 있는 버전을 다시 버퍼 풀과 디스크에 반영하고 언두 로그를 곧바로 삭제한다.