[SQL] 4. 트랜잭션과 동시성

 

트랜잭션이란?

 

트랜잭션은 데이터베이스 관리 시스템 (DBMS)에서 데이터의 상태를 변화시키기 위한 작업(삽입, 삭제, 갱신 등)의 논리적 단위이다.

일반적으로 여러 개의 연산으로 구성되어, BEGIN/COMMIT/ROLLBACK 경계 내에서 모든 연산이 성공적으로 완료 되거나, 하나라도 실패하면 모두 취소되어 이전 상태로 돌아가는 특징을 가진다. 이러한 특성을 통해 데이터의 무결성과 일관성을 보장한다.

은행 계좌 이체를 예로 들면, 돈을 출금하는 작업과 입금하는 작업은 하나의 트랜잭션으로 묶을 수 있다.
출금 작업만 성공하고 입금이 실패하면, 돈이 사라지는 문제가 발생한 것이기 때문이다. 
그래서 출금과 입금은 모두 성공하거나, 둘 다 실패하여 이전 상태로 롤백되어야한다.

이렇게 트랜잭션은 데이터의 무결성과 일관성을 보장하기 때문에 매우 중요하다. 트랜잭션 처리를 통해 데이터베이스 시스템의 안정성과 신뢰성을 확보할 수 있다. 트랜잭션에 대해서 알아보도록 하자.

ACID 특성

ACID는 트랜잭션의 주요 특징이다. 데이터베이스 트랜잭션이 안전하게 수행된다는 것을 보장하기 위한 성질을 가리키는 축약어이다.

일반적으로 DB엔진은 Atomicity/Isolation/Durability를 제공하고, Consistency 자체는 트랜잭션 로직+제약으로 보장하게 된다.

 

Atomicity (원자성)

All or Nothing, 트랜잭션에 포함된 모든 연산이 성공해야하고, 하나라도 실패하면 전체가 취소(rollback)된다.

트랜잭션 내에서 일부만 성공하는 상태가 존재해서는 안된다.

 

Consistency (일관성)

Valid State, 트랜잭션 처리 전과 처리 후 데이터 모순이 없는 상태를 유지하는 것을 의미한다.

트랜잭션의 시작 전후로 데이터베이스는 항상 정의된 무결성 제약조건을 만족하는 상태여야 한다.

이때 데이터 무결성은 PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL 등의 제약조건을 말한다.

• 기본 키(PK): 한 줄을 가리키는 고유번호가 겹치면 안 됨
• 외래 키(FK): 참조한 대상이 실제로 존재해야 함
• 유니크(UNIQUE): 이메일 같은 값은 중복 금지
• 체크(CHECK): 재고·잔액은 음수 금지 같은 범위 제한
• NOT NULL: 꼭 필요한 칸은 빈칸 금지

Isolation (격리성, 독립성)

트랜잭션을 수행 시 다른 트랜잭션의 연산 작업이 끼어들지 못하도록 보장하는 것을 의미한다.

동시 트랜잭션이 서로 간섭하지 않도록 보장하여 순차적으로 실행되는 것처럼 보이게 한다.

 

Durability (지속성)

성공적으로 커밋된 트랜잭션은 영구적으로 반영되며, 이후 시스템에 장애가 발생하더라도 커밋된 레코드가 영향을 받지 않고 데이터베이스에 유지되도록 한다.

 

최근까지 DB 대부분이 ACID를 보장하기 위해 락(lock)에 의존했었다. 업데이트 되는 데이터에 락을 걸어 충돌 현상이 발생하더라도 DB 무결성을 보장하는 것이다. 하지만 많은 수의 락을 관리하게 되면 동시작업 수행이 어렵고 성능 저하를 초래한다.

그리고 최근에는 그 대안으로 수정되는 모든 데이터를 별도 복사본으로 관리하여 읽기 성능과 동시성 개선을 위해 사용되는 방식인 MVCC(다중 버전 동시성 제어)가 있다.

---

락(lock)과 MVCC(다중 버전 동시성 제어)

여러 트랜잭션이 동시에 같은 데이터에 접근하면 정합성(일관성)과 성능(동시성)이 충돌하게 된다.

데이터베이스에서 락(lock)과 MVCC(Multi-Version Concurrency Control)는 트랜잭션의 동시성 제어를 위해 사용되는 주요 기술이다. 이때 동시성이란 여러 트랜잭션이 동시에 데이터베이스에 접근하여 작업을 수행할 때, 데이터의 일관성과 무결성을 유지하면서 효율성을 높이는 것을 의미한다. 

락과 mvcc의 주 목적은 의도하지 않은 데이터의 손실이나 오류를 방지하고 데이터의 정확성을 유지하는 것이다.

락과 MVCC는 ‘DB 엔진(정확히는 스토리지 엔진/동시성 제어 서브시스템)’ 레벨에서 설계·구현되는 기능이지만 설정과 격리수준, 쿼리, 스키마 설계에 따라 엔진이 어떻게 동작하도록 할지를 꽤 많이 결정할 수 있다.

 

LOCK (락)

락은 충돌하는 동작(예: 동시에 같은 행 수정)을 서로 배제하기 위해 객체(행/페이지/테이블/스키마/범위)에 잠금을 거는 메커니즘이다.

데이터 접근을 직렬화하여 특정 트랜잭션이 데이터에 접근할 때, 다른 트랜잭션의 접근을 제한하여 데이터 일관성을 유지한다. 이때 직렬화는 여러 사용자가 동시에 데이터베이스에 접근할 때, 데이터의 일관성과 무결성을 유지하기 위해 트랜잭션의 실행 순서를 보장하는 것을 의미한다. 

주로 데이터베이스 테이블이나 레코드 단위로 설정되고, 한 트랜잭션이 특정 데이터를 사용 중일 때 다른 트랜잭션은 해당 데이터에 접근할 수 없도록 한다.

 

락 모드

S : Shared Lock(공유 락)

• 여러 트랜잭션이 동시에 접근할 수 있도록 허용하지만 쓰기는 금지한다. 읽기 작업 시 사용된다.
• 읽기는 공유되고 S끼리 호환되며, X와는 비호환

X : Exclusive Lock(배타 락)

• 한 트랜잭션만 읽거나 쓸 수 있도록 허용하며, 다른 트랜잭션의 접근을 완전히 차단한다. 쓰기 작업시 사용된다.
• INSERT/UPDATE/DELETE: 동일 키/행 충돌 → X락으로 조정.

락은 너무 광범위하게 사용하면 DeadLock(서로가 서로의 락을 기다리는 순환 대기 상태) 이나 성능 저하를 유발할 수 있다.

 

데드락 (DeadLock)

서로가 서로의 락을 기다리는 순환 대기 상태.

데드락을 회피하기 위해선 접근 순서를 통일시키고, 인덱스를 적절한 범위로 좁혀 잠금 범위를 최소화하고, 트랜잭션은 되도록 짧게하여 오래 잡히는 락이 없도록 하는 것이 좋다.

데드락이 발생하면 DB 엔진이 자동 감지하고 희생자 트랜잭션을 롤백한다. 애플리케이션은 재시도 로직을 준비하는 등의 예방 설계 (인덱스, 순서, 짧은 트랜잭션 등)로 발생 확률을 줄여야 한다.

 

MVCC (Multi-Version Concurrency Control)

서비스가 커져가며 읽기 트래픽이 급증하게 되면서, 읽기와 쓰기의 상호차단이 기존 데이터베이스의 성능이 감소하였다.

MVCC는 각 데이터 레코드(행)에 대해 여러 버전을 유지하고, 각 트랜잭션이 특정 시점의 데이터 버전을 참조하도록 하여 동시성을 향상시키는 기술이다. 각 트랜잭션은 특정 시점에 유효한 데이터 버전을 조회하게 된다. 

동시에 여러 트랜잭션이 같은 데이터를 다루더라도, 각자 “일관된 시점의 데이터”를 보는 방식이다.

그래서 어떤 사용자는 옛날 버전의 데이터를 보고 있을 수도 있고, 다른 사용자는 최신 버전을 보고 있을 수도 있다.

DB는 행에 대해 여러 버전을 보관하고 있기 때문에 읽기 작업은 락을 사용하지 않고도 데이터를 읽을 수 있게되고, 쓰기 작업은 새로운 버전의 데이터를 생성한다.

 

MVCC의 내부 동작 매커니즘

MySQL(InnoDB) 기준으로 클러스터 인덱스(PK 인덱스) 레코드에는 숨겨진 내부 컬럼이 있는데, 이 정보를 통해 DB는 같은 행에 대해 version chain(버전 체인)을 만들 수 있다.

• DB_TRX_ID : 이 버전을 생성/수정한 트랜잭션 ID
• DB_ROLL_PTR : Undo 레코드 포인터(이전 버전으로 거슬러 가는 히스토리 체인 시작점)
• DB_ROW_ID : 프라이머리 키가 없을 때 내부적으로 쓰는 행 ID

이 버전 메타데이터가 각 행 버전에 붙게 된다.

 

스냅샷(Snapshot)

스냅샷은 트랜잭션이 시작될 때 DB가 당시 시점의 데이터를 찍어둔 일종의 사진과 같다. MySQL(InnoDB) 기준으로 스냅샷이 트랜잭션 시작 시점의 커밋된 트랜잭션 ID 목록으로 만들어지며, 이 스냅샷을 기반으로 어떤 버전을 보여줄지를 결정한다.

 

읽기(Consistent Read)

• 트랜잭션 시작(또는 문장 시작)에 Read View/Snapshot을 만든다.
• 해당 스냅샷 시점에 커밋 완료된 버전만 “보인다”는 가시성 규칙으로 행 버전을 선택한다.
• 그래서 다른 세션의 쓰기가 진행 중이어도, 내 읽기는 락 없이 빠르고 일관적이게 된다.

쓰기(Update/Delete/Insert)

• 기존 버전을 논리적으로 폐기하고, 새 버전을 만든다.(Undo/Undo-Log/히스토리 체인 등으로 과거 버전 보존 -> 스냅샷 읽기 가능).
• 동일 행에 대한 동시 쓰기는 여전히 락(X)으로 직접 충돌을 조정(“MVCC가 락을 없애는 게 아님”이 핵심 포인트)

청소(가비지 콜렉터)

• InnoDB: Purge 스레드가 Undo 히스토리를 정리. 긴 트랜잭션은 청소 지연·공간 팽창의 주범.
• Purge 스레드는 더 이상 어떤 스냅샷에서도 참조되지 않는 Undo 레코드를 찾아 제거하려 하는데, 오랫동안 열린 트랜잭션이 있으면 Purge가 지연되고, Undo/Redo Tablespace가 비대해집니다. 이것이 MySQL에서 “오래 살아있는 트랜잭션이 성능 병목”이 되는 이유가 되기도 한다.

MySQL(InnoDB)에서는 기본적으로 Repeatable Read를 기본적인 격리수준으로 삼고있는데, 이를 MVCC를 통해 구현하고 있다.

 

각 격리 수준별 MVCC 동작 차이는 아래와 같다.

• READ COMMITTED: 매 SELECT마다 새로운 스냅샷 생성 → 최신 커밋 내용이 보임
• REPEATABLE READ: 트랜잭션 시작 시점의 스냅샷을 계속 사용 → 같은 쿼리 결과가 항상 동일
• SERIALIZABLE: 사실상 SELECT에도 공유 락을 걸어 직렬화 → MVCC라기보다 2PL에 가까움

추가 상식
SQL에서 2PL (Two-Phase Locking):은 데이터베이스 트랜잭션의 동시성 제어 기법 중 하나이며, 데이터 일관성을 유지하면서 여러 트랜잭션이 동시에 실행될 수 있도록 하는 프로토콜이다. 트랜잭션이 데이터를 접근하고 수정할 때 사용하는 Lock을 두 단계로 나누어 관리한다.
1. 확장 단계(Growing Phase)
트랜잭션은 잠금 연산(Lock)만 수행할 수 있고, 해제는 할 수 없다. 
2. 축소 단계(Shrinking Phase)
트랜잭션은 잠금 해제 연산(Unlock)만 수행할 수 있고, 새로운 lock 획득은 불가능하다.

예시:
T1이 먼저 실행되어 데이터 A에 대한 잠금을 획득하고 확장 단계에 들어감.
T2는 데이터 B에 대한 잠금을 획득하고 확장 단계에 들어감.
만약 T1이 데이터 B에 접근하려고 하면, 2PL 프로토콜에 따라 T2가 데이터 B에 대한 잠금을 가지고 있기 때문에 T1은 대기
T1이 데이터 A에 대한 잠금을 해제하고 축소 단계로 진입하면, T2는 데이터 A에 접근할 수 있게 된다.

2PL은 트랜잭션들이 마치 순차적으로 실행되는 것처럼 동작하도록 보장하여 데이터의 일관성을 유지한다.
하지만 Deadlock(교착 상태)이 발생할 수 있는 가능성을 가지고 있다.
MVCC와는 서로 보완관계라고 볼 수 있으며, MVCC는 읽기 시 락을 피하는 기술, 2PL은 쓰기 시 충돌을 직렬화하는 규칙이므로 서로 같이 작동한다고 볼 수 있다.

---

트랜잭션 주요 이상 현상

Lost Update 

두 명이 같은 문서를 동시에 편집 → 마지막에 저장한 사람 변경만 반영, 앞사람 수정은 사라짐.

• 원인: 동시 쓰기 충돌을 제어하지 못함.

Dirty Read 

A가 문서를 임시로 고쳤다가 아직 저장(Commit) 안 한 상태에서 B가 그 수정본을 읽는 현상.
나중에 A가 롤백하면, B는 존재하지 않는 데이터로 다른 트랜잭션에서 읽어 들여 데이터의 일관성이 깨질 수 있게 된다.

일반적으로 Read Uncommitted 와 같이 낮은 격리 수준에서 발생 한다.

 

Non-Repeatable Read

같은 문서를 두 번 읽었는데 중간에 누군가가 수정/삭제해버림.
→ 내 눈앞에서 내용이 달라져 버림.
(“같은 책을 두 번 읽었는데 내용이 바뀜” 같은 상황)

 

Phantom Read 

조건에 맞는 데이터를 조회했는데, 두 번째 조회할 때 새로운 행이 끼어들어옴.
→ 같은 WHERE 조건인데 결과 행 개수가 달라짐.

InnoDB의 Nest-Key Lock
InnoDB는 팬텀 리드 방지를 위해 Next-Key Lock이라는 특수한 잠금 방식을 사용한다.

Next-Key Lock = 레코드 락(행 자체) + 갭 락(행과 행 사이의 빈 구간)
- 레코드 락 (Record Lock): 특정 행을 잠그는 락
- 갭 락 (Gap Lock): 특정 행과 그 전후 사이의 “빈 공간”까지 잠그는 락
즉, 단순히 “행만 잠그는 게 아니라, 그 행이 있는 위치의 앞뒤 빈 공간까지 막아버리는 것을 말한다.

만약 age 의 데이터가 10, 20, 30, 40 있을 때 
SELECT * FROM users WHERE age BETWEEN 15 AND 35 FOR UPDATE; 이런 쿼리를 실행한다고 하자. (트랜잭션 A)
일반적인 레코드 락의 경우 조건에 맞는 20, 30 두 행에만 락이 잡혀서 다른 트랜잭션이 25라는 데이터를 삽입하면 트랜잭션 A의 결과는 다시 실행했을 때 팬텀(새로운 행)이 생겨버린다.

하지만 Nest-Key Lock을 사용하게 되면 조건에 맞는 20, 30 두 행에 레코드 락이 잡힘과 동시에 20과 30 사이의 빈공간에 갭 락을 걸어 버리는 것이다. 갭 락이 걸렸기 때문에 트랜잭션 B에서 25라는 데이터를 넣으려해도 트랜잭션 A가 끝날 때 까지는 대기 상태에 머무는 것이다.

 

Dirty Write

A와 B가 동시에 같은 행을 수정 중인데, A가 아직 Commit 안 했는데 B가 덮어씀.
→ 롤백 시 데이터 정합성이 깨질 수 있음.

 

Read Skew

한 트랜잭션 안에서 여러 행을 읽는데, 일부는 옛날 버전, 일부는 최신 버전을 읽어서 데이터 간 불일치 발생.

 

Write Skew

두 트랜잭션이 동시에 조건 검사를 통과하고 쓰기를 수행해서 제약 조건 위반 발생.

(예: “항상 최소 1명 의사가 당직 중이어야 한다” 규칙 → A, B 의사가 동시에 퇴근 처리 → 둘 다 조건 통과 → 결과적으로 당직자 0명)

---

격리 수준

READ UNCOMMITTED

• 커밋 안 된 데이터도 읽을 수 있음. (Dirty Read 발생 가능)

READ COMMITTED

• “항상 Commit된 데이터만 보겠다.”
• 그러나 트랜잭션마다 스냅샷이 새로 생김 → Non-Repeatable Read, Phantom Read 가능.

REPEATABLE READ

• 트랜잭션 시작 시점의 스냅샷을 계속 유지.
• 같은 행을 반복해서 읽으면 동일한 결과.
• 그러나 새로운 행(팬텀)은 막지 못할 수 있음.
• MySQL InnoDB는 Next-Key Lock 덕분에 팬텀까지 방지

SERIALIZABLE

• 사실상 모든 SELECT에 공유락 → 직렬 실행과 동일한 효과.
• 동시성 최소, 정합성 최대.

격리 수준과 이상 현상에 대한 정리

격리 수준	Dirty Read	Non-Repeatable	Read Phantom	Read Lost	기타
READ UNCOMMITTED	허용됨	허용됨	허용됨	허용됨	Dirty Write 가능
READ COMMITTED	차단	허용됨	허용됨	허용됨	Read Skew 가능
REPEATABLE READ	차단	차단	DB마다 다름 (MySQL InnoDB는 차단)	허용됨	Write Skew 가능
SERIALIZABLE	차단	차단	차단	차단	대부분 이상 차단

 

---

 

설계 패턴

• Idempotency: 중복 키/유니크 인덱스, 멱등 키, 재시도 설계
  • 목적: 네트워크·프로세스 재시도에도 중복 실행 없이 결과 한 번만 반영.
  • 핵심 기법
    • 멱등 키: 비즈니스 고유키(예: 주문ID+결제시도ID).
    • 유니크 인덱스: 멱등 키에 고유 제약을 걸어 DB가 중복을 차단.
    • Upsert(중복 시 무시/갱신): “이미 처리됨”을 안전하게 인지.
    • 응답 캐시: 같은 멱등 키 재요청 시 동일 결과 반환.
• 인덱스와 동시성: 커버링 인덱스로 범위 축소 → 락 경합·팬텀 위험 감소
  • 커버링 인덱스로 조회 범위를 정확히 좁히면:
    • 읽기가 테이블 페이지까지 내려가지 않음 → 잠금 범위·시간 단축.
    • Next-Key Lock의 범위도 작아져 팬텀·경합 위험 감소.
  • 인덱스 설계 팁: 조건의 선두 컬럼 정합, 범위 컬럼은 맨 뒤, 필요 컬럼은 INCLUDE(DB별 지원).
• 긴 읽기 트랜잭션 금지
  • 이유: MVCC 히스토리(Undo/버전)가 청소 못 되고 쌓임 → Purge/Vacuum 백로그, 디스크 팽창, 쓰기 지연.
  • 규칙: 보고서/스크롤·페이지네이션 같은 긴 조회는 배치·스냅샷 테이블·Materialized View로 대체.
• 트랜잭션 경계 최소화: I/O 포함 로직/외부 호출을 경계 밖으로
  • 원칙: DB I/O만 트랜잭션에 넣고, 외부 API/파일/네트워크 호출은 경계 밖.
  • 패턴: “쓰기 확정 → 아웃박스(이벤트 기록) 커밋 → 별도 퍼블리셔가 전송”으로 2단계 부작용 처리.