[MySQL] MySQL 엔진의 잠금

MySQL에서 사용되는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다.

 

MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치지만, 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간 상호 영향을 미치지 않

는다.

 

MySQL 엔진에서는 테이블 데이터 동기화를 위한 테이블 락 이외에도 테이블의 구조를 잠그는 메타데이터 락,

그리고 사용자 필요에 맞게 사용할 수 있는 네임드 락을 제공하는데, 이번에는 MySQL 엔진 레벨의 잠금에 대해 공부해보았다.

 

글로벌 락

글로벌 락은 MySQL이 제공하는 잠금 중에서 가장 범위가 크다.

일단 한 세션에서 글로벌 락을 획득하면, 다른 세션에서 실행한 SELECT를 제외한 대부분의 DDL과 DML이 대기 상태로 남는다.

글로벌 락이 미치는 범위는 MySQL 서버 전체이며, 작업 대상 테이블이나 데이터베이스가 다르더라도 영향을 미친다.

글로벌 락은 "FLUSH TABLES WITH READ LOCK" 명령으로 획득할 수 있는데,
이 명령은 해당 테이블에 읽기 잠금을 걸기 위해 먼저 실행된 SQL과 그 트랜잭션이 완료될 때 까지 기다려야한다.
따라서 장시간 실행되는 쿼리와 글로벌 락 명령이 겹치면 오랜 시간동안 MySQL서버의 모든 데이터에 대한 CRUD가 멈출 수도 있다.
따라서 글로벌 락 명령어는 웹 서비스 용으로 사용되는 MySQL 서버에서 가급적 사용하지 않는 것이 좋다.

 

MySQL 서버가 업그레이드되면서 InnoDB 스토리지 엔진의 사용이 일반화되었는데, InnoDB 스토리지 엔진은 트랜잭션을 지원하기 때문에 일관된 데이터 상태를 위해 모든 데이터 변경 작업을 멈출 필요는 없다.

따라서 MySQL에는 조금 더 가벼운 글로벌 락의 필요성이 생겼고, 백업 락이 등장하였다.

 

특정 세션에서 백업 락을 획득하면 일반적인 테이블의 데이터 변경은 허용되지만,

사용자 관리 기능 및 데이터베이스/테이블의 생성/삭제 등 스키마 변경 기능 등이 차단된다.

 

백업 락을 적용하기 좋은 상황으로는 백업 상황이 있는데,

만약 백업 시에 글로벌 락을 적용했다고 가정해보면, 레플리카 서버에서 백업을 하는 동안 소스서버에 문제가 생기면 레플리카 서버의 구조가 변경될 때 까지 서비스를 멈춰야 할 수도 있다.

이런 경우에는 범위가 더 작은 백업 락이 실용적인 선택이 될 수 있다.

 

테이블 락

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

명시적으로는 "LOCK TABLES table_name [READ | WRITE]" 명령으로 획득할 수 있는데, InnoDB 엔진을 사용하는 테이블에도 사용 가능하다.

이 명시적 테이블 락은 특별한 상황이 아니면 사용할 일이 거의 없다. (글로벌 락과 같이 다른 작업에 영향을 많이 미침)

 

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

이 테이블 락은 쿼리 실행동안 자동으로 획득되었다가 쿼리 완료 시 자동으로 해제된다.

InnoDB 기반 테이블의 경우 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 단순한 데이터 변경 쿼리로는 묵시적인 테이블 락이 생성되지 않는다. (자세히는 DDL의 경우만 영향을 미침)

 

네임드 락

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

이 락의 특징은 대상이 테이블이나 레코드, 데이터베이스 객체가 아니라 단순히 사용자가 지정한 문자열이라는 것이다.

자주 사용되지는 않지만, 여러 클라이언트가 상호 동기화를 처리해야 할 때 사용하면 쉽게 처리할 수 있다.

 

메타데이터 락

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

메타데이터 락은 명시적으로 획득하거나 해제하는 것이 아니고, "RENAME TABLE tab_a To tab_b" 같이 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금이다.

 

헷갈릴 수 있는 부분이, 이런 DDL 뿐만 아니라 DML의 경우에도 메타데이터의 락을 획득할 수 있다.

 

SELECT, INSERT, UPDATE, DELETE와 같은 쿼리가 실행되면 공유 락을,

ALTER, DROP, RENAME과 같은 DDL 작업은 배타 락을 획득하는데,

중요한건 여기서 말하는 공유 락과 배타 락은 흔히 말하는 레코드 레벨의 공유 락, 배타 락과는 다른 개념인 테이블 메타데이터 레벨의 락이다.

 

만약 하나의 객체에 여러 세션이 동시에 접근하려고 하는 경우, 우선순위에 따라 락이 획득된다,

일반적으로 쓰기 락이 읽기 락보다 우선순위가 높으며, 쿼리는 메타데이터 락을 하나씩 순차적으로 획득하며 교착상태를 감지한다.

 

DDL 명령어의 경우 DDL 작업 간의 교착 상태를 줄이기 위해 테이블 이름 순서대로 락을 획득하기도 한다.

 

MySQL 메타데이터 락 공식 문서에 수록된 동작 예시를 보자.

//client 1 : x와 x_new에 대한 쓰기 잠금 획득
LOCK TABLE x WRITE, x_new WRITE;

//client 2 : x에 데이터 삽입 시도
INSERT INTO x VALUES(1);

//client 3 : x와 x_new의 테이블 이름 변경 시도
RENAME TABLE x TO x_old, x_new TO x;

//client 1 : 쓰기 락 해제
UNLOCK TALBES;

이 경우, x, x_new 순으로 쓰기 락을 획득한다.

client2 와 client 3의 쿼리는, client1이 락을 보유하고 있으므로  x테이블에 대한 대기 상태에 빠지고,

client 1이 쓰기 락을 해제하면 client 3의 DDL이 먼저 실행되고 이후 client 2의 DML이 실행된다.

 

이를 통해 락 획득 대기중인 테이블이 같은 경우 DDL이 DML보다 우선순위가 높아 먼저 실행되는걸 볼 수 있다.

 

비슷한듯 다른 예시를 보면 

//client1
LOCK TABLE x WRITE, new_x WRITE;

//client2
INSERT INTO x VALUES(1);

//client3
RENAME TABLE x TO old_x, new_x TO x;

//client1
UNLOCK TABLES;

이 경우에는 UNLOCK 이후 대기중인 INSERT와 RENAME 중 어떤 명령이 먼저 실행될까 ?

LOCK 명령문에서 테이블 이름 순서인 new_x, x 순으로 락을 획득한다.

클라이언트 2는 x에 대한 대기상태에 들어가고,

클라이언트 3은 테이블 이름순대로 new_x -> old_x -> x 순으로 락을 요청하게 된다. (new_x에 대한 대기)

 

락 해제 시 클라이언트 2는 x 테이블의 락을 획득하게 되고, 클라이언트 3은 insert 작업이 종료되어야 x에 대한 락을 획득한다.

따라서 락이 걸린 테이블이 서로 다르면, 락을 먼저 요청한 트랜잭션이 우선임을 알 수 있다.