[MySQL] 트랜잭션 격리 수준을 쿼리를 통해 직접 테스트해보기

🌱 들어가기 전

 지난 리마큐 포스팅에서 그림으로 설명했던 'CREW' 테이블에 대해서 직접 코드 레벨로 작성해보고자 한다.

CREATE TABLE `CREW` (
  `id` bigint NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

 

사전 설정을 위해서 스토리지 엔진을 innoDB로 설정한 'CREW' 테이블을 생성해준다.

SET AUTOCOMMIT = false;

⭐️ 그리고, autocommit 모드는 꼭 OFF로 설정해두고 진행하자.

나는 이번에 2개의 세션을 열어서 진행하였다.

 

---

 

🌱 READ UNCOMMITTED

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

글로벌 설정으로 격리 수준을 변경하면 여파가 크기 때문에 각 세션에 대해서 위와 같이 READ UNCOMMITTED를 지정해주었다.

 

SELECT @@SESSION.transaction_isolation;

실제로 세션 정보를 확인해보면 위와 같이 READ-UNCOMMITTED로 변경된 것을 확인할 수 있다.

 

---

 

💬 Dirty Read 확인해보기 - INSERT

START TRANSACTION;
INSERT INTO CREW (id, name) VALUES (1, 'hello');
commit;

그리고, 그림이랑 똑같이 만들기 위해서 우선 1번 레코드인 'hello'를 삽입해두자. (어떤 세션이든 상관 없다)

 

# 세션 1
START TRANSACTION;
INSERT INTO CREW (id, name) VALUES (2, 'journey');

이후, 세션 1에서 Commit 하지 않고 단순히 2번 레코드 'journey'를 삽입하자.

 

# 세션 2
select * from CREW;

세션 2에서 결과를 확인해보면, 세션 1에서 아직 커밋을 하지 않았음에도 삽입이 되어 있는 것을 확인할 수 있다. (Dirty Read)

 

그럼, 이 상태에서 세션 1을 롤백시키면 어떻게 될까?

# 세션 1
ROLLBACK;

롤백 사항이 반영되어 데이터가 나오지 않는 것을 볼 수 있다.

즉, 이전에는 값이 보였는데 다시 조회하니 값이 사라지는 현상이 발생할 수 있기 때문에 주의해야 한다.

 

---

 

💬 Dirty Read 확인해보기 - UPDATE

⭐️ 위의 경우보다 update 시에 더 명확하게 드러나는 것 같아서 한 가지 더 테스트를 하고자 한다.

# 기본 데이터 삽입 후 커밋
START TRANSACTION;
INSERT INTO CREW (id, name) VALUES (1, 'hello');
COMMIT;

# 해당 데이터에 대해서 업데이트
START TRANSACTION;
UPDATE CREW SET name = 'hi' where id = 1;

 

아까처럼 hello라는 레코드가 삽입된 다음, 업데이트에 대한 트랜잭션을 시작하고 커밋을 하지 않은 상태이다.

# 세션 2
SELECT * FROM CREW;

하지만, READ UNCOMMITTED로 인해서 커밋되지 않은 사항에 대해서도 변경된 레코드 값이 조회되는 것을 볼 수 있다.

 

# 세션 1
ROLLBACK;

이 상태에서 세션 1에 대해 롤백을 한다면 hello라는 값을 받게 된다...!

 

---

 

🌱 READ COMMITTED

 

 

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

이번에는 트랜잭션 격리 레벨을 Read committed로 변경하고 살펴보자.

 

---

 

💬 해결된 Dirty Read 현상 확인하기

START TRANSACTION;
INSERT INTO CREW (id, name) VALUES (1, 'hello');
INSERT INTO CREW (id, name) VALUES (2, 'journey');
COMMIT;

먼저, 그림과 같은 상황을 만들기 위해 2개의 레코드를 삽입해두자. (이전의 실험 결과는 TRUNCATE로 제거하였다.)

 

# 세션 1
START TRANSACTION;
UPDATE CREW SET name = 'hi' WHERE id = 1;

이후, 세션 1에서 1번 레코드 'hello'에 대해 hi로 업데이트하는 쿼리를 작성한다. 단, 커밋하지 않는다.

 

# 세션 2
SELECT * FROM CREW;

이때 세션 2에서 조회해보면 언두 영역에 있는 데이터를 읽어오기 때문에 hi가 아닌 hello로 보이는 것을 볼 수 있다. 

⭐️ 이전의 read uncommitted 예제와 달라진 것을 확인할 수 있다. (dirty read 해결)

 

이후, 세션 1에서 커밋을 하고 나면 아래와 같이 변경 사항이 완전히 반영된다.

# 세션 1
COMMIT;

# 세션 2
SELECT * FROM CREW;

 

---

 

💬 NON-REPEATABLE READ 확인하기

하지만, 여기서도 문제가 있다.

위와 같은 상황에서 세션 2의 조회 쿼리 2번이 하나의 트랜잭션에서 수행된다면 어떨까?

# 세션 1
START TRANSACTION;
UPDATE CREW SET name = 'hi' WHERE id = 1;

# 세션 2
START TRANSACTION;
SELECT * FROM CREW; # 1번 결과

# 세션 1
COMMIT;

# 세션 2
SELECT * FROM CREW; # 2번 결과

현재는 쿼리를 직접 날리고 있기 때문에 알아보기 어렵지만, 만약 스프링에서 개발을 진행하는 상황이라고 해보자.

하나의 트랜잭션으로 묶여 있고, 동일한 쿼리를 날렸음에도 위와 같이 다른 결과를 받아올 수 있는 문제가 발생할 수 있어 주의해야 한다. (non-repeatable read 발생)

 

---

 

🌱 REPEATABLE READ

 

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

마찬가지로 이번에는 격리 수준을 repeatable read로 설정해준다.

 

---

 

💬 해결된 NON-REPEATABLE READ 확인하기

START TRANSACTION;
INSERT INTO CREW (id, name) VALUES (1, 'hello');
INSERT INTO CREW (id, name) VALUES (2, 'journey');
COMMIT;

샘플 레코드도 삽입해둔다.

 

# 세션 2
SELECT * FROM CREW;

먼저, 세션 2에서 조회 쿼리를 날려두면 삽입한 2개의 레코드를 그대로 반환하는 것을 볼 수 있다.

 

# 세션 1
START TRANSACTION;
UPDATE CREW SET name = 'hi' where id = 1;

# 세션 2
SELECT * FROM CREW;

이 상태에서 세션 1이 1번 레코드에 대해 업데이트 쿼리를 날리고, 세션 2에서 조회하면 그대로 'hello'라는 레코드가 뜬다.

 

# 세션 1
COMMIT;

# 세션 2
SELECT * FROM CREW;

하지만, read committed와 다르게 세션 1에서 커밋을 했더라도 세션 2의 결과는 hi가 아닌 hello가 그대로 나오는 것을 볼 수 있다.

⭐️ 즉, 이전에 발생하였던 non-repeatable read를 해결한 것이다.

 

# 세션 2
COMMIT;

# 다시 조회
SELECT * FROM CREW;

이후, 세션 2가 시작하였던 트랜잭션을 커밋하고 다시 조회 쿼리를 날려보면 위와 같이 변경된 사항을 읽어오는 것을 확인할 수 있다.

 

---

 

💬 PHANTOM READ 테스트하기

CREATE TABLE `ISAM_CREW` (
  `id` bigint NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

우선, innoDB에서는 Phantom Read가 발생하지 않도록 하기 때문에 새로운 테이블을 만들자.

 

START TRANSACTION;
INSERT INTO ISAM_CREW (id, name) VALUES (1, 'hello');
INSERT INTO ISAM_CREW (id, name) VALUES (2, 'journey');
COMMIT;

그리고, 마찬가지로 샘플 데이터를 넣어두자.

 

# 세션 2
START TRANSACTION;
SELECT * FROM ISAM_CREW WHERE id >= 2 FOR UPDATE;

세션 2에서 SELECT FOR UPDATE 구문을 활용하여 레코드를 조회해보자. 현재 'journey' 레코드만 나오는 것을 볼 수 있다.

 

# 세션 1
START TRANSACTION;
INSERT INTO ISAM_CREW(id, name) VALUES (3, 'cat');
COMMIT;

이후, 세션 1에서 새로운 레코드를 삽입하고 커밋을 해보자.

 

# 세션 2
SELECT * FROM ISAM_CREW WHERE id >= 2 FOR UPDATE;

위와 같이 journey에 이어 cat이라는 3번 레코드가 추가된 것을 볼 수 있다.

같은 트랜잭션 내인데도 다른 결과가 나오는 'phantom read' 현상이 발생하게 된 것이다.

 

---

 

💬 PHANTOM READ - innoDB에서는 정말 발생하지 않을까?

앞서 사용했던 innoDB 스토리지 엔진으로 생성된 'CREW' 테이블을 다시 한 번 가져오자.

위에서 진행했던 실험과 동일하게 샘플 레코드 삽입 후 SELECT FOR UPDATE를 진행해주자.

# 세션 2
START TRANSACTION;
SELECT * FROM CREW WHERE id >= 2 FOR UPDATE;

# 세션 1
START TRANSACTION;
INSERT INTO CREW(id, name) VALUES (3, 'cat'); # 삽입 대기

innoDB에서는 세션 2의 트랜잭션이 커밋되기 전까지 ⭐️ 넥스트 키락이 걸리기 때문에 삽입이 불가능하며, 타임아웃이 발생하게 된다.

 

# 세션 2
COMMIT;

타임아웃이 발생하기 전에 세션 2에서 커밋을 진행하게 되면 insert가 성공적으로 진행된다.

 

# 세션 1 - 이전에 작성하였던 insert query
START TRANSACTION;
INSERT INTO CREW(id, name) VALUES (3, 'cat');

# 이 트랜잭션에서 조회 진행
SELECT * FROM CREW;

# 세션 3 - 세션 1의 커밋 전에 조회 진행
SELECT * FROM CREW;

# 세션 1 커밋
COMMIT;

# 세션 3 재조회
SELECT * FROM CREW;

여기서 insert을 진행했던 트랜잭션에서 select를 작성하면 삽입이 된 모습을 볼 수 있다.

물론 commit 이전에는 다른 트랜잭션에서 값을 확인할 수 없기 때문에 commit 후에 확인이 가능하다.

 

---

 

💬 PHANTOM READ - SELECT FOR UPDATE

하지만, innoDB를 사용하더라도 select for update를 사용하게 되면 데이터의 부정합이 발생할 수 있다.

마찬가지로 샘플 레코드 삽입 후 아래와 같이 진행해주자.

 

# 세션 2
START TRANSACTION;
SELECT * FROM CREW WHERE id >= 1;

# 세션 1
START TRANSACTION;
UPDATE CREW SET name = 'hi' where id = 1;
COMMIT

# 세션 2 (기존의 트랜잭션에서 진행)
SELECT * FROM CREW WHERE id >= 1 FOR UPDATE;

그럼 위와 같이 처음에는 hello, journey가 조회되다가 insert가 진행되자 undo 영역의 데이터가 아닌 원본 테이블의 내용을 읽어 hi, journey가 조회되는 것을 확인할 수 있다.

 

---

 

🌱 SERIALIZABLE

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

가장 마지막으로 serializable에 대해서 알아보자.

 

# 세션 2
START TRANSACTION;
select * from CREW;

테이블에 아무것도 삽입하지 않은 상태에서, 세션 2가 조회 쿼리를 날렸다. 당연히 아무것도 조회되지 않는다.

# 세션 1
START TRANSACTION;
INSERT INTO CREW (id, name) VALUES (1, 'hello');

이후, 세션 1이 삽입 쿼리를 날리려고 하면 오른쪽과 같이 계속 대기 상태에 들어가는 것을 볼 수 있다. (쿼리 실행 16s)

 

세션 2가 커밋하지 않으면 계속 대기하다가 위와 같이 타임아웃이 발생한다.

즉, 세션 2는 단순히 조회 쿼리만 날렸는데도 세션 1이 삽입하지 못하기 때문에 읽기에 대해서도 락이 걸린다는 것을 확인할 수 있다.

 

---

 

이렇게 트랜잭션 격리 수준에 대해서 전부 테스트를 완료하였다.

한 번쯤 쿼리를 날려보며 직접 테스트해보고 싶던 내용이었는데, 이렇게 진행하니까 매우 재밌었다 😊

종종 공부하면서 다시 복습해야겠다.