트랜잭션의 격리 수준(isolation level)이란 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정한다.

1. READ UNCOMMITTED
DIRTY READ라고도 하며 일반적인 데이터베이스에서는 거의 사용하지 않는다.
2. READ COMMITTED
3. REPEATABLE READ
4. SERIALIZABLE
   동시성이 중요한 데이터베이스에서는 거의 사용되지 않는다.

4개의 격리 수준에서 뒤로 갈수록 각 트랜잭션 간의 데이터 고립 정도가 높아진다. 격리 수준이 높아질수록 MySQL 서버의 처리 성능이 많이 떨어질 것으로 생각하지만, SERIALIZABLE 격리 수준이 아니라면 크게 성능의 개선이나 저하는 발생하지 않는다.

SQL-92, SQL-99 표준에 따르면 REPEATABLE READ 격리 수준에서는 PHANTOM READ가 발생할 수 있지만, InnoDB에서는 독특한 특성으로 인해 발생하지 않는다.

일반적인 온라인 서비스 용도의 데이터베이스는 READ COMMITED와 REPEATABLE READ 중 하나를 사용한다.

• 오라클: READ COMMITED
• MySQL: REPEATABLE READ

READ UNCOMMITTED

READ UNCOMMITTED 격리 수준에서는 각 트랜잭션에서 변경 내용이 COMMIT, ROLLBACK 여부에 상관 없이 다른 트랜잭션에서 보인다.

A가 emp_no가 500000이고 first_name이 Lara인 새로운 사원을 INSERT 하는데, B가 변경된 내용을 커밋하기도 전에 emp_no=500000인 사원을 검색해도 조회 가능하다.

이에 따라 A의 INSERT가 ROLLBACK 되어도, B는 조회한 내용이 정상적인 사원이라고 생각하여 처리하게 된다.

이처럼 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상을 더티 리드라고 하며, 더티 리드가 허용되는 격리 수준이 READ UNCOMMITTED이다.

더티 리드 현상은 데이터가 나타났다 사라지는 현상을 초래하므로 개발자와 사용자를 혼란스럽게 만들 수 있다. 더티 리드를 유발하는 READ UNCOMMITTED는 RDBMS 표준에서는 트랜잭션의 격리 수준으로 인정하지 않을 정도로 정합성에 문제가 많으므로 READ COMMITTED 이상의 격리 수준을 사용할 것을 권장한다.

READ COMMITTED

오라클 DBMS에서 기본으로 사용되는 격리 수준이며, 온라인 서비스에서 가장 많이 선택되는 격리 수준이다. 어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있기 때문에 더티 리드 현상은 발생하지 않는다.

A가 emp_no=500000인 사원의 first_name을 “Lara"에서 “Toto"로 변경했는데, 이때 새로운 값인 “Toto"는 employees 테이블에 즉시 기록되고 이전 값인 “Lara"는 언두 영역으로 백업된다.

COMMIT이 완료 되기 전 B가 emp_no=500000인 사원의 first_name을 조회하면, 언두 영역에 백업된 레코드에서 가져온 “Lara"를 조회하게 된다.

NON-REPEATABLE READ

READ COMMITTED 격리 수준에서도 NON-REPEATABLE READ라는 부정합 문제가 있다.

B가 BEGIN 명령으로 트랜잭션을 시작하고 first_name이 “Toto"인 사용자를 검색했는데 일치하는 결과가 없었다. 하지만 A가 사원 번호가 500000인 사원의 이름을 “Toto"로 변경하고 커밋을 실행한 후, B가 똑같은 쿼리로 다시 조회하면 1건이 조회된다.

중요한 포인트는 사용자가 동일 트랜잭션 내에서 같은 쿼리를 활용하여 조회했는데도 불구하고 다른 결과를 조회해 온 것이며, 이는 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때는 항상 같은 결과를 가져와야 한다는 REPEATABLE READ 정합성에 어긋나는 것이다.

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

트랜잭션 내에서 실행되는 SELECT 문장과 트랜잭션 없이 실행되는 SELECT 문장의 차이
READ COMMITTED 격리 수준에서는 차이가 별로 없으나 REPEATABLE READ 격리 수준에서는 SELECT 쿼리도 트랜잭션 범위 내에서만 작동한다.
즉 트랜잭션을 시작한 상태에서 SELECT 쿼리를 실행한다면, 다른 트랜잭션에서 데이터를 변경하고 COMMIT 되었다고 하더라도 동일한 결과를 반환한다.

REPEATABLE READ

MySQL의 InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준이다. 바이너리 로그를 가진 MySQL 서버에서는 최소 REPEATABLE READ 격리 수준 이상을 사용해야 한다.

InnoDB 스토리지 엔진의 REPEATABLE READ 격리수준에서는 트랜잭션이 ROLLBACK될 가능성에 대비해 변경되기 전 레코드를 언두 공간에 백업해두고 실제 레코드 값을 변경하고(MVCC) 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있도록 보장하므로 NON-REPEATABLE READ 부정합이 발생하지 않는다.

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

InnoDB의 모든 트랜잭션은 고유한 트랜잭션 번호(순차적으로 증가하는 값)를 가지며, 언두 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션 번호가 포함되어 있다. 언두 영역에 백업된 데이터는 InnoDB 스토리지 엔진이 불필요하다고 판단하는 시점에 주기적으로 삭제한다.

REPEATABLE READ 격리 수준에서는 MVCC를 보장하기 위해 실행중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수가 없다. 그렇다고 가장 오래된 트랜잭션 번호 이전의 트랜잭션에 의해 변경된 모든 언두 데이터가 필요한 것은 아니며, 특정 트랜잭션 번호의 구간 내에서 백업된 언두 데이터가 보존 돼야 한다.

12번 트랜잭션에서 사원 이름을 “Toto"로 변경하고 커밋을 수행했다. 하지만 10번 트랜잭션에서 12번 트랜잭션 커밋 전 후로 해당 사원을 조회해도 자신의 트랜잭션 번호보다 작은 트랜잭션에서 변경한 것만 보게 되므로, 이전 값인 “Lara"를 받아온다.

트랜잭션을 시작하고 장시간 종료하지 않으면 언두 영역이 백업된 데이터로 무한정 커질 수도 있으며, 이로 인해 백업된 레코드가 많아지면 MySQL 서버의 처리 성능이 떨어질 수 있다.

PHANTOM READ

REPEATABLE READ 격리 수준에서도 부정합이 발생 가능하다.

A가 employees 테이블에 INSERT를 실행하는 도중 B가 SELECT ... FOR UPDATE 쿼리로 테이블을 2번 조회하는데 다른 결과를 반환하고 있다.

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

이렇게 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안 보였다 하는 현상을 PHANTOM READ(PHANTOM ROW)라고 한다.

SERIALIZABLE

가장 단순한 격리 수준이면서 동시에 가장 엄격한 격리 수준이다. 그만큼 동시 처리 성능도 다른 트랜잭션 격리 수준보다 떨어진다.

InnoDB 테이블에서 기본적으로 순수한 SELECT 작업은 아무런 레코드 잠금도 설정하지 않고 실행(Non-locking consistent read: 잠금이 필요없는 일관된 읽기)되지만 트랜잭션의 격리 수준이 SERIALIZABLE로 설정되면 읽기 작업도 공유 잠금(읽기 잠금)을 획득해야만 하며, 동시에 다른 트랜잭션은 레코드를 변경하지 못하게 된다. 따라서 SERIALIZABLE 격리 수준에서는 일반적인 DBMS에서 일어나는 PHANTOM READ 문제가 발생하지 않는다.

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

엄밀하게는 SELECT ... FOR UPDATE, SELECT ... FOR SHARE 쿼리의 경우 REPEATABLE READ 격리수준에서 PHANTOM READ 현상이 발생할 수 있다.
하지만 레코드의 변경 이력(언두 레코드)에 잠금을 걸 수는 없기 때문에, 이러한 잠금을 동반한 SELECT 쿼리는 예외적인 상황으로 볼 수 있다.