[MySQL - Transaction Isolation Levels]

■ MySQL 격리 수준(from Real MySQL 8.0)

여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것

크게 "READ UNCOMMITTED" "READ COMMITTED" "REPEATABLE READ" " SERIALIZABLE"로 나뉜다.

하지만 일반적인 데이터베이스에서 READ UNCOMMITTED는 거의 사용하지 않고, SERIALIZABLE또한 동시성이 중요한 데이터베이스에서는 거의 사용되지 않는다.

격리 수준이 높아질수록 성능 개선이나 저하는 발생하지 않는다.

 

※ 일반적인 온라인 서브스 용도에서는 READ COMMITTED와 REPEATABLE READ 중 하나를 사용합니다.

 

 

 

READ UNCOMMITTED

Insert한 정보를 commit하기 전에 select해서 볼 수 있다. 이처럼 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상을 더티 리드(Dirty read)라고 한다.

Dirty read는 데이터가 나타났다가 사라졌다 하는 현상을 초래(Insert했다가 commit전에 롤백하면 보였다 사라지는 현상)

정합성에 문제가 많은 격리 수준이다. MySQL을 사용한다면 최소한 READ COMMITTED 이상의 격리 수준을 사용할 것을 권장한다.

LOCK이 발생하지 않는다.

INSERT, UPDATE, DELETE 후 COMMIT 이나 ROLLBACK에 상관없이 현재의 데이터를 읽어오기 때문에 ROLLBACK이 될 데이터도 읽어올 수 있으므로 주의가 필요

 

 

READ COMMITTED

오라클에서 기본으로 사용되는 격리 수준이며, 온라인 서비스에서 가장 많이 선택되는 격리 수준이다. 

Commit이 완료된 데이터만 다른 트랜잭션에서 조회할 수  있기 때문에 Dirty read같은 현상은 발생하지 않는다. 

하지만 하나의 트랜잭션 내에서 똑같은 select 쿼리를 실행했을 때는 같은 결과를 가져와야하는 REPEATABLE READ 정합성에 어긋난다.

MySQL에서 많은 양의 데이터를 복제하거나 이동할 때 이 Level 추천

Lock이 발생하지 않는다.

 

 

REPEATABLE READ 

commit전에는 undo의 내용이 보인다

MySQL의 Innodb에서 기본으로 사용되는 격리 수준이다. 바이너리 로그를 가진 MySQL 서버에서는 최소 REPEATABLE READ격리 수준 이상을 사용해야한다. 

MVCC 방식을 보장하고 NON-REPEATABLE READ부정합이 발생하지 않는다.

SELECT시 현재 시점의 스냅샷을 만들고 스냅샷을 조회한다.

record lock과 gap lock이 발생한다.

CREATE SELECT, INSERT SELECT시 lock이 발생한다.

Default LEVEL이다.

SELECT시 현재 시점의 스냅샷을 만들고 스냅샷을 조회한다.

동일 트랜잭션 내에서 일관성을 보장한다.

 

※ Insert 도중에 다른 트랜잭션에서 Select ..For Update 로 조회하면 부정합이 발생할 수있다.

 

 

SERIALIZABLE

가장 단순한 격리 수준, 기본적으로 순수한 Select작업은 아무런 레코드 잠금도 설정하지 않고 실행됨. 

일반적인 DB에서 일어나는 PHANTOM READ문제가 일어나지 않는다. Innodb에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ격리 수준에서도 이미 PHANTOM READ가 발생하지 않기때문에

굳이 SERIALIZABLE을 사용할 필요 없어보인다.

SELECT 문에 사용하는 모든 테이블에 shared lock이 발생한다.

 

 

---

사전 확인

 

■ Isolation Levels 확인 

default : REPEATABLE-READ

mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

 

 

■ autocommit OFF 설정

commit 명령 전까지 변경 사항이 반영되지 않게하기 위해 설정

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)


mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.01 sec)

 

 

■ Start transaction

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)


mysql> create table test(name varchar(30), age int);
Query OK, 0 rows affected (0.05 sec)


mysql> insert into test values('kim',27);
Query OK, 1 row affected (0.00 sec)


mysql> commit;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from test;
+------+------+
| name | age  |
+------+------+
| kim  |   27 |
+------+------+
1 row in set (0.00 sec)

 

 

---

격리 수준별 테스트

 

1.READ-UNCOMMITTED 

mysql> set global transaction_isolation='READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)


mysql> show variables like 'transaction_isolation';
+-----------------------+------------------+
| Variable_name         | Value            |
+-----------------------+------------------+
| transaction_isolation | READ-UNCOMMITTED |
+-----------------------+------------------+
1 row in set (0.01 sec)

 

■ 좌측 세션에서 업데이트된 사항이 commit이 되지 않아도 다른세션(우측 세션)에서 변경된 내용을 조회 할 수 있습니다. => DIRTY READ

 

 

 

2.READ-COMMITTED 

mysql> set global transaction_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)


mysql> show variables like 'transaction_isolation';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.00 sec)

 

■ commit(좌측 세션)이 되기전까지 새롭게 변형된 데이터(우측 세션)가 반영되지 않습니다.

 

■ 좌측 세션 업데이트 내역을 commit 이후 우측 세션에서 해당 사항 조회 가능

 

 

 

3.REPEATABLE-READ

mysql> set global transaction_isolation='REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)


mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

 

■ 좌측 세션에서 변경 사항을 commit해도 다른 세션(우측 세션)에서는 해당 사항이 반영되지 않습니다.

즉, 하나의 트랜잭션 내에서 똑같은 조회 쿼리를 실행하면 같은 결과를 가져옵니다.

 

 

 

4.SERIALIZABLE

mysql> set global transaction_isolation='SERIALIZABLE';
Query OK, 0 rows affected (0.00 sec)


mysql> show variables like 'transaction_isolation';
+-----------------------+--------------+
| Variable_name         | Value        |
+-----------------------+--------------+
| transaction_isolation | SERIALIZABLE |
+-----------------------+--------------+
1 row in set (0.00 sec)

■ 1번 세션에서 해당 테이블 조회중

 

■ 2번 세션(다른 세션)에서 해당 테이블에 접근 불가

※ 한 세션에서 트랜잭션(조회, 수정)을 진행하고 있으면 새로운 세션에서 접근 할 수 없다. (shared lock이 걸리기 때문에 select는 가능)

 

 

 

 

참고

 

https://velog.io/@kimkevin90/MySQL-Transaction-Isolation-Levels

MySQL - Transaction Isolation Levels