영암사는 승경이네

개요

 MySQL의 잠금은 크게 두가지로 MySQL 엔진, InnoDB 스토리지 엔진 잠금이 있다. 이번 포스팅에서는 먼저 MySQL 엔진에 대한 잠금을 알아보자.

MySQL 엔진과 InnoDB 스토리지 엔진이 뭔가요?

MySQL 엔진과 InnoDB 스토리지 엔진은 MySQL 서버의 구성요소이다. MySQL 엔진은 쿼리 파싱, 최적화, 실행 뿐 아니라 캐시, 커넥션과 같은 부가 기능들을 관리하고, InnoDB 스토리지 엔진은 실제 데이터의 저장, 디스크 I/O, 인덱스 관리와 같은 하드웨어 처리를 담당한다. 즉, MySQL 엔진이 쿼리를 실행하면, InnoDB 스토리지 엔진은 데이터를 읽고, 쓰는 역할을 하는것이다.

MySQL 엔진 잠금

1.  글로벌 락

 글로벌 락은 SELECT를 제외한 DDL이나 DML 문장에 대한 락으로, MySQL 서버에 존재하는 모든 테이블과 DB에 영향을 미치는 락이다. FLUSH TABLES WITH READ LOCK 명령으로 락을 획득한다.

 다만, 이미 UPDATE와 같은 DML 트랜잭션이 진행중이라면 끝날때까지 대기해야하는데, 읽기 잠금을 획득해야하기 때문이다. 또한 다른 트랜잭션에서 장시간 SELECT 쿼리를 실행하는 상황에서도 대기해야한다.

 글로벌 락은 mysqldump와 같이 일관된 백업을 받아야할 때 사용하곤 한다.

DDL (Data Definition Language)
데이터 구조(스키마)를 정의/변경하는 명령어이다.

DML (Data Manipulation Language)
데이터 자체 조작하는 명령어로 SELECT, INSERT, UPDATE, DELETE 가 있다.

SELECT 쿼리를 기다려야 하는 이유?

FLUSH TABLES WITH READ LOCK을 끊어 읽어보면 FLUSH, READ LOCK 이라는 단어가 눈에 띈다. FLUSH는 버퍼 풀의 레코드를 디스크에 저장하는 작업을, READ LOCK은 읽기 잠금을 뜻한다. 읽기 잠금인데 다른 트랜잭션에서 발생하는 SELECT 쿼리에 영향을 받는 이유는 뭘까? 글로벌 락은 당연하게도 READ LOCK 뿐 아니라 METADATA LOCK을 획득해야 하고, 더 나아가 MySQL의 테이블 핸들이 닫혀있어야 한다. SELECT 쿼리는 테이블 핸들을 필요(열어야하는)로 하는 작업이다.

테이블 핸들 (Table Handle)
접근하는 테이블을 가리키는 내부 객체이다. 쿼리를 통해 테이블을 액세스 하면 테이블 핸들이 열리고, 트랜잭션이 끝나면 핸들이 닫힌다.

글로벌 락 테스트해보기

총 두가지를 테스트해볼 것이다. 첫번째는 글로벌 락이 걸렸을 때 읽기 잠금이 걸리는지, 두번째는 테이블 핸들이 열렸을 때 글로벌 락을 획득하지 못하고 대기하는지이다.

1) 글로벌 락이 걸렸을 때 읽기 잠금 

1번 트랜잭션에서 글로벌 락 획득

# transaction 1

start transaction; // 트랜잭션 시작

FLUSH TABLES WITH READ LOCK; // 글로벌 락

2번 트랜잭션에서 읽기 잠금 테스트

# transaction 2

start transaction;

SELECT * FROM TBL_USER; //성공 : 테이블 데이터 조회됨

UPDATE TBL_USER SET NAME = '승경' WHERE USER_ID = 1; // 실패 : 글로벌 락으로 인함

2) 테이블 핸들이 열렸을 때 글로벌 락 획득 대기

1번 트랜잭션에서 select sleep from [table] 쿼리를 통해 테이블 핸들 오픈

# transaction 1 

start transaction;

select sleep(10) from tbl_user; // tbl_user 에 로우 개수만큼 10초씩 sleep (테이블 핸들을 열기 위함)

2번 트랜잭션에서 글로벌 락 획득 시도

# transaction 2

start transaction;

FLUSH TABLES WITH READ LOCK; // 실패 또는 SELECT 작업이 끝난 후 성공

2.  테이블 락

테이블 락은 테이블 단위로 설정되는 잠금으로 명시적, 묵시적으로 락을 획득할 수 있다. 명시적 테이블 락은 'LOCK TABLES [TABLE NAME] [ READ or WRITE ]' 명령으로 획득할 수 있다. 단, 테이블 락은 어플리케이션에 상당한 영향을 끼치므로 실무에서는 거의 사용되지 않는다.

 묵시적 테이블 락은 DDL 쿼리 실행 시 발생하며, 읽기/쓰기를 모두 차단한다. DML 쿼리에 대해서는 스토리지 엔진에 따라 달리 동작하는데, MyISAM 또는 MEMORY 스토리지 엔진의 경우 자동으로 테이블 락이 발생한다. 데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후, 즉시 잠금이 해제되는 프로세스이다. InnoDB 스토리지 엔진은 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 단순 DML 쿼리로 테이블 락이 설정되지 않는다.

DDL 쿼리도 트랜잭션처럼 Commit 해야 반영되나요?

맞다. Commit을 해야 실제로 반영된다. 다만, DDL 쿼리는 Auto Commit 으로 처리된다.

테이블 락  테스트해보기

테이블에 쓰기 잠금을 걸었을 때 테이블 데이터를 조회, 수정하는 쿼리를 날려보자.

1) 1번 트랜잭션에서 WRITE LOCK 실행

start transaction;

LOCK TABLES tbl_user WRITE; // tbl_user에 대한 Write Lock

2) 2번 트랜잭션에서 SELECT 및 UPDATE 쿼리 실행

start transaction;

select * from tbl_user; // 실패 : 테이블 Write Lock 으로 인함
update tbl_user set name = '승경' where user_id = 1; // 실패 : 테이블 Write Lock 으로 인함

3.  네임드 락

 네임드락은 임의의 문자열에 대한 잠금으로, GET_LOCK() 함수를 사용한다. 이 잠금은 테이블이나 레코드, 데이터 베이스 객체가 아닌 단순히 사용자가 지정한 문자열에 대한 잠금을 획득하고 반납하는 잠금이다. 반환은 RELEASE_LOCK() 함수를 사용한다.

 네임드 락을 사용하는 예는 다중 어플리케이션에서의 배치 실행이다. 다중 어플리케이션에서 한 번의 배치만 돌게 하기 위해 네임드 락을 사용하기도 한다.

SELECT GET_LOCK('batch_lock', 0); // batch_lock 획득 시도, 획득 대기시간 없음(0초)

 여러 어플리케이션에서 동시에 batch_lock 이라는 네임드 락 획득을 시도하면 최초 획득한 트랜잭션의 어플리케이션만 배치가 돌고, 나머지는 대기 시간이 없어 실패와 함께 배치 실행을 하지 않게 된다.

 네임드 락을 사용하는 또 다른 예는 비지니스 로직에 대한 잠금 범위를 지정하고 싶을때이다. 특정 로직이 실행되기 전 네임드 락을 획득하고, 로직이 끝난 후 네임드 락을 반환한다면, 멀티 스레드 환경에서 해당 로직이 순차적으로 처리될것이다.

// 비지니스 로직 시작
...
SELECT GET_LOCK('service_1', 100); // 네임드 락 획득
...
// 비지니스 로직 종료
...
SELECT RELEASE_LOCK('service_1'); // 네임드 락 반환

단, 락을 획득한 후 해제해주지 않으면 시스템이 멈추는 위험을 초래할 수 있고, 락 이름 충돌 가능성이 있으므로 구분할 수 있는 prefix를 붙여주는 것이 좋다.

4.  메타 데이터 락

 메타 데이터 락은 테이블이나 뷰 등 DB 객체의 이름이나 구조를 변경하는 경우에 획득하는 잠금이다. 이 락은 자동으로 획득/반환되는 묵시적 잠금이다. 앞서 설명한 글로벌 락, 테이블 락이 발생할 경우 메타 데이터 락도 함께 획득한다.