MySQL 엔진의 잠금

  MySQL에서 사용되는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있습니다. MySQL 엔진은 MySQL 서버에서 스토리지 엔진을 제외한 나머지 부분으로 이해하면 되는데, MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치지만, 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간 상호 영향을 미치지 않습니다. MySQL 엔진에서는 테이블 데이터 동기화를 위한 테이블 락 이외에도 테이블의 구조를 잠그는 메타데이터 락 그리고 사용자의 필요에 맞게 사용할 수 있는 네임드 락이라는 잠금 기능도 제공합니다. 

 

글로벌 락

 FLUSH TABLES WITH READ LOCK 명령어로 획득 할 수 있으며, MySQL에서 제공하는 잠금 가운데 가장 범위가 큽니다.

일단 한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 대부분의 DDL 문장을 실행하는 경우 글로벌 락이 해제될 때까지 해당 문장이 대기 상태로 남게 됩니다. 글로벌 락이 영향을 미치는 법위는 MySQL 서버 전체이며, 작업 대상 테이블이나 데이터베이스가 다르더라도 동일하게 영향을 미친다. 여러 데이터베이스에 존재하는 MyISAM이나  데이터베이스가 다르더라도 동일하게 영향을 미칩니다., 여러 데이터베이스에 존재하는 MyISAM이나 MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받아야 할 때는 굴로벌 락을 사용해야 합니다.

 

 InnoDB거 기본 스토리지 엔진으로 채택되면서 조금 더 가벼운 그로벌 락의 필요성이 느껴졌고. MySQL 8.0 버전부터는 Xtrabackup이나 Enterprise Backup과 같은 백업 툴들의 안정적인 실행을 위해 백업 락이 도입됬습니다.

 

테이블락

 개별 테이블 단위로 설정되는 잠금이며, 명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있습니다.

명시적으로는 "LOCK TABLES table_name[READ : WRITE]" 명령으로 특정 테이블의 락을 획득할 수 있습니다. 테이블 락은 MyISAM뿐 아니라 InnoDB 스토리지 엔진을 사용하는 테이블도 동일하게 설정할 수 있습니다.

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

 

 묵시적인 테이블 락은 MyISAM이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생합니다. MySQL 서버가 데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후, 즉시 잠금을 해제하는 형태로 사용됩니다. 즉, 묵시적인 테이블 락은 쿼리가 실행되는 동안 자동으로 획득됐다가 쿼리가 완료된 후 자동으로 해제됩니다. 하지만 InnoDB 테이블의 경우 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 단순 데이터 벼경 쿼리로 인해 묵시적인 테이블 락이 설정되지는 않습니다. 더 정확히는 InnoDB 테이블에도 테이블 락이 설정되지만 대부분의 데이터 변경(DML) 쿼리에서는 무시되고 스키마를 변경하는 쿼리(DDL)의 경우에만 영향을 미칩니다.

 

네임드락

 GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있습니다. 이 잠금의 특징은 대사잉 테이블이나 레코드 또는 AUTO_INCREMENT와 같은 데이터베이스 객체가 아니라는 것입니다. 네임드 락은 단순히 사용자가 지정한 문자열(String)에 대해 획득하고 반남하는 잠금입니다. 네임드 락은 자주 사용되지는 않습니다. 예를 들어, 데이터베이스 서버 1대에 5대의 웹 서버가 접속해서 서비스하는 상황에서 5대의 웹 서버가 어떤 정보를 동기화해야 하는 요건처럼 여러 클라이언트가 상호 동기화를 처리해야 할 떄 네임드 락을 이용하면 쉽게 해결할 수 있습니다.

 

-- // "mylock" 이라는 문자열에 대해 잠금을 획득한다.

-- // 이미 잠그을 하숑 중이면 2초 동안만 대기한다.(2초 이후 자동 잠금 해제됨)

mysql>SELECT GET_LOCK('mylock',2);

 

-- // "mylock"이라는 문자열에 대해 장금이 설정돼 있는지 확인한다.

mysql> SELECT IS_FREE_LOCK('mylock');

 

-- // "mylock"이라는 문자열에 대해 획득했던 잠금을 반납한다.

mysql> SELECT RELEASE_LOCK('mylock');

 

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

 

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

 

mysql> SELECT GET_LOCK('mylock_1',10);

-- // mylock_1에 대한 작업 실행

mysql> SELECT GET_LOCK('mylock_2',10);

-- // mylock_2에 대한 작업 실행

 

mysql>SELECT RELEASE_LOCK('mylock_2');

mysql>SELECT RELEASE_LOCK('mylock_1');

 

-- //mylock_1과 mylock_2를 동시에 모두 해제하고자 한다면 RELEASE_ALL_LOCKS() 함수 사용

mysql> SELECT RELEASE_ALL_LOCKS();

 

메타데이터 락

데이터베이스 객체의 이름이나 구조를 변경하는 경우에 획득하는 잠금입니다. 메타데이터 락은 명시적으로 획득하거나 해제할 수 있는 것이 아니고 "RENAME TABLE tab_a TO tab_b" 같이 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금입니다. RENAME TABLE 명령의 경우 원본 이름과 변경될 이름 두 개 모두 한꺼번에 잠금을 설정한다. 또한 실시간으로 테이블을 바꿔야 하는 요건이 배치 프로그램에서 자주 발생하는데,

 

-- //배치 프로그램에서 별도의 임시 테이블(rank_new)에 서비스용 랭킹 데이터를 생성

-- //랭킹 배치가 완료되면 현재 서비스용 랭킹 테이블(rank)을 rank_backup으로 백업하고

--// 새로 만들어진 랭킹 테이블(raw_new)을 서비스용으로 대체하고자 하는 경우

mysql> RENAME TABLE rank TO rank_backup, rank_new TO rank;

 

위와 같이 하나의 RENAME TABLE 명령문에 두 개의 RENAME 작업을 한꺼번에 싱행하면 실제 애플리케이션에서는 "Table not found 'rank'" 같은 상황을 발생시키지 않고 적용하는 것이 가능합니다. 하지만 이 문장을 다음과 같이 2개로 나눠서 실행하면 아주 짧은 시간이지만 rank 테이블이 존재하지 않는 순간이 생기며, 그 순간에 실행되는 쿼리는 "Table not found 'rank'" 오류를 발생시킵니다.

 

mysql> RENAME TABLE rank TO rank_backup;

myslq> RENAME  TABLE rank_new TO rank;

 

 때로는 메타데이터 잠금과 InnoDB의 투랜잭션을 동시에 사용해야 하는 경우도 있습니다. 예를 들어, 다음과 같은 구조의 INSERT만 실행되는 로그 테이블을 가정해보면, 이 테이블은 웹 서버의 액세스 로그를 저장만 하기 때문에 UPDATE와 DELETE가 없습니다.

 

mysql>CREATE TABLE access_log(

                id BIGINT NOT NULL AUTO_INCREMENT,

                client_ip INT USINGNED,

                access_dttm TIMESTAMP,

                ...

                PRIMARY KEY(id)

                );

 

 그런데 어느 날 이 테이블의 구조를 변경해 할 요건이 발생했다면, 물론 MySQL 서버의 Online DDL을 이용해서 변경할 수도 있지만 시간이 너무 오래 걸리는 경우라면 언두 로그의 증가와 Online DDL이 실행되는 동안 누적된 Online DDL 버퍼의 크기 등 고민해야 할 문제가 많습니다. 더 큰 문제는 MySQL 서버의 DDL은 단일 스레드로 작동하기 때문에 상당히 많은 시간이 소모될 것입니다. 이때는 새로운 구조의 테이블을 생성하고 먼저 최근(1시간 직전 또는 하루 전)의 데이터까지는 프라이머리 키인 id 값을 범위별로 나눠서 여러 개의 스레드로 빠르게 복사합니다.

 

--// 테이블의 압축을 적용하기 위해서 KEY_BLOCK_SIZE=4 옵션을 추가해 신규 테이블을 생성

mysql>CREATE TABLE  access_log_new(

                id BIGINT NOT NULL AUTO_INCREMENT,

                client_ip INT USINGNED,

                access_dttm TIMESTAMP,

                ...

                PRIMARY KEY(id)

                ) KEY_BLOCK_SIZE=4;

--// 4개의 스레드를 이용해 ID 범위별로 레코드를 신규 테이블로 복사

mysql_thread1> INSERT INTO access_log_new SELECT * FROM access_log WHERE id>=0 AND id<10000;

mysql_thread2> INSERT INTO access_log_new SELECT * FROM access_log WHERE id>=10000 AND id<20000;

mysql_thread3> INSERT INTO access_log_new SELECT * FROM access_log WHERE id>=20000 AND id<30000;

mysql_thread4> INSERT INTO access_log_new SELECT * FROM access_log WHERE id>=30000 AND id<40000;

 

그리고 나머지 데이터는 다음과 같이 트랜잭션과 테이블 잠금, RENAME TABLE 명령으로 응용 프로그램의 중단 없이 실행할 수 있습니다. 이때 "남은 데이터를 복사"하는 시간 동안은 테이블의 잠금으로 인해 INSERT를 할 수 없게 됩니다. 그래서 가능하면 미리 아주 최근 데이터까지 복사해 둬야 잠금 시간을 최소화해서 서비스에 미치는 영향을 줄일 수 있습니다.

-- // 트랜잭션을 autocomit으로 실행(BEGINE이나 START TRANSACTION으로 실행하면 안됨)

mysql> SET autocommit=0;

 

-- // 작업 대상 테이블 2개에 대해 테이블 쓰기 락을 획득

mysql>LOCK TABLES access_log WRITE, access_log_new WRITE;

 

-- // 데이터를 복사

mysql>SELECT MAX(id) as @MAX_ID FROM access_log_new;

mysql>INSERT INTO access_log_new SELECT *FROM access_log WHERE pk>MAX_ID;

mysql>COMMIT;

 

-- // 새로운 테이블로 데이터 복사가 완료되면 RENAME 명령으로 새로운 테이블을 서비스로 투입

mysql> RENAME TABLE access_log TO access_log_old,access_log_new TO access_log;

mysql> UNLOCK TABLES;

 

-- // 불필요한 테이블 삭제

mysql> DROP TABLE access_log_old;