[MySQL] 격리 수준(Isolation Level)

예전에 작성한 글에서 데이터베이스의 격리 수준에 대해서 간단하게 정리한 글을 작성한 적이 있습니다. JPA를 공부하면서 데이터베이스 격리 수준이라는 개념이 또 한번 등장해서 이참에 격리 수준이 도대체 무엇인지 공부하고 정리해보는 시간을 가져볼까합니다.

 

격리 수준이란?

여러 트랜잭션이 처리될 때, 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있도록 허용할지 말지를 결정하는 것입니다. 격리 수준은 다음과 같이 크게 4가지로 나뉩니다.

 

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

이 중에서 READ UNCOMMITTED는 일반적인 DB에서 잘 사용되지 않고, SERIALIZABLE은 동시성이 중요한 DB에서 거의 사용되지 않습니다. 

 

4개의 격리 수준에서 뒤로 갈수록 각 트랜잭션 간의 데이터 격리 정도가 높아지며, 동시성도 떨어집니다. (격리 수준이 높아질수록 MySQL 처리 성능이 많이 떨어지진 않습니다, 그러나 SERIALIZABLE 격리 수준에서는 크게 성능 저하가 발생할 수 있습니다.)

 

데이터 부정합 문제

DB의 격리 수준을 이야기하면서 항상 함께 언급되는 것이 데이터 부정합 문제입니다. 이러한 부정합 문제는 다음과 같이 3가지가 있고, 격리 수준의 레벨에 따라 발생할 수도, 발생하지 않을 수도 있습니다.

 

	DIRTY READ	NON-REPEATABLE READ	PHANTOM READ
READ UNCOMMITTED	발생	발생	발생
READ COMMITTED	발생하지 않음	발생	발생
REPEATABLE READ	발생하지 않음	발생하지 않음	발생(InnoDB는 발생 X)
SERIALIZABLE	발생하지 않음	발생하지 않음	발생하지 않음

 

일반적인 온라인 서비스 용도의 DB는 READ COMMITTED와 REPEATABLE READ 둘 중에서 하나를 사용합니다. (오라클 같은 경우는 READ COMMITTED, MySQL에서는 REPEATABLE READ를 주로 사용)

다음은 각 격리 수준에 좀 더 자세히 알아보도록 하겠습니다.

 

READ UNCOMMITTED

READ UNCOMMITTED 격리 수준에서는 트랜잭션에서의 변경 내용이 COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에게 보여집니다.

 

READ UNCOMMITTED

위의 그림을 보면 사용자 A는 emp_no가 50000이고 name이 "김철수"인 새로운 직원을 INSERT 했습니다. 그리고 사용자 B가 변경된 내용을 COMMIT 하기도 전에 emp_no = 50000인 직원을 검색하고, 이에 대한 검색 결과를 얻어낸 것을 확인할 수 있습니다. 이런 경우 문제가 될 수 있는데, 만약 사용자 A가 처리 도중 알 수 없는 문제가 발생해 INSERT된 내용을 ROLLBACK 한다 하더라도 사용자 B는 "김철수"가 계속 정상적인 직원이라고 생각하고 계속해서 트랜잭션을 처리하게 됩니다.

 

이처럼 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데 다른 트랜잭션에서 볼 수 있게 되는 현상을 DIRTY READ라고 합니다. 이러한 DIRTY READ 현상은 READ UNCOMMITTED 격리 수준에서만 나타납니다. 위에서 살펴본 것 처럼 DIRTY READ를 유발하게 되면 온라인 서비스를 운영하면서 큰 혼란을 줄 수 있어 사용을 권장하지 않습니다. 또한 RDBMS 표준에서 트랜잭션의 격리 수준으로 인정하지 않을 정도로 정합성에 문제가 많으므로 최소한 READ COMMITTED 이상의 격리 수준 사용을 권장합니다.

 

READ COMMITTED

READ COMMITTED는 오라클 DBMS에서 기본적으로 사용되고 있는 격리 수준이고 온라인 서비스에서 가장 많이 선택되는 격리 수준입니다. 해당 레벨에서는 READ UNCOMMITTED에서 나타났던 DIRTY READ 현상이 발생하지 않습니다. 어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있기 때문입니다.

 

READ COMMITTED

사용자 A는 emp_no가 50000인 직원의 name을 "김철수"에서 "이미영"으로 변경했는데, 이때 새로운 값인 "이미영"은 employees 테이블에 즉시 기록되고, 이전 값인 "김철수"는 UNDO 영역으로 백업됩니다. 사용자 A가 COMMIT을 수행하기 전에 사용자 B가 emp_no가 50000인 직원을 조회하면 조회된 결과의 name은 "이미영"이 아니라 "김철수"로 조회가 됩니다. 이는 employees 테이블의 값을 가져온게 아니라 UNDO 영역의 값을 가져왔기 때문입니다.

 

READ COMMITTED 격리 수준에서는 어떤 트랜잭션에 변경한 내용이 COMMIT 되기 전까지는 다른 트랜잭션에서 일어난 변경 내역을 조회할 수 없습니다. 최종적으로 사용자 A가 변경된 내용을 COMMIT 하면 그때부터 다른 트랜잭션에도 백업된 UNDO 영역의 "김철수"가 아니라 새롭게 변경된 "이미영"을 조회할 수 있게 됩니다.

 

※ UNDO 영역의 레코드 값은 트랜잭션의 격리 수준을 보장하기 위한 용도뿐 아니라 트랜잭션의 ROLLBACK에 대한 복구에도 사용됩니다.

 

그러나 READ COMMITTED 격리 수준에서도 NON-REPEATABLE READ(REPEATABLE READ가 불가능)라는 부정합 문제가 존재합니다.

 

NOT-REPEATABLE READ

처음 사용자 B가 BEGIN 명령으로 트랜잭션을 시작하고 name이 "이미영"인 직원을 검색했는데, 일치하는 결과가 나오지 않습니다. 하지만 사용자 A가 직원 번호가 50000인 사원의 이름을 "이미영"으로 변경하고 COMMIT을 실행한 후, 사용자는 똑같은 SELECT 쿼리로 다시 조회하면 이번에는 결과가 1건 조회됩니다. 이는 별다른 문제가 없어보이지만, 사실 사용자 B가 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때 항상 같은 결과를 가져와야 한다는 REPEATABLE READ 정합성에 어긋나는 것 입니다.

 

이러한 부정합 문제는 일반적인 웹 프로그램에서는 크게 문제가 되지 않지만 하나의 트랜잭션에서 동일 데이터를 여러번 읽고 변경하는 작업이 금전적인 처리와 연결되면 문제가 발생할 수 있습니다. 예를들어 다른 트랜잭션에서 입금과 출금 처리가 계속 진행되고 있을 때, 다른 트랜잭션에서 오늘 입금된 금액의 총 합을 조회한다고 가정해봅시다. REPEATABLE READ가 보장되지 않기 때문에 총합을 계산하는 SELECT 쿼리가 실행될 때 마다 다른 결과를 가져오게 됩니다. 따라서 중요한 것은 사용 중인 트랜잭션의 격리 수준에 의해 실행하는 SQL 문장이 어떤 결과를 가져오게 되는지 정확히 예측할 수 있어야 합니다. (따라서 트랜잭션의 격리 수준이 어떻게 작동하는지 공부해야 합니다.)

 

참고로 트랜잭션 내에서 실행되는 SELECT 쿼리와 트랜잭션 외부에서 실행되는 SELECT 쿼리의 차이를 혼동하는 경우가 있습니다. READ COMMITTED 격리 수준에서는 둘의 차이가 별로 없지만 REPEATABLE READ 격리 수준에서는 얘기가 달라집니다. 해당 격리 수준에서는 기본적으로 SELECT 쿼리 문장이 트랜잭션 범위 내에서만 작동하게 됩니다. 즉 "BEGIN TRANSACTION"으로 트랜잭션을 시작한 상태에서 온종일 동일한 쿼리를 반복해서 실행해봐도 동일한 결과를 보게 됩니다. 아무리 다른 트랜잭션에서 그 데이터를 변경하고 COMMIT을 실행해도 절대 변하지 않습니다.

 

REPEATABLE READ

REPEATABLE READ는 MySQL의 InnoDB 스토리지 엔진에서 기본적으로 사용되는 격리 수준입니다. 바이너리 로그를 가진 MySQL의 장비에서는 최소 REPEATABLE READ 격리 수준 이상을 사용해야 합니다. 해당 격리 수준에서는 READ COMMITTED 격리 수준에서 발생하는 NON-REPEATABLE READ 부정합이 발생하지 않습니다. InnoDB 스토리지 엔진은 트랜잭션이 ROLLBACK될 가능성에 대비해 변경되기 전 레코드를 UNDO 영역에 백업해두고, 실제 레코드 값을 변경합니다. 이러한 방식을 MVCC(Multi Concurrency Control)이라고 합니다. (이를 통해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있도록 보장합니다.) READ COMMITTED도 MVCC를 이용하여 COMMIT 되기 전의 데이터를 보여주지만, 둘의 차이는 UNDO 영역에 백업된 레코드의 여러 버전 가운데 몇번째 이전 버전까지 찾아 들어가야 하는지에 있습니다.

 

모든 InnoDB의 트랜잭션은 고유한 트랜잭션 번호(순차적으로 증가하는 번호)를 가지며 UNDO 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함되어 있습니다. 그리고 UNDO 영역의 백업된 데이터는 InnoDB 스토리지 엔진이 불필요하다고 판단하는 시점에서 주기적으로 삭제합니다. 해당 격리 수준에서는 MVCC를 보장하기 위해 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 UNDO 영역의 데이터는 삭제할 수 없습니다. 그렇다고 가장 오래된 트랜잭션 번호 이전의 트랜잭션에 의해 변경된 모든 UNDO 데이터가 필요한 것은 아닙니다. 즉 특정 트랜잭션 번호의 구간 내에서 백업된 UNDO 데이터가 보존되어야 하는 것입니다.

 

REPEATABLE READ

시나리오가 실행되기 전에 employees 테이블은 번호가 6인 트랜잭션에 의해 INSERT 되었다고 가정합니다. 해당 시나리오는 사용자 A가 emp_no가 50000인 직원의 이름을 변경하는 과정에서 사용자 B가 emp_no가 50000인 직원을 SELECT할 때 어떤 과정을 거쳐서 처리되는지 보여줍니다. 

 

사용자 A의 트랜잭션 번호는 12였으며, 사용자 B의 트랜잭션 번호는 10입니다. 이때 사용자 A는 직원의 이름을 "이미영"으로 변경하고 COMMIT을 수행했습니다. 그런데 사용자 B가 emp_no가 50000인 직원을 A 트랜잭션이 변경을 실행하기 전과 변경 후 COMMIT을 했을 때 각각 한번 씩 SELECT하면 똑같이 "김철수"라는 결과를 가져옵니다. 사용자 B가 BEGIN 명령으로 트랜잭션을 시작하고 나서부터, 해당 트랜잭션 안에서 실행되는 모든 SELECT 쿼리는 자신의 트랜잭션 번호인 10보다 작은 트랜지션 번호에서 변경한 것만 보게 됩니다.

 

위의 예제에서는 UNDO 영역에 백업된 데이터가 하나만 있는 것으로 표현했지만 사실 여러개의 백업된 데이터가 존재할 수 있습니다. 만약 한 사용자가 BEGIN으로 트랜잭션을 시작하고 장시간동안 트랜잭션을 종료하지 않으면 UNDO 영역이 백업된 데이터로 무한정 커져 MySQL 서버의 성능이 저하될 수 있으니 조심해야합니다.

 

REPEATABLE READ 격리 수준에서도 PHANTOM READ 라는 부정합 문제가 발생할 수 있습니다. 다음 예제는 사용자 A가 employees 테이블에 INSERT를 실행하는 도중, 사용자 B가 SELECT ... FOR UPDATE 쿼리로 employees 테이블을 조회했을 때 발생하는 시나리오입니다.

 

PHANTOM READ(ROWS)

사용자 B는 BEGIN 명령으로 트랜잭션을 시작한 후, SELECT를 수행하고 있습니다. REPEATABLE READ 격리 수준에서는 B에서 발생하는 두번의 SELECT 쿼리 결과가 같아야합니다. 그러나 위의 예제에서는 서로 결과가 다른 것을 확인할 수 있습니다. 이렇게 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다가 안보였다가 하는 현상을 PHANTOM READ(ROWS) 라고 합니다. SELECT ... FOR UPDATE 쿼리는 SELECT하는 레코드에 쓰기 잠금을 걸어야 하는데, UNDO 레코드에서는 잠금을 걸 수 없습니다. 그래서 SELECT ... FOR UPDATE나 SELECT .. LOCK IN SHARE MODE로 조회되는 레코드는 UNDO 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오게 됩니다.

 

SERIALIZABLE

가장 단순하면서도 가장 엄격한 격리 수준입니다. 동시 처리 성능도 다른 트랜잭션 격리 수준보다 떨어지는 특징을 가지고 있습니다. 해당 격리 수준에서는 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없습니다. 또한 PHANTOM READ 정합성 문제가 발생하지 않습니다. 그러나 InnoDB 스토리지 엔진에서는 REPEATABLE READ 격리 수준에서도 PHANTOM READ 문제가 발생하지 않기 때문에 굳이 SERIALIZABLE을 사용할 필요성은 없습니다.

 

 

 

 

 

Reference

www.yes24.com/Product/Goods/6960931

Real MySQL