• mysql
  • transaction
  • lock
  • book

Real MySQL 8.0 Ch.5 — 트랜잭션과 잠금

MySQL 엔진과 InnoDB의 잠금, 격리 수준, REPEATABLE READ에서 Phantom Read가 막히는 이유를 정리한다.

Manx10분 읽기
시리즈 · Computer Science 서적3 / 8
  1. Real MySQL 8.0 Ch.4 — MySQL 엔진과 스레드 구조
  2. Real MySQL 8.0 Ch.4 — InnoDB 스토리지 엔진 아키텍처
  3. Real MySQL 8.0 Ch.5 — 트랜잭션과 잠금
  4. Real MySQL 8.0 Ch.6~7 — 데이터 압축과 암호화
  5. Real MySQL 8.0 Ch.8 — B-Tree 인덱스
  6. Real MySQL 8.0 Ch.8 — R-Tree, 전문 검색, 함수 기반, 멀티 밸류 인덱스
  7. Real MySQL 8.0 Ch.8 — 클러스터링 인덱스, 유니크 인덱스, 외래키
  8. Real MySQL 8.0 Ch.9 — 옵티마이저와 기본 데이터 처리
  • 트랜잭션: DB의 논리적인 작업 단위로, ACID 특성을 보장한다.
  • 잠금: 동시성을 제어하기 위한 기능이다. 여러 트랜잭션이 같은 데이터에 동시에 접근할 때 정합성을 보장한다.
  • 격리 수준: 동시에 실행되는 트랜잭션들이 서로 얼마나 간섭할 수 있는지를 정의한 단계다.

트랜잭션

트랜잭션은 꼭 필요한 최소의 코드에만 적용하는 것이 좋다. 커넥션은 개수가 제한적이어서, 트랜잭션이 커넥션을 소유하는 시간이 길어질수록 여유 커넥션이 줄어든다.

특히 메일 전송이나 FTP 파일 전송처럼 다른 서버와 통신하는 작업은 어떻게 해서든 트랜잭션 밖으로 빼야 한다. 상대 서버와 통신할 수 없는 상황이 되면 웹 서버뿐 아니라 DBMS까지 위험해진다.

MySQL 엔진의 잠금

MySQL의 잠금은 MySQL 엔진 레벨과 스토리지 엔진 레벨로 나뉜다. MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치지만, 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간에 서로 영향을 미치지 않는다.

글로벌 락

  • FLUSH TABLES WITH READ LOCK 명령으로 획득한다.
  • MySQL이 제공하는 잠금 중 범위가 가장 크다. 영향 범위가 서버 전체여서, 작업 대상 테이블이나 데이터베이스가 달라도 영향을 받는다.
  • 다른 세션에서 SELECT를 제외한 대부분의 DDL, DML 문장이 대기 상태가 된다.

백업 락

글로벌 락은 범위가 너무 넓어, 8.0부터는 백업을 위한 더 가벼운 백업 락이 도입됐다. 백업 락을 획득하면 다음 작업을 할 수 없다.

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

대신 일반적인 테이블의 데이터 변경은 허용된다. MySQL 서버는 보통 소스 서버와 레플리카 서버로 구성하고 백업은 주로 레플리카 서버에서 실행하는데, 백업 락은 백업 중에도 복제가 멈추지 않게 해 준다.

테이블 락

개별 테이블 단위로 설정되는 잠금이다.

  • 명시적: LOCK TABLES table_name [READ | WRITE]로 획득하고 UNLOCK TABLES로 해제한다. MyISAM뿐 아니라 InnoDB 테이블에도 설정할 수 있다.
  • 묵시적: MyISAM이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 자동으로 발생한다.

InnoDB는 스토리지 엔진 차원에서 레코드 기반 잠금을 제공하므로, 단순한 데이터 변경 쿼리로 테이블 락이 설정되지는 않는다. InnoDB 테이블의 테이블 락은 DML에서는 무시되고 스키마를 변경하는 DDL에만 영향을 미친다.

네임드 락

임의의 문자열에 대해 잠금을 설정한다. 잠금 대상이 테이블이나 레코드 같은 데이터베이스 객체가 아니다.

메타데이터 락

테이블이나 뷰 같은 데이터베이스 객체의 이름이나 구조를 변경할 때 획득하는 잠금이다. 명시적으로 획득하는 것이 아니라, 테이블 이름을 변경하는 등의 작업에서 자동으로 획득된다.

InnoDB 스토리지 엔진의 잠금

InnoDB는 MySQL 엔진의 잠금과 별개로, 스토리지 엔진 내부에 레코드 기반의 잠금 방식을 탑재하고 있다.

  • 잠금 정보를 상당히 작은 공간으로 관리한다. 그래서 레코드 락이 페이지 락이나 테이블 락으로 레벨업(lock escalation)되는 경우가 없다.
  • 레코드와 레코드 사이의 간격을 잠그는 갭 락이 존재한다.
  • 잠금 정보는 information_schema의 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 테이블을 조인해 조회할 수 있다. 8.0에서는 performance_schema의 data_locks, data_lock_waits 테이블로 대체되고 있다.

정렬된 인덱스 레코드를 기준으로 Record Lock, Gap Lock, Next-Key Lock이 걸리는 범위를 비교한 그림

레코드 락

레코드 자체가 아니라 인덱스 레코드를 잠근다. 인덱스가 하나도 없는 테이블이라도 내부적으로 자동 생성된 클러스터링 인덱스를 이용해 잠금을 건다.

SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;

이 쿼리를 실행하면 다른 트랜잭션은 t.c1 = 10인 행을 INSERT, UPDATE, DELETE할 수 없다.

  • 보조 인덱스를 이용한 변경 작업은 넥스트 키 락 또는 갭 락을 사용한다.
  • PK 또는 유니크 인덱스에 의한 변경 작업은 갭은 잠그지 않고 레코드 자체에만 락을 건다.

갭 락

레코드와 바로 인접한 레코드 사이의 간격을 잠근다. 그 간격에 새로운 레코드가 생성되는 것을 막는다. 단독으로 쓰이기보다 넥스트 키 락의 일부로 자주 사용된다.

SELECT c1 FROM t WHERE c1 BETWEEN 10 AND 20 FOR UPDATE;

이 쿼리를 실행하면 10~20 사이의 간격이 잠기므로, 다른 트랜잭션은 t.c1 = 15인 레코드를 INSERT할 수 없다.

유니크 인덱스로 단일 행을 찾는 다음과 같은 검색에는 갭 락이 사용되지 않는다.

SELECT * FROM child WHERE id = 100;

넥스트 키 락

레코드 락과, 그 인덱스 레코드 앞 간격에 대한 갭 락을 합친 잠금이다. 인덱스에 10, 11, 13, 20이 있다면 넥스트 키 락이 걸릴 수 있는 구간은 다음과 같다.

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

넥스트 키 락과 갭 락의 주목적은 바이너리 로그에 기록된 쿼리가 레플리카 서버에서 실행될 때, 소스 서버에서 만든 결과와 동일한 결과를 만들도록 보장하는 것이다. 이 잠금 때문에 데드락이나 대기가 자주 생기므로, 바이너리 로그 포맷을 ROW로 바꿔 넥스트 키 락과 갭 락을 줄이는 것이 좋다.

인덱스와 잠금

InnoDB의 잠금은 레코드가 아니라 인덱스를 잠그는 방식이다. 그래서 변경할 레코드를 찾기 위해 검색한 인덱스의 레코드가 모두 잠긴다.

  • WHERE 조건이 여러 개여도, 인덱스를 사용한 조건에 일치하는 레코드가 전부 잠긴다. 인덱스로 걸러지지 않는 조건은 잠금 범위를 줄이지 못한다.
  • UPDATE를 위한 적절한 인덱스가 없으면 잠금 범위가 넓어져, 다른 클라이언트가 대기하게 되고 동시성이 떨어진다.
  • 인덱스가 아예 없다면 테이블을 풀 스캔하면서 UPDATE하는데, 이때 테이블의 모든 레코드를 잠근다.

InnoDB에서 인덱스 설계가 중요한 이유다.

격리 수준

격리 수준 Dirty Read Non-Repeatable Read Phantom Read
READ UNCOMMITTED O O O
READ COMMITTED - O O
REPEATABLE READ - - O (InnoDB는 발생하지 않음)
SERIALIZABLE - - -

격리 수준이 높을수록 동시 처리 성능이 떨어진다. 실무에서는 주로 READ COMMITTED와 REPEATABLE READ를 사용한다.

READ UNCOMMITTED

각 트랜잭션의 변경 내용이 COMMIT이나 ROLLBACK 여부와 상관없이 다른 트랜잭션에 보인다. 커밋되지 않은 데이터를 읽는 이 현상이 Dirty Read다.

READ COMMITTED

Oracle이 기본으로 사용하는 격리 수준이다.

  • 다른 트랜잭션이 변경 중인 레코드는 언두 영역에 백업된 변경 전 데이터로 읽는다.
  • 그 트랜잭션이 커밋되면 그때부터는 언두 레코드가 아니라 변경된 값을 읽는다.

그래서 한 트랜잭션 안에서 같은 SELECT를 두 번 실행했을 때, 그 사이에 다른 트랜잭션이 커밋하면 결과가 달라진다. 하나의 트랜잭션 안에서는 같은 SELECT가 항상 같은 결과를 반환해야 한다는 REPEATABLE READ 정합성에 어긋나며, 이것이 Non-Repeatable Read다.

REPEATABLE READ

InnoDB가 기본으로 사용하는 격리 수준이다. MVCC를 위해 언두 영역에 백업된 이전 데이터를 이용해, 같은 트랜잭션 안에서는 같은 결과를 보여 주도록 보장한다.

READ COMMITTED도 커밋되기 전의 데이터를 언두 영역에서 읽는다. 둘의 차이는 언두 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가느냐에 있다.

  • 모든 InnoDB 트랜잭션은 고유한 트랜잭션 번호를 가진다.
  • 언두 영역에 백업된 레코드에는 변경을 일으킨 트랜잭션의 번호가 포함돼 있다.
  • REPEATABLE READ에서는 자신의 트랜잭션 번호보다 작은 트랜잭션 번호에서 변경한 것만 보게 된다.
  • 언두 영역의 데이터는 불필요해진 시점에 주기적으로 삭제된다. 다만 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 앞선 언두 데이터는 삭제할 수 없다.

SERIALIZABLE

읽기 작업에도 잠금을 설정하는 가장 엄격한 격리 수준이다. InnoDB는 REPEATABLE READ에서도 이미 Phantom Read가 발생하지 않으므로 굳이 SERIALIZABLE을 쓸 필요성은 낮다.

InnoDB에서 Phantom Read가 발생하지 않는 이유

Phantom Read는 하나의 트랜잭션 안에서 같은 SELECT를 여러 번 수행했는데, 다른 트랜잭션의 INSERT 같은 작업 때문에 레코드가 보였다 안 보였다 하는 현상이다.

Transaction A가 age > 15 조건으로 두 번 조회하는 사이 Transaction B가 레코드를 INSERT해 결과 건수가 1건에서 2건으로 달라지는 예시

  1. Transaction A가 age > 15 조건으로 SELECT하면 1건이 나온다.
  2. A가 끝나지 않은 상태에서 Transaction B가 age가 22인 레코드를 INSERT한다.
  3. A가 같은 조건으로 다시 SELECT하면 처음과 결과 건수가 다르다.

Non-Repeatable Read와의 차이는 다음과 같다.

  • Non-Repeatable Read: 조회된 레코드의 값이 달라진다.
  • Phantom Read: 없던 레코드가 생기거나 있던 레코드가 사라진다.

표준 격리 수준 정의에서는 REPEATABLE READ에서 Phantom Read가 발생할 수 있지만, InnoDB에서는 두 가지 장치가 이를 막는다.

  • 일반 SELECT는 MVCC로 처리된다. 자신보다 나중에 시작한 트랜잭션이 INSERT한 레코드는 보이지 않는다.
  • SELECT ... FOR UPDATE 같은 잠금 읽기와 변경 작업은 검색과 인덱스 스캔에 넥스트 키 락을 사용한다. 조회한 인덱스 레코드와 그 앞뒤 간격까지 잠기므로, 다른 트랜잭션은 그 범위에 레코드를 INSERT할 수 없다.

참고