🌱 Transaction lsolation Level! (feat. MySQL)

Transaction은 이미 블로깅 해놨으니 바로 격리수준으로 넘어가보자!

🤔 MySQL의 격리수준

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

격리 수준은 다음과 같이 4가지로 정의할 수 있습니다.

• READ UNCOMMITTED (커밋되지 않은 읽기)
• READ COMMITTED (커밋된 읽기)
• REPEATABLE READ (반복 가능한 읽기)
• SERIALIZABLE (직렬화 가능)

순서대로 READ UNCOMMITTED의 격리 수준이 가장 낮고 SERIALIZABLE의 격리 수준이 가장 높습니다. 격리 수준이 높아질수록 MySQL 서버의 처리 성능이 많이 떨어질 것으로 생각하는 사용자가 많은데, 사실 그렇지 않습니다.

SERIALIZABLE 격리 수준이 아니라면 크게 성능의 개선이나 저하는 발생하지 않습니다.

"DIRTY READ"라고도 하는 "READ UNCOMMITTED"는 일반적인 데이터베이스에서는 거의 사용하지 않고, SERIALIZABLE 역시 동시성이 중요한 데이터베이스에서는 거의 사용되지 않습니다.

아래 표는 트랜잭션 격리 수준에 따라 발생하는 문제점입니다.

SQL-92 또는 SQL-99 표준에 따르면 REPEATABLE READ 격리 수준에서는 PHANTOM READ가 발생할 수 있으나 InnoDB에서는 독특한 특성 때문에 REPEATABLE READ 격리 수준에서도 PHANTOM READ가 발생하지 않습니다.
오라클의 경우 주로 READ COMMITTED을, MySQL의 경우 REPEATABLE READ를 주로 사용합니다.

👎 READ UNCOMMITTED

격리 수준에서는 위 그림처럼 각 트랜잭션에서의 변경 내용이 COMMIT이나 ROLLBACK 여부에 상관 없이 다른 트랜잭션에서 보여지게 됩니다. 위 그림은 다른 트랜잭션이 사용자 B가 실행하는 SELECT 쿼리의 결과에 어떠한 영향을 미치는지 보여주는 예시입니다.

READ UNCOMMITTED 격리 수준에서는 위 그림처럼 각 트랜잭션에서의 변경 내용이 COMMIT 이나 ROLLBACK 여부에 상관 없이 다른 트랜잭션에서 보여지게 됩니다.

사용자 A가 INSERT한 내용이 커밋이 되지 않았음에도 사용자 B는 조회가 가능하고 여기서 A가 INSERT 내용을 롤백해버려도 B는 달라진 내용이 어떤 것인지 알지 못한 상태로 작업하게 됩니다.

이처럼 어떠한 트랜잭션에서 처리한 작업이 완료되지 않았음에도 불구하고 다른 트랜잭션에서 볼 수 있게 되는 현상을 더티 리드(Dirty Read)라 하고, 더티 리드가 허용도니는 격리 수준이 READ UNCOMMITTED 입니다. 더티 리드 현상은 데이터가 나타났다가 사라졌다 하는 현상을 초래할 수 있으므로 개발자와 사용자를 혼란 스럽게 할 수 있습니다. 또한 더티 리드를 유발하는 READ UNCOMITTED 격리 수준은 RDBMS 표준에서 트랜잭션의 격리 수준으로 인정하지 않을 정도로 정합성에 문제가 많은 격리 수준입니다. 따라서 MySQL을 사용한다면 최소 READ COMMITTED 이상의 격리 수준을 사용할 것을 권장합니다.

🤔 READ COMMITTED

READ COMMITTED 격리 수준은 오라클 DBMS에서 기본적으로 사용되고 있으며 온라인 서비스에서 가장 많이 선택되는 격리 수준입니다. 이 레벨에서는 위 READ UNCOMMITTED 수준에서 발생할 수 있는 더티 리드(Dirty Read)와 같은 현상은 발생하지 않습니다. 어떠한 트랜잭션에서 데이터를 변경하더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있기 때문입니다.

언두 레코드는 InnoDB의 시스템 테이블 스페이스의 언두 영역에 기록이 되는데, 언두 레코드는 트랜잭션의 격리 수준을 보장하기 위한 용도뿐 아니라 트랜잭션의 ROLLBACK에 대한 복구에도 사용이 됩니다.

★ 언두(Undo) 로그 ★
언두 영역은 UPDATE 문장이나 DELETE와 같은 문장으로 데이터를 변경했을 때 변경되기 전의 데이터(이전 데이터)를 보관하는 곳입니다. 예를 들어 다음 쿼리를 살펴보겠습니다.

MySQL > UPDATE member SET name = '이주현' WHERE member_id = '5';

위 문장이 실행되면 트랜잭션을 커밋하지 않아도 실제 데이터 파일 내용은 "이주현"으로 변경이 됩니다. 그리고 변경되기 전의 값이 "홍길동"이라면 언두 영역에는 "홍길동"이라는 값이 백업이 되는 것입니다. 이 상태에서 만약 사용자가 커밋하게 되면 현재 상태(이주현)가 그대로 유지되고, 롤백하게 된다면 언두 영역의 백업된 데이터(홍길동)를 다시 데이터 파일로 복구합니다.

이러한 언두의 데이터는 크게 두 가지 용도로 사용이 됩니다.

1) 트랜잭션의 롤백 대비용

2) 트랜잭션의 격리 수준을 유지하면서 높은 동시성을 제공

🚫 "NON-REPEATABLE READ" - READ COMMITTED 격리 수준에서 발생할 수 있는 문제점

사용자 B가 트랜잭션을 시작하고 'Choi OO'을 조회하면 일치하는 데이터가 존재하지 않는다.

하지만 이후에 사용자 A가 2001의 NAME을 'Choi OO'으로 수정하고 커밋한 후 사용자 B가 동일한 쿼리로 조회하면 이번에는 결과가 1건 조회된다. 이는 별다른 문제는 없어보이나 사용자 B가 하나의 트랜잭션내에서 동일한 SELECT 쿼리를 실행했을 때 항상 같은 결과를 보장해야 한다는 "REPEATABLE READ" 정합성에 어긋나게 됩니다.

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

예를 들어, 다른 트랜잭션에서 입금과 출금 처리가 계속 진행되고 있을 때 다른 트랜잭션에서 오늘 입금된 금액의 총합을 조회한다고 가정해보겠습니다. "REPEATABLE READ"가 보장되지 않기 때문에 총합을 계산하는 SELECT 쿼리를 실행할 떄마다 다른 결과를 가져올 것입니다. 중요한 것은 사용중인 트랜잭션의 격리 수준에 의해 실행되는 SQL 문장이 어떠한 결과를 가져오게 되는지 정확히 예측할 수 있어야 합니다. 그리고 이를 위해서는 각 트랜잭션의 격리 수준이 어떻게 작동하는지 알고 있어야합니다.

😍 REPEATABLE READ

REPEATABLE READ는 MySQL의 InnoDB 스토리지 엔진에서 기본적으로 사용되는 격리 수준입니다. 이 격리 수준에서는 READ COMMITTED 격리 수준에서 발생하는 "NON-REPEATABLE READ" 부정합이 발생하지 않습니다.

InnoDB 스토리지 엔진은 트랜잭션이 ROLLBACK될 가능성에 대비해 변경되 전 레코드를 언두(Undo) 영역에 백업해두고 실제 레코드 값을 변경합니다. 이러한 변경 방식을 MVCC(Multi Version Concurrency Control)이라고 한다.

REPEATABLE READ는 이 MVCC를 위해 언두 영역에 백업된 이전 데이터를 통해 동일한 트랜잭션 내에서는 동일한 결과를 보여줄 수 있도록 보장합니다.

READ COMMITTED 격리 수준 또한 MVCC를 이용해 COMMIT되기 전의 데이터를 보여주나 REPEATABLE READ와 READ COMMITTED의 차이는 언두 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전의 버전까지 찾아 들어가야 하는지에 있습니다.

MVCC(Multi Version Concurrency Control) - 잠금을 사용하지 않는 일관된 읽기를 제공

MVCC는 다중 버전 병행수행 제어의 약자로 DBMS에서는 쓰기(Write) 세션이 읽기(Read) 세션을 블로킹하지 않고, 읽기 세션이 쓰기 세션을 블로킹하지 않게 서로 다른 세션이 동일한 데이터에 접근했을 때 각 세션마다 스냅샷 이미지를 보장해주는 메커니즘
이는 RDBMS에서 동시성을 높이기 위해 등장한 기술로, 소수의 전산실 운영자들이 서버 컴퓨터를 사용하던 시절에는 MVCC가 선택사항이었지만 인터넷이 보편화되고 온라인으로 업무를 처리하는 시대에서는 DBMS를 선택하는데 있어 MVCC가 가장 중요한 요소가 됐다.

출처 : 데이터넷(http://www.datanet.co.kr)

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

REPEATABLE READ 격리 수준에서는 MVCC를 보장하기 위해 실행중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수 없습니다.

위 그림에서는 언두 영역에 백업된 데이터가 하나만 있는것으로 표현했지만, 실제로는 하나의 레코드에 대해 백업이 하나 이상 얼마든지 존재할 수 있습니다. 만약 한 사용자가 BEGIN 명령으로 트랜잭션을 시작한 후 장시간동안 트랜잭션을 종료하지 않으면 언두 영역이 백업된 데이터로 무한정 커질 수 있고, 이렇게 언두 영역에 백업된 레코드가 많아질수록 MySQL 서버의 처리 성능이 떨어질 수 있습니다.

🚫 "PHANTOM READ(PHANTOM ROW)" - REPEATABLE READ 격리 수준에서 발생할 수 있는 문제점

REPEATABLE 격리 수준에서도 다음과 같은 부정합이 발생할 수 있습니다.

아래 그림은 사용자 A가 employees 테이블에 INSERT를 실행하는 도중에 사용자 B가 SELECT .. FOR UPDATE 쿼리로 employees 테이블을 조회했을 때의 결과입니다.

위 그림에서 사용자 B는 트랜잭션 시작 후 SELECT 쿼리를 수행하고 있습니다. 따라서 이전 REPEATABLE READ 격리 수준에서의 설명처럼 동일한 트랜잭션 내에서는 결과가 동일해야 합니다.

하지만 위 그림에서 사용자 B가 실행하는 두 번의 SELECT .. FOR UPDATE 쿼리 결과는 서로 다릅니다. 이렇게 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다가 안보였다가 하는 현상을 PHANTOM READ(PHANTOM ROW)라고 합니다.

SELECT .. FOR UPDATE 쿼리는 SELECT하는 레코드에 쓰기 잠금을 걸어야 하는데, 언두 레코드에는 잠금을 걸 수 없습니다.

따라서 위와 같은 쿼리는 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오게 됩니다.

💎 SERIALIZABLE

가장 단순한 격리 수준이면서 가장 엄격한 격리 수준입니다. 또한 동시 처리 성능도 다른 트랜잭션 격리 수준보다 현저히 떨어집니다.

트랜잭션의 격리 수준이 SERIALIZABLE로 설정되면 읽기 작업도 공유 잠금(읽기 잠금)을 획득해야 하며, 동시에 다른 트랜잭션은 그러한 레코드를 변경할 수 없습니다. 즉, 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없습니다.

SERIALIZABLE 격리 수준에서는 일반적인 DBMS에서 발생하는 "PHANTOM READ" 문제가 발생하지 않지만, 위에서 설명드렸듯이 InnoDB 스토리지 엔진에서는 REPEATABLE READ 격리 수준에서도 "PHANTOM READ"가 발생하지 않기 때문에 굳이 SERIALIZABLE 격리 수준을 사용할 필요성은 없어보입니다.

✏️ 정리

🥇 트랜잭션 격리 수준

• READ UNCOMMITTED: 트랜잭션내에서 커밋하지 않은 데이터에 다른 트랜잭션의 접근이 가능
• READ COMMITTED: 트랜잭션내에서 커밋된 데이터만 다른 트랜잭션이 읽는 것을 허용
• REPEATABLE READ: 트랜잭션 내에서 한 번 조회한 데이터를 반복해서 조회해도 결과는 동일
• SERIALIZABLE: 가장 엄격한 격리 수준으로 완벽한 읽기 일관성 모드 제공

👿 격리 수준에 따라 발생할 수 있는 문제점

• DIRTY READ: 어떠한 트랜잭션에서 처리한 작업이 완료되지 않았음에도 불구하고 다른 트랜잭션에서 볼 수 있게 되는 현상
• NON-REPEATABLE READ: 동일한 SELECT 쿼리를 실행했을 때 항상 같은 결과를 보장해야 한다는 "REPEATABLE READ" 정합성에 어긋나는 현상
• PHANTOM READ: 한 트랜잭션내에서 동일한 쿼리를 두 번 수행했는데, 첫 번째 쿼리에서 존재하지 않던 유령(Phantom) 레코드가 두 번째 쿼리에서 나타나는 현상

참고한 블로그👍