[Real MySQL 1권] REPETABLE READ & SERIALIZABLE

 

1) REPETABLE READ란?

- REPETABLE READ는 MySQL의 InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준이다.

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

  

- 이 격리 수준에서는 READ COMMITTED 격리 수준에서 발생하는 

  "NON-REPEATABLE READ" 부정합이 발생하지 않는다.

  InnoDB 스토리지 엔진은 트랜잭션이 ROLLBACK될 가능성에 대비해 

  변경되기 전 레코드를 언두(Undo) 공간에 백업해두고 실제 레코드 값을 변경한다. 

 

- 이러한 변경 방식을 MVCC라고 한다.

  REPETABLE READ는 이 MVCC를 위해 언두 영역에 백업된 이전 데이터를 이용해

  동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있게 보장한다. 

  사실 READ COMMITTED도 MVCC를 이용해 COMMIT되기 전의 데이터를 보여준다.

  REPEATABLE READ와 READ COMMITTED의 차이는 언두 영역에 백업된 레코드의

  여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가야 하느냐에 있다. 

 

 

2) REPETABLE READ의 동작

 

 

- 위의 그림은 REPEATABLE READ 격리 수준이 작동하는 방식을 보여준다.

  우선 이 시나리오가 실행되기 전에 employees 테이블은 번호가 6인 트랜잭션에 의해 INSERT됐다고 가정하자.

  그래서 위 그림에서 employees 테이블의 초기 두 레코드는 트랜잭션 번호가 6으로 표현된 것이다.

  위의 시나리오에서는 사용자 A가 emp_no가 500000인 사원의 이름을 변경하는 과정에서

  사용자 B가 emp_no=500000인 사원을 SELECT 할 때 어떤 과정을 거쳐서 처리되는지 보여준다. 

 

- 위의 그림에서 사용자 A의 트랜잭션 번호는 12였으며, 사용자 B의 트랜잭션의 번호는 10이었다. 

  이 때, 사용자 A는 사원의 이름을  "Toto"로 변경하고 커밋을 수행했다.

  그런데 A 트랜잭션이 변경을 수행하고 커밋을 했지만, 

  사용자 B가 emp_no=500000인 사원을 A 트랜잭션의 변경 전후

  각각 한 번씩 SELECT했는데 결과는 항상 "Lara"라는 값을 가져온다. 

 

- 사용자 B가 BEGIN 명령으로 트랜잭션을 시작하면서 10번이라는 트랜잭션 번호를 부여받았는데,

  그때부터 사용자 B의 10번 트랜잭션 안에서 실행되는 모든 SELECT 쿼리는 

  트랜잭션 번호가 10(자신의 트랜잭션 번호)보다 작은 트랜잭션 번호에서 변경한 것만 보게 된다. 

 

- 위의 그림에서는 언두 영역에 백업된 데이터가 하나만 있는 것으로 표현했지만

  사실 하나의 레코드에 대해 백업이 하나 이상 얼마든지 존재할 수 있다.

  한 사용자가 BEGIN으로 트랜잭션을 시작하고 장시간 트랜잭션을 종료하지 않으면

  언두 영역이 백업된 데이터로 무한정 커질 수도 있다.

  이렇게 언두에 백업된 레코드가 많아지면 MySQL 서버의 처리 성능이 떨어질 수 있다.

 

 

3) REPETABLE READ의 부정합

 

- REPEATABLE READ 격리 수준에서도 다음과 같은 부정합이 발생할 수 있다. 

  위의 그림에서는 사용자 A가 employees 테이블에 INSERT를 실행하는 도중에

  사용자 B가 SELECT ... FOR UPDATE 쿼리로 employees 테이블을 조회했을 때

  어떤 결과를 가져오는지 보여준다. 

 

- 위의 그림에서 사용자 B는 BEGIN 명령으로 트랜잭션을 시작한 후 SELECT를 수행한다.

  그러므로 두 번의 SELECT 쿼리 결과는 똑같아야 한다. 

  하지만 실제로 사용자 B가 실행하는 두 번의 SELECT ... FOR UPDATE 쿼리 결과는 서로 다르다.

  

- 이렇게 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안 보였다 하는 현상을 

  PHANTOM READ라고 한다.

  

- SELECT ... FOR UPDATE 쿼리는 SELECT하는 레코드에 쓰기 잠금을 걸어야 하는데,

  언두 레코드에는 잠금을 걸 수 없다. 

  그래서  SELECT ... FOR UPDATE나 SELECT ... LOCK IN SHARE MODE로 조회되는 레코드는

  언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드 값을 가져오게 되는 것이다. 

 

 

4) SERIALIZABLE

- 가장 단순한 격리 수준이면서 동시에 가장 엄격한 격리 수준이다.

  그만큼 동시 처리 성능도 다른 트랜잭션 격리 수준보다 떨어진다.

  InnoDB 테이블에서 기본적으로 순수한 SELECT 작업은 

  아무런 레코드 잠금도 설정하지 않고 실행된다. 

  InnoDB 매뉴얼에서 자주 나타나는 "Non-locking consistent read"라는 말이 이를 의미하는 것이다.

 

- 하지만 트랜잭션의 격리 수준이 SERIALIZABLE로 설정되면 

  읽기 작업도 공유 잠금(읽기 잠금)을 획득해야만 하며,

  동시에  다른 트랜잭션은 그러한 레코드를 변경하지 못하게 된다.

  즉, 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없는 것이다. 

 

- SERIALIZABLE 격리 수준에서는 일반적인 DBMS에서 일어나는 

  "PHANTOM READ"라는 문제가 발생하지 않는다.

  하지만 InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 

  REPEATABLE READ 격리 수준에서도 이미 PHANTOM READ가 발생하지 않기 때문에

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