MySQL / MariaDB 트랜잭션 Isolation Level

(출처 : http://labs.brandi.co.kr/2019/06/19/hansj.html - 다른 분의 개인 블로그)

(출처 : https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html - 공식 DOC)

 

⊙ 이 페이지에서는 MySQL / MariaDB 트랜잭션 Isolation Level 의 개념 및 종류를 다룬다.

⊙ Isolation 은 데이터베이스의 기본 원리인 ACID 의 I 를 뜻한다.
(Atomic : 원자성 , Consistency : 일관성, Isolation : 격리성, 독립성 , Durability : 지속성)

⊙ 즉, 데이터베이스를 사용하는 사용자 혹은 세션간의 트랜잭션 처리에 대해서 다른 사용자(세션)의 영향을 받지 않도록 보장한다. 또한 이는 DB Lock 과 밀접하게 연관되어서 트랜잭션간의 Lock (혹은 격리) 수준에도 영향을 준다.
(격리성과 lock 수준의 정도는 비례관계다.)

 

⊙ MySQL / MariaDB 는 트랜잭션 간에 총 4개의 Isolation Level 을 설정 할 수 있고 각 Level 은 아래의 특성을 따른다.

⊙ MySQL / MariaDB 와 PostgreSQL / PPAS 의 Isolation Level 은 동일하나 약간씩의 차이가 있다.
(PostgreSQL / PPAS 참조 : PostgreSQL / PPAS 트랜잭션 Isolation Level )

READ UNCOMMITTED


  - select for update, update, delete 구문 실행 후, commit, rollback 에 관계 없이 수행 전의 데이터를 읽어 들인다.

이로 인해 데이터 간의 일관성을 보장하기 위한 lock 이 발생하지 않는다.

 

  - 위의 방식으로 인해, 일관성이 보장되지 않는 데이터를 읽어 들일 수도 있다. (Dirty Read 라고 한다.)

 

  - 아래의 Read Committed 와 유사한 작동 메커니즘을 보인다.

READ COMMITTED

  - ORACLE, PostgreSQL, MSSQL 의 default Isolation Level 이다.

  - Read uncommitted 와는 다르게 select for update, update, delete 구문 실행 후, commit 된 데이터를 읽어 들인다.

 

  - 변경이 발생되는 레코드에 대해서 인덱스 레코드만 lock 이 걸리기 때문에, 인덱스 데이터 중복 (duplicate) 등의 문제가 발생 하지 않는 한 지속 기입, 수정이 가능하다.

  - MySQL / MariaDB 에서는 데이터 변경이 발생 시, commit 되지 않은 데이터 변경 내역을 undo tablespace 에서 관리함. 특정 트랜잭션에서 commit 전의 변경 발생 시, 다른 트랜잭션은 이로 인해 발생하는 변경 전의 데이터를 undo 에 보관된 기록에서 가져와서 조회 함.

  - 위의 방식으로 인해 비슷한 시간대에 동일 쿼리를 수행했는데 처음 수행했을때 보여지는 데이터 row 와 그 이후에 보여지는 row 간에 정합성 차이 혹은 조회 일관성 이상등이 발생 할 수 있다.
이로 인하여 처음 수행시 보여지지 않았으나 두번째 수행시 보이는 등의 이상이 발생하는 row 를 phantom row 라고 하며 (read 이상은 phantom read 라고 한다),
이를 방지하기 위해서 gap lock 이라는 개념이 존재하는데 해당 격리 단계 에서는 사용하지 않는다.

 

 

REPEATABLE READ

  - MySQL / MariaDB (InnoDB 기준) 의 default Isolation Level 이다.

  - 동일 세션내의 트랙잭션에서도 데이터 조회 변동 시점간의 gap 을 관리하기 위하여 snapshot 이라는 개념을 사용하며, 이를 통하여 다른 세션으로 인하여 데이터가 변동되도 각 시점간의 snapshot 을 이용하여 일관된 데이터를 조회 및 변동 할 수 있다.
(snapshot 은 MySQL / MariaDB 의 MVCC 관리기법에서 사용하는 undo 공간에서 관리된다.)

  - 위의 방식으로 인하여 gap lock 을 사용하므로 이 lock 이 발생 할 수 있으며
select for update, update, delete 구문 실행 후, commit, rollback 및 다양한 상황에서 조건에 따라 record lock 도 발생 가능하다.

  - 조건에 따라 index range scan lock 도 발생하기 때문에 다른 세션으로 부터 들어오는 create select, insert select 방식의 조건 데이터 조회 후 기입 방식도 lock 이 걸릴수 있다.
(기입 시, 조건 데이터 조회에 따른 index scan 을 발생 시킬수도 있기 때문)

SERIALIZABLE (직렬화)

  - 격리성이 제일 높은 단계다.

  - 작동구조는 REPEATABLE READ 와 유사하지만, 여기에 추가로 autocommit 이 0 일 때, 모든 select 구문을 select ~ for share 로 전환시켜서 shared lock 이 발생되게 한다. autocommit 이 1일때는 DB 내부적으로 lock 을 발생시킬 필요가 없는 일부 쿼리들은 선택적으로 lock 이 발행 하지 않기 때문에 해당 Isolation 을 조건 여부 없이 계속 사용되게 하고 싶다면 autocommit 을 0으로 만드는 것이 좋다.

  - MySQL / MariaDB 에서 online 으로 단일 세션 혹은 global 레벨로 수정 할 수 있다.
아래 사진과 같이 tx_isolation, transaction_isolation 의 2개의 항목이 있으나 동일한 옵션이다. 해당 옵션은 5.7 버전 까지 존재한다. (8.0 에서 부터 transaction_isolation 항목 삭제)