Today I Learned

5장을 한 문장으로 줄이면 **"동시에 여러 명이 같은 데이터를 만질 때, 어디까지 서로 침범하게 둘 것인가"**의 문제다. 트랜잭션은 "작업 하나가 통째로 되든가 통째로 안 되든가"를 보장하고(원자성), 잠금은 "그동안 남이 못 건드리게" 막고, 격리 수준은 "그럼 남한테는 어디까지 보여줄까"를 정한다. 이 셋은 따로 노는 게 아니라 같은 문제의 세 얼굴이다.

# 5.1 트랜잭션 — MyISAM엔 없고 InnoDB엔 있다

# 부분 업데이트(Partial Update)라는 재앙

트랜잭션의 가치는 InnoDB와 MyISAM을 나란히 놓고 보면 바로 드러난다. fdpk가 프라이머리 키인 테이블에 이미 3이 들어있는 상태에서, 두 엔진에 각각 이런 쿼리를 날려보자.

INSERT INTO tab_myisam (fdpk) VALUES (1),(2),(3);  -- MyISAM
INSERT INTO tab_innodb (fdpk) VALUES (1),(2),(3);  -- InnoDB

둘 다 3에서 중복 키 오류(Duplicate entry '3')로 쿼리는 실패한다. 그런데 실패한 뒤 테이블을 조회하면 결과가 다르다.

  • MyISAM: 1, 2가 그대로 남는다. 앞에서부터 차례로 저장하다가 3에서 터졌는데, 이미 넣은 1, 2는 되돌리지 않는다.
  • InnoDB: 아무것도 안 남는다. 쿼리 하나가 일부라도 실패하면 전체를 원래 상태로 되돌린다.

MyISAM(과 MEMORY)의 이 "하다 만" 상태가 **부분 업데이트(Partial Update)**다. 테이블 데이터의 정합성을 맞추는 걸 지옥으로 만든다. 애플리케이션 코드로 이걸 방어하려면 IF (_is_insert1_succeed) { ... } ELSE { DELETE ... } 식으로 성공/실패를 매 문장마다 검사하고 수동 복구하는 코드를 짜야 한다. 2개 이상 쿼리가 얽히면 이 코드는 금세 감당 불가가 된다.

트랜잭션이 지원되면 이 장황한 코드가 통째로 사라지고 이렇게 끝난다.

try {
  START TRANSACTION;
  INSERT INTO tab_a ...;
  INSERT INTO tab_b ...;
  COMMIT;
} catch(exception) {
  ROLLBACK;
}

🐘 PostgreSQL 비교 — PG는 애초에 스토리지가 하나(힙)뿐이고 전부 트랜잭션을 지원한다. "엔진에 따라 트랜잭션이 되고 안 되고"라는 MySQL 특유의 함정 자체가 없다. 그래서 PG에선 "이 테이블이 트랜잭션 되나?"를 물을 일이 없다 — 항상 된다.

# 트랜잭션의 범위는 최소화하라

트랜잭션도 DBMS 커넥션과 똑같이 꼭 필요한 최소의 코드에만 적용하는 게 좋다. 책이 든 예시가 인상적이다 — 게시판 글쓰기 처리에 10단계가 있다고 할 때, 그중 DBMS 작업은 5·6번(글 저장, 첨부파일 정보 저장)뿐이고 나머지는 사용자 입력 검증, FTP 파일 전송, 메일 발송 같은 것들이다.

여기서 초보가 흔히 저지르는 실수가 트랜잭션을 1번(커넥션 생성)부터 10번(완료)까지 통째로 감싸는 것이다. 그러면 어떻게 되냐면:

  • 메일 발송·FTP 전송 같은 외부 통신이 트랜잭션 안에 들어가면, 그 네트워크 대기 시간 내내 DB 커넥션과 잠금을 붙잡고 있게 된다.
  • 가용한 커넥션 개수가 줄어들고, 어느 순간 전 커넥션이 대기 상태로 물려 DBMS 서버가 위험한 상태에 빠진다.

원칙은 외부 통신처럼 DBMS 밖에서 끝낼 수 있는 작업은 트랜잭션 범위에서 빼고, 실제 데이터 변경(5·6번)만 최대한 짧게 묶는 것이다. 커넥션을 오래 잡는 게 곧 서버 전체의 부하라는 건 4장의 "트랜잭션을 빨리 커밋하라"와 정확히 같은 이야기다.

# 5.2 MySQL 엔진의 잠금

잠금은 레벨이 두 종류다. MySQL 엔진 레벨(글로벌·테이블·네임드·메타데이터 락)과 스토리지 엔진 레벨(InnoDB 레코드 락). MySQL 엔진 레벨 잠금은 모든 스토리지 엔진에 공통으로 영향을 미친다.

# 글로벌 락 — 서버 전체를 얼린다

글로벌 락(GLOBAL LOCK)은 FLUSH TABLES WITH READ LOCK 명령으로 건다. MySQL에서 제공하는 잠금 중 가장 범위가 크다. 한 세션에서 이걸 걸면 다른 세션에서는 SELECT을 제외한 대부분의 DDL·DML이 글로벌 락이 풀릴 때까지 대기한다. 영향 범위가 서버 전체다.

FLUSH TABLES WITH READ LOCK은 그냥 대기가 아니다

이 명령은 실행 순간 서버에 존재하는 모든 테이블을 닫으면서 잠금을 건다. 그런데 테이블을 닫으려면 먼저 그 테이블에서 실행 중인 모든 쿼리가 끝나야 한다. 그래서 장시간 실행 중인 SELECT가 하나라도 있으면, 이 명령도 그게 끝날 때까지 멈춰버리고 — 그동안 뒤따라오는 다른 모든 쿼리까지 줄줄이 멈춘다. 잠깐이면 될 백업이 서버를 통째로 세울 수 있다는 뜻이다.

여러 데이터베이스에 걸친 MyISAM·MEMORY 테이블을 mysqldump로 일관된 백업 받을 때나 이 글로벌 락이 필요하다. 그런데 요즘은 InnoDB가 기본이라 이런 무거운 글로벌 락을 쓸 일이 거의 없다. InnoDB는 트랜잭션으로 일관성을 맞추니까.

# 백업 락 (8.0 신규) — 글로벌 락의 가벼운 대안

그래서 8.0부터 훨씬 가벼운 **백업 락(Backup Lock)**이 도입됐다.

LOCK INSTANCE FOR BACKUP;
-- 백업 실행
UNLOCK INSTANCE;

백업 락은 특정 세션에서 이걸 걸어도 일반적인 테이블의 데이터 변경(DML)은 그대로 허용한다. 대신 아래는 막는다.

  • 데이터베이스·테이블 등 모든 객체 생성/변경/삭제 (DDL)
  • REPAIR TABLE, OPTIMIZE TABLE
  • 사용자 관리 및 비밀번호 변경

이게 왜 유용하냐면, 백업은 보통 레플리카(Replica) 서버에서 돌리는데, 백업이 도는 도중에 소스 서버에서 스키마 변경(DDL)이 넘어오면 백업이 깨진다. 백업 락은 데이터 변경은 계속 받으면서 스키마 변경만 잠깐 막아 이 문제를 푼다. Xtrabackup이나 Enterprise Backup 같은 툴을 안정적으로 돌리려고 만든 것이다.

# 테이블 락·네임드 락·메타데이터 락

**테이블 락(Table Lock)**은 개별 테이블 단위 잠금이다. LOCK TABLES ... READ|WRITE로 명시적으로 걸 수 있지만 — 온라인 작업에 큰 영향을 줘서 명시적으로 쓸 일은 거의 없다. 우리가 신경 쓸 건 묵시적 테이블 락인데, InnoDB는 애초에 레코드 기반 잠금이라 단순 DML에서는 테이블 락이 걸리지 않고 스키마를 바꾸는 쿼리(DDL)에서만 영향이 있다.

**네임드 락(Named Lock)**은 GET_LOCK() 함수로 임의의 문자열에 잠금을 거는 것이다. 대상이 테이블·레코드가 아니라 그냥 내가 정한 문자열이라는 게 특이하다.

SELECT GET_LOCK('mylock', 2);      -- 'mylock' 문자열에 잠금 획득 (2초 타임아웃)
SELECT IS_FREE_LOCK('mylock');     -- 잠겨있는지 확인
SELECT RELEASE_LOCK('mylock');     -- 해제
SELECT RELEASE_ALL_LOCKS();        -- 8.0: 여러 개 한 번에 해제

어디에 쓰냐면 — 여러 대의 웹 서버가 하나의 공유 자원을 동기화해야 할 때다. 5대의 서버가 어떤 정보를 동시에 갱신하려 할 때, 애플리케이션 로직으로 순서를 맞추긴 어렵지만 이 서버들이 다 같은 MySQL 한 대를 본다면 네임드 락으로 줄을 세울 수 있다. 배치 프로그램끼리 같은 레코드를 건드려 데드락이 자주 나는 경우에도, 프로그램끼리 네임드 락으로 순서를 정하면 간단히 풀린다.

**메타데이터 락(Metadata Lock)**은 테이블·뷰의 이름이나 구조를 변경할 때 자동으로 걸리는 잠금이다. RENAME TABLE tab_a TO tab_b 같은 걸 하면 원본과 대상 양쪽에 한 번에 잠금이 걸린다. 명시적으로 거는 게 아니라 자동이다.

책이 든 실전 예시가 좋다 — 로그 테이블 구조를 바꿔야 하는데 ALTER가 너무 오래 걸릴 때, 새 구조의 빈 테이블을 만들어 최근 데이터만 복사해두고, 마지막에 RENAME TABLE로 원본과 새 테이블의 이름을 한 문장에서 맞바꾸는 기법이다.

RENAME TABLE rank TO rank_backup, rank_new TO rank;

이걸 두 문장으로 나눠서(RENAME rank TO rank_backup; 그리고 RENAME rank_new TO rank;) 실행하면, 그 찰나에 rank 테이블이 존재하지 않는 순간이 생겨서 그 사이 쿼리가 Table not found 'rank'로 터진다. 한 문장에 넣어야 메타데이터 락으로 원자적으로 처리돼서 이 틈이 안 생긴다.

🐘 PostgreSQL 비교 — 네임드 락은 PG의 **어드바이저리 락(Advisory Lock, pg_advisory_lock())**과 정확히 같은 발상이다. 애플리케이션이 임의로 정한 키에 거는 잠금. 메타데이터 락도 PG에 ACCESS EXCLUSIVE LOCK 형태로 존재하고, PG는 RENAME을 포함한 대부분의 DDL을 트랜잭션 안에 넣어 롤백할 수 있어서 이런 이름 맞바꾸기가 더 자연스럽다.

# 5.3 InnoDB 스토리지 엔진의 잠금

여기가 5장의 핵심이다. InnoDB는 MySQL 엔진 레벨 잠금과 별개로 스토리지 엔진 내부에서 레코드 기반 잠금을 갖는다. 이 레코드 기반 잠금 덕에 MyISAM보다 훨씬 뛰어난 동시성을 낸다.

# 레코드 락 / 갭 락 / 넥스트 키 락

InnoDB 잠금에서 헷갈리기 쉬운 게 이 세 가지인데, 사실 관계가 단순하다.

  • 레코드 락(Record Lock): 레코드 자체를 잠근다. 단, 결정적으로 중요한 차이 — InnoDB는 레코드 자체가 아니라 인덱스의 레코드를 잠근다. (이게 뒤에서 인덱스와 잠금 얘기의 핵심이 된다.)
  • 갭 락(Gap Lock): 레코드가 아니라 레코드와 레코드 사이의 간격을 잠근다. 그 간격에 새 레코드가 INSERT되는 걸 막는 역할이다. 갭 락 자체를 단독으로 쓰기보단 넥스트 키 락의 일부로 쓰인다.
  • 넥스트 키 락(Next Key Lock): 레코드 락 + 갭 락을 합친 것. 즉 "이 레코드"와 "이 레코드 앞의 간격"을 같이 잠근다.

넥스트 키 락과 갭 락이 존재하는 근본 이유는 바이너리 로그로 복제할 때 소스 서버와 레플리카 서버의 결과를 똑같이 맞추기 위해서다(STATEMENT 포맷 기준). 그래서 가능하면 바이너리 로그 포맷을 ROW로 바꿔서 넥스트 키 락·갭 락을 줄이는 게 권장된다.

# 자동 증가 락(Auto Increment Lock)

AUTO_INCREMENT 컬럼이 있는 테이블에 여러 레코드가 동시에 INSERT될 때, 각 레코드에 중복 없이 순차 증가하는 값을 넣어주려고 InnoDB가 내부적으로 쓰는 테이블 수준 잠금이다.

특징은 아주 짧다는 것 — INSERT·REPLACE 같은 새 레코드 저장 쿼리에서만 걸리고, 값을 가져오는 순간 잠금이 걸렸다가 즉시 해제된다(커밋까지 기다리지 않는다). 그래서 대부분 문제가 안 되지만, innodb_autoinc_lock_mode 시스템 변수로 동작을 바꿀 수 있다.

  • 0: 5.0 방식. 모든 INSERT에 자동 증가 락.
  • 1: 단순 INSERT는 락 없이 가벼운 래치(mutex)로 처리. 단, INSERT ... SELECT처럼 건수를 미리 모르는 경우는 자동 증가 락 사용. 이 모드에선 연속된 INSERT가 연속된 값을 받는 게 보장돼 **연속 모드(Consecutive mode)**라 한다.
  • 2: 항상 락 없이 처리. 가장 빠르지만 한 INSERT 문 안에서도 값이 연속이 아닐 수 있어 인터리브 모드(Interleaved mode).

8.0 기본값이 바뀌었다

5.7까지 기본값은 1이었는데, **8.0부터 기본값이 2**로 바뀌었다. 바이너리 로그 기본 포맷이 STATEMENT에서 ROW로 바뀐 것과 연동된 변화다. 만약 STATEMENT 포맷 바이너리 로그를 쓴다면 innodb_autoinc_lock_mode를 2가 아닌 1로 되돌려 쓰는 게 권장된다.

# 인덱스와 잠금 — 5장에서 제일 중요한 대목

InnoDB의 레코드 잠금은 레코드가 아니라 인덱스를 잠근다. 이 한 문장이 실무에서 두고두고 발목을 잡는다. 무슨 뜻인지 예로 보자. employees 테이블에 first_name 컬럼만 인덱스(ix_firstname)가 있고, first_name='Georgi'인 사원이 253명, 그중 last_name='Klassen'까지 맞는 사람은 딱 1명이라고 하자.

UPDATE employees SET hire_date=NOW()
 WHERE first_name='Georgi' AND last_name='Klassen';

실제로 바뀌는 레코드는 1건이다. 그런데 이 UPDATE가 잠그는 레코드는 몇 건일까? 253건 전부다. 인덱스로 걸러낼 수 있는 조건은 first_name뿐이고, last_name은 인덱스에 없어서 스토리지 엔진이 253건을 다 잠근 뒤 MySQL 엔진이 그 안에서 last_name을 필터링하기 때문이다.

만약 인덱스가 하나도 없으면? 테이블을 풀 스캔하면서 테이블의 30만 건 전 레코드를 다 잠근다. 이게 MySQL의 방식이고, 인덱스 설계가 조회 속도만이 아니라 동시성의 문제이기도 한 결정적 이유다. 잘못 설계된 인덱스 하나가, 실제로는 1건만 바꾸는 쿼리로 테이블 전체를 잠가 다른 클라이언트를 다 굶길 수 있다.

🐘 PostgreSQL 비교 — PG는 이 점이 다르다. PG의 행 잠금은 인덱스가 아니라 실제 튜플(행) 자체에 건다. 그래서 인덱스가 없어도 "조건에 안 맞는 행까지 다 잠그는" InnoDB식 현상은 없다(조건에 맞는 행만 잠근다). 대신 PG는 갭 락이라는 개념 자체가 없고, 팬텀 문제를 다른 방식(Serializable Snapshot Isolation)으로 푼다.

# 레코드 수준 잠금 확인 및 해제

레코드 잠금은 테이블 잠금보다 문제가 어디서 생겼는지 찾기가 더 어렵다. 8.0에서는 performance_schema.data_locks, data_lock_waits 테이블로 어떤 트랜잭션이 어떤 잠금을 쥐고, 누가 누구를 기다리는지 조회할 수 있다. (5.7까지 쓰던 information_schema의 INNODB_LOCKS 등은 8.0에서 Deprecated 됐다.)

흐름은 이렇다. SHOW PROCESSLIST로 오래 대기 중인 스레드를 찾고 → data_lock_waits를 조인해서 **"18번이 17번을 기다리고, 19번은 17·18번을 기다린다"**는 대기 순서를 파악하고 → 원인인 17번 스레드를 KILL 17;로 강제 종료하면, 밀렸던 UPDATE들이 줄줄이 풀린다.

이때 data_locks를 보면 LOCK_MODE가 IX(Intentional Exclusive, 의도 배타 잠금)면 테이블 레벨의 의도 잠금이고, X,REC_NOT_GAP이면 갭이 포함되지 않은 순수 레코드 잠금이라는 걸 읽을 수 있다.

# 5.4 MySQL의 격리 수준

격리 수준(Isolation Level)은 여러 트랜잭션이 동시에 돌 때, 한 트랜잭션이 다른 트랜잭션이 변경·조회하는 데이터를 어디까지 볼 수 있게 할지를 정하는 것이다. 아래로 갈수록 격리가 강해지고 동시 처리 성능은 떨어진다. 각 수준에서 어떤 부정합이 생기는지가 핵심이다.

격리 수준 DIRTY READ NON-REPEATABLE READ PHANTOM READ
READ UNCOMMITTED 발생 발생 발생
READ COMMITTED 없음 발생 발생
REPEATABLE READ 없음 없음 발생 (InnoDB는 없음)
SERIALIZABLE 없음 없음 없음

이 표에서 InnoDB만의 특이점 두 개를 기억하면 5장의 격리 수준은 거의 끝난다.

① READ UNCOMMITTED — 더티 리드(Dirty Read). 다른 트랜잭션이 아직 COMMIT도 안 한 변경 내용이 보인다. 트랜잭션 A가 데이터를 바꾸고 아직 커밋 안 했는데, 그 사이에 A가 롤백해버리면 — B는 존재한 적도 없는 유령 값을 읽고 그게 맞다고 착각한 채 처리를 이어간다. 정합성에 심각한 문제라 표준에서도 격리 수준으로 잘 안 친다. 최소 READ COMMITTED 이상을 쓰라는 게 책의 권고다.

② READ COMMITTED — 커밋된 것만 보인다. 오라클의 기본값이고 온라인 서비스에서 가장 많이 쓴다. 다른 트랜잭션이 커밋 완료한 데이터만 보이니 더티 리드는 없다. 대신 NON-REPEATABLE READ가 생긴다 — 한 트랜잭션 안에서 같은 SELECT을 두 번 했는데, 그 사이 다른 트랜잭션이 값을 바꾸고 커밋해버리면 두 번의 결과가 다르다. 여기서 중요한 원리 하나: READ COMMITTED에서도 변경된 값을 바로 읽는 게 아니라, **언두 영역에 백업된 "커밋 이전 버전"**을 읽는다. 커밋된 순간 참조 대상이 최신 언두로 바뀔 뿐이다.

③ REPEATABLE READ — MySQL InnoDB의 기본값. 같은 트랜잭션 안에서는 몇 번을 다시 읽어도 항상 같은 결과가 나온다. 어떻게? 4장에서 본 **MVCC(Multi Version Concurrency Control, 다중 버전 동시성 제어)**를 언두 로그로 구현한다. 모든 InnoDB 트랜잭션은 고유한 **트랜잭션 번호(순차 증가)**를 갖고, 언두에 백업된 레코드에는 그 변경을 만든 트랜잭션 번호가 붙어있다. 내 트랜잭션 번호보다 큰 번호가 만든 변경은 안 보이게 언두에서 이전 버전을 찾아 읽으면, 트랜잭션이 시작된 시점의 스냅숏이 유지된다.

④ 그리고 InnoDB의 진짜 특이점 — REPEATABLE READ인데 PHANTOM READ도 안 난다. 팬텀 리드는 같은 범위 조회를 두 번 했을 때 없던 레코드(유령, Phantom)가 튀어나오는 현상이다. 표준 이론상 REPEATABLE READ에선 팬텀이 생길 수 있는데, InnoDB는 갭 락(넥스트 키 락)으로 조회 범위의 간격까지 잠가서 그 사이에 INSERT가 못 들어오게 막는다. 그래서 REPEATABLE READ만으로도 팬텀이 안 생긴다.

SELECT ... FOR UPDATE에는 예외가 있다

순수 SELECT은 언두 스냅숏을 읽어서 팬텀이 안 나지만, SELECT ... FOR UPDATE나 SELECT ... FOR SHARE는 잠금을 걸어야 하는데 언두 레코드에는 잠금을 걸 수 없다. 그래서 이때는 언두가 아니라 현재 레코드를 읽어서, 드물게 팬텀이 보일 수 있다.

⑤ SERIALIZABLE — 가장 엄격하고 가장 느리다. 읽기 작업도 공유 잠금(읽기 잠금)을 걸어서 동시에 다른 트랜잭션이 그 레코드를 못 바꾼다. 그런데 앞서 봤듯 InnoDB는 REPEATABLE READ에서 이미 팬텀이 없으므로, 굳이 SERIALIZABLE을 쓸 이유가 거의 없다.

🐘 PostgreSQL 비교 — 기본 격리 수준이 다르다. MySQL(InnoDB)은 REPEATABLE READ, PostgreSQL은 READ COMMITTED가 기본이다. 그리고 팬텀을 막는 방식이 다르다 — InnoDB는 갭 락(비관적, 미리 잠금)으로 막고, PG는 SERIALIZABLE에서 **SSI(Serializable Snapshot Isolation, 낙관적)**로 커밋 시점에 충돌을 감지해 롤백시킨다. 그래서 PG의 REPEATABLE READ에서는 팬텀이 이론대로 생길 수 있다(InnoDB와 다른 지점).

# 정리

5장에서 실무로 들고 갈 것만 추리면 이렇다.

  • 트랜잭션 범위는 최소화하라. 특히 메일 발송·FTP 같은 외부 통신을 트랜잭션 안에 넣지 마라. 그 대기 시간만큼 커넥션과 잠금을 붙잡아 서버를 위험에 빠뜨린다.
  • 글로벌 락(FLUSH TABLES WITH READ LOCK)은 서버를 세운다. 8.0에선 백업 락(LOCK INSTANCE FOR BACKUP)이 DML은 허용하고 DDL만 막는 가벼운 대안이니 이걸 써라.
  • InnoDB 레코드 잠금은 레코드가 아니라 인덱스를 잠근다. 이게 5장 최고의 실무 포인트다. 인덱스로 못 거른 조건은 스캔한 레코드를 전부 잠근다 — 1건 바꾸는 UPDATE가 253건, 인덱스가 없으면 30만 건을 잠글 수 있다. 인덱스 설계는 조회 속도가 아니라 동시성 문제다.
  • 여러 서버가 공유 자원을 동기화해야 하면 네임드 락(GET_LOCK)을 떠올려라. 배치 데드락 회피에도 쓴다.
  • 이름·구조를 바꾸는 RENAME은 한 문장에 몰아라. 두 문장으로 나누면 그 틈에 Table not found가 터진다.
  • InnoDB 기본 격리 수준은 REPEATABLE READ이고, 갭 락 덕에 이 수준에서 이미 팬텀이 없다. 그래서 SERIALIZABLE 쓸 일이 거의 없다. PG는 기본이 READ COMMITTED라는 것도 같이 기억.
  • 최소 READ COMMITTED 이상을 써라. READ UNCOMMITTED(더티 리드)는 격리 수준 취급도 안 한다.

반대로 지금 깊이 안 파도 되는 것 — 자동 증가 락의 innodb_autoinc_lock_mode 세부 모드(0/1/2)와 넥스트 키 락의 바이너리 로그 복제 관련 동작은 STATEMENT 포맷을 쓸 때만 신경 쓰면 된다(8.0 기본 ROW 포맷이면 대부분 알아서 처리된다).

6장 데이터 압축은 별도 글로 분리했다.

← Real MySQL 8.0 - 6장. 데이터 압축 →