데이터베이스 격리 수준

데이터베이스 격리 수준이란?

동시에 여러 트랜잭션이 데이터를 변경하거나 조회할 때 한 트랜젝션의 작업 내용이 다른 트랜젝션에 어떻게 보일지를 결정하는 기준.

 

MySQL MVCC와 Undo log

MySQL MVCC

MySQL에서는 락을 사용하지 않고 일관된 읽기를 지원하기 위해 Multi-Version Concurrency Control(MVCC)을 사용한다. MVCC란 원본 데이터와 변경 데이터를 동시에 유지하는 방식으로 MySQL에서는 Undo log를 통해 이를 구현한다.

Undo Log

Undo log는 트랜잭션 실행 이후 Rollback할 경우 기존 값을 복구하기 위해 만들어놓은 로그 영역이다.

위와 같이 Update 문을 실행하면 Commit 여부와는 관계 없이 Undo log에 먼저 기록이 남게 된다. 이후 격리 수준 등 상황에 따라 Undo log에 남아있는 값 혹은 데이터 파일에 저장된 값을 선택적으로 가져오게 된다.

• Insert문은 되돌릴 값이 없기 때문에 Undo log가 생성되지 않는다.
• Undo log는 트랜젝션 종료 이후 삭제된다.

 

READ-UNCOMMITTED

커밋되지 않은 정보를 읽을 수 있는 격리 수준이다.

DIRTY-READ가 발생하는 READ-UNCOMMITTED

1. 사용자 A가 테이블에 Lara라는 유저의 정보를 입력한다. (emp_no=50,000)
2. 사용자 B가 사용자 A의 트랜잭션이 끝나기 전에 emp_no가 50,000인 유저를 조회한다.
3. 사용자 B에게 유저 Lara의 정보가 보인다.

READ-UNCOMITTED는 위와 같이 정합성의 문제가 있어 실제로는 거의 쓰이지 않는 격리 수준이다.

 

READ-COMMITTED

커밋까지 완료된 정보를 읽을 수 있는 격리 수준이다.

DIRTY-READ가 발생하지 않는 READ-COMMITTED

1. 사용자 A가 트랜잭션을 시작하고 emp_no=50,000, first_name=Lara 테이블을 first_name=Toto로 업데이트 한다.
   1. emp_no가 50,000인 유저의 이름을 Toto로 변경하기 전에 Undo log에 Lara를 기록한다.
   2. emp_no가 50,000인 유저의 이름을 Toto에서 Lara로 바꾼다.
2. 사용자 B가 id가 50,000인 유저를 조회한다.
3. Undo log에서 값을 가져와서 emp_no=50,000, first_name=Lara가 반환된다.
4. 사용자 A의 요청이 Commit 된다.
5. 모든 트랜젝션이 종료되면서 Undo log가 삭제된다.

위와 같은 프로세스로 READ-COMMITTED에서는 Commit 되지 않은 값에 대해서는 읽어오지 않는다.

 

NON-REPEATABLE READ가 발생하는 READ-COMMITTED

1. 첫 트랜잭션이 시작되고 사용자 B가 first_name이 Toto인 값을 찾는다.
   1. 값이 반환되지 않는다.
2. 두번째 트랜젝션에서 사용자 A가 first_name이 Toto인 값을 입력한다.
3. 그 이후 첫 트랙잭션에서 first_name이 Toto인 값을 찾는다.
   1. 값이 반환된다.

READ-COMMITTED에서는 Commit 된 값은 조건 없이 읽어오기 때문에 “하나의 트랜잭션 내에서는 똑같은 SELECT 쿼리를 실행했을 때 항상 값은 결과를 가져오지 못하는” NON-REPEATALE-READ 가 발생한다.

 

REPEATABLE-READ

NON-REPEATALE-READ가 발생하지 않는 REPEATABLE-READ

하나의 트랜잭션 내에서 똑같은 SELECT 쿼리가 항상 같은 결과를 가져오는 격리 수준.

1. 사용자 B가 트랜젝션 ID=10으로 트랜잭션을 시작한다.
2. 사용자 B가 트랜잭션 ID=10에서 emp_no의 값이 50,000인 데이터를 조회한다.
3. 사용자 A가 트랜젝션 ID=12으로 트랜젝션을 시작한다.
4. 사용자 A가 트랜잭션 ID=12에서 값을 변경하기 전에 Undo log로 값을 복사한다.
5. 사용자 A가 트랜잭션 ID=12에 emp_no=50,000인 firtst_name의 값을 Toto로 변경한다.
6. 사용자 B가 다시 ID=10에서 emp_no의 값이 50,000인 데이터를 조회한다.

⭐️ REPEATABLE-READ에서는 자신보다 작은 트랜젝션 번호의 변경 사항만 보게 된다.

 7. 사용자 B의 쿼리의 결과로 Undo log에 있는 emp_no=50,000 firtst_name=Lara를 조회한다.

 

MySQL InnoDB는 REPEATABLE-READ을 기본 격리 수준으로 한다.

Undo log는 관련된 트랜잭션이 계속 되는 동안 지속적으로 생성되기 때문에 트랜잭션이 길어지면 다음과 같은 문제점을 갖는다.

• Undo log가 많아지게 되면서 기록들 사이에서 값을 탐색하는 시간이 길어진다.
• Ungo log의 값을 저장하는 공간이 지나치게 커질 수 있다.

 

PHANTOM-READ가 발생하는 REPEATABLE-READ

1. 사용자 B가 트랜젝션 ID=10으로 트랜잭션을 시작한다.
2. 사용자 B가 트랜잭션 ID=10에서 emp_no의 값이 50,000보다 크거나 같은 데이터를 조회한다.
   1. 1건의 결과가 돌아온다.
3. 사용자 A가 트랜젝션 ID=12으로 트랜젝션을 시작한다.
4. 사용자 A가 트랜잭션 ID=12에서 emp_no=50,001, firtst_name=Georgi인 값을 삽입한다.
5. 사용자 B가 다시 ID=10에서 emp_no의 값이 50,000인 데이터를 FOR UPDATE 쿼리로 조회한다.
6. SELECT … FOR UPDATE는 레코드에 잠금을 거는데, Undo log는 잠금을 걸 수 없기 때문에 테이블에서 값을 조회 해와서 결과값이 2개가 된다.

 

PHANTOM-READ가 발생하지 않는 REPEATABLE-READ

바로 위의 PHANTOM-READ가 발생하는 REPEATABLE-READ와 유사하지만 첫 트랜잭션의 처음 명령어가 FOR UPDATE라는 점이 다르다.

이 경우 ID=10인 트랜잭션의 FOR UPDATE문에 의해 emp_no가 50,000보다 큰 범위에 대해서는 NEXT-KEY-LOCK이 걸려 ID=10 트랜잭션이 완료되기 전까지 새로 삽입하려 하는 값이 Commit 되지 않는다.

따라서 일반적인 경우 MySQL에서는 REPEATABLE-READ의 경우에서 PHANTOM-READ가 발생하지 않는다.

 

SERIALIZABLE

단순한 읽기 작업에도 공유락을 획득해야 하며 동시에 다른 트랜젝션은 그 레코드를 변경할 수 없다. 한 트랜젝션에서 읽거나 쓰는 레코드에 접근할 수 없다.

 

QnA

Q. PHANTOM-READ는 UPDATE에서 발생하지 않는 것인지?

A. PHANTOM-READ = 하나의 트랜잭션에서 같은 조건으로 2번 읽었는데 결과가 다른 현상.

1. 트랜잭션 A가 salary > 5000;로 급여가 5000 이상인 직원을 조회
2. 그 사이에 트랜잭션 B가 특정 직원의 급여를 5000에서 6000으로 수정
3. 트랜잭션 A가 다시 salary > 5000;로 급여가 5000 이상인 직원을 조회

결과가 달라짐 (UPDATE에서도 PHANTOM-READ가 발생한다)

Q. MySQL은 기본 레벨이 REPEATABLE-READ인데 그러면 락을 걸고 읽기를 하는건지?

A. 기본적으로는 락을 걸지 않고 스냅샷 기반 읽기를 수행한다. 그러나 특정한 경우에는 락을 사용해서 읽기 작업을 수행할 수 있다.

# 예시
1. SELECT ... FOR UPDATE
2. SELECT ... LOCK IN SHARE MODE

 

참고자료:

RealMySQL8.0

https://mangkyu.tistory.com/299