[데이터 베이스] MySQL트랜잭션 격리수준

트랜잭션의 문제점

여러 트랜잭션이 실행되면서 데이터 불일치에 대한 문제가 발생하게 됩니다. 이런 문제가 없으면 좋겠지만은 여러 트랜잭션이 하나의 데이터를 공유하기 때문에 발생할 수 밖에 없는 문제 입니다.

 

여러 트랜잭션이 실행되면서 발생할 수 있는 데이터 불일치 문제에는 DIRTY READ, NON REPEATABLE READ, PHANTOM READ가 있습니다.

 

DIRTY READ

A트랜잭션의 작업 내용이 완료(commit)되지 않았는데, 그 내용을 B트랜잭션에서 볼 수 있는 현상.

 

A트랜잭션에서 B트랜잭션이 commit하지 않은 데이터를 조회하여 작업 후 commit하였습니다. 이때 만약 B트랜잭션이 롤백된다면, 결국 A트랜잭션은 잘못된 데이터를 사용하여 commit하게 됩니다.

 

사실 생각해보면 DIRTY READ는 말이 되지 않습니다. 트랜잭션은 하나의 논리적인 작업 집합이며, 이 작업이 종료되어야 내부에서 수행한 결과가 유효하게 됩니다. 하지만 작업이 종료되지도 않았는데, 즉 rollback 될지도 모르는 값을 사용한다는 것은 굉장히 위험부담이 있습니다.

 

NON REPEATABLE READ

ACID에 의하면 여러 트랜잭션이 수행되더라도 각각의 트랜잭션은 다른 트랜잭션의 수행결과에 영향을 받아서는 안됩니다. 즉, 하나의 트랜잭션에서 동일한 조건으로 데이터를 조회해도 항상 같은 값이 나와야 합니다. 

 

NON REPEATABLE READ는 트랜잭션에서 데이터를 2번 읽는 쿼리 사이에 다른 트랜잭션에서 데이터를 수정 후 commit했을 때 2번 읽은 데이터가 서로 다른것을 의미합니다. 즉, 데이터를 같은 조건으로 두번 읽었는데 내용이 다른 겁니다. 

 

예를 들어, userId가 1인 유저를 두번 조회했는데 첫번째는 나이가 20, 두번째는 나이가 25인 데이터가 조회되는 현상입니다.

 

이러한 부정한 현상은 일반적인 웹 프로그램에서는 크게 문제되지 않을 수 있지만 하나의 트랜잭션에서 동일 데이터를 여러 번 읽고 변경하는 작업이 금전적인 처리와 연결되면 문제가 클 수가 있습니다.

 

PHANTOM READ

트랜잭션에서 동일한 조회쿼리를 2번 실행됐을 때, 첫번째 조회때 있었던(없었던) 데이터가 두번째 조회때 생긴것(삭제된)을 의미합니다. 

 

예를 들어, age > 20인 유저를 조회했을 때 첫 조회시 20개의 row가 조회되었지만, 두번째 조회시 20개보다 더 많거나, 더 적게 조회되는 경우입니다.

 

첫 조회시 존재했던 데이터가 두번째 조회시 없어지거나, 새로 생긴것이 마치 유령같다고 해서 PHANTOM READ입니다.

 

NON REPEATABLE READ, PHANTOM READ 서로 비슷한거 같은데 ?

두 부정합 문제 모두 하나의 트랜잭션에서 데이터를 여러번 읽었을 때, 결과값이 서로 다른것을 의미하기 때문에 얼핏 생각해보면 그냥 똑같은거 아냐? 라고 생각할 수 있지만 다음과 같은 차이점이 있습니다.

• NON REPEATABLE READ : 다른 트랜잭션에서 데이터를 Update한 경우
• PHANTOM READ : 다른 트랜잭션에서 데이터를 Insert/Delete한 경우

예를 들어, userId가 1인 유저의 name을 수정했고, 트랜잭션에서 userId가 1인 유저를 2번 조회했는데 데이터의 이름(name)이 서로 다르다면 이는 NON REPEATABLE READ입니다.

 

반면에, user age > 20인 유저를 트랜잭션에서 2번 조회 했는데 첫번째보다 두번째 조회했을 때 조회된 row 수가 더 많거나, 적다면 이는 PHANTOM READ입니다. 

---

표준 SQL 92 트랜잭션 격리수준

앞서 여러 트랜잭션이 수행되면서 발생할 수 있는 문제 3가지를 알아봤습니다. 이 문제들이 모두 발생하지 않게 할 수 있지만, 그렇게 하면 제약사항이 많아져 동시 처리 가능한 트랜잭션 수가 줄어들어 결국 DB의 전체 처리량이 하락하게 됩니다

그렇기 때문에 이상 현상을 허락하는 몇몇 단계를 만들어 사용자가 상황에 맞게 선택할 수 있게 만들었으며, 여기서 단계가 앞으로 살펴볼 격리 수준입니다.

 

이상현상 모두 안나타나게 할 수 있지만...그렇게 하면 DB 처리량이 하락하니깐, 몇몇 단계를 만들어 놨는데 각 단계에서 허락하는 이상현상이 다르니깐, 너의 상황에 맞게 적절한 단계를 선택해 사용해. 만약, DIRTY READ만 발생하지 않아도 된다면 READ COMMITED 레벨은 선택해.

SQL-92

isolation level은  SQL-92에서 정의되어 있습니다. 실무에서 사용하는 DBMS는 표준에서 정의한 isolation level를 기반으로 정의되어 있으며, 각자 RDBMS의 성격에 맞게 조금씩 다르기도 합니다. 

postgresql

mysql(InnoDB)

oracle

MySQL의 경우 표준 isolation level을 따라가고 있으며 postgresql은 Serialiation Anomaly이라는 이상현상을 새롭게 추가하여 isolation level을 정의하고 있습니다. 또한 oracle db는 READ UNCOMMITTED을 제공하고 있지 않으며 REPEATABLE READ와 SERIALIZABLE는 모두 SERIALIZABLE 수준을 사용하고 있습니다.

 

이처럼 SQL-92 표준에서 정의한 것과는 다르게 각각의 DBMS 마다 isolation level을 정의하기 때문에 표준을 참고하여 각각의 DBMS에서 isolation level을 어떻게 정의하고 있는지 확인 후 사용해야 합니다.

---

MySQL InnoDB 트랜잭션 격리수준

InnoDB는 SQL-92 표준 isolation level을 따라가고 있으며 각 level에서 발생하는/발생하지 않는 데이터 부정한 문제는 아래의 표에서 확인할 수 있습니다.

	DIRTY READ	NON REPEATABLE READ	PHANTOM READ
READ UNCOMMITTED	발생	발생	발생
READ COMMITTED	없음	발생	발생
REPEATABLE READ	없음	없음	발생(InnoDB는 없음)
SERIALIZABLE	없음	없음	없음

 

SQL-92에서는 REPEATABLE READ에서 PHANTOM READ가 발생한다고 되어 있지만, InnoDB를 사용하는 경우 PHANTOM READ가 발생하지 않습니다. 이것이 가능한 이유는 MySQL 공식문서에서 나와있는 것처럼 REPEATABLE READ의 경우 snapshot기반으로 constitent read가 가능하기 때문입니다.

위에서 말하고 있는 consistent read는 일관된 읽기로 다른 트랜잭션의 실행 결과에 상관없이 현재 내가 실행중인 트랜잭션에서 처음 조회했던 데이터 그대로(snapshot) 동일한 데이터를 계속 조회할 수 있는 것을 의미합니다. MySQL 공식문서에서는 consistent read를 다음과 같이 설명하고 있습니다.

consistent read를 한다는 것은 InnoDB가 MVCC(Multiversion Concurrency Control)를 내부적으로 사용한다는 것을 의미합니다. 여기서 MVCC는 InnoDB 엔진이 트랜잭션이 rollback될 가능성에 대비해 변경되기 전 레코드를 언두(undo) 공간에 백업해두고 실제 레코드 값을 변경하는 것을 의미합니다. 즉 레코드에 대해 여러개의 버전(값)을 유지하는 것입니다.

 

그렇기 때문에 REPEATABLE READ 수준의 트랜잭션에서 2번의 조회시, 첫번째 조회할 때 snapshot을 찍고(조회된 결과를 undo 영역에 넣고), 두번째 조회시 첫번째에 찍으 스냅샷 데이터를 읽어 오게 됩니다. 두번의 조회 사이에 데이터가 변경되었다 하더라도, 실제로 스냅샷 찍었을 때의 데이터를 가져오기 때문에, 즉 첫번째와 동일한 데이터를 가져오기 때문에(consistent read) PHANTOM READ가 발생하지 않게 됩니다.

 

SERIALIZABLE는 가장 단순한 격리 수준이면서 동시에 가장 엄격한 격리 수준입니다. InnoDB 테이블에서 기본적으로 순수한 select 작업은 아무런 레코드 잠금을 설정하지 않고 실행됩니다. 하지만 SERIALIZABLE 격리 수준은 읽기 작업도 공유 잠금(읽기 잠금)을 획득해야 하며 즉, 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없는 것입니다.

 

하지만 InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 이미 PHANTOM READ가 발생하지 않기 때문에 굳이 SERIALIZABLE를 사용할 필요성은 없습니다.

 

READCOMMITTED와 REPEATABLE READ의 차이는 언두 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가야 하느냐에 있습니다.

 

일반적인 온라인 서비스 용도의 데이터베이스는 READ COMMITTED와 REPEATABLE READ 중 하나를 사용합니다. 오라클 같은 DBMS는 주로 READ COMMITTED 수준을 많이 사용하며, MySQ InnoDB는 기본적으로 에서는 REPEATABLE READ를 사용하고 있습니다.

 

DIRTY READ를 유발하는 READ UNCOMMITTED는 RDBMS 표준에서는 트랜잭션의 격리 수준으로 인정하지 않을 정도로 정합성에 문제가 많은 격리 수준입니다. MySQL을 사용한다면 최소한 READ COMMITTED 이상의 수준을 사용할 것을 권장합니다.

 

---

참고

- https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

- https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

- http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

- https://stackoverflow.com/questions/11043712/what-is-the-difference-between-non-repeatable-read-and-phantom-read

- Real MySQL 8.0