Real MySQL - (5) 격리 수준

트랜잭션의 격리 수준

• 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것
• READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE 4가지로 나뉜다
• DIRTY READ라고도 하는 READ UNCOMMITTED는 일반적인 데이터베이스에서는 거의 사용하지 않는다
• SERIALIZABLE 또한 동시성이 중요한 데이터베이스에서는 거의 사용되지 않는다
• 4개의 격리 수준에서 뒤로 갈수록 각 트랜잭션 간 데이터 격리 정도가 높이지며, 동시 처리 성능도 떨어진다
• 하지만, SERIALIZABLE 격리 수준이 아니라면 크게 성능 개선이나 저하는 발생하지 않는다
• 일반적인 온라인 서비스 용도 데이터베이스는 READ COMMITTED와 REPEATABLE READ 중 하나를 사용한다

READ UNCOMMITTED

• 각 트랜잭션에서의 변경 내용이 COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보인다
• 이러한 현상을 DIRTY READ라고 한다
• 커밋하기 전 데이터를 다른 트랜잭션에서 조회할 수 있기 때문에, 해당 건이 롤백되더라도, 여전히 다른 트랜잭션에서 정상 데이터로 간주하고 계속 처리하게 된다
• 정합성에 문제가 많이 발생하는 격리 수준이므로 최소한 READ COMMITTED 이상의 격리 수준을 사용할 것을 권장한다

READ COMMITTED

• 온라인 서비스에서 가장 많이 선택되는 격리 수준
• COMMIT 완료된 데이터만 다른 트랜잭션에서 조회할 수 있기 때문에 DIRTY READ가 발생하지 않는다
• 데이터 변경 전 데이터를 언두 로그로 복사하고, COMMIT 전에 다른 트랜잭션에서 조회하면 이 언두 로그 데이터를 조회하게 된다
• 이 격리 수준에서도 NON-REPEATABLE READ라는 부정합 문제가 있다

1. B 사용자가 트랜잭션을 시작하고 데이터를 READ
2. A 사용자가 해당 데이터를 변경 후 COMMIT
3. B 사용자가 해당 데이터를 READ

• 위 시나리오대로 진행하면 B 사용자는 한 트랙잭션 내에서 같은 SELECT 쿼리를 실행했을 때 항상 같은 결과를 가져와야한다는 REPEATABLE READ 정합성에 어긋나게 된다

REPEATABLE READ

• InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준
• 바이너리 로그를 가진 MySQL 서버에서는 최소 REPEATABLE READ 격리 수준 이상을 사용해야 함
• READ COMMITTED 격리 수준에서 발생하는 NON-REPEATABLE READ 가 발생하지 않음
• InnoDB 스토리지 엔진은 트랜잭션이 ROLLBACK 될 가능성에 대비해 변경되기 전 레코드를 언두 공간에 백업하고, 실제 레코드 값을 변경한다. 이를 MVCC라고 한다
• REPEATABLE READ는 이 MVCC를 위해 언두 영역에 백업된 데이터를 이용해 동일 트랜잭션 내에서 동일한 결과를 보여줄 수 있게 보장한다
• READ COMMITTED도 MVCC를 이용해 COMMIT되기 전 데이터를 보여주지만, 이 둘의 차이는 언두 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가야 하느냐에 있다
• 모든 InnoDB의 트랜잭션은 고유한 트랜잭션 번호(순차 증가함)를 가지며, 언두 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함돼 있다
• 언두 영역의 백업된 데이터는 불필요하다고 판단하는 시점에 주기적으로 삭제된다
• REPEATABLE READ 격리 수준에서는 MVCC를 보장하기 위해 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수가 없다
• 특정 트랜잭션 번호 구간 내에서 백업된 언두 데이터가 보존돼야 한다

1. 사용자 B가 데이터를 조회한다 (트랜잭션 번호 6)
2. 사용자 A가 데이터를 변경한다 (트랜잭션 번호 12, 언두 로그 저장)
3. 사용자 B가 데이터를 조회한다. 이 때 사용자 B의 트랜잭션 보다 높은(2번) 것은 보이지 않고, 그보다 작은 트랜잭션 번호에서 변경한 것만 보인다
4. 그래서 사용자 B는 1번 결과를 그대로 반환받게 된다

• 장시간 트랜잭션을 종료하지 않아 언두 영역이 백업된 데이터로 무한정 커지는 등 언두에 백업 레코드가 많아지면 MySQL 서버의 처리 성능이 떨어질 수 있다
• 해당 격리 수준에서도 발생할 수 있는 부정합(PHANTOM READ/PHANTOM ROW)이 있다

1. 사용자 B가 데이터를 조회한다 (SELECT ... FOR UPDATE / 번호가 50번보다 큰 건 조회)
2. 사용자 A가 데이터를 추가한다 (51번 번호를 갖는 데이터 추가)
3. 사용자 B가 1번 쿼리를 다시 수행하면, 2번에서 추가된 데이터도 함께 조회된다

• SELECT ... FOR UPDATE 쿼리는 SELECT 하는 레코드에 쓰기 잠금을 걸어야하는데, 언두 레코드에는 잠금을 걸 수 없다
• 따라서 잠금을 동반한 SELECT 쿼리(SELECT ... FOR UPDATE나 SELECT ... LOCK IN SHARE MODE)로 조회되는 레코드는 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져온다

SERIALIZABLE

• 가장 단순하고, 가장 엄격한 격리 수준. 동시 처리 성능도 가장 떨어짐
• InnoDB 테이블에서 순수한 SELECT(INSERT ... SELECT ... CREATE TABLE ... AS SELECT ...)은 아무 레코드 잠금도 설정하지 않고 실행됨
• 읽기 작업도 공유 잠금(읽기 잠금)을 획득해야 하며, 동시에 다른 트랜잭션은 레코드를 변경하지 못하게 된다
• 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서 절대 접근할 수 없다
• 이 격리 수준에서는 PHANTOM READ가 발생하지 않는다
• 하지만, InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 이미 PHANTOM READ가 발생하지 않기 때문에 굳이 이 격리 수준을 사용할 필요는 없다