[Real Mysql 8.0] 5.2 MySQL 엔진의 잠금

5.2  MySQL 엔진의 잠금

 

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

 

MySQL 엔진 :MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미침

스토리지 엔진 : 스토리지 엔진 간 상호 영양을 미치지 않음.

 

5.2.1GLOBAL LOCK

• FLUSH TABLES WITH READ LOCK 명령으로 획득 할 수 있다.
• MySQL 에서 제공하는 잠금 가운데 가장 범위가 크다.
• 한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT 를 제외한 대부분의 DDL ,DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 해당 문장이 대기 상태로 남는다.
• 글로벌 락이 영향을 미치는 범위는 MySQL 서버 전체이다.
  • 작업 대상 테이블이나, 데이터베이스가 다르더라고 동일하게 영향을 미친다.
• InnoDB 스토리지 엔진은 트랜잭션을 지원하기 때문에 글로벌 락을 사용할 필요는 없다.

5.2.2 테이블 락

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

 

명시적인 테이블 락

• 명시적 방법 : "LOCK TABLES table_name [READ | WRITE]" 명령 이용 , UNLOCK TABLES 명령으로 잠금을 해제
• 명시적인 테이블 락도 특별한 상황이 아니면 어플리케이션에서 사용할 필요가 없다.
• 글로벌 락과 마찬가지로 온라인 작업에 상당한 영향을 미침

묵시적인 테이블 락

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

 

5.2.3 네임드 락

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

네임드 락은 단순히 사용자가 지정한 문자열(String) 에 대해 흭득하고 반납(해제) 하는 잠금이다.

• 자주 사용되지는 않는다.
• 여러 클라이언트가 상호 동기화를 처리해야 할때  네이드락을 이용하면 쉽게 해결할 수 있다.
• 예를 들어, 데이터베이스 서버 1대에 5대의 웹서버가 접속해서 서비스하는 상황에서 웹서버가 어떤 정보를 동기화 해야하는 경우이다.

5.2.4 메타데이터 락

메타데이터 락은 데이터베이스 객체(테이블, 뷰 등) 의 이름이나 구조를 변경하는 경우에 흭득하는 잠금이다.

• 명시적으로 흭득하거나 해제불가하다
• RENAME TABEL tab_a TO tab_b 와 같이 테이블 이름을 변경하는 경우 자동으로 흭득되는 잠금이다.

-- // 배치 프로그램에서 별도의 임시 테이블(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개로 나눠 실행하면 "Table not found ' rank ' "  오류를 아주 짧은 시간 발생시킨다.

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

 

때로는 메타데이터 잠금과 InnoDB 의 트랜잭션을 동시에 사용해야 하는 경우도 있다.

테이블의 구조를 변경해야 할 요건이 있다.

MySQL 서버의 DDL 은 단일 스레드로 작동하기 때문에 상당히 많은 시간이 소모될 것이다.

이때는 새로운 구조의 테이블을 생성하고 최근(1시간 또는 하루전) 의 데이터까지는프라이머리 키인 id 값을 범위별로 나눠서 여러 개의 스레드로 빠르게 복사한다.

--// 테이블의 압축을 적용하기 위해 KEY_BLOCK SIZE=4 옵션을 추가해 신규 테이블 생성
mysql > CREATE TABLE access_log_new (
		id BIGINT NOT NULL AUTO_INCREMENT,
        client_ip INT UNSIGNED,
        access_dttm TIMESTAMP,
        ...
        PRIMARY KEY(id)
        );
--// 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 를 할 수 없게 된다. 그래도 아주 최근 데이터까지 복사해 둬야 잠금 시간을 최소화해 서비스에 미치는 영향을 줄일 수 있다.

--// 트랜잭션을 autocommit으로 실행(BEGIN이나 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;
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;