8. MySQL 엔진의 잠금

MySQL 엔진의 잠금

MySQL에서 사용되는 잠금은 크게 스토리지 엔진 레벨이나 MySQL 엔진 레벨로 나눠볼 수 있다. MySQL 엔진은 MySQL 서버에서 스토리지 엔진을 제외한 나머지 부분으로 이해하면 되는데, MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치게 되지만 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간 상호 영향을 미치지는 않는다. MySQL 엔진에서는 테이블 데이터 동기화를 위한 테이블 락 말고도 사용자의 필요에 맞게 사용할 수 있는 유저 락과 테이별 명에 대한 잠금을 위한 네임 락이라는 것도 제공한다. 이러한 잠금의 특징과 이러한 잠금이 어떤 경우에 사용되는지 한번 살펴보자.

1. 글로벌 락

글로벌 락(GLOBAL LOCK)은 "FLUSH TABLES WITH READ LOCK" 명령으로만 획득할 수 있으며, MySQL에서 제공하는 잠금 가운데 가장 범위가 크다. 일단 한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 대부분의 DDL 문장이나 DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 해당 문장이 대기 상태로 남는다. 글로벌 락이 영향을 미치는 범위는 MySQL 서버 전체이며, 작업 대상 테이블이나 데이터베이스가 다르다 하더라도 동일하게 영향을 미친다. 여러 데이터베이스에 존재하는 MyISAM이나 MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받아야 할 때는 글로벌 락을 사용해야 한다.

글로벌 락을 거는 "FLUSH TABLES WITH READ LOCK" 명령은 실행과 동시에 MySQL 서버에 존재하는 모든 테이블에 잠금을 건다. "FLUSH TABLES WITH READ LOCK" 명령이 실행되기 전에 테이블이나 레코드에 쓰기 잠금을 걸고 있는 SQL이 실행되고 있었다면, 이 명령은 해당 테이블의 읽기 잠금을 걸기 위해 먼저 실행된 SQL이 완료되고 그 트랜잭션이 완료될 때까지 기다려야 한다. 그런데 "FLUSH TABLES WITH READ LOCK" 명령은 테이블에 읽기 잠금만 걸기 전에 먼저 테이블을 플러시해야 하기 때문에 테이블에 실행되고 있는 모든 종류의 쿼리가 완료돼야만 테이블을 플러시하고 잠금을 걸 수 있다. 그래서 장시간 SELECT 쿼리가 실행되고 있을 때는 "FLUSH TABLES WITH READ LOCK" 명령은 SELECT 쿼리가 종료될 때까지 기다려야만 한다.
장시간 실행되는 쿼리와 "FLUSH TABLES WITH READ LOCK" 명령이 최악의 케이스로 실행되면 MySQL 서버의 모든 테이블에 대한 INSERT나 UPDATE, 그리고 DELETE 쿼리가 아주 오랜 시간 동안 실행되지 못하고 기다려야 할 수도 있다. 글로벌 락은 MySQL 서버의 모든 테이블에 큰 영향을 미치기 때문에 웹 서비스용으로 사용되는 MySQL 서버에는 가급적 사용하지 않는 것이 좋다. 또한 mysqldump 같은 백업 프로그램은 우리가 알지 못하는 사이에 이 명령을 내부적으로 실행하고 백업할 때도 있따. 만약 mysqldump를 이용해 백업을 수행한다면 mysqldump에서 사용하는 옵션에 따라 MySQL 서버에 어떤 잠금을 걸게 되는지 자세히 확인해보는 것이 좋다.

2. 테이블 락(TABLE LOCK)

개별 테이블 단위로 설정되는 잠금이며, 명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있다. 명시적으로는 "LOCK TABLES table_name [READ | WRITE]" 명령으로 특정 테이블의 락을 획득할 수 있다. 테이블 락은 MyISAM 뿐 아니라 InnoDB 스토리지 엔진을 사용하는 테이블로 동일하게 설정할 수 있다. 명시적으로 획득한 잠금은 "UNLOCK TABLES" 명령으로 잠금을 반납(해제)할 수 있다. 명시적인 테이블 락도 특별한 상황이 아니면 애플리케이션에서 거의 사용할 필요가 없다. 명시적으로 테이블을 잠그는 작업은 글로벌 락과 동일하게 온라인 작업에 상당한 영향을 미치기 때문이다.

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

3. 유저 락(USER LOCK)

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

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

-- // "mylock"이라는 문자열에 대해 잠금이 설정돼 있는지 확인한다.
mysql > SELECT IS_FREE_LOCK('mysql');

-- // "mylock"이라는 문자열에 대해 획득했던 잠금을 반납(해제)한다.
mysql > SELECT RELEASE_LOCK('mysql');

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

또한 유저 락의 경우, 많은 레코드를 한 번에 변경하는 트랜잭션의 경우에 유용하게 사용할 수 있다. 배치 프로그램처럼 한꺼번에 많은 레코드를 변경하는 쿼리는 자주 데드락의 원인이 되곤 한다. 각 프로그램의 실행 시간을 분산하거나 프로그램의 코드를 수정해서 데드락을 최소화할 수는 있지만, 이는 간단한 방법이 아니며 완전한 해결책이 될 수도 없다. 이러한 경우에 동일 데이터를 변경하거나 참조하는 프로그램끼리 분류해서 유저 락을 걸고 쿼리를 실행하면 아주 간단히 해결할 수 있다.

4. 네임 락

데이터베이스 객체(대표적으로 테이블이나 뷰 등)의 이름을 변경하는 경우 획득하는 잠금이다. 네임 락(NAME LOCK)은 명시적으로 획득하거나 해제할 수 있는 것이 아니고 "RENAME TABLE tab_a TO tab_b"와 같이 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금이다. RENAME TABLE 명령의 경우 원본 이름과 변경될 이름 두 개 모두 한꺼번에 잠금을 설정한다. 또한 실시간으로 테이블을 바꿔야 하는 요건이 배치 프로그램에서 자주 발생하는데, 다음 예제를 잠깐 살펴보자.

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

-- // 랭킹 배치가 완료되면 현재 서비스용 랭킹 테이블(rank)을 rank_backup으로 백업하고
-- // 새로 만들어진 랭킹 테이블(rank_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;
mysql > RENAME TABLE rank_new TO rank;

참고

• Real MySQL