5.2 MySql 엔진의 잠금

MySql에서 사용되는 잠금은 크게 스토리지 엔진 레벨과 MySql엔진 레벨로 나뉨

MySql 엔진 레벨의 잠금

모든 스토리지 엔진에 영향을 미침

테이블락, 메타데이터 락, 네임드 락(Named Lock, 사용자 필요에 맞게 사) 등이 있음

스토리지 엔진 레벨의 잠금

스토리지 엔진 간 상호영향 미치지 않음

5.2.1 글로벌 락(Global Lock)

• 글로벌 락
  
  MySql에서 제공하는 잠금 중에 가장 범위가 큼.
  
  select 를 제외한 대부분의 ddl, dml이 락 해제 전까지 대기함.
  

-- 글로벌 락 획득 명령어
-- 모든 테이블을 잠금
flush tables with read lock;

-- 잠금 해제
unlock tables;

flush tables with read lock; 은 잠금 전에 테이블을 플러시 하기 때문에

테이블에 실행 중인 모든 쿼리가 종료되어야 함.



mysqldump 같은 백업 프로그램이 내부적으로 이 명령어를 실행하여 백업할 때도 있음.

InnoDB 스토리지 엔진은 트랜잭션을 지원하기 때문에 일관된 데이터 상태를 위해 모든 데이터 변경 작업을 멈출 필요가 없음.

또, 8.0 버전부터 InnoDB 가 기본 스토리지 엔진으로 채택되며 더 가벼운 글로벌 락의 필요성이 생김.

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

-- 백업을 위한 인스턴스 락
lock instance for backup;

unlock instance;

특정 세션에서 백업 락을 획득하면 모든 세션에서 다음과 같은 작업을 못함.

• 데이터베이스 및 테이블 등 모든 객체 생성 및 변경, 삭제(백업 중 스키마 변경이 있으면 백업이 실패함)
• REPAIR TABLE과 OPTIMIZE TABLE 명령
• 사용자 관리 및 비밀번호 변경

하지만 일반적인 테이블의 데이터 변경은 허용됨.

백업 락의 필요성

MySql 서버는 소스 서버(Source server)와 레플리카 서버(Replica server)로 구성되어 있음.

레플리카 서버가 주로 백업을 실행하는데 만약 백업을 위해 flush tables with read lock;을 해버리면 백업시간 만큼 복제가 늦어짐.

5.2.2 테이블 락(Table Lock)

개별 테이블 단위로 설정되는 락

명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있음.

명시적 : lock tables table_name [read | write]

묵시적 : MyISAM이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생

잠금 > 변경 > 해제 형태로 사용

InnoDB 테이블은 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 단순 데이터 변경 쿼리로 묵시적 테이블 락이 발생하지 않음.

(스키마를 변경하는 쿼리(DDL)의 경우에만 영향을 미침)

5.2.3 네임드 락(Named Lock)

네임드 락 참조

분산 락 참조

GET_LOCK()함수를 이용해 임의의 문자열에 대해 잠금을 설정.

쉽게 풀면 락에 이름을 붙여 해당 명칭에 대한 락을 획득하는 것.

잠금의 대상이 테이블이나 레코드, AUTO_INCREMENT 같은 데이터베이스 객체가 아님.

분산 락을 쉽게 구현할 수 있음.

네임드 락은 많은 레코드에 대해 복잡한 요건으로 레코들를 변경하는 트랜잭션에 유용.

동일한 데이터를 변경하거나 참조하는 프로그램끼리 분류해서 네임드 락을 걸고 쿼리를 실행하면 아주 간단히 해결함.

네임드 락은 중첩해서사용도 가능.

5.2.4 메타데이터 락

데이터베이스 객체의 이름이나 구조를 변경하는 경우에 획득하는 잠금.

테이블을 변경하는 경우 자동으로 획득하는 잠금.

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

-- 랭킹 배치가 완료되면 현재 서비스용 랭킹 테이블을 rank_bakcup으로 백업
-- 새로 만들어진 랭킹 테이블을 서비스용으로 대체
rename table rank to rank_backup , rank_new to rank;

하나의 rename table 명령문에 두개의 rename 작업을 한번에 실행하면 오류 없이 잘 실행되지만

rename table rank to rank_backup;, rename table rank_new to rank로 나눠 실행하면

짧은 시간동안 테이블을 존재하지 않는 순간이 생겨 오류가 발생.

메타데이터 잠금과 InnoDB 트랜잭션을 같이 사용해야 하는 경우
로그를 insert 만 하는 테이블을 구조변경이 필요할 때.

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

-- 테이블 압축을 적용하기 위해 key_block_size = 4옵션을 추가(key_block_size 는 6장에서 함)
create table access_log_new(
  id bigint not null auto_increment,
  client_ip int unsigned,
  access_dttm timestamp,
  ...
  primary key (id)
) key_block_size = 4;

-- 4개의 스레드를 이용해 id 범위별로 레코드를 신규 테이블로 복사
insert into acess_log_new select * from access_log where id >= 0 and id < 10000;
insert into acess_log_new select * from access_log where id >= 10000 and id < 20000;
insert into acess_log_new select * from access_log where id >= 20000 and id < 30000;
insert into acess_log_new select * from access_log where id >= 30000 and id < 40000;

--트랜잭션을 autocommit으로 실행
set autocommit = 0;

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

-- 남은 데이터 복사
select max(id) as @max_id from access_log_new;
insert into access_log_new select * from access_log where id > @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;