[MySQL] 격리성 수준에 따른 MVCC

Real MySQL 8.0 - 4.2.3 MVCC에서 "MVCC가 격리 수준에 따라 동작하는 방식이 다를 수 있다"라고 하는데
직접 실습을 통해 더 알아보고 싶어서 작성하게 되었다.  

---

MVCC(Multi-Version Concurrency Control)

데이터베이스가 동시성을 제어하기 위해 사용하는 방법 중 하나로, 여러 트랜잭션이 동시에 데이터베이스에 접근할 때 발생할 수 있는 갱신 충돌을 방지하고 데이터 일관성을 유지하는데 도움을 준다.

• MySQL의 InnoDB에서는 Undo log를 활용하여 MVCC를 구현한다. 
  
• 하나의 레코드에 대해 `여러 개의 버전(Multi Version)`이 동시에 관리된다.

 

트랜잭션의 격리 수준

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

 

https://escapefromcoding.tistory.com/725

• `READ UNCOMMITTED` ➡️ `READ COMMITTED` ➡️ `REPEATABLE READ` ➡️ `SERIALIZABLE`로 갈수록 트랜잭션 간 고립 정도가 높아지며, 성능이 떨어지는 것이 일반적이다.

READ UNCOMMITTED

각 트랜잭션에서의 변경 내용이 `COMMIT`이나 `ROLLBACK` 여부에 상관없이 다른 트랜잭션에 보인다.

READ COMMITTED

어떤 트랜잭션에서 데이터를 변경했더라도 `COMMIT`이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있다.

REPEATABLE READ

자신의 트랜잭션이 시작되기 이전에 `COMMIT`된 내용에 대해서만 조회가 가능하다.

 

사실 위의 글만 읽어봤을 때 `READ COMMITTED`과 `REPEATABLE READ` 둘 다 `COMMIT`이 완료된 데이터만 조회가 가능하다라는 건 알겠는데 차이를 정확히 잘 모르겠다. 그래서 예제를 통해 정리해 보았다!

 

READ COMMITTED vs REPEATABLE READ

💡 격리성 수준 테스트를 위한 설정(AUTOCOMMIT OFF, 격리성 레벨 설정)을 해주어야 하는데, 본 예제에서 설명하므로 이 예제에서는 생략한다.

• `book` 스키마와 `programming`이라는 테이블을 만든 뒤, 데이터를 넣는다.

CREATE TABLE programming (
	id INT PRIMARY KEY,
    title VARCHAR(255),
    price INT
);

INSERT INTO programming VALUES (1, 'Real MySQL 8.0 1권', 27000);

 

• 다음 순서대로 테스트를 진행했을 때, `READ COMMITTED`과 `REPEATABLE READ` 각각 어떤 결과가 나오는지 알아볼 것이다.
  • i. 트랜잭션 A가 시작된 후, `price`를 확인한다.
  • ii. 동시에 트랜잭션 B가 `price` 값을 변경한다.
  • iii. 다시 트랜잭션 A가 `price`를 확인한다.

1. READ COMMITTED

• i. 트랜잭션 A

START TRANSACTION;
SELECT * FROM programming WHERE id = 1;

• ii. 트랜잭션 B

UPDATE programming SET price = 30000 WHERE id = 1;
COMMIT;

• iii. 트랜잭션 A

SELECT * FROM programming WHERE id = 1;

➡️ `price`가 `30000`으로 변경되었다.

 

2. REPEATABLE READ

• i. ~ iii. 과정을 동일하게 진행했을 때, 다음과 같은 데이터를 확인할 수 있다.

 

➡️ `price`가 여전히 `27000`인 것을 확인할 수 있다.

📌 정리 
- `READ COMMITTED`는 다른 트랜잭션의 커밋된 변경 사항을 반영함
- `REPEATABLE READ`는 동일한 트랜잭션 내에서의 일관성을 유지하면서, 다른 트랜잭션에 의한 변경 사항은 반영하지 않음 ➡️ 즉 타 트랜잭션의 변경 사항인 `price` 값을 반영하지 않음

SERIALIZABLE

가장 엄격한 격리 수준으로, 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없다.

 

 

트랜잭션의 격리 수준에 따른 MVCC 적용 테스트

💡 트랜잭션 격리 수준을 테스트하기 위해서는 AUTOCOMMIT 옵션을 꺼야 한다!

테스트 세팅

MySQL Workbench에서 스키마를 생성한 뒤, 다음 코드를 실행하여 `member` 테이블 생성 및 데이터 추가를 진행한다.

CREATE TABLE member (
	m_id INT NOT NULL,
	m_name VARCHAR(20) NOT NULL,
	m_area VARCHAR(100) NOT NULL,
	PRIMARY KEY (m_id),
	INDEX ix_area (m_area)
);

INSERT INTO member (m_id, m_name, m_area) VALUES (12, '홍길동', '서울');

SELECT @@AUTOCOMMIT;
SET AUTOCOMMIT = FALSE;

테스트 전에 `AUTOCOMMIT`옵션 값을 확인해서 `1`이면 `FALSE` 값으로 변경하여 `0`이 출력되도록 바꿔준다

 

READ UNCOMMITTED

• 첫 번째 세션

-- 격리 수준 READ UNCOMMITTED로 설정
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;

-- 격리 수준 확인용
SHOW VARIABLES LIKE 'transaction_isolation';

SELECT * FROM member WHERE m_id = 1; -- 인천

 

 

SELECT문 실행 결과

• 두 번째 세션

세션 시작 전에 `AUTOCOMMIT` 값이 `OFF(0)`가 되도록 설정하기!

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SHOW VARIABLES LIKE 'transaction_isolation';

update member set m_area = '서울' WHERE m_id = 1;
SELECT * FROM member WHERE m_id = 1;

첫 번째 세션에 설정한 격리 수준과 동일하게 설정한 뒤, `m_id = 1`의 레코드의 `m_area` 값을 `'서울'`로 변경한다.  

SELECT문 실행 결과

• 첫 번째 세션

SELECT * FROM member WHERE m_id = 1; -- 서울

다시 첫 번째 세션으로 돌아와서 SELECT문을 실행해 보면 `서울`로 값이 변경된 것을 확인할 수 있다

SELECT문 실행 결과

 

READ COMMITTED

• 첫 번째 세션

-- 격리 수준 READ COMMITTED로 설정
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;

-- 격리 수준 확인용
SHOW VARIABLES LIKE 'transaction_isolation';

SELECT * FROM member WHERE m_id = 1; -- 인천

 

SELECT문 실행 결과

• 두 번째 세션

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SHOW VARIABLES LIKE 'transaction_isolation';

update member set m_area = '서울' WHERE m_id = 1;
SELECT * FROM member WHERE m_id = 1;

SELECT문 실행 결과

• 첫 번째 세션

SELECT * FROM member WHERE m_id = 1; -- 인천

다시 첫 번째 세션으로 돌아와서 SELECT문을 실행해보면 여전히 `인천`이 출력되는 것을 볼 수 있다.

SELECT문 실행 결과

REPEATABLE READ

-- 격리 수준 REPEATABLE READ로 설정
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;

-- 격리 수준 확인용
SHOW VARIABLES LIKE 'transaction_isolation';

SELECT * FROM member WHERE m_id = 1; -- 인천

 

SELECT문 실행 결과

• 두 번째 세션

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SHOW VARIABLES LIKE 'transaction_isolation';

update member set m_area = '서울' WHERE m_id = 1;
SELECT * FROM member WHERE m_id = 1;

SELECT문 실행 결과

• 첫 번째 세션

SELECT * FROM member WHERE m_id = 1; -- 인천

다시 첫 번째 세션으로 돌아와서 SELECT문을 실행해보면 여전히 `인천`이 출력되는 것을 볼 수 있다.

SELECT문 실행 결과

 

SERIALIZABLE

-- 격리 수준 SERIALIZABLE로 설정
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;

-- 격리 수준 확인용
SHOW VARIABLES LIKE 'transaction_isolation';

SELECT * FROM member WHERE m_id = 1; -- 인천

SELECT문 실행 결과

• 두 번째 세션

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SHOW VARIABLES LIKE 'transaction_isolation';

update member set m_area = '서울' WHERE m_id = 1;
SELECT * FROM member WHERE m_id = 1;

SELECT문 실행 결과

• 첫 번째 세션

SELECT * FROM member WHERE m_id = 1; -- 인천

다시 첫 번째 세션으로 돌아와서 SELECT문을 실행해보면 여전히 `인천`이 출력되는 것을 볼 수 있다.

SELECT문 실행 결과

정리

• `READ UNCOMMITTED` 격리 수준에서는 커밋 여부와 관계없이 변경된 상태의 데이터를 반환한다.
• `READ COMMITTED` 이상의 격리 수준에서는 변경되기 이전의 내용을 보관하고 있는 Undo 영역의 데이터를 반환한다.
  
  • 추가적으로, `COMMIT 후`에는 `READ COMMITTED`도 변경된 상태의 데이터를 반환한다.

 

마무리

책을 단순히 읽기만 했을 때와 이렇게 코드를 직접 작성하고 실행시켰을 때의 이해도가 확실히 차이나는 것 같다. 실습을 통해 격리 수준 설정법 등 조작법도 익히고 `READ COMMITTED`과 `REPEATABLE READ`의 차이점에 대해 조금 더 깊게 알아볼 수 있어서 좋았다.

 

Ref

Real MySQL 8.0 - 4장 아키텍처

Real MySQL 8.0 - 5장 트랜잭션과 잠금

https://mangkyu.tistory.com/53

https://mangkyu.tistory.com/288