본문 바로가기
데이터베이스

[트랜잭션 시리즈 - 2] 트랜잭션 격리 수준별 대표적인 트러블 케이스 정리

by 시니성 2025. 3. 24.

1. Dirty Read (더티 리드)

발생하는 격리 수준: READ UNCOMMITTED

READ UNCOMMITTED는 가장 낮은 격리 수준으로, 다른 트랜잭션에서 커밋되지 않은 데이터를 읽을 수 있습니다.

발생 원인

  • READ UNCOMMITTED 격리 수준에서는 트랜잭션이 다른 트랜잭션의 커밋되지 않은 변경사항을 볼 수 있습니다.
  • 이 격리 수준에서는 데이터 읽기 작업에 어떠한 잠금(lock)도 획득하지 않습니다.
  • 다른 트랜잭션의 변경사항이 커밋되기 전에도 변경된 데이터를 읽을 수 있습니다.

은행 계좌 이체 예시 분석

  1. 철수의 트랜잭션이 계좌 잔액에서 50만원을 차감하고 아직 커밋하지 않았습니다.
  2. 영희의 트랜잭션이 READ UNCOMMITTED 격리 수준에서 실행 중이므로, 철수의 트랜잭션이 아직 커밋되지 않았지만 변경된 데이터(차감된 잔액)를 볼 수 있습니다.
  3. 철수의 트랜잭션이 롤백되면, 영희가 읽은 데이터는 실제로 존재하지 않는(더티) 데이터가 됩니다.

은행 계좌 이체의 SQL 예제 코드

-- 초기 상태: 공동 계좌에 1,000,000원 있음
-- accounts 테이블: account_id, balance

-- 트랜잭션 A (철수의 인출) - 현금이 부족해 결국 롤백될 예정
BEGIN;
UPDATE accounts SET balance = balance - 500000 WHERE account_id = 'JOINT-001';
-- 이 시점에서 공동 계좌 잔액은 500,000원이지만 아직 커밋되지 않음

-- 트랜잭션 B (영희의 잔액 확인) - READ UNCOMMITTED 격리 수준에서 실행
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
SELECT balance FROM accounts WHERE account_id = 'JOINT-001';
-- 결과: 500,000원 (Dirty Read 발생: 커밋되지 않은 트랜잭션 A의 변경사항이 보임)

-- 트랜잭션 A가 롤백됨 (ATM에 현금 부족 등의 이유로)
ROLLBACK;

-- 트랜잭션 B가 읽은 500,000원은 이제 실제 데이터베이스에 존재하지 않는 더티 데이터
-- 실제 잔액은 다시 1,000,000원이 됨
COMMIT;

자바/스프링 애플리케이션 예제 코드

// 철수의 인출 트랜잭션
@Transactional
public void withdrawMoney(String accountId, BigDecimal amount) throws InsufficientFundsException {
    Account account = accountRepository.findById(accountId)
        .orElseThrow(() -> new AccountNotFoundException());

    // 잔액 확인 및 차감
    if (account.getBalance().compareTo(amount) < 0) {
        throw new InsufficientFundsException();
    }

    account.setBalance(account.getBalance().subtract(amount));
    accountRepository.save(account);

    // ATM에서 현금이 부족하여 예외 발생 - 롤백됨
    throw new ATMInsufficientCashException();
}

// 영희의 잔액 확인 트랜잭션 (READ_UNCOMMITTED 격리 수준)
@Transactional(isolation = Isolation.READ_UNCOMMITTED)
public BigDecimal checkBalance(String accountId) {
    Account account = accountRepository.findById(accountId)
        .orElseThrow(() -> new AccountNotFoundException());

    // 여기서 더티 데이터(철수의 인출이 반영된 잔액)를 읽을 수 있음
    return account.getBalance();
}

왜 다른 격리 수준에서는 발생하지 않는가?

  • READ COMMITTED 이상의 격리 수준에서는 다른 트랜잭션이 커밋한 데이터만 읽을 수 있습니다.
  • READ COMMITTED 격리 수준에서는 각 SELECT 문이 실행될 때 읽기용 스냅샷이 생성되어, 다른 트랜잭션이 아직 커밋하지 않은 변경사항은 볼 수 없습니다.

2. Non-repeatable Read (반복 불가능한 읽기)

발생하는 격리 수준: READ UNCOMMITTED, READ COMMITTED

READ COMMITTED 격리 수준에서는 Dirty Read는 방지하지만, 한 트랜잭션 내에서 같은 데이터를 두 번 읽을 때 일관성을 보장하지 않습니다.

발생 원인

  • READ COMMITTED 격리 수준에서는 다른 트랜잭션이 커밋한 변경사항이 현재 트랜잭션에 즉시 보입니다.
  • 각 SELECT 문마다 새로운 읽기용 스냅샷이 생성되기 때문에, 트랜잭션 도중에 다른 트랜잭션의 커밋이 발생하면 데이터 변경이 반영됩니다.
  • 트랜잭션 전체가 아닌 개별 쿼리에 대한 일관성만 보장됩니다.

온라인 쇼핑몰 상품 구매 예시 분석

  1. 민수의 트랜잭션에서 첫 번째 SELECT 문이 노트북 가격(100만원)을 읽습니다.
  2. 지수의 트랜잭션이 가격을 80만원으로 변경하고 커밋합니다.
  3. 민수의 트랜잭션에서 두 번째 SELECT 문이 실행될 때, READ COMMITTED 격리 수준에서는 새로운 스냅샷이 생성되어 변경된 가격(80만원)을 읽게 됩니다.
  4. 결과적으로 민수의 트랜잭션 내에서 같은 데이터를 두 번 읽었을 때 다른 결과가 나옵니다.

온라인 쇼핑몰 상품 구매의 SQL 예제 코드

-- 초기 상태: 노트북 가격이 1,000,000원
-- products 테이블: product_id, name, price

-- 트랜잭션 A (민수의 상품 조회) - READ COMMITTED 격리 수준에서 실행
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT price FROM products WHERE product_id = 'LAPTOP-001';
-- 결과: 1,000,000원

-- 트랜잭션 B (지수의 가격 변경) - 플래시 세일을 시작함
BEGIN;
UPDATE products SET price = 800000 WHERE product_id = 'LAPTOP-001';
COMMIT;

-- 트랜잭션 A (민수가 다시 가격 확인)
SELECT price FROM products WHERE product_id = 'LAPTOP-001';
-- 결과: 800,000원 (Non-repeatable Read 발생: 같은 트랜잭션 내에서 다른 결과)

-- 민수가 구매 버튼 클릭 (80만원에 구매하게 됨)
INSERT INTO orders (user_id, product_id, price) VALUES ('USER-001', 'LAPTOP-001', 800000);
COMMIT;

자바/스프링 애플리케이션 예제 코드

// 민수의 상품 구매 트랜잭션 (READ_COMMITTED 격리 수준)
@Transactional(isolation = Isolation.READ_COMMITTED)
public Order purchaseProduct(String userId, String productId) {
    // 첫 번째 가격 조회 (결과: 1,000,000원)
    Product product = productRepository.findById(productId)
        .orElseThrow(() -> new ProductNotFoundException());
    BigDecimal initialPrice = product.getPrice();

    // 사용자가 고민하는 동안 시간이 흐름
    // (이 사이에 지수의 트랜잭션이 가격을 800,000원으로 변경하고 커밋)

    // 구매 결정 전 마지막으로 가격 다시 확인 (결과: 800,000원)
    // 동일한 트랜잭션이지만 READ_COMMITTED 격리 수준에서는 다른 트랜잭션의 커밋된 변경사항이 보임
    product = productRepository.findById(productId).get();
    BigDecimal finalPrice = product.getPrice();

    // 애플리케이션 로그
    log.info("초기 가격: {}, 최종 가격: {}", initialPrice, finalPrice);

    // 800,000원으로 주문 생성
    Order order = new Order(userId, productId, finalPrice);
    return orderRepository.save(order);
}

// 지수의 가격 변경 트랜잭션
@Transactional
public void updateProductPrice(String productId, BigDecimal newPrice) {
    Product product = productRepository.findById(productId)
        .orElseThrow(() -> new ProductNotFoundException());
    product.setPrice(newPrice);
    productRepository.save(product);
}

왜 다른 격리 수준에서는 발생하지 않는가?

  • REPEATABLE READ 이상의 격리 수준에서는 트랜잭션 시작 시점의 스냅샷을 사용합니다.
  • REPEATABLE READ에서는 트랜잭션이 시작된 후에 다른 트랜잭션이 변경하고 커밋한 데이터도 트랜잭션 전체에서 일관되게 보이지 않습니다(트랜잭션 시작 시점의 데이터만 보임).
  • 이를 통해 같은 트랜잭션 내에서 같은 데이터를 여러 번 읽어도 일관된 결과를 보장합니다.

3. Phantom Read (팬텀 읽기)

발생하는 격리 수준: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ(일부 데이터베이스)

REPEATABLE READ 격리 수준은 기존 레코드의 변경은 방지하지만, 새로운 레코드의 삽입이나 삭제로 인한 결과 집합 변화는 완벽히 방지하지 못할 수 있습니다.

발생 원인

  • REPEATABLE READ 격리 수준은 읽은 데이터에 대한 일관성은 보장하지만, 아직 읽지 않은 새로운 데이터(삽입)나 삭제된 데이터에 대해서는 보장하지 않을 수 있습니다.
  • 대부분의 데이터베이스에서 REPEATABLE READ는 행 수준의 잠금만 제공하고, 범위 잠금은 제공하지 않습니다.
  • 예를 들어, MySQL InnoDB는 REPEATABLE READ에서도 넥스트-키 락(next-key lock)을 사용하여 팬텀 읽기를 방지하는 반면, 다른 많은 데이터베이스는 그렇지 않습니다.

대학 수강신청 예시 분석

  1. 김 교수의 트랜잭션에서 첫 번째 쿼리가 수강 학생 수(30명)를 조회합니다.
  2. 다른 트랜잭션들(이씨와 박씨의 수강신청)이 새로운 레코드를 삽입하고 커밋합니다.
  3. 김 교수의 트랜잭션에서 두 번째 쿼리가 실행될 때, REPEATABLE READ 격리 수준이지만 범위 쿼리(COUNT(*))를 사용했기 때문에 새로 삽입된 레코드(총 32명)가 결과에 포함될 수 있습니다.
  4. 결과적으로 같은 쿼리를 두 번 실행했을 때 결과 집합의 크기가 달라집니다.

대학 수강신청의 SQL 예제 코드

-- 초기 상태: 데이터베이스 개론 수업에 30명 등록
-- enrollments 테이블: student_id, course_id, enroll_date

-- 트랜잭션 A (김 교수의 학생 수 조회) - REPEATABLE READ 격리 수준에서 실행
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT COUNT(*) FROM enrollments WHERE course_id = 'DB-101';
-- 결과: 30명

-- 트랜잭션 B (이씨의 수강신청)
BEGIN;
INSERT INTO enrollments (student_id, course_id, enroll_date) 
VALUES ('S-1234', 'DB-101', CURRENT_TIMESTAMP);
COMMIT;

-- 트랜잭션 C (박씨의 수강신청)
BEGIN;
INSERT INTO enrollments (student_id, course_id, enroll_date) 
VALUES ('S-5678', 'DB-101', CURRENT_TIMESTAMP);
COMMIT;

-- 트랜잭션 A (김 교수가 다시 학생 수 확인)
SELECT COUNT(*) FROM enrollments WHERE course_id = 'DB-101';
-- 결과: DBMS에 따라 다름
-- PostgreSQL, SQL Server 등: 32명 (Phantom Read 발생)
-- MySQL InnoDB: 30명 (Next-key lock으로 Phantom Read 방지)

-- 김 교수가 학생 명단을 보기 위해 실행
SELECT student_id FROM enrollments WHERE course_id = 'DB-101';
-- REPEATABLE READ에서도 새로 삽입된 학생 ID가 포함될 수 있음 (Phantom Read)
COMMIT;

자바/스프링 애플리케이션 예제 코드

// 김 교수의 학생 조회 트랜잭션 (REPEATABLE_READ 격리 수준)
@Transactional(isolation = Isolation.REPEATABLE_READ)
public void analyzeClassEnrollment(String courseId) {
    // 첫 번째 학생 수 조회
    long initialCount = enrollmentRepository.countByCourseId(courseId);
    log.info("초기 수강 학생 수: {}", initialCount);

    // 수업 계획 수립 중 시간이 흐름
    // (이 사이에 이씨와 박씨의 트랜잭션이 새로운 수강 신청을 추가하고 커밋)

    // 최종 학생 수 다시 확인
    long finalCount = enrollmentRepository.countByCourseId(courseId);
    log.info("최종 수강 학생 수: {}", finalCount);

    // 학생 명단 가져오기
    List<Student> students = enrollmentRepository.findStudentsByCourseId(courseId);

    // 학생 수와 명단의 크기가 다를 수 있음 (Phantom Read)
    if (finalCount != initialCount || students.size() != initialCount) {
        log.warn("Phantom Read 감지: 초기 수 {}, 최종 수 {}, 명단 크기 {}", 
                initialCount, finalCount, students.size());
    }
}

// 학생의 수강신청 트랜잭션
@Transactional
public void enrollCourse(String studentId, String courseId) {
    // 중복 등록 방지
    if (enrollmentRepository.existsByStudentIdAndCourseId(studentId, courseId)) {
        throw new DuplicateEnrollmentException();
    }

    Enrollment enrollment = new Enrollment(studentId, courseId, LocalDateTime.now());
    enrollmentRepository.save(enrollment);
}

왜 다른 격리 수준에서는 발생하지 않는가?

  • SERIALIZABLE 격리 수준에서는 읽기 작업에도 범위 잠금(range lock)을 획득합니다.
  • 범위 잠금은 쿼리의 조건에 해당하는 범위에 새로운 데이터 삽입을 방지합니다.
  • 따라서 SERIALIZABLE에서는 같은 쿼리를 여러 번 실행해도 항상 같은 결과 집합을 얻을 수 있습니다.

4. 팬텀 삽입(Phantom Insert) 문제

발생하는 격리 수준: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ(일부 데이터베이스)

팬텀 삽입은 팬텀 읽기의 특수한 형태로, 특히 "확인 후 삽입" 패턴에서 문제가 됩니다.

발생 원인

  • "확인 후 삽입" 패턴에서는 데이터가 존재하는지 먼저 확인한 후, 없으면 삽입하는 로직을 사용합니다.
  • REPEATABLE READ 격리 수준에서도 다른 트랜잭션이 새 데이터를 삽입하는 것을 방지하지 못할 수 있습니다.
  • 특히 유일성 제약(unique constraint)이 있는 경우, 두 트랜잭션이 동시에 같은 값을 삽입하려고 할 때 충돌이 발생합니다.

병원 처방코드 생성 예시 분석

  1. 의사 A와 B의 트랜잭션이 동시에 처방코드 "ABCD1234"가 존재하는지 확인합니다(두 트랜잭션 모두 없다고 확인).
  2. 의사 B의 트랜잭션이 먼저 해당 코드를 삽입하고 커밋합니다.
  3. 의사 A의 트랜잭션이 같은 코드를 삽입하려 할 때, REPEATABLE READ 격리 수준에서는 의사 B의 삽입을 감지하지 못하고 삽입을 시도하여 중복 키 오류가 발생합니다.

병원 처방코드 생성의 SQL 예제 코드

-- 초기 상태: 처방코드 테이블
-- prescription_codes 테이블: code(PK), doctor_id, patient_id, created_at

-- 트랜잭션 A (의사 A의 처방코드 생성) - REPEATABLE READ 격리 수준에서 실행
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
-- 처방코드 "ABCD1234"가 존재하는지 확인
SELECT * FROM prescription_codes WHERE code = 'ABCD1234';
-- 결과: 없음

-- 이 시점에서 트랜잭션 B가 같은 코드를 먼저 삽입

-- 트랜잭션 B (의사 B의 처방코드 생성)
BEGIN;
-- 처방코드 "ABCD1234"가 존재하는지 확인 
SELECT * FROM prescription_codes WHERE code = 'ABCD1234';
-- 결과: 없음
-- 처방코드 삽입
INSERT INTO prescription_codes (code, doctor_id, patient_id, created_at) 
VALUES ('ABCD1234', 'D-5678', 'P-9012', CURRENT_TIMESTAMP);
COMMIT;

-- 트랜잭션 A (계속)
-- 처방코드 삽입 시도
INSERT INTO prescription_codes (code, doctor_id, patient_id, created_at) 
VALUES ('ABCD1234', 'D-1234', 'P-5678', CURRENT_TIMESTAMP);
-- 오류 발생: 중복 키 위반 (Unique constraint violation)
ROLLBACK;

자바/스프링 애플리케이션 예제 코드

// 의사 A의 처방코드 생성 서비스 (REPEATABLE_READ 격리 수준)
@Service
public class PrescriptionService {

    private final PrescriptionRepository repository;

    @Transactional(isolation = Isolation.REPEATABLE_READ)
    public PrescriptionCode createPrescriptionCode(String code, String doctorId, String patientId) {
        // 코드 존재 여부 확인
        if (repository.findByCode(code).isPresent()) {
            throw new DuplicateKeyException("처방코드가 이미 존재합니다: " + code);
        }

        // 이 시점에서 의사 B가 같은 코드를 삽입하고 커밋할 수 있음

        // 처방코드 생성 시도
        try {
            PrescriptionCode prescriptionCode = new PrescriptionCode(code, doctorId, patientId);
            return repository.save(prescriptionCode);
        } catch (DataIntegrityViolationException e) {
            // 중복 키 오류 발생 (팬텀 삽입 문제)
            throw new DuplicateKeyException("처방코드 생성 중 충돌 발생: " + code, e);
        }
    }
}

// SERIALIZABLE 격리 수준을 사용한 안전한 구현
@Transactional(isolation = Isolation.SERIALIZABLE)
public PrescriptionCode createPrescriptionCodeSafely(String code, String doctorId, String patientId) {
    // SERIALIZABLE 격리 수준에서는 이 코드가 동시성 안전함
    if (repository.findByCode(code).isPresent()) {
        throw new DuplicateKeyException("처방코드가 이미 존재합니다: " + code);
    }

    PrescriptionCode prescriptionCode = new PrescriptionCode(code, doctorId, patientId);
    return repository.save(prescriptionCode);
}

왜 SERIALIZABLE 격리 수준에서는 발생하지 않는가?

  • SERIALIZABLE 격리 수준에서는 SELECT 쿼리가 범위 잠금을 획득합니다.
  • 의사 A의 트랜잭션이 처방코드를 확인할 때, 해당 코드에 대한 범위 잠금이 설정됩니다.
  • 의사 B의 트랜잭션이 같은 코드를 삽입하려고 할 때, 잠금 충돌이 발생하여 대기하거나 롤백됩니다.
  • 따라서 의사 A의 트랜잭션이 완료된 후에야 의사 B의 트랜잭션이 진행될 수 있어서, 동시 삽입으로 인한 충돌이 방지됩니다.

트랜잭션 격리 수준별 동시성 문제 방지 요약

격리 수준 Dirty Read Non-repeatable Read Phantom Read 특징 및 사용 시나리오
READ UNCOMMITTED ❌ 발생 ❌ 발생 ❌ 발생 가장 낮은 격리 수준, 성능은 높지만 일관성 문제 발생. 임시 통계 조회 등에 사용
READ COMMITTED ✅ 방지 ❌ 발생 ❌ 발생 대부분 DBMS의 기본값, 일반적인 웹 애플리케이션에 적합
REPEATABLE READ ✅ 방지 ✅ 방지 ❌/✅* 트랜잭션 내 데이터 일관성 보장, 보고서 생성 등에 적합
SERIALIZABLE ✅ 방지 ✅ 방지 ✅ 방지 가장 높은 격리 수준, 완벽한 일관성 제공, 금융 거래 등 중요 작업에 적합

*일부 데이터베이스(MySQL InnoDB 등)에서는 REPEATABLE READ 수준에서도 Phantom Read를 방지할 수 있습니다.

트랜잭션 격리 수준 선택 시 고려사항

각 격리 수준은 일관성과 성능 사이의 균형을 제공합니다:

  1. READ UNCOMMITTED: 가장 낮은 격리 수준으로, 성능은 가장 좋지만 데이터 일관성 문제가 발생할 수 있습니다. 임시 통계 조회 등 정확성이 덜 중요한 작업에 사용할 수 있습니다.
  2. READ COMMITTED: 대부분의 데이터베이스 시스템의 기본값으로, 커밋된 데이터만 읽는 기본적인 일관성을 제공합니다. 일반적인 웹 애플리케이션에 적합합니다.
  3. REPEATABLE READ: 트랜잭션 내에서 데이터 일관성을 보장하지만, 팬텀 읽기 문제가 발생할 수 있습니다. 보고서 생성이나 일관된 백업 등에 유용합니다.
  4. SERIALIZABLE: 가장 높은 격리 수준으로, 완벽한 데이터 일관성을 제공하지만 성능이 저하될 수 있습니다. 금융 거래와 같이 데이터 정확성이 매우 중요한 작업에 적합합니다.

실제 애플리케이션에서는 성능과 일관성 요구사항을 고려하여 적절한 격리 수준을 선택해야 합니다. 특히 동시성이 높은 시스템에서는 SERIALIZABLE 격리 수준이 성능 병목이 될 수 있으므로, 애플리케이션 수준에서 추가적인 동시성 제어 메커니즘(예: 낙관적 락, 버전 관리 등)을 고려할 수 있습니다.

특히 마지막의 팬텀 인서트의 경우 최근 채용 과제 테스트를 진행하면서 직접 겪고 해결하는 과정을 경험했었는데요, 그 과정에서 정리해야 될 필요성을 느껴 이렇게 두 편에 걸친 글을 작성하게 되었습니다. (+ 기술면접에서 대답을 못했기 때문에....)
직접 경험한 트러블 슈팅에 관한 내용은 아래 링크를 참고해 주세요.

2025.03.24 - [개발 경험 기록/데이터베이스] - 채용 과제 테스트 중 디지털 치료제 처방 코드 생성 시스템에서 팬텀 삽입 문제 해결하기

 

채용 과제 테스트 중 디지털 치료제 처방 코드 생성 시스템에서 팬텀 삽입 문제 해결하기

이번 글에서는 최근 채용 과제 테스트로 처방코드 생성 API 개발하던 도중 발견한 팬텀 삽입(Phantom Insert) 문제와 이를 데코레이터 패턴과 트랜잭션 격리 수준을 통해 해결한 경험을 공유하려고

shin-e-dog.tistory.com

 

728x90

'데이터베이스' 카테고리의 다른 글

[트랜잭션 시리즈 - 1] 트랜잭션의 격리 수준 정리  (0) 2025.03.24
재귀적 쿼리에 대한 이해  (1) 2023.09.20
MySQL `EXPLAIN`의 조인 타입(`type` 컬럼) 깊게 이해하기  (0) 2023.09.12
MySQL `EXPLAIN`의 이해와 활용  (0) 2023.09.12
복합키란 무엇인가?  (1) 2023.09.06

관련글

티스토리툴바