[MySQL] MySQL에 적용된 격리 수준 - InnoDB

 

 

 

📖 개요

---

잠금(Lock)은 동시성 제어를 위한 중요한 기능이고 트랜잭션(Transaction)은 작업의 원자성을 보장해주는 것이 주요 속성입니다. 여기서 격리 수준(Isolation Level)은 하나의 트랜잭션 내부 또는 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지를 결정하는 레벨을 의미합니다.

MySQL에서 사용하는 스토리지 엔진, InnoDB에서 격리 수준에 따라 트랜잭션이 어떻게 동작하는지 살펴보려고 합니다.

 

동작 방식을 살펴보기전에 격리 수준에 대한 이해가 부족하다면 아래 글을 참조하시면 도움이 될 수 있습니다.

[Database] RDBMS의 격리 수준 (Isolation Level)

 

InnoDB는 기본적으로 REPEATABLE READ가 적용되어 있습니다. SQL 표준에서는 REPEATABLE READ에서 팬텀 리드가 발생할 수 있다고 설명하지만, InnoDB는 넥스트 키 락(Next-Key Lock) 메커니즘을 통해 이를 방지하고 있습니다. 이 글에서는 넥스트 키 락의 개념과 함께, 각 격리 수준별 동작 방식에 대한 이해를 돕기 위해 간단한 실습 예제를 다룰 예정입니다. 이를 통해 InnoDB의 격리 수준을 더욱 명확하게 이해하는 것을 목표로 합니다.

 

 

목차

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE
• 마치며

 

 

실습 환경

• MySQL 8.0.32
• 서로 다른 트랜잭션의 동작을 확인하기 위해 mysql 클라이언트 세션 3개 생성

• 실습용 데이터베이스 & 테이블 생성

-- 테스트 데이터베이스 생성
CREATE DATABASE test;

-- 테스트 테이블 생성 
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    name VARCHAR(10) NOT NULL
);

 

 

 

🟢 READ UNCOMMITTED

---

각 트랜잭션에서의 변경 내용을 COMMIT 이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 조회할 수 있는 격리 수준입니다.

 

1. 트랜잭션 A를 시작합니다.
2. 트랜잭션 A에서 users 테이블에 id=1, name=’ian’ 레코드를 삽입합니다.
3. 트랜잭션 B를 시작합니다.
4. 트랜잭션 B에서 users 테이블에서 id=1인 레코드를 조회합니다.
   • 아직 커밋되지 않은 데이터라도 격리 수준이 READ UNCOMMITTED 이기 때문에 id=1인 유저 레코드가 반환됩니다.
5. 트랜잭션 A에서 예외가 발생해, 롤백을 수행합니다.
   • 삽입했던 id=1인 유저 레코드가 삭제됩니다.

위 예시처럼, 어떤 트랜잭션에서 커밋되지 않은 변경사항을 다른 트랜잭션에서 조회할 수 있는 격리 수준입니다. 롤백 발생 후 id=1인 유저 데이터는 없어야될 데이터이지만, 트랜잭션 B 입장에서 존재하는 유저로 인지하게 됩니다. 이러한 문제를 Dirty Read 라고 합니다.

예시에서는 트랜잭션 A가 하나의 쿼리만 실행했지만, 한 트랜잭션 내에서 더 많은 쿼리가 수행될 수 있고 해당 변경 사항을 다른 트랜잭션에서 커밋 전에 읽는다면 치명적인 정합성 문제가 발생할 수 있습니다.

 

 

🧪 실습

START TRANSACTION;
INSERT INTO users (id, name) VALUES (1, 'ian');

• 트랜잭션 A(왼쪽)를 시작하고 {id=1, name='ian'} 인 레코드를 삽입합니다.

 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT * FROM users WHERE id = 1;

• 트랜잭션 B(오른쪽)을 시작하기 전에 격리 수준을 READ UNCOMMITTED로 설정합니다.
  • InnoDB의 기본 격리 수준은 REPEATABLE READ이기 때문에 따로 설정이 필요합니다.
• 트랜잭션 B(오른쪽)를 시작하고 users 테이블에서 id=1 인 레코드를 조회합니다.
  • 아직 커밋되지 않았지만 조회가 되는 것을 확인할 수 있습니다.

 

ROLLBACK;
SELECT * FROM users WHERE id = 1;

• 트랜잭션 A(왼쪽)에서 명시적으로 롤백을 수행하고 users 테이블에서 id=1인 레코드를 조회합니다.
  • 롤백으로 인해 트랜잭션 A가 삽입했던 유저 레코드가 없어졌음을 확인할 수 있습니다.
• 결과적으로 트랜잭션 B에서 Dirty Read 가 발생했습니다.

 

 

 

🔵 READ COMMITTED

---

어떤 트랜잭션에서 데이터를 변경했더라도 커밋이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있는 격리 수준입니다.

 

1. users 테이블에 id=1, name=’ian’ 레코드가 존재합니다.
2. 트랜잭션 A를 시작합니다.
3. 트랜잭션 A에서 users 테이블에 있는 id=1인 레코드를 name=’patrick’으로 업데이트 합니다.
   • 테이블에 변경사항이 반영되고, 언두 로그에 업데이터 이전 데이터가 기록됩니다.
4. 트랜잭션 B를 시작합니다.
5. 트랜잭션 B에서 users 테이블에 있는 id=1인 레코드를 조회합니다.
   • 적용된 격리 수준이 READ COMMITTED이고, 트랜잭션 A가 커밋되지 않았기 때문에 언두 로그를 우선적으로 바라보고 데이터가 반환됩니다.
6. 트랜잭션 A를 커밋합니다.

 

위 예시처럼, 어떤 트랜잭션에서 커밋된 변경사항만 다른 트랜잭션에서 조회할 수 있는 격리 수준입니다. 트랜잭션 A가 id=1인 레코드를 업데이트한 후 테이블에는 최신 변경 사항이, 언두 로그에는 이전 버전이 기록됩니다.

트랜잭션 A가 커밋되지 않은 상태에서 트랜잭션 B가 해당 레코드를 읽으려고 할 땐 언두 로그를 바라보게 됨으로써 이전 버전을 읽게 되고 Dirty Read 문제가 방지되는 것을 볼 수 있습니다. 예시는 레코드의 UPDATE 상황이지만, INSERT, DELETE도 마찬가지로 언두 로그에 이전 버전이 기록될 수 있고 Dirty Read 를 방지합니다.

 

격리 수준 READ COMMITTED에서 Dirty Read 를 방지하는 것을 확인했는데, 이번엔 Non-Repeatable Read 문제 발생 예시를 보겠습니다.

 

1. users 테이블에 id=1, name=’ian’ 레코드가 존재합니다.
2. 트랜잭션 A를 시작합니다.
3. 트랜잭션 A에서 users 테이블에 있는 name=’ian’인 레코드를 조회합니다.
   • name=’ian’인 레코드 한 건이 반환됩니다.
4. 트랜잭션 B를 시작합니다.
5. 트랜잭션 B에서 users 테이블에 있는 name=’ian'인 레코드를 name=’patrick’으로 업데이트 합니다.
6. 트랜잭션 B를 커밋합니다.
7. 트랜잭션 A에서 아까 조회했던 users 테이블에 있는 name=’ian’인 레코드를 다시 조회합니다.
   • 격리 수준이 READ COMMITTED 이기 때문에 레코드의 커밋된 버전만 읽게되고 name=’ian’ 매칭되는 레코드가 없으므로 반환 결과가 없습니다.

 

READ COMMITTED에서는 Dirty Read를 방지할 수 있으나, 한 트랜잭션 내에서 같은 쿼리가 일관된 결과를 보장해줄 수는 없습니다. 해당 문제를 Non-Repeatable Read라고 하며, 이 또한 정합성 문제를 일으킬 수 있습니다.

 

 

🧪 실습

users 테이블에 id=1, name=’ian’ 레코드 하나를 INSERT하고 격리 수준 READ COMMITTED에서 문제가 발생하는 경우에 대해서만 실습을 진행해보겠습니다.

 

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT * FROM users WHERE name = 'ian';

• 트랜잭션 A(왼쪽)을 시작하기 전에 격리 수준을 READ COMMITTED로 설정합니다.
  • InnoDB의 기본 격리 수준은 REPEATABLE READ이기 때문에 따로 설정이 필요합니다.
• 트랜잭션 A(왼쪽)를 시작하고 users 테이블에서 name=’ian’ 인 레코드를 조회합니다.
  • 존재하던 레코드 1건이 조회되는 것을 볼 수 있습니다.

 

START TRANSACTION;
UPDATE users SET name = 'patrick' WHERE name = 'ian';
COMMIT;

• 트랜잭션 B(오른쪽)를 시작하고 users 테이블에서 name=’ian’ 인 레코드를 name=’patrick’으로 업데이트 합니다.
• 트랜잭션 B(오른쪽)를 커밋합니다.

 

SELECT * FROM users WHERE name = 'ian';

• 트랜잭션 A(왼쪽)에서 처음 조회했던 users 테이블의 name=’ian’인 레코드를 조회합니다.
  • 아직 트랜잭션을 끝내지 않은 채로 같은 조회 쿼리를 수행했으나 트랜잭션 B(오른쪽)가 실행시킨 커밋 내역으로 인해서 조회되는 결과가 없는 것을 확인할 수 있습니다.

• 결과적으로 트랜잭션 A에서 Non-Repeatable Read 가 발생했습니다.

 

 

 

🟣 REPEATABLE READ

---

동일한 트랜잭션 내에서 동일한 SELECT 쿼리를 반복해서 수행했을 때 항상 같은 결과를 보장하는 격리 수준입니다.

 

1. users 테이블에 id=1, name=’ian’ 레코드가 존재합니다.
2. 트랜잭션 A를 시작합니다.
   • TRX ID는 15 입니다.
3. 트랜잭션 A에서 users 테이블에 있는 name=’ian’인 레코드를 조회합니다.
   • name=’ian’인 레코드 한 건이 반환됩니다.
4. 트랜잭션 B를 시작합니다.
   • TRX ID는 17 입니다.
5. 트랜잭션 B에서 users 테이블에 있는 name=’ian'인 레코드를 name=’patrick’으로 업데이트 합니다.
6. 트랜잭션 B를 커밋합니다.
7. 트랜잭션 A에서 아까 조회했던 users 테이블에 있는 name=’ian’인 레코드를 다시 조회합니다.
   • 격리 수준이 REPEATABLE READ이기 때문에 트랜잭션 A의 TRX ID는 15이고, 이후 트랙잭션 (TRX ID 16 이상)들의 업데이트 사항들은 커밋됐다고 해도 언두 로그를 우선으로 바라보게 됩니다.
   • 결과적으로 TRX ID 17 이 변경하고 커밋한 사항은 TRX ID 15인 트랜잭션 A는 알지 못하고 언두 로그를 통해 시작 시점과 같은 상태의 레코드를 정상적으로 1건 조회합니다.
     • 마치 트랜잭션 시작 시점에 스냅샷을 찍어놓고 사용하는 듯한 동작을 합니다.

 

격리 수준 REPEATABLE READ에서는 트랜잭션 시작 시점에 따라서 MVCC(Multi-Version Concurrency Control) 메커니즘으로 언두 로그를 활용할 수 있습니다. 덕분에 Non-Repeatable Read 문제를 방지할 수 있게 됩니다.

SQL 표준에 기술된 격리 수준을 보면, REPEATABLE READ에서 Phantom Read가 발생할 수 있다고 하지만, 정확히 어떻게 동작하기 때문에 Phantom Read가 발생하는지는 명시되어 있지 않습니다. 그리고 InnoDB에서는 넥스트 키 락 이라는 메커니즘으로 REPEATABLE READ에서도 Phantom Read를 방지하고 있습니다. 덕분에 기본 설정된 격리 수준만으로 Dirty Read, Non-Repeatable Read, Phantom Read를 방지할 수 있는 능력을 갖춘 셈이겠네요.

 

이번엔 Phantom Read를 차단하는 시나리오에 대해서 간단히 보겠습니다.

 

해당 시나리오는 언두 로그를 활용하는 MVCC 메커니즘이 있지만, 넥스트 키 락이 없을 때 동작입니다.

1. users 테이블에 {id=1, name=’ian’}, {id=2, name=’bob’} 레코드가 존재합니다.
2. 트랜잭션 A를 시작합니다.
   • TRX ID는 15 입니다.
3. 트랜잭션 A에서 users 테이블에 있는 1 이상의 id를 가지는 레코드를 조회합니다.
   • FOR UPDATE 키워드를 통해 비관적 락을 사용했기 때문에, 언두 로그를 바라볼 수 없습니다.
   • 레코드 두 건이 반환됩니다.
4. 트랜잭션 B를 시작합니다.
   • TRX ID는 17 입니다.
5. 트랜잭션 B에서 users 테이블에 id=3, name=’json’인 레코드를 삽입합니다.
   • 테이블에 새 레코드가 추가되고 롤백 & MVCC 동작을 위해 언두 로그에 기록됩니다.
6. 트랜잭션 B를 커밋합니다.
7. 트랜잭션 A에서 users 테이블에 있는 1 이상의 id를 가지는 레코드를 다시 조회합니다.
   • FOR UPDATE 키워드를 통해 비관적 락을 사용했기 때문에, 언두 로그를 바라볼 수 없기 때문에 트랜잭션 B에 의해 삽입되고 커밋된 id=3인 레코드까지 포함하여 총 3건의 레코드가 반환됩니다.

언두 로그를 확인하면 트랜잭션 A의 시작 시점 기준으로 id=3인 레코드는 이후에 삽입되고 커밋된 데이터니까 필터링해서 잘 조회할 수 있을 것 같은데, 어째서 조회가 되어서 Phantom Read를 발생시킬까요?

FOR UPDATE 를 사용하면 SELECT 임에도 바로 락을 획득합니다. 위에서는 간단히 시나리오를 구성했지만, FOR UPDATE를 사용했다는 것은 실제로 레코드 UPDATE를 위해서 SELECT를 한 것이고 UPDATE를 위해 읽는 데이터가 이전 버전으로 조회된다면 예기치 못한 값으로 업데이트가 발생할 수 있습니다. 그러므로, 락과 관련된 레코드에 한해서는 언두 로그를 바라볼 수 없게 되고 위와 같이 Phantom Read가 발생하게 됩니다.

이번엔 실제 InnoDB에서 넥스트 키 락이 적용되어 있다고 했을 때 같은 시나리오를 보겠습니다.

 

해당 시나리오에서는 넥스트 키 락이 동작합니다.

1. users 테이블에 {id=1, name=’ian’}, {id=2, name=’bob’} 레코드가 존재합니다.
2. 트랜잭션 A를 시작합니다.
   • TRX ID는 15 입니다.
3. 트랜잭션 A에서 users 테이블에 있는 1 이상의 id를 가지는 레코드를 조회합니다.
   • FOR UPDATE 키워드를 통해 비관적 락을 사용했기 때문에, 언두 로그를 바라볼 수 없고 관련 레코드에 넥스트 키 락(레코드 락 + 갭 락)이 적용됩니다.
   • 레코드 두 건이 반환됩니다.
4. 트랜잭션 B를 시작합니다.
   • TRX ID는 17 입니다.
5. 트랜잭션 B에서 users 테이블에 id=3, name=’json’인 레코드를 삽입합니다.
   • 해당 테이블에 id=3은 갭 락이 해제되어야 삽입 가능하므로 트랜잭션 B는 대기상태가 됩니다.
6. 트랜잭션 A에서 users 테이블에 있는 1 이상의 id를 가지는 레코드를 다시 조회합니다.
   • 처음 결과가 마찬가지로, 2건의 레코드가 반환됩니다.

위 시나리오가 실제 MySQL에서 사용하는 스토리지 엔진인 InnoDB의 동작입니다. 격리 수준의 기본값이 REPEATABLE READ이고 넥스트 키 락으로 Phantom Read를 완전히 차단합니다.

넥스트 키 락은 레코드 락과 갭 락을 합쳐놓은 형태를 의미합니다. 예시에서 나온 레코드 두 개에 레코드 락을 걸고 이후 들어올 값들에 대한 갭 락을 걸어서 팬텀이 생기는 현상을 완전히 차단한 것 입니다. 이처럼 기본 격리 수준만으로 Dirty Read, Non-Repeatable Read, Phantom Read를 방지해주는 안전한 놈이라고 볼 수 있습니다.

 

 

🧪 실습

users 테이블에 {id=1, name=’ian’}, {id=2, name=’bob’} 레코드 두 개를 INSERT 하고 기본 격리 수준인 REPEATABLE READ에서 넥스트 락 메커니즘이 어떻게 동작하는지, 락 획득은 어떻게 하는지 실습을 진행해보겠습니다.

 

START TRANSACTION;
SELECT * FROM users WHERE id >= 1 FOR UPDATE;

• 트랜잭션 A(왼쪽 상단)을 시작하고 users 테이블에서 1이상의 id를 가진 레코드를 모두 조회합니다.
  • FOR UPDATE를 통해 비관적 락을 명시합니다.

 

SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA, LOCK_STATUS, ENGINE_TRANSACTION_ID
FROM performance_schema.data_locks;

• 트랜잭션 A가 비관적 락을 획득한 이후 락 현황을 조회합니다.
  • users 테이블에 인텐션 배타 락과 존재하는 레코드 2개에 대한 레코드 락 그리고 LOCK_DATA 칼럼에 supremum pseudo-record 라는 표시로 갭 락을 잡고 있음을 확인할 수 있습니다.

 

START TRANSACTION;
INSERT INTO users (id, name) VALUES (3, 'json');

• 트랜잭션 B(오른쪽 상단)을 시작하고 users 테이블에 id=3, name=’json’인 레코드를 삽입합니다.
  • 트랜잭션 A가 획득한 갭 락으로인해 트랜잭션 B의 INSERT가 대기 상태가 되고, 설정된 타임아웃 값이 지나자 트랜잭션 재실행할 것을 요구합니다.

 

 

 

🔴 SERIALIZABLE

---

모든 트랜잭션이 마치 순차적으로 실행되는 것 같은 동작을 보장하는 격리 수준입니다.

 

1. users 테이블에 {id=1, name=’ian’}, {id=2, name=’bob’} 레코드가 존재합니다.
2. 트랜잭션 A를 시작합니다.
   • TRX ID는 15 입니다.
3. 트랜잭션 A에서 users 테이블에 있는 1 이상의 id를 가지는 레코드를 조회합니다.
   • 격리 수준이 SERIALIZABLE에서는 SELECT 처리 관련 레코드(넥스트 키 락 포함)에 공유 락이 적용됩니다.
   • 레코드 두 건이 반환됩니다.
4. 트랜잭션 B를 시작합니다.
   • TRX ID는 17 입니다.
5. 트랜잭션 B에서 users 테이블에 id=3, name=’json’인 레코드를 삽입합니다.
   • 해당 테이블에 id=3은 갭 락이 해제되어야 삽입 가능하므로 트랜잭션 B는 대기상태가 됩니다.
6. 트랜잭션 A에서 users 테이블에 있는 1 이상의 id를 가지는 레코드를 다시 조회합니다.
   • 처음 결과가 마찬가지로, 2건의 레코드가 반환됩니다.

 

 

🧪 실습

users 테이블에 {id=1, name=’ian’}, {id=2, name=’bob’} 레코드 두 개를 INSERT 하고 SERIALIZABLE에서 락을 어떻게 획득하여 순차적인 처리처럼 보이게 하는지 실습해보겠습니다.

 

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM users WHERE id >= 1;

• 트랜잭션 A(왼쪽)을 시작하기 전에 격리 수준을 SERIALIZABLE로 설정합니다.
  • InnoDB의 기본 격리 수준은 REPEATABLE READ이기 때문에 따로 설정이 필요합니다.
• 트랜잭션 A(왼쪽)를 시작하고 users 테이블에서 1이상의 id를 가진 레코드를 모두 조회합니다.
  • 격리 수준을 SERIALIZABLE로 설정했기 때문에 명시적으로 락 획득을 하지 않아도 SELECT에서 공유 락을 획득합니다.

 

SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA, LOCK_STATUS, ENGINE_TRANSACTION_ID
FROM performance_schema.data_locks;

• 트랜잭션 A가 SELECT 한 이후 락 현황을 조회합니다.
  • users 테이블에 인테션 공유 락과 존재하는 레코드 2개에 대한 레코드 락 그리고 LOCK_DATA 칼럼에 supremum pseudo-record 라는 표시로 갭 락을 잡고 있음을 확인할 수 있습니다.

 

START TRANSACTION;
INSERT INTO users (id, name) VALUES (3, 'json');

• 트랜잭션 B(오른쪽 상단)을 시작하고 users 테이블에 id=3, name=’json’인 레코드를 삽입합니다.
  • 트랜잭션 A가 획득한 갭 락으로인해 트랜잭션 B의 INSERT가 대기 상태가 되고, 설정된 타임아웃 값이 지나자 트랜잭션 재실행할 것을 요구합니다.

 

 

 

👋🏻 마치며

---

MySQL에서 사용하는 스토리지 엔진, InnoDB의 격리 수준에 따른 동작들을 자세히 살펴보았습니다.

처음 격리 수준의 개념을 접했을 땐, 표준 내용을 기반으로 대입해서 생각했기 때문에 InnoDB의 동작 이해를 하지 못했었습니다. 표준이라는게 반드시 따를 필요 없는 것이고 인터페이스 같은 역할이기 때문에 실제 RDBMS의 동작은 표준을 기반으로 하되, 구현체(실제 제품)에 따라 조금씩 다를 수 있고 더 훌륭한 처리를 할 수 있다는 것을 알게 되었습니다.

 

읽어주셔서 감사합니다. 궁금한 점에 대한 질문 환영합니다!

부족한, 잘못된 설명이 있다면 알려주시면 감사하겠습니다 🙏🏻