MySQL

MySQL의 트랜잭션 격리 수준 실습해보기

kindof

·

2023. 9. 14. 19:54

트랜잭션은 ACID라는 아래 네 가지 조건을 만족해야 합니다.

 

  • 원자성(Atomicity) : 트랜잭션 내에서 실행한 작업들은 마치 하나의 작업인 것처럼 모두 성공하든, 모두 실패해야 한다.
  • 일관성(Consistency) : 모든 트랜잭션은 일관성있는 DB 상태를 유지해야 한다. 가령, 트랜잭션의 결과는 DB에서 정한 무결성 제약 조건을 항상 만족해야 한다.
  • 격리성(Isolation) : 동시에 실행되는 트랜잭션들이 서로에게 영향을 미치지 않도록 격리해야 한다.
  • 지속성(Durability) : 트랜잭션이 성공적으로 끝나면 그 결과가 항상 기록되어야 한다. 

 

그리고 이 네 가지 조건 중에서 격리성을 완벽히 보장하기 위해서는 사실 상 개별 트랜잭션이 동시에 처리되지 않고 순서대로 처리되어야 하는데요. 문제는 이렇게 하면 동시성 처리가 거의 불가능해져서 애플리케이션 성능이 매우 떨어진다는 것입니다.

 

따라서, 적절한 트랜잭션의 격리 수준을 설정하여 동시성을 확보하면서 어느정도의 부정합성을 교환하는 Trade-Off가 필요하게 됩니다.

 

그래서 이번 글에서는 MySQL의 트랜잭션 격리 수준에 대해 정리해보고, 실제로 각 격리 수준에 대한 테스트를 해봄으로써 데이터 정합성과 관련해 어떤 문제가 있을 수 있는지 테스트해보려고 합니다.

 

1. 트랜잭션 격리 수준

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

Transaction isolation level

예를 들어, 위 그림에서 Transaction 1 은 TableA에서 ID = 1 인 데이터를 조회하려고 합니다. 동시에, Transaction 2에서는 해당 데이터의 일부를 수정하고 있습니다.

 

각기 다른 트랜잭션 격리 수준은 "Transaction 1에서 조회한 데이터는 어떻게 보일까?"라는 질문의 답을 다르게 내놓게 됩니다.

 

2. MySQL의 네 가지 격리 수준

MySQL에서는 크게 네 단계, READ UNCOMMITTED, READ COMMITTED, REPETABLE READ, SERIALIZABLE로 격리 수준을 구분합니다. 

 

각 격리 수준에 따라 데이터 부정합의 문제가 발생하는 부분이 있고 발생하지 않는 부분이 있는데요. 

 

하나씩 살펴보도록 하겠습니다.

 

2-1. READ UNCOMMITTED

READ UNCOMMITTED 격리 수준에서는 각 트랜잭션에서의 변경 내용이 커밋이나 롤백 여부에 관계없이 다른 트랜잭션에서 보이게 됩니다.

 

[1] MySQL에서는 autocommit 모드가 활성화되어 있기 때문에 해당 격리 수준을 테스트하기 위해 autocommit 옵션을 비활성화하고 테스트해보겠습니다.

 

[Preferences] > [SQL Execution] 탭에서 'New Connections use auto commit mode' 항목을 비활성화하면 됩니다.

autocommit 비활성화

SELECT @@AUTOCOMMIT;

// Result
0

 

[2] 하나의 세션에서 트랜잭션을 시작하고, INSERT 단계까지 진행하겠습니다. COMMIT은 하지 않은 상태입니다.

START TRANSACTION;

INSERT into test_db.student values (-1111, 'jsh', 'MATH' , '1996-09-18', 'Male', null);		  

 

[3] MySQL에서는 기본 트랜잭션 격리 수준이 아래에서 설명할 REPETABLE READ 입니다. 따라서 지금 테스트해 볼 READ UNCOMMITTED 수준으로 격리 수준을 설정하고, 위에서 아직 커밋하지 않은 데이터를 읽어보겠습니다.

START TRANSACTION;

INSERT into test_db.student values (-1111, 'jsh', 'MATH' , '1996-09-18', 'Male', null);		  
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

SELECT * from student WHERE id = -1111;

 

Commit 하지 않은 데이터를 읽을 수 있다.

아직 커밋하지 않은 데이터임에도 불구하고 다른 세션에서 해당 데이터를 조회할 수 있습니다.

 

이처럼 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 데이터를 볼 수 있는 현상을 더티 리드(Dirty Read)라 하는데요.

 

만약 INSERT를 수행하던 세션에서 COMMIT 이전에 ROLLBACK을 했다면 INSERT / ROLLBACK 사이에서 데이터를 조회했을 때는 데이터가 존재하고 커밋 이후에는 데이터가 사라지는 정합성 문제가 발생하게 됩니다.

 

이에 따라, RDBMS 표준에서는 READ UNCOMMITTED를 트랜잭션 격리 수준으로 인정하지 않을 정도로 문제가 많은 격리 수준으로 취급하고 있습니다.

 

 

2-2. READ COMMITTED

READ COMMITTED 격리 수준은 위에서 지적했던 더티 리드(Dirty Read) 문제가 발생하지 않습니다. 이름에서 알 수 있는 것처럼 COMMIT이 완료된 데이터만 조회할 수 있기 때문인데요.

 

아래와 같이 READ COMMITTED으로 격리 수준을 바꾸고, 아직 커밋하지 않은 데이터를 조회하면 결과가 나오지 않습니다.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED ;
SELECT * from student 
WHERE id = -1111;

READ COMMITTED 에서는 커밋하지 않은 데이터를 조회하지 못한다.

 

하지만 해당 격리 수준에서도 'NON-REPETABLE READ(하나의 트랜잭션에서 SELECT 쿼리 조회 결과가 다름)'라는 부정합 이슈가 있습니다.

 

아래와 같이 테스트를 해보겠습니다(왼쪽을 세션 B, 오른쪽이 세션 A라고 하겠습니다).

 

[1] 먼저 B 세션에서 BEGIN으로 트랜잭션을 열고, 없는 데이터를 조회합니다.

START TRANSACTION;

SELECT * from student WHERE id = -9999;

// 조회결과 없음.		  
   
   

 

[2] 이 때, A 세션에서 id = -9999인 학생을 추가했습니다.

START TRANSACTION;

SELECT * from student WHERE id = -9999;

// 조회결과 없음.		  
  START TRANSACTION;

INSERT into test_db.student values (-9999, 'jsh', 'MATH' , '1996-09-18', 'Male', null);

COMMIT ;
   

 

[3] 아직 트랜잭션을 커밋하지 않은 B 세션에서 다시 데이터를 조회합니다.

START TRANSACTION;

SELECT * from student WHERE id = -9999;

// 조회결과 없음.		  
  START TRANSACTION;

INSERT into test_db.student values (-9999, 'jsh', 'MATH' , '1996-09-18', 'Male', null);

COMMIT ;
SELECT * from student WHERE id = -9999;  

 

같은 트랜잭션 내에서 SELECT 쿼리 결과가 달라진다.

 

결과를 보면, B 세션의 동일한 트랜잭션 내에서 수행한 SELECT 쿼리의 결과가 달라졌다는 것을 알 수 있습니다.

 

매 조회마다 트랜잭션을 열고 닫아버리면 위 문제는 발생하지 않겠지만 어떠한 로직에서는 하나의 트랜잭션 내에서 SELECT 쿼리를 여러 번 반복하는 상황이 있을 수 있고, 트랜잭션의 비용 측면에서도 매 조회마다 트랜잭션을 만드는 것이 부담될 수 있는 상황도 있습니다.

 

따라서, 트랜잭션 내에서 실행되는 SELECT 쿼리가 어떤 결과를 가져오게 될 지 정확히 예측할 수 없다는 문제는 꽤 발견하기 어려운 버그가 될 수 있습니다.

 

 

2-3. REPETABLE READ

REPEATABLE READ 격리 수준은 MySQL, InnoDB 엔진에서 기본으로 채택하는 격리 수준입니다.

 

이름에서 알 수 있듯이 'NON-REPETABLE READ' 부정합 문제가 발생하지 않습니다.

 

먼저 테스트를 통해 결과를 확인하고 동작 원리를 살펴보도록 하곘습니다.

 

[1] 위의 테스트들과 동일하게 B 세션에서 트랜잭션을 열고 존재하지 않는 데이터를 조회해보겠습니다.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

START TRANSACTION;
SELECT * from student WHERE id = -99999;

// 조회 결과 없음.		  
   
   

 

[2] A 세션에서 id = -99999 데이터를 INSERT 하고 커밋합니다.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

START TRANSACTION;
SELECT * from student WHERE id = -99999;

// 조회 결과 없음.		  
  START TRANSACTION;

INSERT into test_db.student values (-99999, 'jsh', 'MATH' , '1996-09-18', 'Male', null);

COMMIT;
   

 

[3] 다시 B 세션의 동일한 트랜잭션에서 데이터를 조회해보겠습니다.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

START TRANSACTION;
SELECT * from student WHERE id = -99999;

// 조회 결과 없음.		  
  START TRANSACTION;

INSERT into test_db.student values (-99999, 'jsh', 'MATH' , '1996-09-18', 'Male', null);

COMMIT;
SELECT * from student WHERE id = -99999;

// 조회결과 없음.		  

 

[4] B 세션에서 트랜잭션을 종료하고, 새로운 트랜잭션에서 다시 해당 데이터를 조회해보겠습니다.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

START TRANSACTION;
SELECT * from student WHERE id = -99999;

// 조회 결과 없음.		  
  START TRANSACTION;

INSERT into test_db.student values (-99999, 'jsh', 'MATH' , '1996-09-18', 'Male', null);

COMMIT;
SELECT * from student WHERE id = -99999;

// 조회결과 없음.		  
COMMIT; // 과거 트랜잭션 종료
---------------------------

// 새롭게 조회
SELECT * from student WHERE id = -99999;		  

 

새로운 트랜잭션이 열려야 데이터를 조회할 수 있다.

동일한 트랜잭션에서는 새로 생긴 데이터가 조회되지 않았고, 새로운 트랜잭션을 시작하고 조회했을 때에서야 데이터가 조회되는 것을 확인할 수 있습니다.

 

HOW?

REPEATABLE READ가 어떻게 동작하는지 이해하기 위해 아래 그림을 보겠습니다.

REPETABLE READ

InnoDB 엔진은 트랜잭션이 롤백될 가능성에 대비해 변경되기 전 레코드를 Undo 공간에 백업하고 실제 레코드 값을 변경합니다. 이러한 변경 방식을 MVCC(Multi Version Concurrency Control)이라고 하는데요.

 

REPETABLE READ는 MVCC를 위해 Undo 영역에 있는 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있도록 보장합니다.

 

즉, 위 그림에서 A 세션이 t2 라는 트랙잭션 ID로 데이터를 INSERT하면 해당 데이터가 Undo 영역에 복사되고 COMMIT할 때 실제 테이블에 데이터가 들어가게 되는데요.

 

이후에 B 세션에서 SELECT 쿼리를 발생시키면 해당 트랜잭션의 ID(t1, t1 < t2)를 통해 데이터를 조회하게 됩니다.

 

이 때, B 세션의 트랜잭션은 A 세션보다 먼저 열렸으므로 트랜잭션 ID인 t1 역시 t2보다 작은 값을 가지고 있습니다. 그리고 t1 트랜잭션 안에서 실행되는 모든 쿼리는 트랜잭션 번호가 t1보다 작은(이전의) 트랜잭션의 변경값만 보게 되는 것이죠.

 

사실 이러한 동작 메커니즘은 "모든 InnoDB 엔진에서 트랜잭션들은 고유한 트랜잭션 번호(순차적으로 증가하는 값)를 가지며, Undo 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함되어 있다"는 것을 활용합니다.

 

한편, 일반적으로는* REPEATABLE READ 수준에서도 PHANTOM READ(트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안 보였다 하는 문제)가 발생할 수 있습니다.

 

일반적인 SELECT 쿼리는 Undo 영역을 조회하기 때문에 PHANTOM READ 현상이 발생하지 않지만, 데이터를 수정하기 위해 특정 데이터에 대한 LOCK을 걸어야만 하는 SELECT ... FOR UPDATE 쿼리는 Undo 레코드에 Lock을 걸 수 없고, 따라서 실제 테이블을 바라봐야만 하기 때문입니다.

 

하지만, 방금 설명에서 일반적으로는* 이라는 단서를 붙였는데요. InnoDB 엔진에서는 Gap Lock, Next Key Lock이라는 개념이 존재하여 REPETABABLE READ 격리 수준에서도 PHANTOM READ 현상이 발생하지 않는다고 합니다.

 

(GAP Lock, Next Key Lock 이 무엇인지에 대해서는 다른 글에서 따로 정리해야 할 것 같습니다.)

 

2-4. SERIALIZABLE

마지막으로 살펴볼 가장 엄격한 격리 수준인 SERIALIZABLE에서는 SELECT 쿼리에 대해 Lock을 획득해야만 합니다.

 

따라서, 다른 트랜잭션은 레코드들에 대한 변경을 하지 못하게 됩니다.

 

즉, 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없도록 원천 차단하는 것이죠.

 

하지만 InnoDB 엔진에서는 REPEATABLE READ 수준에서도 PHANTOM READ가 발생하지 않기 때문에 굳이 SERIALIZABLE 격리 수준을 사용할 필요성은 없다고 합니다.

 

 

3. 정리 / Reference

이번 글에서는 MySQL InnoDB 엔진에서 사용되는 트랜잭션의 네 가지 격리 수준에 대해 살펴봤습니다.

 

텍스트로만 이해하는 것보다, 직접 테스트를 해보며 실습을 해보니 각 트랜잭션 격리 수준이 어떻게 차이가 나는지 명확하게 볼 수 있었던 것 같습니다.

 

한편, MySQL InnoDB 엔진에서 기본으로 사용되는 격리 수준은 REPEATABLE READ 인데요.

 

해당 격리 수준은 트랜잭션 ID를 통해 Undo 영역의 레코드를 확인하기 때문에 동일한 트랜잭션 내에서 수행하는 SELECT 쿼리는 동일한 결과를 보여주는 것을 알 수 있었습니다. 또한, 갭 락과 넥스트 키 락이라는 개념으로 해당 격리 수준에서도 PHANTOM READ가 발생하지 않는다는 것도 짚어봤습니다.

 

사용하는 데이터베이스와 엔진마다 트랜잭션의 격리 수준은 조금씩 다른 것 같으니, 필요에 따라 트랜잭션 격리 수준을 확인 / 변경하면서 사용하면 될 것 같습니다.

 

감사합니다.

 

https://product.kyobobook.co.kr/detail/S000001766482

https://medium.com/daangn/mysql-gap-lock-%EB%8B%A4%EC%8B%9C%EB%B3%B4%EA%B8%B0-7f47ea3f68bc

 

 

'MySQL' 카테고리의 다른 글

MySQL, InnoDB 엔진에서의 B-Tree 인덱스 구조와 클러스터링, 커버링 인덱스에 대해  (0) 2023.09.09
MySQL 다중 컬럼 인덱스(Multi-column Index) 사용 시 주의할 점  (0) 2023.08.03
M1 Mac 환경에서 MySQL 사용 환경 구축하기  (0) 2023.06.03

MySQL 카테고리와 연관된 콘텐츠