REAL MySQL 5장 트랜잭션

5.1 트랜잭션

5.1.1 MySQL에서 트랜잭션

MyIsam은 트랜잭션을 지원하지 않고, InnoDB는 지원함.

데이터가 바뀌면 완전히 실행되거나 실행되지 않거나 자체가 트랜잭션

5.1.2 주의사항

트랜잭션 범위를 너무 크게 가져가지 말자

5.2 MySQL 엔진의 잠금

스토리지 엔진이 아닌 MySQL 엔진 자체의 락도 존재

테이블의 구조를 잠그는 메타데이터 락

사용자의 필요에 맞게 사용할 수 있는 네임드 락

5.2.1 글로벌 락

글로벌 락
FLUSH TABLES WITH READ LOCK 명령으로 모든 테이블에 읽기 잠금을 거는 것

• DDL (테이블 구조 변경), DML (데이터 수정), 사용자 관리 같은 작업 전부 막힘
• 데이터를 안정적으로 백업하려고 쓰지만, 락을 걸면 MySQL 전체가 멈추기 때문에 서비스 중단 위험이 있음
• 그래서 실무에서는 거의 사용하지 않음

백업 락 (Backup Lock)

• XtraBackup이나 Enterprise Backup 도구가 사용하는 락
• 읽기 작업은 가능하지만, DDL 같은 구조 변경 작업만 제한함
• 서비스는 정상적으로 돌아가고, 백업도 안전하게 수행 가능
• 실무에서는 글로벌 락 대신 백업 락을 사용하는 게 일반적

5.2.2 테이블 락

테이블 락 (Table Lock)

특정 테이블 단위로 설정되는 잠금으로, 명시적 또는 묵시적으로 설정할 수 있음

1. 명시적 테이블 락

사용자가 직접 LOCK TABLES 명령어를 사용해 테이블에 락을 거는 방식

LOCK TABLES table_name READ;
LOCK TABLES table_name WRITE;
UNLOCK TABLES;

• 일반 애플리케이션에서는 거의 사용되지 않음
• 락을 건 동안 해당 테이블에 대한 읽기 또는 쓰기 작업이 제한됨
• UNLOCK TABLES 명령으로 해제 가능

2. 묵시적 테이블 락

MySQL이 내부적으로 쿼리 실행 시 자동으로 거는 락

• 주로 MyISAM이나 MEMORY 엔진에서 사용
• 데이터 변경 쿼리(DML)를 실행하면 자동으로 테이블에 락이 걸림
• 쿼리 실행 완료 시 자동 해제

3. 스토리지 엔진에 따른 차이

스토리지 엔진	기본 락 방식	설명
MyISAM, MEMORY	테이블 락	대부분의 쿼리에서 테이블 단위로 잠금 발생
InnoDB	레코드 락 (Row Lock)	DML에서는 레코드 단위 잠금, DDL에서는 테이블 락 가능성 있음

• InnoDB는 MVCC 기반으로 동작하여 대부분의 작업에서 테이블 락 없이 동시성 처리 가능
• DDL(예: ALTER TABLE) 실행 시에는 InnoDB에서도 테이블 락 발생

4. 정리

• 명시적 테이블 락은 특별한 목적이 아니면 잘 사용하지 않음
• 묵시적 락은 MyISAM이나 MEMORY 테이블에서 일반적으로 발생
• InnoDB는 주로 레코드 락을 사용하지만, 구조 변경 시에는 테이블 락이 발생할 수 있음

5.2.3 네임드 락

아주 단순한 구조에서 사용하지만 실무에서는 거의 사용 안함

5.2.4 메타데이터 락

테이블의 정의를 건드릴 수 없도록 막는 락

DDL 작업은 항상 조심해서 사용해야함

5.3 InnoDB 스토리지 엔진 잠금

InnoDB 스토리지 엔진은 MySQL에서 제공하는 잠금과 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재

레코드 기반의 잠금 방식 덕분에 MyISAM보다 훨씬 뛰어난 동시성 처리가 가능함

5.3.1 InnoDB 스토리지 엔진의 잠금

5.3.1.2 갭 락

---

쿼리와 갭 락 정리

쿼리 예시	설명	갭 락 발생 여부	이유
SELECT * FROM users WHERE id BETWEEN 2 AND 4;	단순 조회	없음	읽기만 하고 트랜잭션 보호 없음
SELECT * FROM users WHERE id BETWEEN 2 AND 4 FOR UPDATE;	읽지만 락을 걸어서 보호	있음	팬텀 리드 방지, 넥스트 키 락 자동 발생
UPDATE users SET id = 1000 WHERE id = 5;	특정 레코드 수정	없음	레코드만 수정, 범위 없음
INSERT INTO users (id) VALUES (3);	값 추가	가능 or 대기	다른 트랜잭션이 갭 락 걸고 있으면 대기됨
DELETE FROM users WHERE id = 2;	레코드 삭제	없음	대상 레코드에만 락 (갭 락 없음)

---

갭 락이 발생하는 조건 요약

조건	설명
InnoDB 엔진	갭 락은 InnoDB에서만 작동
REPEATABLE READ 이상 격리 수준	갭 락은 이 수준에서 팬텀 리드를 막기 위해 사용
SELECT ... FOR UPDATE or LOCK IN SHARE MODE	범위 조회 + 트랜잭션 보호 시 자동 발생

---

갭 락은 “읽기 + 보호” 조건일 때 생기고,
“수정/추가/삭제만 하는 쿼리”에서는 생기지 않는다.

---

5.3.1.3 넥스트 키 락 (Next-Key Lock)

---

정의

넥스트 키 락은 레코드 락 + 앞쪽 갭 락을 동시에 거는 InnoDB의 잠금 방식

---

목적

• 팬텀 리드를 방지하기 위해 사용
• 같은 쿼리를 두 번 실행했을 때 결과가 달라지지 않도록 보호

---

발생 조건

• InnoDB 스토리지 엔진
• 트랜잭션 격리 수준이 REPEATABLE READ
• SELECT ... FOR UPDATE 또는 LOCK IN SHARE MODE 사용 시

---

예시

테이블에 다음 값이 있다고 가정:

[3]     [5]     [7]

SELECT * FROM users WHERE id = 5 FOR UPDATE;

• 넥스트 키 락 범위: (3,5]
• id = 5 → 레코드 락
• 3과 5 사이의 공간 → 갭 락
• 따라서 id = 4 또는 id = 4.9 등의 INSERT가 막힘

---

특징 요약

구분	설명
레코드 락	[5] 레코드 자체만 잠금
갭 락	(3,5) 존재하지 않는 공간 잠금
넥스트 키 락	(3,5] 레코드 + 앞 갭을 동시에 잠금

---

주의 사항

• 잠금 범위가 넓어서 동시성 저하가 발생할 수 있음
• INSERT 충돌이 생길 수 있음
• 필요 시 binlog format을 'ROW'로 바꾸면 완화 가능

---

한줄 정리

넥스트 키 락은 레코드와 앞 공간을 동시에 잠가 팬텀 리드를 방지하는 InnoDB의 기본 락 방식

5.3.1.4 AUTO_INCREMENT 락

---

정의

AUTO_INCREMENT 컬럼이 있는 테이블에 여러 트랜잭션이 동시에 INSERT 할 때,
숫자 값이 중복되지 않고 순서대로 할당되도록
MySQL이 내부적으로 자동으로 거는 테이블 수준의 잠금

---

동작 방식

• INSERT, REPLACE 시 AUTO_INCREMENT 값이 필요한 순간
• 해당 테이블에 짧게 락을 걸어 순서대로 번호를 할당
• 번호가 할당되면 즉시 락 해제
• UPDATE, DELETE 에는 영향 없음

---

예시

-- 동시에 실행되는 두 쿼리
INSERT INTO users(name) VALUES ('Alice');
INSERT INTO users(name) VALUES ('Bob');

-- 결과적으로 id 1, 2가 순서대로 할당됨

---

락 설정 모드 (innodb_autoinc_lock_mode)

모드	설명
0 (전통 모드)	모든 INSERT에 락 사용. 가장 안전하지만 병목 발생 가능
1 (기본값)	단일 INSERT만 락 사용. 성능과 안전성 균형
2 (교차 모드)	락 없이 병렬 수행. 성능 좋지만 번호 순서 보장 안됨 (복제 주의)

---

주의 사항

• AUTO_INCREMENT 락은 명시적으로 제어할 수 없음
• 값만 할당되면 즉시 해제되므로 성능 영향은 미미
• innodb_autoinc_lock_mode 설정에 따라 동작 방식이 달라짐
• 복제 환경에서는 모드 2(교차 모드) 사용 시 유의

---

한줄 정리

AUTO_INCREMENT 락은 중복 없이 순서대로 번호를 부여하기 위해
INSERT 시 잠깐 걸리는 자동 잠금 메커니즘

5.3.2 인덱스와 잠금

---

개념 정리

• InnoDB는 레코드를 직접 잠그는 것이 아니라,
  사용된 인덱스를 기준으로 잠금을 설정
• InnoDB의 클러스터 인덱스는 실제 레코드 데이터를 저장하는 구조이며,
  보조 인덱스는 PK(클러스터 인덱스)를 참조하는 방식으로 동작

---

쿼리 흐름 예시

-- 보조 인덱스: KEY ix_firstname (first_name)
UPDATE employees
SET hire_date = NOW()
WHERE first_name = 'Georgi' AND last_name = 'Klassen';

• first_name 조건이 보조 인덱스를 사용함
• 보조 인덱스에서 first_name = 'Georgi' 조건을 만족하는 모든 항목의 PK 값을 수집
• 수집한 PK를 통해 클러스터 인덱스를 조회하여 실제 레코드에 접근
• last_name = 'Klassen' 조건은 실제 레코드에서 확인

---

락 발생 흐름

단계	락 대상
보조 인덱스 탐색	first_name='Georgi'에 해당하는 인덱스 엔트리들
PK 접근	보조 인덱스가 가리키는 클러스터 인덱스(실제 레코드)

• 보조 인덱스에 락이 걸릴 수 있음
• PK를 통해 접근한 클러스터 인덱스에도 락이 걸림

---

주의 사항

• WHERE 절에 보조 인덱스만 사용되면 더 많은 레코드가 잠길 수 있음
• 이를 방지하려면 복합 인덱스 사용이 효과적 (예: first_name, last_name)
• 락의 범위는 실제 영향을 주는 레코드 수가 아닌, 인덱스 경로에 의해 결정됨

---

InnoDB는 사용된 인덱스를 기준으로 락을 설정하며,
보조 인덱스를 사용한 쿼리는 해당 인덱스를 먼저 따라가고,
거기서 얻은 PK를 통해 클러스터 인덱스에 접근하여 락을 검

5.4 MySQL의 격리 수준

---

격리 수준별 주요 비교

격리 수준	읽기 일관성 방식	Undo 로그 사용	팬텀 리드 방지	Gap Lock 사용	주요 특징
READ UNCOMMITTED	없음 (Dirty Read 허용)	사용 안 함	방지 안 됨	사용 안 함	커밋되지 않은 값까지 읽음. 성능은 좋지만 정합성 낮음
READ COMMITTED	커밋된 시점 기준 읽기	사용함	방지 안 됨	사용 안 함	커밋된 데이터만 읽음. 반복 쿼리 결과가 다를 수 있음
REPEATABLE READ (기본값)	트랜잭션 시작 시점 스냅샷	사용함	방지됨	사용함	동일 쿼리 결과 보장. 팬텀 리드는 갭 락으로 차단
SERIALIZABLE	공유 락 기반 직렬화	사용함	완벽히 방지	강제 사용	모든 SELECT에도 락. 동시성 최소, 정합성 최상

---

각 격리 수준 설명

READ UNCOMMITTED

• Dirty Read 허용
• Undo 로그 사용하지 않음
• 가장 낮은 수준의 격리로 일관성 보장이 되지 않음

READ COMMITTED

• 항상 최신 커밋된 데이터를 읽음
• Undo 로그를 통해 읽기 일관성 제공
• 팬텀 리드가 발생할 수 있음

REPEATABLE READ

• 트랜잭션 시작 시점 기준 스냅샷을 기반으로 읽기
• 동일한 쿼리의 결과가 항상 같음
• 갭 락(Next-Key Lock)을 통해 팬텀 리드 방지
• InnoDB 기본값

SERIALIZABLE

• 모든 읽기에도 락이 걸리는 가장 강력한 격리 수준
• 트랜잭션 간 충돌을 완전히 방지
• 동시성은 가장 낮음

---

Undo 로그의 역할

역할	설명
MVCC 구현	다중 버전 일관성 유지 (READ COMMITTED, REPEATABLE READ에서 사용)
롤백 지원	트랜잭션 실패 시 이전 상태로 복원
지연 삭제	참조 중인 Undo가 있으면 GC 대상에서 제외 (purge 대기)

---

결론

• InnoDB는 Undo 로그와 MVCC 기반으로 다양한 격리 수준을 지원
• 일반적인 사용에는 REPEATABLE READ가 정합성과 성능의 균형이 가장 뛰어남
• READ COMMITTED는 충돌이 적어 실무에서 많이 쓰임
• SERIALIZABLE은 철저한 정합성이 필요할 때 사용하며 성능은 가장 낮음