MySQL 격리 수준

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

‘Dirty Read’라고도 하는 Read Uncommitted는 거의 사용하지 않고, Serializable 또한 동시성이 중요한 DB에서는 거의 사용되지 않는다.

4개의 격리 수준에서 순서대로 뒤로 갈수록 트랜잭션 간 데이터 격리(고립) 정도가 높아지며 동시성도 떨어진다.

격리 수준이 높아질수록 MySQL 서버의 처리 성능이 많이 떨어질 것으로 예상하는데, Serializable이 아니라면 크게 개선이나 저하가 발생하지 않는다.

• SQL-92, SQL-99 표준에 따르면 Repeatable Read에서는 Phantom Read가 발생할 수 있지만, InnoDB는 독특한 특성으로 Repeatable Read에서도 발생하지 않는다.

일반적인 DB는 Read Committed와 Repeatable Read 중 하나를 사용한다.

Read Uncommitted

Read Uncommitted 격리 수준에서는 그림과 같이 트랜잭션에서의 변경 내용이 commit, rollback 여부 상관없이 다른 트랜잭션에서 보인다.

사용자 A 트랜잭션에서 insert를 수행하고 commit 하기 전에, 사용자 B에서 조회가 가능하다. 문제는 사용자 A 트랜잭션에서 롤백을 해도 여전히 사용자 B 트랜잭션에서는 ‘Lura’를 조회하고 계속 처리한다는 점이다.

이렇게 어떤 트랜잭션에서 처리한 작업이 완료되지 않은 상태를 다른 트랜잭션에서 볼 수 있는 현상을 더티 리드(Dirty Read)라고 한다.

Read Uncommitted 격리 수준에서 발생하는 더티 리드는 정합성에도 문제가 많다. 그래서 MySQL을 사용한다면 최소한 Read Committed 이상의 격리 수준 사용을 권장한다.

Read Committed

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

사용자 A 트랜잭션에서 데이터를 변경했는데, 새로운 값은 테이블에 즉시 기록되고 이전 값은 언두 영역으로 백업 된다. 데이터를 변경한 트랜잭션에서 커밋을 수행하기 전, 다른 트랜잭션에서 조회하면 이전 값이 조회된다.

• 그림에서 사용자 B 같은 경우에는 원본 테이블이 아닌 언두 영역에 백업된 레코드를 가져온 것이다.

Read Committed 격리 수준에서는 어떤 트랜잭션에서 변경한 내용이 커밋 되기 전까지는 다른 트랜잭션에서 변경 내역을 조회할 수 없다. 이 격리 수준에서도 “Non-Repeatable Read”라는 부정합의 문제가 있다.

이는 레코드가 반복되어 읽어지는 상황에서 발생하는 부정합 문제이다.

그림처럼 사용자 B가 트랜잭션을 시작하고 ‘Toto’인 사용자를 검색했는데 결과는 없었다. 하지만 다른 트랜잭션(사용자 A)에서 데이터를 변경하고 커밋 후, 똑같은 트랜잭션(사용자 B)에서 조회한 결과 1건이 조회된다.

이는 하나의 트랜잭션 내에서 select query를 실행했을 때, 항상 같은 결과를 가져와야 한다는 “Repeateble read” 정합성에 어긋나는 것이다.

• 이러한 현상을 “Non-Repeatable Read” 라고 한다.

• 금융 서비스에서 하나의 트랜잭션에서 입/출금 처리가 진행될 때, 다른 트랜잭션에서 조회를 진행할 때 select query 실행마다 다른 결과를 가져올 수 있다.

중요한 것은 사용 중인 격리 수준에서 실행되는 쿼리가 어떤 결과를 가져오게 되는지 예측할 수 있어야 한다.

Repeatable Read

Repeatable Read는 MySQL InnoDB Storage Engine에서 기본으로 사용되는 격리 수준이다.

Binary log를 가진 MySQL 서버에서는 최소 Repeatable Read 격리 수준 이상을 사용해야 한다.

• 이 격리 수준에서는 “Non-Repeatable Read” 부정합이 발생되지 않는다.

InnoDB는 트랜잭션이 Rollback 될 가능성에 대비해 변경되기 전 언두(Undo) 공간에 백업하고 실제 레코드 값을 변경한다. 이러한 변경 방식을 MVCC라고 하며, Repeatable Read 격리 수준은 MVCC를 위해 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있게 보장한다.

Read Committed도 MVCC를 이용해 commit 되기 전의 데이터를 보여주지만, Repeatable Read와 차이는 언두 영역에 백업된 레코드의 여러 버전 중 몇 번째 이전 버전까지 찾아가느냐에 있다.

🔥트랜잭션 내부에서 실행되는 select와 외부에서 실행되는 select의 차이가 없는 Read Committed와 다르게 Repeatable Read 에서의 select query는 트랜잭션 범위 내에서만 존재한다.

Read Committed:

• 트랜잭션 내/외부에서 실행되는 select query 결과가 크게 다르지 않다.

• 각 select query는 해당 시점에 커밋 된 데이터만 읽는다.

• 동일한 트랜잭션 내에서도 여러 번의 select query는 서로 다른 결과를 반환할 수 있다.

  • B 트랜잭션에서 두 번의 select query를 실행할 때 A의 트랜잭션의 커밋 전/후

Repeatable Read:

• select query는 트랜잭션이 시작된 시점의 데이터 스냅샷을 기준으로 실행된다.

• 트랜잭션 내에서 여러 번의 select query가 실행되어도 항상 같은 결과를 반환한다.

• 다른 트랜잭션에서 데이터를 변경하고 커밋 해도, 현재 트랜잭션 내의 select query 결과는 변하지 않는다.

모든 InnoDB의 트랜잭션은 고유한 transaction_id(순차적으로 증가하는 값)을 가지며, 언두 영역에 백업된 모든 레코드에는 변경을 발생시킨 transaction_id가 포함되어 있다.

언두 영역의 백업된 데이터는 InnoDB 엔진이 불필요하다고 판단하는 시점에 주기적으로 삭제한다. MVCC를 보장하기 위해 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수 없다. (필요로 생각되는 더 작은 trx_id를 삭제할 수 없다.)

그림에서 테이블에 저장된 레코드의 trx_id는 6이다.

사용자 B의 트랜잭션(id=10)이 먼저 시작되고, 중간에 사용자 A의 트랜잭션(id=12)이 데이터를 변경해도 사용자 B의 트랜잭션의 select query 결과는 항상 동일하다.

• 사용자 B 트랜잭션은 자신보다 작은 트랜잭션 번호(10보다 작은)의 변경사항만을 바라본다.

• MVCC의 핵심 원칙 중, 실행 중인 트랜잭션이 이전 버전의 데이터를 읽어야 할 수 있어야 하기 때문에 해당 트랜잭션보다 오래된(번호가 작은) 데이터는 보존되어야 한다.

Repeatable Read 격리 수준에서도 Phantom Read 부정합이 발생할 수 있다.

그림처럼 사용자 B의 트랜잭션이 먼저 실행하고 조회한다. 그리고 사용자 A의 트랜잭션에서 레코드를 삽입하고 commit 한다. 그런데 마지막에 사용자 B 트랜잭션에서 전체 레코드를 조회하면 레코드의 총 레코드 갯수가 다르다.

Repeatable Read 격리 수준이라면 select qeury의 결과가 똑같아야 하는데, 다르게 나온 것이다.

이렇게 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다, 안 보였다 하는 현상을 “Phantom Read" 라고 한다.

select … for update query는 레코드에 쓰기 잠금을 걸어야 하는데, 언두 레코드에는 잠금을 걸 수 없다.

그래서 select … for update나 select … lock in share mode로 조회되는 레코드는 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오게 된다.

🤔 앞에서 학습한 MySQL 잠금(Next-Key Lock)에 의하면 위 그림은 잘못된 것이 아닌가?

맞다. 사용자 B 트랜잭션에서 select .. where emp_no ≥500000 for update query가 수행되면, emp_no=500000인 레코드와 그 이후의 Gap(500000-INF)에 대해서 락을 건다. 사용자 A 트랜잭션에서 삽입하려는 레코드의 emp_no=500001은 500000 이후의 갭에 있으므로, insert가 막혀야 한다.

그래서 격리 수준의 표를 보면, Repeatable Read의 팬텀 리드는 InnoDB에서 발생하지 않는다. (Next-Key Lock)

Repeatable Read에서는 트랜잭션 시작 시점의 데이터 스냅샷을 기준으로 일관된 읽기를 보장하지만, 이것이 언두 로그를 계속 유지한다는 의미는 아니다.

Serializable

가장 엄격한 격리 수준으로 동시 처리 성능도 다른 격리 수준보다 떨어진다.

이 격리 수준으로 설정되면 읽기 작업도 공유 잠금(읽기 잠금)을 획득해야 하며, 동시에 다른 트랜잭션에서는 해당되는 레코드를 변경하지 못한다.

• 하나의 트랜잭션에서 읽고 쓰는 레코드에 다른 트랜잭션에서는 절대 접근할 수 없다.

Serializable 격리 수준에서는 일반 DBMS에서 발생하는 팬텀 리드 문제가 발생하지 않는다. 하지만 InnoDB 스토리지 엔진을 사용한다면 갭 락과 넥스트 키 락 덕분에 Repeatable Read에서도 팬텀 리드가 발생하지 않기 때문에 굳이 Serializable를 사용할 필요성은 없다.