[MySQL] 잠금

잠금

잠금은 동시성을 제어하기 위한 기능이다. 여러 커넥션에서 동시에 동일한 자원(레코드나 테이블)을 요청할 경우 순서대로 한 시점에는 하나의 커넥션만 변경 가능하다.

 

MySQL에서 사용되는 잠금은 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나뉜다. MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 주지만, 스토리지 엔진 레벨의 잠금은 스토리지 엔진간 상호 영향을 주지 않는다.

MySQL 서버

글로벌 락

글로벌 락은 MySQL에서 제공하는 잠금 중 가장 범위가 큰 잠금이다. 일단 한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 대부분의 DDL, DML 실행은 대기 상태가 된다. 

 

아래 명령어를 사용하면 글로벌 락을 획득할 수 있다.

 

mysql> FLUSH TABLES WITH READ LOCK

 

해당 명령은 실행과 동시에 모든 테이블을 잠금을 건다. 

 

명령이 실행되기 전에 쓰기 잠금을 거는 SQL이 실행됐다면, 이 명령은 읽기 잠금을 걸기 위해 먼저 실행된 트랜잭션을 기다린다. 읽기 잠금을 거는 SQL을 실행해도 마찬가지이다. 

 

글로벌 락은 MySQL 서버의 모든 변경 작업을 멈춘다. 하지만 InnoDB 사용이 일반화되고 트랜잭션이 지원되면서 일관된 데이터 상태 유지를 위해 글로벌 락을 걸 필요는 없어졌다. 그래서 MySQL 8.0부터 Xtrabackup이나 Enterprise Backup과 같은 백업 툴들의 안정적인 실행을 위해 백업 락이 도입됐다.

 

특정 세션이 백업락을 획득하면 모든 세션이 다음과 같이 테이블의 스키마나 사용자의 인증 관련 정보를 변경할 수 없게 된다.

 

1. DB 및 테이블 등 모든 객체 생성 및 변경, 삭제

2. REPAIR TABLE과 OPTIMIZE TABLE 명령

3. 사용자 관리 및 비밀번호 변경

 

하지만 일반적인 테이블의 데이터 변경은 허용한다. MySQL 서버 구성은 소스 서버와 레플리카 서버(복제) 구성된다. 백업은 주로 레플리카 서버에서 실행된다. 이때 다음과 같은 문제가 발생할 수 있다.

 

1. 만약 글로벌 락을 통해 백업을 시도하면 복제는 백업 시간만큼 지연된다. 

2. 레플리카 서버에서 백업 중 소스 서버에 문제가 생긴다면 백업 시간만큼 서비스가 중단된다.

 

백업 서드파티 툴들은 복제가 진행되는 상태에서도 일관된 백업을 만들 수 있다. 하지만 툴 실행중 스키마 변경이 실행되면 백업이 중단된다. 장시간 진행되는 백업이 DDL 명령 하나로 실패될 수 있다. 이러한 이유로 백업 락은 정상적으로 복제는 실행되지만 백업 실패를 막기 위해 DDL명령이 실행되면 복제를 일시 중지하는 역할을 한다.

테이블 락

테이블 락은 개별 테이블 단위로 설정되는 잠금이다. 명시적 또는 묵시적으로 획득할 수 있다. 아래 명령어를 통해 명시적으로 테이블 락을 획득할 수 있다.

 

mysql> LOCK TABLE table_name [ READ | WRITE]

 

묵시적인 테이블 락은 MyISAM이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생한다. 쿼리가 종료되면 락은 해제된다.

 

네임드 락

네임드 락은 GET_LOCK() 함수를 이용해 임의의 문자열에 대한 잠금을 설정할 수 있다. 네임드 락의 특징은 테이블이 레코드 또는 AUTO_INCREMENT와 같은 DB 객체가 아닌 특정 문자열을 잠금을 거는 것이다. 예시를 보면 이해가 빠를 것이다.

 

// "mylock"이라는 문자열에 대해 잠금 획득
// 만약 이 잠금이 사용중이라면 2초 동안 대기 (2초후 자동 잠금 해제)
mysql> SELECT GET_LOCK('mylock', 2);

// "mylock"이라는 문자열이 잠금 상태인지 확인
mysql> SELECT IS_FREE_LOCK('mylock');

// "mylock"이라는 문자열에 대해 획득했던 잠금 해제(반납)
mysql> SELECT RELEASE_LOCK('mylock');

 

세 함수 모두 정상적으로 락을 획득하거나 해제한 경우 1을, 아니면 NULL이나 0을 반환한다.

 

메타데이터 락

메타데이터 락은 데이터베이스 객체(테이블이나 뷰 등)의 이름이나 구조를 변경하는 경우 획득하는 잠금이다. 명시적으로 획득하거나 해제하는 것은 불가능하고 "RENAME TABLE tab_a TO tab_b" 같이 테이블의 이름 변경하는 경우 자동으로 획득한다. 해당 명령의 경우 원본 이름과 변경될 이름 둘 모두 한번에 잠금을 설정한다. 배치 프로그램에서 테이블을 바꾸는 경우가 많은데 다음 예시를 살펴보자.

 

// 배치 프로그램에서 임시 테이블(rank_new)에 서비스용 랭킹 데이터 생성
// 랭킹이 배치 완료되면 현재 서비스용 테이블(rank)를 rank_backup으로 백업
// 그리고 새로 만들어진 테이블로 대체
mysql> RENAME TABLE rank TO rank_backup, rank_new TO rank;

 

명령어가 두 RENAME 작업을 한번에 실행하면 애플리케이션에서는 rank 테이블을 찾지 못하는 에러를 발생시키지 않고 적용하는 것이 가능하다. 만약 두 작업을 따로 실행한다면 작업 사이에 rank 테이블을 찾지 못하는 에러가 발생할 것이다.

 

메타테이터 잠금과 InnoDB 트랜잭션을 동시에 사용해야 하는 경우도 있다. INSERT만 실행되는 로그 테이블을 가정해 보자.

 

mysql> CREATE TABLE access_log (
        id BIGINT NOT NULL AUTO_INCREMENT,
        client_ip INT UNSIGNED,
        ...
        PRIMARY KEY(id)
       );

 

그런데 어느날 테이블 구조를 변경해야 하는 요구사항이 생겼다. MySQL 서버의 Online DDL을 사용해 변경할 수도 있지만 여러 요소를 고려해야한다. 심지어 MySQL 서버의 Online DDL은 단일 스레드로 동작하여 많은 시간이 소요된다. 이때는 다음과 같은 방법을 사용한다.

 

1. 새로운 구조의 테이블을 생성한다.

2. 최근(1시간 직전 또는 하루 전 등)의 데이터까지 PK인 id값을 범위로 나눠 여러 스레드로 빠르게 복사한다.

3. 나머지 데이터는 트랜잭션과 테이블 잠금, RENAME TABLE 명령으로 애플리케이션 중단 없이 복사.

// 새로운 구조의 테이블 생성
mysql> CREATE TABLE access_log_new (
        id BIGINT NOT NULL AUTO_INCREMENT,
        client_ip INT UNSIGNED,
        ...
        PRIMARY KEY(id)
      ) KEY_BLOCK_SIZE=4;
      
// 여러개의 스레드를 이용해 id 범위별로 레코드를 신규 테이블로 복사
mysql_tread1> INSERT INTO access_log_new SELECT * FROM access_log WHERE id>=0 AND id<10000;
mysql_tread2> INSERT INTO access_log_new SELECT * FROM access_log WHERE id>=10000 AND id<20000;
mysql_tread3> INSERT INTO access_log_new SELECT * FROM access_log WHERE id>=20000 AND id<30000;

// autocommit으로 트랜잭션 실행
mysql> SET autocommit=0;

// 작업 대상 테이블에 대해 쓰기 락 획득
mysql> LOCK TABLES access_log WRITE, access_log_new WRITE;

// 남은 데이터 복사
mysql> SELECT MAX(ID) as @MAX_ID FROM access_log;
mysql> INSERT INTO access_log_new SELECT * FROM access_log WHERE pk>@MAX_ID;
mysql> COMMIT;

// 새로운 테이블을 적용
mysql> RENAME TABLE access_log TO access_log_old, access_log_new TO access_log;
mysql> UNLOCK TABLES:

// 불필요한 테이블 삭제
mysql> DROP TABLE access_log_old;

InnoDB 스토리지 엔진

InnoDB 스토리지 엔진의 잠금

레코드 락

레코드 자체만을 잠그는 것을 의미한다. InnoDB 스토리지 엔진에서는 인덱스의 레코드를 잠근다. 인덱스가 하나도 없다면 자동 생성된 클러스터드 인덱스를 사용한다. 

 

유니크 인덱스에 의한 변경 작업은 레코드 락만 사용한다. 하지만 보조 인덱스를 이용한 변경은 대부분  넥스트 키 락 또는 갭락을 사용한다.

갭 락

레코드와 바로 인접한 레코드 사이의 간격을 잠그는 것을 의미한다. 갭 락의 역할은 레코드와 레코드 사이의 간격에 새로운 레코드가 삽입(Insert) 되는 것을 제어하는 것이다.

넥스트 키 락

레코드 락과 갭락을 합쳐 놓은 형태의 잠금을 의미한다.

 

InnoDB의 기본 격리 수준은 REPEATABLE READ이다. 하지만 넥스트 키 락을 사용하기 때문에 Phantom Read는 발생하지 않는다.

자동 증가 락

AUTO_INCREMENT 칼럼 속성을 사용할 때 동시에 여러 레코드가 INSERT되는 경우, 중복되지 않는 칼럼을 갖도록 잠금을 거는 것이다.

인덱스와 잠금

InnoDB의 락은  레코드가 아닌 인덱스를 잠그는 것이다. 즉, 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 한다. 다음 예를 살펴보자

mysql> SELECT COUNT(*) FROM employees WHERE first_name='John';

+-------+
|    250|
+-------+

mysql> SELECT COUNT(*) FROM employees WHERE first_name='John' AND last_name='James';

+-------+
|      1|
+-------+

mysql> UPDATE employees SET hire_date=NOW() WHERE first_name='John' AND last_name='James';

first_name에 대한 인덱스가 있다고 가정하자. first_name이 John인 레코드는 250개이지만 last_name이 Jame인 레코드는 하나이다. 하지만 마지막 UPDATE문을 실행하면 last_name에 대한 인덱스가 없으므로 250개의 레코드가 잠긴다.