들어가며

해당 포스트는 Real MySQL 8.0 (1권) 을 보며 공부한 내용을 기록하기 위해 작성한 포스트이다.

---

MySQL 서버의 잠금

MySQL 서버에서의 잠금은 크게 MySQL 엔진 레벨 잠금과 스토리지 엔진 레벨 잠금으로 구분할 수 있다.

MySQL 엔진 레벨 잠금

MySQL 엔진 레벨 잠금의 특징은 모든 스토리지 엔진에 영향을 미친다는 점이다. 반면 스토리지 엔진 레벨 잠금은 스토리지 엔진 간 상호 영향을 미치지 않는다.

1. 글로벌 락

• MySQL이 제공하는 잠금 가운데 가장 범위가 큼
• 한 세션에서 글로벌 락을 획득하면 다른 세션에서 DML, DDL 문장 실행 시 글로벌 락이 해제될 때 까지 대기해야 함
• 글로벌 락의 영향 범위는 MySQL 서버 전체이며 작업 대상 테이블이나 DB가 다르더라도 동일하게 영향을 미침
• InnoDB 에서는 기본적으로 트랜잭션이 제공되기 때문에 이를 사용할 필요가 없어졌다.
• 모든 테이블에 락을 걸기 때문에 효율성이 매우 안좋다.

2. 테이블 락

• 개별 테이블 단위로 설정되는 잠금을 말한다.
• 명시적 or 묵시적으로 락을 획득할 수 있다.
  • 명시적 획득 : LOCK TABLES table_name [READ | WRITE]
    • 명시적으로 얻은 락은 명시적으로 반납해줘야 함 : UNLOCK TABLES
    • 글로벌 락과 마찬가지로 온라인 작업에 많은 영향을 미치기 때문에 특별한 상황이 아니면 사용할 필요가 없다.
  • 묵시적 획득 : MyISAM 혹은 MEMORY 테이블 데이터 변경 쿼리 실행 시 발생
    • 쿼리가 실행되는 동안 자동으로 획득했다가 완료되면 자동으로 해제함
    • InnoDB는 기본적으로 레코드 기반 잠금을 사용하기 때문에 단순 데이터 변경 시에는 묵시적 테이블 락이 발생하지 않는다. 더 정확히는, 테이블 락이 발생하긴 하지만 DML 쿼리에서는 무시하고 DDL의 경우에만 발생한다.

3. 네임드 락

• 네임드 락의 특이점은 테이블이나 레코드에 잠금을 거는 방법이 아닌 문자열에 잠금을 건다는 점이다. 사용자가 지정한 문자열에 대해 락을 획득하고 반납하는 형식이다.
• 여러 클라이언트가 상호 동기화가 필요할 때 사용하게 된다. 같은 테이블이나 레코드에 대해서도 서로 다른 작업에 대해서 각자의 락을 걸 수 있기 때문에 많은 레코드에 대해 복잡한 요건으로 레코드를 변경하는 트랜잭션에 유용하게 사용될 수 있다.

4. 메타데이터 락

• 테이블이나 뷰 등의 DB 객체의 이름이나 구조를 변경하는 경우에 사용하는 잠금이다.

스토리지 엔진 레벨 잠금

InnoDB 스토리지 엔진은 MySQL에서 제공하는 잠금과는 별개로 엔진 내부에서 레코드 기반의 잠금 방식을 사용한다. 이 떄문에 MyISAM보다 뛰어난 동시성 처리를 제공한다.

1. 레코드 락

• InnoDB 가 다른 DBMS 의 레코드 락과 다른 점은 레코드 자체에 락을 거는 방식이 아니라, 인덱스의 레코드에 락을 건다는 점이다.
• 인덱스가 하나도 없는 테이블이라도 내부적으로 자동 생성된 클러스터 인덱스를 활용해서 레코드 락을 설정한다.
• 인덱스에 락을 걸기 때문에 tight 하게 맞는 인덱스가 없는 경우 사용하는 동안 다른 레코드도 잠금이 설정될 수 있다.
  • ex) first_name 인덱스가 존재한다고 가정하자.
    • SELECT COUNT(*) FROM member WHERE first_name = "길동" -> 100
    • SELECT COUNT(*) FROM member WHERE first_name = "길동" and last_name = "홍" -> 1
    • UPDATE member SET hire_date = NOW() WHERE first_name = "길동" and last_name = "홍"
    • 위의 UPDATE 문을 실행하면 100개의 record에 레코드 락이 걸리게 된다. 그 first_name = "길동"에 해당하는 인덱스의 레코드에 락을 걸기 때문이다.

2. 갭 락

• 갭 락은 레코드 자체가 아닌, 레코드와 레코드 간의 사이 간격만을 잠그는 것을 의미한다.
• 레코드와 레코드 사이에 새로운 레코드가 생성되는 것을 제어하기 위해 사용한다.

3. 넥스트 키 락

• 레코드 락과 갭 락을 합쳐놓은 형태의 락을 말한다. 인덱스 레코드와 해당 레코드 이전의 갭을 조합하여 레코드를 잠그는 방식이다.
• 주 목적은 바이너리 로그에 기록되는 쿼리가 사용될 떄 데이터 정합성 문제 없이 동일한 결과를 만들어내기 위함이다.
• 의외로 갭락과 넥스트키락에 의한 데드락이 발생하거나 다른 트랜잭션을 기다리게 하는 일이 많이 발생한다. 이러한 문제를 줄이기 위해서 MySQL 8.0 에서는 바이너리 로그 포맷의 기본 설정이 ROW 형태로 변경됐다.
• 넥스트 키 락 덕분에 InnoDB 에서는 REPEATABLE_READ 에서 발생하는 PHANTOM_READ 가 발생하지 않는다.
  • A 가 데이터의 일관성을 위해서 SELECT ... FOR UPDATE 쿼리를 사용해서 배타 잠금(쓰기 잠금)을 사용했다고 가정해보자.
    • A 의 트랜잭션이 끝나지 않은 상태에서 B 가 새로운 데이터를 INSERT 한다면, 상식적으로는 A가 쓰기 잠금을 걸어 놨기 때문에 B 의 쿼리문 전 후에 발생한 A의 SELECT 쿼리문에 대해서는 동일한 결과가 반환되어야 한다.
    • 하지만 undo_log 는 lock 을 걸 수 없기 떄문에 SELECT ... FOR UPDATE 는 undo_log가 아닌 본 테이블에서 데이터를 가져오기 때문에 SELECT 시에 B 가 INSERT 해둔 데이터까지 가져오면서 PHANTOM_READ가 발생하게 된다.
    • InnoDB 에서는 넥스트 키 락 을 사용하기 때문에 PHANTOM_READ를 방지할 수 있다.
• ex) id = 10, 20, 30 의 인덱스가 있다고 가정해보자.
  • id = 10 의 넥스트 키 락은 10 <= id < 20 까지의 잠금이 설정되는 것을 말한다.
  • id = 20 의 넥스트 키 락은 20 <= id < 30 의 잠금이 설정된다.

바이너리 로그 ?
DDL 이나 DML 로 인한 이벤트를 기록하는 이진 파일을 말한다.
(1) Replica 와 Source Server 간의 데이터 Sync 를 맞추기 위해 사용
(2) 서버 장애 시 데이터를 복구하기 위해 사용

4. 자동 증가 락

• 자동 증가 락은 MySQL 에서 AUTO_INCREMENT 칼럼이 사용되는 테이블에서 자동 증가 값을 채번할 때 중복되지 않은 값을 채번하기 위해서 사용하는 락이다.
• 새로운 레코드를 저장하는 쿼리시에만 필요하며, 트랜잭션과는 무관하게 INSERT or REPLACE 문장에서 AUTO_INCREMENT 값을 가져오는 순간만 잠시 락이 걸렸다 즉시 해제된다.
• 아주 짧은 시간 지속되기 때문에 거의 문제가 되지 않는다.