MySQL 엔진의 잠금

MySQL에서 사용되는 Lock은 크게 Storage Engine/MySQL Engine 레벨로 나눌 수 있다.

MySQL 엔진은 MySQL 서버에서 스토리지 엔진을 제외한 나머지 부분으로 이해하면 되는데, MySQL 레벨의 잠금은 모든 스토리지 엔진에 영향을 주지만, 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간 영향을 주지 않는다.

MySQL 엔진에서는 테이블 데이터 동기화를 위한 Table Lock 이외에도 테이블의 구조를 잠그는 Metadata Lock, 사용자의 필요에 맞게 사용할 수 있는 Named Lock이라는 잠금 기능을 제공한다.

글로벌 락

글로벌 락(Global Lock)은 다음 명령으로 획득할 수 있다. MySQL에서 제공하는 잠금 중 가장 범위가 크다.

flush tables with read lock

한 세션에서 글로벌 락을 획득하면 다른 세션에서 select를 제외한 대부분의 DDL, DML 실행은 글로벌 락이 해제될 때까지 대기 상태로 남는다. 글로벌 락이 영향을 미치는 범위는 MySQL 서버 전체이며, 작업 대상 테이블이나 DB가 다르더라도 동일하게 영향을 준다.

여러 DB에 존재하는 MyISAM이나 MEMORY 테이블에 대해 mysldump로 일관된 백업을 받아야 하는 경우에는 글로벌 락을 사용해야 한다.

백업 락

백업 락은 MySQL 8.0 버전부터 도입된 더 가벼운 형태의 글로벌 락이다.

글로벌 락은 MySQL 서버의 모든 변경 작업을 멈춘다. 하지만 InnoDB 스토리지 엔진은 트랜잭션을 지원하기 때문에 일관된 데이터 상태를 위해 모든 데이터 변경 작업을 멈출 필요는 없다.

그래서 8.0 버전부터는 Xtrabackup, Enterprise Backup과 같은 백업 툴들의 안정적인 실행을 위해 백업 락이 도입됐다.

mysql> LOCK INSTANCE FOR BACKUP;   // 백업 실행
musql> UNLOCK INSTANCE;

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

• DB 및 Table 등 모든 객체 생성 및 변경, 삭제

• REPAIR Table과 Optimize table 명령

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

백업 락은 일반적인 테이블의 데이터 변경은 허용된다.

일반적인 MySQL 서버는 source/replica 서버로 구성되는데, 주로 백업은 replica 서버에서 실행된다.

하지만 백업이 flush tables with read lock 명령으로 글로벌 락을 획득하면 복제는 백업 시간만큼 지연될 수밖에 없다. replica 서버에서 백업을 실행하는 도중 source 서버에 문제가 생기면 replica 서버의 데이터가 최신 상태가 될 때까지 서비스를 멈춰야 할 수도 있다.

Xtrabackup, Enterprise Backup 툴로 복제가 진행되는 상태에서도 일관된 백업을 만들 수 있지만, 도중에 스키마 변경이 실행되면 백업이 실패하게 된다. 그러면 그만큼 시간들 들여서 백업을 실행해야 한다.

MySQL 서버의 백업 락은 이를 위해 도입되었으며, 백업 락은 복제를 정상적으로 실행하면서, DDL 명령이 실행될 경우 복제를 일시 중지하여 백업의 안정성을 보장한다.

테이블 락

Table Lock은 테이블 단위로 설정되는 잠금이며 특정 테이블의 락을 획득할 수 있다.

// 명시적으로 획득
LOCK TABLES table_name [READ | WRITE]

// 명시적으로 획득한 Lock 해제
UNLOCK TABLES

테이블 락은 MyISAM, InnoDB 스토리지 엔진을 사용하는 테이블에서 동일하게 설정 가능하다.

명시적인 테이블 락도 특별한 상황이 아니면 애플리케이션에서 사용할 필요가 거의 없다. 명시적으로 테이블을 잠그는 작업은 글로벌 락과 동일하게 온라인 작업에 큰 영향을 미치기 때문이다.

묵시적인 테이블 락은 MyISAM, MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생한다.

MySQL 서버가 데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후, 즉시 잠금을 해제하는 형태로 사용된다. 즉, 묵시적 테이블 락은 쿼리가 실행될 때 자동으로 획득하고 쿼리가 완료된 후 자동으로 해제된다.

하지만 InnoDB 테이블의 경우 레코드 기반 잠금을 제공하기 때문에 단순한 데이터 변경 쿼리로 인해 묵시적인 테이블 락이 설정되지 않는다.

정확히는 InnoDB 테이블에도 테이블 락이 설정되지만 대부분의 데이터 변경(DML) 쿼리에서는 무시되고, 스키마를 변경하는 쿼리(DDL)의 경우에 영향을 미친다.

네임드 락

Name Lock은 GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있다.

이 Lock의 특징은 대상이 테이블이나 레코드 또는 AUTO_INCREMENT와 같은 DB 객체가 아니라는 것이다.

네임드 락은 사용자가 지정한 문자열(String)에 대해 획득하고 반납(해제)하는 잠금이다.

자주 사용되지 않지만 예를 들어, DB 서버 1대에 5대의 웹 서버가 접속해서 서비스하는 상황에서 5대의 웹 서버가 어떤 정보를 동기화해야 하는 요건처럼 여러 클라이언트가 상호 동기화를 처리할 때 네임드 락을 이용하면 쉽게 해결할 수 있다.

// "mylock"이라는 문자열에 대해 잠금을 획득한다.
// 이미 잠금을 사용중이면 2초 동안만 대기한다. (2초 후에 해제)
select get_lock('mylock', 2);

// "mylock"이라는 문자열에 대해 잠금이 설정되어 있는지 확인
select is_free_lock('mylock');

// "mylock"이라는 문자열에 대해 획득했던 잠금을 해제
select release_lock('mylock');

// 3개 함수 모두 정상적으로 락을 획득하거나 해제한 경우 1을, 아닌 경우 null 또는 0을 반환한다.

네임드 락의 경우 많은 레코드에 대해 복잡한 요건으로 레코드를 변경하는 트랜잭션에서 유용하게 사용할 수 있다.

배치 프로그램처럼 한꺼번에 많은 레코드를 변경하는 쿼리는 빈번한 데드락의 원인이 되곤 한다.

각 프로그램의 실행 시간을 분산하거나 프로그램의 코드를 수정해서 데드락을 최소화할 수 있지만, 이는 간단한 방법이 아니면서도 완전한 해결책이 될 수 없다. 이러한 경우 동일 데이터를 변경하거나 참조하는 프로그램끼리 분류해서 네임드 락을 걸고 쿼리를 실행하면 해결할 수 있다.

MySQL 8.0 버전부터는 네임드 락을 중첩해서 사용할 수 있으며, 현재 세션에서 획득한 네임드 락을 한 번에 모두 해제하는 기능도 추가됐다.

select get_lock('mylock_1', 10);
select get_lock('mylock_2', 10);

select release_lock('mylock_2');
select release_lock('mylock_1');

// 동시에 모두 해제
select release_all_locks();

메타데이터 락

Metadata Lock은 DB 객체(테이블, 뷰 등)의 이름이나 구조를 변경하는 경우 획득하는 잠금이다.

메타데이터 락은 명시적으로 획득하거나 해제할 수 없고, 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금이다.

rename table rank TO rank_backup, rank_new TO rank;

// 나눠서 실행: 오류 가능성
rename table rank TO rank_backup;
rename table rank_new TO rank;

rename 명령의 경우 원본 이름과 변경될 이름 두 개 모두 한꺼번에 잠금을 설정한다. 실시간으로 테이블을 바꿔야 하는 배치 프로그램에서 자주 발생하는데, 예시 처럼 하나의 rename table 명령문에 두 개의 rename 작업을 한꺼번에 실행하면 실제 애플리케이션에서는 “table not found rank”와 같은 상황을 발생시키지 않고 적용하는 것이 가능하다.

하지만 2개로 나눠서 실행하면 아주 짧은 시간이지만 rank 테이블이 존재하지 않는 순간이 생기며, 그 순간에 실행되는 쿼리는 “table not found ‘rank’” 오류를 발생시킨다.

create table access_log (
	id bigint ot null auto_increment,
	client_ip int unsigned,
	access_dttm timestamp,
	..
	primary key (id)
);

때로는 메타데이터 잠금과 InnoDB의 트랜잭션을 동시에 사용하는 경우도 있다.

위와 같은 구조에서 insert만 실행되는 로그 테이블에서, 웹 서버의 접근 로그만 저장하기 때문에 update/delete는 없다.

그런데 테이블 구조를 변경하는 일이 생겼다. MySQL 서버의 Online DDL을 이용하여 변경할 수 있지만 시간이 너무 오래 걸리는 경우라면 언두 로그의 증가와 Online DDL이 실행되는 동안 누적된 Online DDL 버퍼의 크기 등 고민해야 할 문제가 많다. 더 큰 문제는 MySQL 서버의 DDL은 단일 스레드로 작동하기 때문에 많은 시간이 소모된다.

// 테이블 압축을 위해 KEY_BLOCK_SIZE=4 옵션을 추가하여 테이블 생성
create table access_log_new (
	id bigint ot null auto_increment,
	client_ip int unsigned,
	access_dttm timestamp,
	..
	primary key (id)
) KEY_BLOCK_SIZE=4;

// 4개의 스레드를 이용해 id 범위별로 레코드를 신규 테이블로 복사
insert into access_log_new select * from access_log where id >= 0 and id < 1000;
insert into access_log_new select * from access_log where id >= 1000 and id < 2000;
insert into access_log_new select * from access_log where id >= 2000 and id < 3000;
insert into access_log_new select * from access_log where id >= 3000 and id < 4000;

이때는 새로운 구조의 테이블을 생성하고 최근(1시간 직전 또는 하루 전)의 데이터까지는 PK인 id 값을 범위별로 나눠 여러 개의 스레드로 빠르게 복사한다.

// 트랜잭션을 autocimmit으로 실행 (begin, start transaction으로 실행하면 안됨)
set autocommit=0;

// 작업 대상 테이블 2개에 대해 테이블 쓰기 락 획득
lock tables access_log write, access_log_new write;

// 남은 데이터 복사
select max(id) as @max_id from access_log;
insert into access_log_new select * from access_log where pk > @max_id;
commit;

// 새로운 테이블로 데이터 복사가 완료되면 rename 명령으로 새로운 테이블을 서비스로 투입
rename table access_log TO access_log_old, access_log_new TO access_log;
unlock tables;

// 불필요 데이터 삭제
drop table access_log_old;

그리고 나머지 데이터는 위와 같이 트랜잭션과 테이블 잠금, rename table 명령으로 응용 프로그램의 중단 없이 실행할 수 있다. 이때 “남은 데이터를 복사”하는 시간 동안은 테이블의 잠금으로 인해 insert를 사용할 수 없다.

그래서 가능하면 미리 아주 최근 데이터까지 복사해야 잠금 시간을 최소화해서 서비스에 주는 영향을 줄일 수 있다.