[MySQL] 트랜잭션 격리 수준별 동시성 실험: 조회 결과와 잠금 차이

데이터베이스를 다루다 보면 한 트랜잭션이 데이터를 수정하는 동안 다른 트랜잭션에서는 무엇이 보여야 하는지 판단해야 할 때가 많습니다. 이때 가장 흔하게 사용되는 기준이 바로 트랜잭션 격리 수준(Transaction Isolation Level)입니다. 격리 수준을 높이면 항상 안전하고 낮추면 항상 빠르다고 생각하기 쉽지만, 실제 동작은 일반 SELECT인지 잠금 읽기인지, 트랜잭션 안에서 첫 읽기가 언제 실행됐는지에 따라 달라집니다.

이 글에서는 MySQL 8.4의 InnoDB를 기준으로 READ UNCOMMITTED부터 SERIALIZABLE까지 두 세션으로 재현하는 방법을 살펴보겠습니다. Dirty Read, Non-repeatable Read, Phantom Read가 어느 단계에서 보이는지뿐 아니라, 조회가 즉시 끝나는지 또는 잠금 대기 상태가 되는지까지 구분합니다.

1. 실험 전에 구분해야 할 두 가지 읽기

InnoDB의 SELECT를 이해할 때 가장 중요한 것은 모든 읽기가 같은 방식으로 동작하지 않는다는 점입니다.

• 일관된 읽기(Consistent Nonlocking Read): 일반 SELECT가 다중 버전 동시성 제어(MVCC)의 스냅샷을 읽습니다.
• 잠금 읽기(Locking Read): SELECT ... FOR UPDATE 또는 SELECT ... FOR SHARE가 현재 버전을 확인하고 필요한 레코드나 범위에 잠금을 설정합니다.

예를 들어 REPEATABLE READ에서 일반 SELECT는 트랜잭션의 스냅샷을 계속 읽을 수 있습니다. 반면 같은 조건에 FOR UPDATE를 붙이면 현재 데이터를 대상으로 잠금을 획득하므로 결과와 대기 여부가 달라질 수 있습니다. 따라서 격리 수준을 비교할 때 일반 SELECT와 잠금 읽기를 섞어서 해석하면 안 됩니다.

2. 실험 테이블과 두 세션 준비

다음 테이블은 잔액 변경과 범위 내 행 삽입을 함께 관찰하기 위한 최소 구성입니다. 반드시 InnoDB에서 수행해야 합니다.

DROP TABLE IF EXISTS isolation_lab;
CREATE TABLE isolation_lab (
    id BIGINT NOT NULL,
    group_id INT NOT NULL,
    amount INT NOT NULL,
    status VARCHAR(20) NOT NULL,
    PRIMARY KEY (id),
    KEY ix_group_id_id (group_id, id)
) ENGINE=InnoDB;

INSERT INTO isolation_lab (id, group_id, amount, status) VALUES
(100, 10, 1000, 'OPEN'),
(200, 10, 2000, 'OPEN'),
(300, 20, 3000, 'OPEN');

세션 A와 세션 B는 서로 다른 MySQL 연결이어야 합니다. 연결 하나에서 START TRANSACTION을 두 번 실행하는 방식으로는 동시성을 재현할 수 없습니다. 각 실험이 끝날 때 두 세션 모두 ROLLBACK하고 초기 데이터를 복원해야 이전 실험의 변경이 다음 결과에 섞이지 않습니다.

3. READ UNCOMMITTED: 커밋되지 않은 변경까지 읽을 수 있습니다

READ UNCOMMITTED에서는 일반 SELECT가 다른 트랜잭션의 미커밋 변경을 볼 수 있습니다. 이것이 Dirty Read입니다.

1. 세션 A에서 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED를 실행하고 트랜잭션을 시작합니다.
2. 세션 A에서 SELECT amount FROM isolation_lab WHERE id=100을 실행해 최초 값을 기록합니다.
3. 세션 B에서 트랜잭션을 시작한 뒤 UPDATE isolation_lab SET amount=9000 WHERE id=100을 실행하되 커밋하지 않습니다.
4. 세션 A에서 같은 SELECT를 다시 실행하고 결과를 기록합니다.
5. 세션 B에서 ROLLBACK한 뒤 세션 A에서 세 번째로 조회합니다.

두 번째 조회가 세션 B의 미커밋 값을 보여 주고, B의 롤백 후 다시 원래 값으로 돌아간다면 Dirty Read가 재현된 것입니다. 다만 일반 SELECT가 미커밋 버전을 읽을 수 있다는 설명을 UPDATE가 잠금 없이 통과한다는 뜻으로 해석하면 안 됩니다. 동일 행을 세션 A와 B가 동시에 UPDATE하면 배타 잠금(Exclusive Lock) 경합이 발생할 수 있습니다.

4. READ COMMITTED: 문장마다 새 스냅샷을 읽습니다

READ COMMITTED의 일반 SELECT는 커밋되지 않은 변경을 읽지 않습니다. 하지만 같은 트랜잭션에서도 SELECT 문장이 시작될 때마다 새로운 읽기 뷰(Read View)를 사용하므로, 중간에 다른 트랜잭션이 커밋하면 다음 SELECT 결과가 달라질 수 있습니다. 이것이 Non-repeatable Read입니다.

1. 세션 A의 격리 수준을 READ COMMITTED로 설정하고 트랜잭션을 시작한 뒤 id 100의 amount를 조회합니다.
2. 세션 B에서 id 100의 amount를 변경하고 COMMIT합니다.
3. 세션 A에서 같은 행을 다시 조회해 최초 결과와 비교합니다.
4. 세션 B에서 INSERT INTO isolation_lab VALUES (150,10,1500,'OPEN')을 실행하고 COMMIT합니다.
5. 세션 A에서 group_id=10 AND id BETWEEN 100 AND 200 조건을 다시 조회합니다.

READ COMMITTED에서는 두 번째 단건 조회에 B가 커밋한 값이 나타날 수 있고, 두 번째 범위 조회에는 새로 커밋된 행이 포함될 수 있습니다. 일반 SELECT만 사용한 이 실험에서는 읽기 자체가 B의 UPDATE나 INSERT를 막는 잠금을 설정하지 않는다는 점도 함께 확인해야 합니다.

5. REPEATABLE READ: 같은 스냅샷과 잠금 범위를 구분해야 합니다

MySQL InnoDB의 기본 격리 수준인 REPEATABLE READ에서는 일반적인 일관된 읽기가 같은 트랜잭션 안에서 동일한 스냅샷을 유지합니다. 첫 SELECT 이후 세션 B가 값을 변경하거나 범위 안에 행을 추가해 커밋하더라도, 세션 A가 같은 일반 SELECT를 반복하면 기존 스냅샷을 계속 볼 수 있습니다.

여기서 흔히 하는 실수는 REPEATABLE READ가 모든 형태의 Phantom Read를 언제나 같은 방식으로 차단한다고 단정하는 것입니다. 일반 SELECT는 MVCC 스냅샷으로 이전 결과를 유지합니다. 반면 FOR UPDATE 같은 잠금 읽기는 현재 버전을 읽으며 검색 조건과 인덱스 경로에 따라 레코드 잠금과 갭 잠금(Gap Lock), 넥스트키 잠금(Next-key Lock)을 사용할 수 있습니다.

1. 세션 A에서 REPEATABLE READ 트랜잭션을 시작하고 group_id 10의 범위를 일반 SELECT로 조회합니다.
2. 세션 B에서 범위 안에 id 150을 삽입하고 COMMIT합니다.
3. 세션 A에서 동일한 일반 SELECT를 반복해 결과를 기록합니다.
4. 실험을 초기화한 뒤 세션 A에서 같은 조건에 FOR UPDATE를 붙여 실행합니다.
5. 세션 B에서 다시 id 150 삽입을 시도하고 즉시 완료되는지, 잠금 대기하는지 확인합니다.

ix_group_id_id는 잠금 범위를 관찰하기 위해 의도적으로 둔 인덱스(Index)입니다. 적절한 인덱스가 없으면 실행 계획에서 더 넓은 레코드를 검사하고 잠글 수 있으므로, 격리 수준 차이로 보이는 현상이 실제로는 접근 경로 차이일 수 있습니다. 범위 조건과 인덱스 설계는 BETWEEN 연산자 가이드와 복합 인덱스 컬럼 순서 결정법을 함께 확인하는 것이 좋습니다.

6. SERIALIZABLE: 일반 SELECT도 잠금 대기가 발생할 수 있습니다

SERIALIZABLE은 동시 트랜잭션이 직렬로 실행된 것과 같은 결과를 목표로 하는 가장 강한 격리 수준입니다. InnoDB에서는 자동 커밋이 꺼진 명시적 트랜잭션의 일반 SELECT가 공유 잠금 읽기처럼 동작할 수 있으므로, 읽기와 쓰기 사이에도 대기가 발생할 수 있습니다.

1. 세션 A에서 SERIALIZABLE로 설정한 뒤 트랜잭션을 시작합니다.
2. 세션 A에서 group_id 10의 범위를 일반 SELECT로 조회하고 트랜잭션을 끝내지 않습니다.
3. 세션 B에서 해당 범위의 기존 행 UPDATE와 범위 내부 INSERT를 각각 시도합니다.
4. 세션 B가 실행 중인 상태에서 세 번째 관찰 세션으로 performance_schema.data_lock_waits를 조회합니다.
5. 세션 A를 COMMIT하거나 ROLLBACK한 후 B의 문장이 완료되는지 확인합니다.

잠금 대기를 관찰할 때는 오류가 발생할 때까지 기다릴 필요가 없습니다. 대기 상태를 확인한 뒤 차단 트랜잭션을 종료하면 됩니다. 운영 서버에서는 재현 목적으로 긴 트랜잭션을 유지하지 말아야 하며, 별도의 테스트 데이터베이스에서 수행하는 것이 더 안전합니다.

7. 격리 수준별 결과와 잠금 비교표

표를 채울 때는 단순히 값만 적지 말고 조회 시작 시각, 종료 시각, 즉시 완료 여부, 대기 시간, 세션 B의 COMMIT 또는 ROLLBACK 시점도 함께 기록해야 합니다. 특히 SERIALIZABLE 행은 SELECT 결과보다 문장이 어느 잠금에 의해 기다렸는지가 더 중요한 관찰값이 될 수 있습니다.

8. 잠금 대기를 확인하는 진단 SQL

SELECT
    waiting_engine_transaction_id,
    waiting_thread_id,
    blocking_engine_transaction_id,
    blocking_thread_id
FROM performance_schema.data_lock_waits;

SELECT
    engine_transaction_id,
    thread_id,
    object_schema,
    object_name,
    index_name,
    lock_type,
    lock_mode,
    lock_status,
    lock_data
FROM performance_schema.data_locks
WHERE object_name = 'isolation_lab'
ORDER BY thread_id, index_name, lock_data;

data_lock_waits는 기다리는 트랜잭션과 차단하는 트랜잭션의 관계를 보여 주며, data_locks는 인덱스 이름, 잠금 모드, 승인 여부를 확인하는 데 사용합니다. 잠금 범위를 해석하기 전에는 세션 A의 조회가 어떤 인덱스를 사용했는지 EXPLAIN으로 확인해야 합니다. 실행 계획의 세부 접근 조건은 EXPLAIN FORMAT=JSON 해석법에서 확인할 수 있습니다.

9. 실험 결과가 예상과 다를 때 확인할 항목

1. 격리 수준 적용 시점: 활성 트랜잭션을 시작하기 전에 세션 격리 수준을 설정했는지 확인합니다.
2. 자동 커밋: @@autocommit과 명시적 START TRANSACTION 사용 여부를 기록합니다. SERIALIZABLE의 일반 SELECT는 자동 커밋 상태에 따라 관찰 조건이 달라질 수 있습니다.
3. 첫 스냅샷 생성 시점: REPEATABLE READ에서는 첫 일관된 읽기가 언제 실행됐는지가 중요합니다. 필요하면 START TRANSACTION WITH CONSISTENT SNAPSHOT으로 시점을 명확하게 만듭니다.
4. 읽기 종류: 일반 SELECT, FOR SHARE, FOR UPDATE를 구분합니다. 세 문장을 같은 종류의 읽기처럼 비교하면 결과를 잘못 해석하게 됩니다.
5. 인덱스와 조건: 등가 검색인지 범위 검색(Range Scan)인지, 사용 인덱스가 무엇인지 확인합니다. 갭 잠금은 추상적인 WHERE 문장이 아니라 실제 인덱스 접근 범위와 관련됩니다.
6. 실험 초기화: 각 격리 수준이 끝난 뒤 두 세션을 ROLLBACK하고 테스트 행을 원래 상태로 복원합니다.
7. 대기 제한: 테스트 세션의 innodb_lock_wait_timeout을 확인하고, 타임아웃과 즉시 성공을 구분해 기록합니다.

10. 어떤 격리 수준을 선택해야 할까?

Dirty Read를 허용할 명확한 이유가 없다면 READ UNCOMMITTED를 일반 업무 트랜잭션의 기본 선택으로 삼기 어렵습니다. 최신 커밋 데이터를 문장 단위로 읽고 긴 스냅샷 유지의 영향을 줄이려면 READ COMMITTED를 검토할 수 있지만, 같은 트랜잭션의 반복 조회 결과가 달라질 수 있음을 애플리케이션이 처리해야 합니다.

REPEATABLE READ는 반복 조회의 일관성과 InnoDB의 잠금 동작을 함께 활용할 수 있지만, 장시간 트랜잭션이 오래된 버전을 계속 필요로 하면 정리되지 못한 언두(Undo)가 누적될 수 있습니다. SERIALIZABLE은 가장 강한 격리를 제공하는 반면 읽기와 쓰기의 경합이 커질 수 있으므로, 단순히 가장 안전해 보인다는 이유로 전체 서비스에 적용하기보다 충돌 허용 여부와 처리량 요구를 먼저 판단해야 합니다.

결국 격리 수준은 이름만으로 선택할 수 없습니다. 일반 SELECT의 스냅샷, 잠금 읽기의 현재 버전 접근, 실제 인덱스 범위, 트랜잭션 유지 시간을 함께 확인해야 합니다. 두 세션 타임라인과 잠금 대기 기록을 남기면 Dirty Read부터 Phantom Read까지의 차이를 추측이 아니라 재현 가능한 근거로 판단할 수 있습니다.