코드프레소 백엔드 개발자 양성 과정

격리 수준

MySQL이 지원하는 네 가지 격리 수준을 소개한다.

Photo by Carlos “Grury” Santos on Unsplash

격리 수준(isolation level)이란 트랜잭션의 네 가지 주요 성질인 원자성, 일관성, 고립성, 내구성 (ACID) 중 고립성(isolation)을 구현하는 개념이다. 고립성은 한 트랜잭션에서 데이터가 수정되는 과정이 다른 트랜잭션과는 독립적으로 진행되어야 한다는 특성이다. 이때 격리 수준은 각기 다른 트랜잭션들이 서로에게서 어느 정도 격리되어 있는지를 나타낸다.

MySQL의 격리 수준

MySQL 8.0은 SQL 표준에서 정의하는 다음과 같은 네 가지 격리 수준을 모두 지원한다.

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

MySQL에서 격리 수준은 SET TRANSACTION 명령문을 이용해 전역이나 세션 단위로 조작할 수 있으며 InnoDB에서의 기본 격리 수준은 REPEATABLE READ이다.

MySQL의 격리 수준은 READ UNCOMMITTED부터 SERIALIZABLE까지 연속선을 이룬다. SERIALIZABLE에 가까울 수록 트랜잭션의 고립성과 데이터의 무결성은 높아지지만, 동시성과 성능은 저하된다.

mydbops.wordpress.com

고립성과 무결성의 지표로 위의 세 가지 개념을 짚고 넘어간다.

1. 더티 리드 (Dirty read) : 생성, 갱신, 혹은 삭제 중에 커밋 되지 않은 데이터 조회를 허용함으로써, 트랜잭션이 종료되면 더 이상 존재하지 않거나, 롤백되었거나, 저장 위치가 바뀌었을 수도 있는 데이터를 읽어들이는 현상이다.
2. 반복 가능하지 않은 조회 (Non-repeatable read) : 한 트랜잭션 내에서 같은 행이 두 번 이상 조회됐는데 그 값이 다른 경우를 가리킨다. (A와 B가 마지막 남은 영화표를 예매하는데 A가 고민하는 중에 B가 표를 구매하여 A는 상반된 정보를 받게 되는 경우 등)
3. 팬텀 리드 (Phantom read) : 한 트랜잭션 내에서 같은 쿼리문이 실행되었음에도 불구하고 조회 결과가 다른 경우를 뜻한다.

READ UNCOMMITTED

READ UNCOMMITTED는 잠금을 일절 실행하지 않아 미처 커밋되지 않고 트랜잭션이 진행 중인 기록조차도 조회할 수 있도록 하는 격리 수준이다. 네 가지 격리 수준 중 트랜잭션의 고립성을 가장 덜 구현하는 가장 느슨한 형태다.

READ UNCOMMITTED는 더티 리드를 유발한다. 대신 데이터베이스나 페이지, 열 따위에 잠금을 유지하는 데에 드는 간접 비용이 제일 적고, 교착 상태에 빠질 위험이 적어 성능은 빠르고 우수하다.

그러므로 READ UNCOMMITTED는

1. 데이터 무결성을 위해 되도록이면 사용하지 않는 것이 이상적이며,
2. 사용하더라도 갱신되지 않을 과거 자료를 열람할 때나 몇몇 행이 제대로 조회되지 않아도 무관할 만큼 거대한 양의 데이터를 어림잡아 집계하는 데에만 사용하는 게 바람직하다.
   예컨데, 실험실에서 전달된 환자의 시험 결과는 변경될 일이 없기 때문에 이 기록을 READ UNCOMMITTED로 조회한다는 미국 의료 종사자의 사례를 찾아볼 수 있었다.
3. 하지만 몇몇 데이터베이스 관리자들은 위와 같이 트랜잭션 도중에 값을 얻어낼 수 있다는 점을 이용해 복잡한 스토어드 프로시저나 함수가 포함된 디버깅을 할 때 중간 과정을 확인하기 위해서도 사용한다고 한다.

READ COMMITTED

READ COMMITTED는 READ UNCOMMITTED보다는 높은 격리 수준으로, 커밋이 완료된 데이터에 대해서만 조회를 허용한다. 여러 RDBMS에서 기본값으로 설정되어있을 만큼 인기 있는 방식이지만 MySQL에서는 기본값으로 채택되어 있지 않다.

MySQL에서 READ COMMITTED의 특징을 요약하자면 다음과 같다.

1. 커밋이 완료된 데이터에 대해서만 조회를 허용한다.
2. 더티 리드가 방지된다.
3. 팬텀 리드와 반복 가능하지 않은 조회(non-repeatable read)가 발생할 수도 있다.
4. InnoDB에서는 행 단위로 잠가 교착 상태를 최대한 방지한다.
5. UPDATE문 실행 시 semi-consistent read를 사용한다. Semi-consistent read는 잠겨 있지 않은 행에 대해서는 여러 세션에서 서로 다른 부분을 수정할 수 있도록 허용한다.

커밋한 값만 읽는 만큼 READ UNCOMMITTED의 더티 리드 현상은 방지되지만, 같은 조회 명령문이 다른 조회 결과를 불러오는 팬텀 리드가 발생할 수 있다. 이는 MySQL의 InnoDB가 인덱스 레코드만 잠그고, 그 위나 아래로 분포할 수 있는 틈새를 잠그는 ‘갭락’(gap lock)을 실행하지 않기 때문이다.

이로 인해 SELECT는, SELECT문 자신 이전에 커밋된 스냅샷만 가지고 조회를 하며, 그 이후에 발생한 변화는 반영하지 않기 때문에, 데이터베이스가 갱신될 시 항상 동일한 결과를 반환하지 않는다.

행 단위로 잠금을 적용하는 MySQL의 InnoDB에서 READ COMMITTED를 사용하면 추가적으로 다음과 같은 특징들이 있다.

• UPDATE나 DELETE문을 사용할 경우, 전체 테이블 대신 영향을 받는 행만 잠겨 교착 상태가 일어날 확률이 최소화된다. 전혀 발생하지 않으리란 보장은 없다.
• 이미 잠긴 행에 UPDATE문을 사용할 경우 InnoDB는 semi-consistent read를 수행한다. Semi-consistent read란 각 행에 대해 가장 최근에 커밋된 사항을 반환하여 MySQL로 하여금 그 행이 WHERE 조건절에 맞는지 판단할 수 있게 해 준다. 그리고 조건에 부합하는 행은 UPDATE가 적용된다.

그러므로 READ COMMITTED는

1. 더티 리드를 방지할 수 있다는 점에서는 READ UNCOMMITTED의 한계를 보완하지만,
2. 잠금이 발생하는 만큼 속도나 성능에 있어서는 좀 더 느려질 수 있으며 교착 상태도 벌어질 수 있고,
3. 트랜잭션 간 고립성을 완전히는 보장하지 못해 팬텀 리드, 반복 가능하지 않은 조회 (non-repeatable read) 등이 여전히 발생할 수 있다.
4. 이는 일반 웹 애플리케이션 구동에는 큰 문제가 없을 수 있어도 (영화 예매의 예) 은행 계좌 정보 조회처럼 각 트랜잭션의 정확도가 생명이라면 적합하지 않다.

REPEATABLE READ

REPEATABLE READ는 MySQL InnoDB의 기본 격리 수준이다. 앞서 다룬 READ COMMITTED 격리 수준에서 반복 가능하지 않은 조회 (non-repeatable read) 현상을 해결하는 정도까지 성능을 타협하고 트랜잭션의 고립성을 높인 것이다. 쓰는 동안 읽을 수 없게함과 동시에 읽는 동안 쓸 수도 없도록 한다.

MySQL에서의 REPEATABLE READ의 특징은 다음과 같다.

1. SELECT시 : 멀티 버전 동시성 제어라는 것을 통해 특정 시점의 데이터베이스 스냅샷을 남긴다. 그러면 쿼리문은 그 시점과 그 이전의 모든 변경점을 보고 반영한다. 쿼리문은 그 이후의 트랜잭션은 반영하지 못하지만, 현재 그 쿼리문이 담긴 트랜잭션 내의 다른 명령문은 볼 수 있다.
   (가령 트랜잭션 상단에서 UPDATE문을 실행했으면 아직 커밋이 일어나지 않았음에도 불구하고 잇따르는 SELECT문은 UPDATE가 반영된 기록을 조회한다.)
   쿼리를 마치고 커밋을 함으로써 최신 스냅샷을 사용할 수 있다.
2. UPDATE나 DELETE시 : REPEATABLE READ는 READ COMMITTED와 달리 작업을 수행하지 않은 열도 잠근다. 커밋이나 롤백이 일어나기 전까지 모두 잠겨있다. 두 번째 UPDATE문 실행 시 아무 작업도 수행되지 못하고 대기한다. READ COMMITTED의 semi-consistent read와 대조를 이룬다.

그러므로 REPEATABLE READ는

1. 반복 가능하지 않은 조회(non-repeatable read)를 방지할 수 있다는 점에서는 READ COMMITTED의 단점을 보완하지만,
2. 잠금이 적용되는 범위가 더욱 넓어져 성능과 속도가 느려지고,
3. 원래는 팬텀 리드도 발생하지만 MySQL의 InnoDB는 멀티 버전 동시성 제어를 통해 팬텀 리드도 어느 정도 극복한다 (next key lock을 이용).

SERIALIZABLE

SERIALIZABLE은 한 트랜잭션을 다른 트랜잭션으로부터 완전히 분리하여 가장 높은 고립성을 띄지만 동시성과 성능은 그만큼 낮아진다. 작업 중인 트랜잭션이 완전히 종료될 때까지 다른 어떤 작업도 허용하지 않아 더티 리드, 반복 가능하지 않은 조회, 팬텀 리드를 완벽히 방지한다.