MySQL 엔진과 InnoDB 엔진의 잠금

☁️ MySQL 엔진의 잠금

MySQL에서 사용되는 잠금은 스토리지 엔진 레벨의 잠금과 MySQL 엔진 레벨의 잠금으로 나눌 수 있다. MySQL 엔진은 스토리지 엔진을 제외한 MySQL 서버의 모든 영역으로 보면되는데, MySQL 엔진 레벨의 잠금은 스토리지 엔진에도 영향을 미치지만, 반대로 스토리지 엔진 레벨의 잠금은 MySQL 엔진에 영향을 미치지 않는다.

MySQL 엔진에서 제공하는 락의 종류를 간단하게만 정리하고 넘어가보려 한다.

 

글로벌 락 (Global Lock)

MySQL에서 제공하는 잠금 가운데 가장 범위가 크다. 하나의 세션이 글로벌 락을 획득하면 다른 세션에서의 Select 작업 외의 모든 작업은 글로벌 락의 해제를 기다려야 한다. 이는 MySQL 서버 전체에 대한 락이므로 작업 테이블이 다른 경우에도 락의 해제를 기다려야 한다.

주로 여러 데이터베이스에 존재하는 MyISAM 혹은 MEMORY 스토리지 엔진의 테이블에 한해 백업을 수행할 때 사용된다. InnoDB에서는 트랜잭션을 지원하기에 좁은 범위의 글로벌 락인 백업 락을 지원한다. 백업 락은 테이블 변경은 허용되지만 스키마 변경은 제한한다.

 

테이블 락 (Table Lock)

이름에서 유추할 수 있듯이 테이블 단위로 설정되는 잠금이다. 명령어를 통해 명시적으로 락을 획득할 수 있고, 묵시적으로도 락을 획득할 수 있다. MyISAM 혹은 MEMORY 스토리지 엔진에서 테이블 쓰기 작업을 수행할 때, 묵시적으로 테이블 락을 획득한다. InnoDB는 레코드 기반 잠금을 제공하기에 테이블 락을 사용할 필요가 없다.

 

네임드 락 (Named Lock)

명시적으로 특정 문자열에 대한 락을 생성하고 이를 통해 동시성을 제어할 수 있는 잠금입니다. A와 B 클라이언트가 존재할 때, 두 클라이언트가 특정 작업에 대해 동시처리를 막아야 하는 상황에 사용한다. A 클라이언트가 my_named_lock 이라는 임의의 문자열을 통해 락을 생성하고 B 클라이언트가 명시적으로 이 락을 획득하기를 기다리는 것이다.

 

메타데이터 락 (Metadata Lock)

테이블이나 뷰등의 데이터베이스 객체의 이름이나 구조를 변경하는 경우 획득하는 잠금이다. 명시적으로 획득하거나 해제할 수 없다.

<Real MySQL 8.0 1> 에서 말하는 메타데이터 락의 사용처를 정리하면 다음과 같다.

 

만약 웹 서버에 접근하는 사용자의 정보를 저장하는 로그 테이블이 존재한다고 가정하자. 이 테이블은 insert만 실행된다. 어느 날 이 테이블의 구조를 변경해야 하는 일이 생겼다. 만약 DDL을 이용하여 변경한다면? DDL이 실행되는 동안 생성되는 언두 로그의 양, 버퍼의 크기 등 고민할 문제가 많을 것이다. 이때, 새로운 구조를 가지는 테이블을 먼저 생성해놓고 기존 테이블의 PK 값을 기준으로 나누어 여러 스레드를 통해 기존 테이블의 데이터를 새로운 테이블로 복사하는 작업을 수행하면 된다. 이후 메타데이터 락을 획득하며 새로운 테이블을 기존의 테이블 이름으로 변경하고 기존의 테이블을 삭제하면 된다.

 

 

☁️ InnoDB 스토리지 엔진 잠금

이제 InnoDB 스토리지 엔진의 잠금을 알아보자.

InnoDB는 레코드(튜플) 기반 잠금을 지원한다. 하나의 레코드에 대한 잠금을 지원하기에 데드락 발생 가능성이 낮고, 동시성 처리가 뛰어나다는 장점이 존재한다. InnoDB에서 지원하는 레코드 기반 잠금의 종류는 다음과 같다.

 

레코드 락

하나의 레코드에 대한 잠금이다. 두 트랜잭션이 하나의 레코드에 대해 Update 작업을 수행하려는 경우 레코드 락을 통해 동시성을 제어한다.

 

갭(Gap) 락

두 레코드 사이의 공간에 대한 잠금이다. 레코드와 레코드 사이의 공간(또는 범위)를 잠금으로써 해당 공간에 다른 레코드가 추가되는 것을 방지할 수 있다.

 

넥스트 키 락

레코드 락과 갭 락이 합쳐진 형태의 잠금이다. 만약 한 트랜잭션이 범위에 대한 작업을 수행하는 경우 다른 트랜잭션이 해당 범위에 레코드를 추가하는 것을 방지하기 위해 갭 락을 사용할 수 있다.

 

자동 증가 락

자동 증가 성향을 가진 레코드에 대한 잠금이다. AUTO_INCREMENT 컬럼이 사용된 테이블에 두개의 레코드가 동시에 INSERT 되려는 경우에 자동 증가 락을 사용하여 하나의 레코드씩 순차적으로 처리되도록 한다.

 

InnoDB의 인덱스 기반 레코드 잠금

InnoDB에서 레코드 락은 작업을 처리하는 개별 레코드를 잠그는 방식이 아닌 인덱스를 잠그는 방식으로 동작한다. 해당 방식은 MySQL에서 기본으로 지원하는 REPEATABLE READ 격리 수준와 밀접한 관련이 있다.

 

일단 InnoDB에서 사용자가 테이블에 대해 아무런 인덱스도 명시적으로 선언하지 않았다면 기본 키를 기반으로 클러스터 형 인덱스가 생성된다는 점을 알고 넘어가자.

 

아래와 같은 employee 테이블이 존재한다고 가정한다.

create table employee {
    id BIGINT NOT NULL AUTO_INCREMENT, 
    first_name VARCHAR(45) NOT NULL,
    last_name VARCHAR(45) NOT NULL,
    salary BIGINT,

    PRIMARY KEY(id),
    kEY idx_first_name (first_name)
}

employee 테이블에는 총 50만건에 레코드가 존재한다고 가정한다. 또, first_name 컬럼이 “정”인 레코드가 200건, last_name 컬럼이 “현수”인 레코드가 1건이 존재한다고 가정한다. 이제 아래와 같은 쿼리를 수행하여 정현수의 연봉은 5000으로 변경하려 한다 ㅎㅎ

UPDATE employee SET salary = 5000 WHERE first_name = '정' AND last_name = '현수';

자 이런 상황에는 어떤 레코드에 락이 걸리면 될까? 나는 정현수 레코드 1개에만 락을 걸면 된다고 생각했었지만 idx_first_index 인덱스에서 ‘정’ 값을 통해 검색가능한 모든 레코드에 락이 걸리게 된다.

만약에 first_name, last_name 과 관련된 아무런 인덱스가 없다면 해당 테이블의 모든 레코드에 대해 락이 걸리게 된다. 왜 대상 레코드만 잠그는 것이 아닌 인덱스를 잠그는지는 레퍼런스가 너무 없어서 나중에 찾아보고 정리하려 한다.

 

여기서 유의할 점은 InnoDB는 인덱스에 락을 걸기 때문에, 인덱스를 설정할 때 락을 잘 고려해야 한다는 것이다. 정보를 수정하는 작업이 많은 테이블인데 아무런 인덱스가 없어 클러스터 형 인덱스만 생성된 경우, 수정 작업이 수행될 때마다 테이블 전체가 락이 걸리기 때문에 동시성 처리에 비효율적이다. 인덱스의 효율과 레코드 락이 걸리는 최소 범위를 잘 고려해서 인덱스를 설계해야 한다.