MySQL 격리 수준 개념 정리 (1)

서론

이번 포스팅은 Lock에 대한 공부를 하는 과정에서 찾아보았던 MySQL의 격리 수준에 대하여 정리한 내용들 입니다.

모든 내용은 MySQL 공식 문서를 참고한 자료이며, 기준은 InnoDB에 대한 설명이 되겠습니다.

 

그리고 이번 포스팅에서는 격리수준 중  SERIALIZABLE 와 REPEATABLE READ에 대하여 다루고 있으며, 추후 격리 수준을 더 다룰 예정입니다.

 

---

 

트랜잭션의 격리 수준(Transaction Isolation Level)

트랜 잭션의 격리 수준이란, 여러 트랜잭션이 동시에 처리될 때, 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있도록 허용 여부를 결정짓는 것입니다.

 

트랜잭션의 격리 수준은 격리 수준이 높은 순으로 SERIALIZABLE, REPEATABLE READ, READ COMMITTED, READ UNCOMMITED가 존재합니다.

 

→ 격리 수준 내림차순

• SERIALIZABLE
• REPEATABLE READ
• READ COMMITTED
• READ UNCOMMITED

 

- SERIALIZABLE(직렬화 가능)

가장 엄격한 격리 수준을 가지는 방식으로, 여러 트랜잭션이 동일한 레코드에 동시에 접근을 허용하지 않으므로, 어떠한 데이터 부정합 문제도 발생하지 않습니다. 하지만 트랜잭션이 순차적으로 처리되어야 하므로 동시 처리 성능이 매우 떨어지게 됩니다.

 

MySQL에서는 SELECT FOR SHARE/UPDATE를 제공합니다. 대상 레코드에 각각 읽기/쓰기 잠금을 걸게 합니다. 일반 SELECT 작업은 아무런 레코드 잠금 없이 실행이 됩니다.

 

하지만 SERIALIZABLE 격리 수준에서는 순수한 SELECT 작업에서도 대상 레코드에 넥스트 키 락을 읽기 잠금(공유락, Shared Lock)으로 겁니다. 따라서 한 트랜잭션에서 네스트 키 락이 걸린 레코드는 다른 트랜잭션이 추가/수정/삭제를 할 수 없습니다.

 

SERIALIZABLE은 가장 안정하지만 가장 성능이 떨어지는 방법이며, 극단적인 안정성을 필요로 하는 곳에 사용이 됩니다.

ex) 은행, 주식

 

★예시

SELECT ... FOR UPDATE 
// 트랜잭션이 끝날 때까지 SELECT로 가져온 Row에 대한 다른 세션의 SELECT,UPDATE,DELETE를 허용x

SELECT ... FOR SHARE
// 트랜잭션이 끝날 떄까지 SELECT로 가져온 Row에 대한 다른 세션은 SELECT는 허용한다.

 

- REPEATABLE READ(반복 가능한 읽기)

일반적인 RDBMS는 번경 전의 레코드를 언두(Undo) 공간에 백업해둡니다. 그러면 변경 전/후 데이터가 모두 존재하므로, 동일한 레코드에 대해 여러 버전의 데이터가 존재한다고 하여 이를 MVCC(Multi-Version Concurrency Control, 다중 버전 동시성 제어) 라고 부릅니다.

 

MVCC를 통해 트랜잭션이 롤백된 경우에 데이터를 복원할 수 있을 뿐만 아니라, 서로 다른 트랜잭션 간에 접근할 수 있는 데이터를 제어할 수 있습니다. 각각의 트랜잭션에는 고유의 번호를 가지게 되며, 백업 레코드에는 어느 트랜잭션에 의해 백업이 되었는지 트랜잭션 번호를 함께 저장합니다.

 

그리고 데이터가 불필요해지는 시점이 정해지게 되면 주기적으로 삭제가 됩니다.

 

REPEATABLE READ는 MVCC를 통해 한 트랜잭션 내에서 동일한 결과를 보장하지만, 새로운 레코드가 추가 되는 경우에 대해서는 보장하지 않습니다.

 

예를 들어 사용자 B의 START TRANSACTION이 되고 idx=2인 값을 조회한다고 했을 때라고 하겠습니다. 트랜잭션은 종료되지 않은 시점입니다.

 

사용자 B 트랜잭션 발생

 

그리고 사용자 A가 접근하여 과일 Table의 orange 정보를 peach로 갱신하는 상황을 보이겠습니다. 그럴 경우 MVCC를 통해 기존 데이터는 변경이 되지만 백업된 언두 로그에는 그대로 남아있습니다.

 

사용자 B 이후에 사용자 A 트랜잭션 발생

 

그렇다면 여기서 사용자 B가 트랜잭션이 종료되지 않는 시점에서 조회를 할 경우 어떻게 되는지 조회 해보겠습니다.

 

사용자 B 트랜잭션 구간 중에 사용자 A의 트랜잭션이 발생

 

사용자 B의 트랜잭션은 (2) 사용자 Adml 트랜잭션(4) 보다 먼저 시작된 상태입니다.

 

이때 REPEATABLE READ는 트랜잭션 번호를 기준으로 하여 자신 보다 먼저 실행된 트랜잭션의 데이터만을 조회합니다.

 

만약 테이블에 자신보다 이후에 실행된 트랜잭션의 데이터가 존재(commit)한다면, 언두 로그를 참고하여 데이터를 조회합니다.

 

따라서 사용자 A의 결과가 커밋이 되었지만, 사용자 B는 A보다 먼저 실행된 트랜잭션이기에 사용자 B는 처음 조회했던 결과를 그대로 얻게 됩니다.

 

즉, REPEATABLE READ(반복 가능한 읽기)는 말 그대로 읽은 정보에 대하여 다른 트랜잭션이 수정하더라고 기존의 동일한 결과를 반환하는 것을 보장합니다.

 

그리고 REPEATABLE READ는 새로운 레코드가 추가가 되더라도 추가된 정보에 대해서는 확인하지 않습니다.

 

따라서 SELECT로 조회한 경우 트랜잭션이 끝나기 전에 다른 트랜잭션에 의해 추가된 레코드는 유령읽기라는 걸 통해 발견될 수 있지만, MVCC를 통해 유령읽기가 발생하지 않습니다.

 

이에 해당하는 상황을 나타내보겠습니다.

 

유령읽기 예시

위 그림과 같이 사용자 B가 처음 idx가 2보다 크거나 같은 경우에 대한 조회를 했을 때 1건에 대한 정보만 조회가 되었습니다.

 

사용자 A가 접근하여 새로운 peache 정보를 추가를 하였지만, MVCC를 통해 사용자 B는 다시 조회를 하여도 유령 읽기가 되지 않아 기존 1건에 대해서 만 조회가 되었습니다.

 

유령 읽기가 되는 경우에 대해서 알아보겠습니다. 바로 잠금이 사용되는 경우에 해당합니다. MYSQL에서는 비관적 락을 제공합니다.

 

비관적 락이란 SELECT FOR UPDATE 구문을 사용한 경우를 의미하며, 읽기와 쓰기 모두 잠금을 하는 형태에 해당합니다.

 

만약 SELECT 만을 허용한다면 , SELECT FOR SHARE을 사용하면 됩니다. 락이 풀리는 시점은 한 트랜잭션이 커밋 또는 롤백이 되는 시점에 해제가 됩니다.

 

이제 해당 잠금을 이용한 예시를 통해 유령 읽기를 확인해 보겠습니다.

 

우선 사용자 B가 idx ≥ 2에 대한 잠금을 실행한 상태입니다. 그 시점에 사용자 A가 새로운 레코드를 추가하고 나서 다시 사용자 B가 SELECT FOR UPDATE 를 했을 경우를 확인해보겠습니다.

 

 

이와 같은 경우는 MVCC가 작동해야 할 것 같지만, SELECT FOR UPDATE로 실행을 하였기 떄문에, 최종 결과는 추가된 레코드까지 포함한 정보를 가져오게 됩니다.

 

왜냐하면 잠금 읽기 같은 경우는 언두 로그를 기준으로 조회를 하는 것이 아닌 테이블 기준으로 조회를 해오기 때문입니다.

 

하지만 MYSQL에는 락 개념이 존재하기 때문에 이러한 유령 읽기가 된다 할지라도 동시성 문제는 발생하지 않습니다.

 

위의 상황은 사용자 B가 idx값이 2보다 크거나 같은 경우에 대해서 락을 걸었습니다. 이렇게 될 경우 사용자 A가 접근했을 때, B가 트랜잭션을 종료하지 않고 있다면, 사용자 A는 타임아웃이 걸리게 됩니다.

 

만약 처음에 사용자 B가 순수 SELECT를 한 상황에서 사용자 A가 INSERT 후 Commit을 했을 경우 다시 사용자 B가 FOR UPDATE를 통해 잠금 읽기를 하게 된다면, 유령 읽기가 됩니다.

 

 

---

 

직접 MySQL에 트랜잭션 확인해보기

사용자 A가 조회하는 동안 사용자 B가 업데이트 이후 커밋하는 과정에 대한 트랜잭션 입니다.

 

위의 빨간색 으로 번호대로 발생한 쿼리 입니다. 

1. 사용자 A가 1번으로 조회를 한 결과 재고 개수가 99851 입니다.
2. 사용자 B가 2번으로 조회를 한 결과 재고 개수가 99851 입니다.
3. 사용자 A가 다시 3번으로 조회를 한 결과 재고 개수는 99851 입니다.
4. 사용자 B가 4번으로 재고 개수 1개를 제거하고 Commit은 하지 않습니다.
5. 사용자 A가 5번으로 조회를 한 결과 재고 개수는 99851개로 유지 됩니다.
6. 사용자 B가 commit을 하고 조회를 한 결과 99850개가 됩니다.

위의 순서와 같이 commit이 발생하기 전까지는 업데이트가 발생을 하여도 이전 값을 그대로 유지하게 됩니다.

 

아래 는 좀 더 자세하게 테스트를 해본 결과 입니다.

 

1. 사용자 A가 1번으로 조회를 합니다. 재고 개수 : 99850
2. 사용자 B가 2번으로 조회를 합니다. 재고 개수 : 99850
3. 사용자 B가 3번으로 재고 -1 업데이트를 합니다. commit x
4. 사용자 A가 4번으로 재고 -1 업데이트를 합니다. commit x
5. 사용자 B가 commit을 하고 5번으로 조회를 합니다. 재고 개수 : 99849
6. 사용자 A가 6번으로 조회를 합니다. 재고 개수 : 99848
7. 사용자 B가 7번으로 조회를 합니다. 재고 개수 : 99849
8. 사용자 A가 commit을 하고 8번으로 조회를 합니다. 재고 개수 : 99848
9. 사용자 B가 9번으로 조회를 합니다. 재고 개수 : 99848

 

마치며..

REPEATABLE READ 격리 수준에서의 커밋 되는 시점과 트랜잭션에 대하여 알아보았습니다. 평상시에는 SQL을 사용하면서도 격리 수준에 대한 생각을 해보지 않았던 것 같습니다.

 

우연히 동시성 제어에 대한 처리 방식들을 정리한 기술 블로그를 찾아보던 중 단순히 격리 수준에 대한 지식으로만으로 Lock 기법을 사용하지 않고 좀 더 심플하고 빠른 속도를 낸다는 사실에 충격이었고 우물안의 개구리같다.. 라는 생각이 들었었습니다.

 

이번 격리 수준에 대하여 정리하면서 좀 더 포괄적인 개념들을 배울 수 있었고 현장에서도 적용해 볼 수 있는 여지가 됬다는 것에 만족감을 느끼고 있습니다.