[MySQL] 잠금(Lock)운영 최적화-2

■ 메타데이터 Locking(잠금)

MySQL은 메타 데이터 잠금을 사용하여 데이터베이스 객체에 대한 동시 액세스를 관리하고 데이터 일관성을 유지합니다. 메타 데이터 잠금은 테이블뿐만 아니라 스키마, 저장된 프로그램 (프로시저, 펑션, 트리거, 예약된 이벤트), 테이블 스페이스, GET_LOCK() 함수로 획득한 사용자 잠금 및 잠금 서비스로 획득한 잠금에도 적용됩니다.

 

성능 스키마 metadata_locks 테이블은 메타 데이터 잠금 정보를 표시합니다. 이 정보는 잠금을 보유하는 세션을 확인하고 잠금 대기등을 차단하는데 유용합니다.

performance_schema의 meta_lock 테이블을 조회하면 관련된 정보를 얻을수 있습니다.

 

메타 데이터 잠금에는 약간의 오버 헤드가 수반되며 쿼리 볼륨이 증가함에 따라 증가합니다. 메타 데이터 경합은 여러 쿼리가 동일한 개체에 액세스하려고할수록 증가합니다.

 

메타 데이터 잠금은 테이블 정의 캐시를 대체하지 않으며 뮤텍스 및 잠금은 LOCK_open 뮤텍스와 다릅니다. 아래 설명에서 메타 데이터 잠금 작동 방식에 대한 정보를 제공합니다.

+ 메타 데이터 잠금 획득

+ 메타 데이터 잠금 해제

 

▶︎ 메타 데이터 잠금 획득

주어진 잠금에 대해 여러개의 웨이터가있는 경우 max_write_lock_count 시스템 변수와 관련된 예외를 제외하고 가장높은 우선순위 잠금요청이 먼저 충족됩니다. 쓰기 잠금요청은 읽기 잠금요청보다 우선순위가 높습니다. 그러나 max_write_lock_count가 일부 낮은 값 (예 : 10)으로 설정되면 읽기 잠금 요청이 10 개의 쓰기 잠금 요청에 대해 이미 전달된 경우 보류중인 쓰기 잠금 요청보다 읽기 잠금 요청이 선호 될 수 있습니다. 일반적으로 max_write_lock_count의 값이 매우 크다고 해서 이 문제가 발생하지는 않습니다.

 

명령문은 동시에 메타 데이터 잠금을 하나씩 획득하지 않고 프로세스에서 교착 상태 감지를 수행합니다.

 

DML문은 일반적으로 명령문에 테이블이 언급된 순서대로 잠금을 획득합니다.

 

DDL문, LOCK TABLES 및 기타 유사한 명령문은 이름순으로 명시적으로 명명된 테이블에 대한 잠금을 획득하여 동시 DDL문 사이의 가능한 교착 상태 수를 줄이려고 합니다. 내재적으로 사용된 테이블 (예 : 잠금해야하는 외래 키 관계의 테이블)에 대해 다른 순서로 잠금을 획득 할 수 있습니다.

 

예를 들어, RENAME TABLE은 이름 순서대로 잠금을 획득하는 DDL 문입니다.

+ 이 RENAME TABLE 문은 tbla의 이름을 다른 것으로 바꾸고 tblc의 이름을 tbla로 바꿉니다.

mysql> RENAME TABLE tbla TO tbld, tblc TO tbla;

 

이 명령문은 tbla, tblc 및 tbld에서 메타 데이터 잠금을 순서대로 획득합니다 (tbld는 이름 순서대로 tblc를 따르기 때문에).

+ 여기에서 약간 다른 문장은 tbla의 이름을 다른 것으로 바꾸고 tblc의 이름을 tbla로 바꿉니다.

mysql> RENAME TABLE tbla TO tblb, tblc TO tbla;

이 경우, 명령문은 tbla, tblb 및 tblc에서 메타 데이터 잠금을 순서대로 획득합니다 (tblb가 tblc보다 이름 순서에 우선하므로).

 

두 명령문은 tbla 및 tblc에서 잠금을 순서대로 획득하지만 잠금을 유지하고 있는 테이블 이름이 tblc 이전 또는 이후에 획득되는지 여부는 알 수 없습니다. 순서가 다를수도 있습니다.

 

다음 예제와 같이 메타 데이터 잠금 획득 순서는 여러 트랜잭션이 동시에 실행될 때 작업 결과에 차이를 만들 수 있습니다.

 

구조가 동일한 두 개의 테이블 x와 x_new로 시작합니다. 세 명의 고객이 다음 표와 관련된 설명을 발행합니다.

Client 1:

LOCK TABLE x WRITE, x_new WRITE;

이 명령문은 x 및 x_new에서 이름 순서로 쓰기 잠금을 요청하고 얻습니다.

 

Client 2:

INSERT INTO x VALUES(1);

이 명령문은 x에 대한 쓰기 잠금을 기다리는 동안 요청 및 차단됩니다.

 

Client 3:

RENAME TABLE x TO x_old, x_new TO x;

이 명령문은 x, x_new 및 x_old에서 이름 순서로 독점 잠금을 요청하지만 x에서 잠금을 기다리는 것은 차단합니다.

 

Client 1:

UNLOCK TABLES;

이 명령문은 x 및 x_new에서 쓰기 잠금을 해제합니다. Client 3의 x에 대한 독점 잠금 요청은  Client 2의 쓰기 잠금 요청보다 우선 순위가 높으므로 Client 3은 x에 대한 잠금을 획득 한 다음 x_new 및 x_old에 대한 잠금을 획득하고 이름을 변경하고 잠금을 해제합니다. 그런 다음 클라이언트 2는 x에서 잠금을 획득하고 삽입을 수행한 후 잠금을 해제합니다.

 

획득 순서 잠금으로 인해 INSERT 전에 RENAME TABLE이 실행됩니다. 삽입이 발생하는 x는 클라이언트 2가 삽입을 발행할 때 이름이 x_new이고 이름이 클라이언트 3에 의해 x로 변경된 테이블입니다.

mysql> SELECT * FROM x;
+------+
| i    |
+------+
|    1 |
+------+

mysql> SELECT * FROM x_old;
Empty set (0.01 sec)

이제 동일한 구조를 가진 x와 new_x라는 테이블로 시작합니다. 다시, 세 명의 클라이언트가 다음 테이블과 관련된 명령문을 발행합니다.

 

Client 1:

mysql> LOCK TABLE x WRITE, new_x WRITE;

명령문은 new_x 및 x에서 이름 순서로 쓰기 잠금을 요청하고 얻습니다.

 

Client 2:

mysql> INSERT INTO x VALUES(1);

이 명령문은 x에 대한 쓰기 잠금을 기다리는 동안 요청 및 차단됩니다.

 

Client 3:

mysql> RENAME TABLE x TO old_x, new_x TO x;

이 명령문은 new_x, old_x 및 x에서 이름 순서로 독점 잠금을 요청하지만 new_x에서 잠금을 기다리는 블록은 차단합니다.

 

Client 1:

mysql> UNLOCK TABLES;

이 명령문은 x 및 new_x에서 쓰기 잠금을 해제합니다. x의 경우 보류중인 유일한 요청은 클라이언트 2에 의한 것이므로 클라이언트 2는 잠금을 획득하고 삽입을 수행하며 잠금을 해제합니다. new_x의 경우 보류중인 유일한 요청은 클라이언트 3에 의한 것입니다. 이 요청은 해당 잠금 (및 old_x의 잠금)을 획득 할 수 있습니다. 클라이언트 2 삽입이 완료되고 잠금이 해제될 때까지 이름 바꾸기 작업은 여전히 x에 대한 잠금을 차단합니다. 그런 다음 클라이언트 3은 x에서 잠금을 획득하고 이름 바꾸기를 수행한 후 잠금을 해제합니다.

 

이 경우 잠금 획득 순서로 인해 RENAME TABLE 전에 INSERT가 실행됩니다. 삽입이 발생하는 x는 원래 x이며 이름 바꾸기 작업을 통해 old_x로 이름이 변경되었습니다.

mysql> SELECT * FROM x;
Empty set (0.01 sec)

mysql> SELECT * FROM old_x;
+------+
| i    |
+------+
|    1 |
+------+

앞의 예에서와 같이 동시 명령문에서 잠금 획득 순서가 조작 결과에서 응용 프로그램과 차이가있는 경우 잠금 획득 순서에 영향을 미치도록 테이블 이름을 조정할 수 있습니다.

 

 

▶︎ 메타 데이터 잠금 해제

트랜잭션 직렬화를 보장하기 위해 서버는 한 세션이 다른 세션에서 완료되지 않은 명시적 또는 암시적으로 시작된 트랜잭션에 사용되는 테이블에서 데이터 정의 언어 (DDL) 명령문을 수행하도록 허용해서는 안됩니다. 서버는 트랜잭션 내에서 사용되는 테이블에 대한 메타 데이터 잠금을 획득하고 트랜잭션이 종료될 때까지 해당 잠금의 릴리스를 연기함으로써 이를 달성합니다. 테이블의 메타 데이터 잠금으로 인해 테이블 구조가 변경되지 않습니다. 이 잠금 방식은 한 세션 내에서 트랜잭션에 의해 사용중인 테이블을 트랜잭션이 종료될 때까지 다른 세션에서 DDL문을 사용할 수 없습니다.

 

이 원칙은 트랜잭션 테이블뿐만 아니라 비 트랜잭션 테이블에도 적용됩니다. 세션이 다음과 같이 트랜잭션 테이블 t 및 비 트랜잭션 테이블 nt를 사용하는 트랜잭션을 시작한다고 가정합니다.

START TRANSACTION;
SELECT * FROM t;
SELECT * FROM nt;

 

서버는 트랜잭션이 종료될 때까지 t와 nt 모두에서 메타 데이터 잠금을 보유합니다. 다른 세션이 두 테이블에서 DDL 또는 쓰기 잠금 작업을 시도하면 트랜잭션 종료시 메타 데이터 잠금이 해제 될 때까지 차단됩니다. 예를 들어, 두 번째 세션이 다음 작업 중 하나를 시도하면 차단됩니다.

DROP TABLE t;
ALTER TABLE t ...;
DROP TABLE nt;
ALTER TABLE nt ...;
LOCK TABLE t ... WRITE;

LOCK TABLES ... READ에도 동일한 동작이 적용됩니다. 즉, 테이블 (트랜잭션 또는 비 트랜잭션)을 업데이트하는 명시적 또는 암시적으로 시작된 트랜잭션은 해당 테이블에 대한 LOCK TABLES ... READ에 의해 차단 및 차단됩니다.

 

서버가 구문상 유효하지만 실행중에 실패한 명령문에 대한 메타 데이터 잠금을 획득하면 잠금을 조기에 해제하지 않습니다. 실패한 명령문이 바이너리 로그에 기록되고 잠금이 로그 일관성을 보호하므로 잠금 해제는 여전히 트랜잭션 종료까지 지연됩니다.

 

자동 커미트 모드에서 각 명령문은 사실상 완전한 트랜잭션이므로 명령문에 대해 획득한 메타 데이터 잠금은 명령문의 끝에만 유지됩니다.

 

PREPARE 문 중 획득 된 메타 데이터 잠금은 명령문이 준비되면 다중 명령문 트랜잭션 내에서 준비가 발생하더라도 해제됩니다.

 

■ 외부 잠금

외부 잠금은 파일 시스템 잠금을 사용하여 여러 프로세스에서 MyISAM 데이터베이스 테이블에 대한 경합을 관리합니다. 외부 잠금은 MySQL 서버와 같은 단일 프로세스가 테이블에 액세스해야하는 유일한 프로세스라고 가정 할 수 없는 상황에서 사용됩니다. 몇가지 예를 들어보겠습니다.

+ 동일한 데이터베이스 디렉토리를 사용하는 여러 서버를 실행하는 경우 (권장하지 않음) 각 서버에는 외부 잠금이 사용 가능해야합니다.

+ myisamchk를 사용하여 MyISAM 테이블에 대해 테이블 유지 보수 작업을 수행하는 경우, 서버가 실행 중이 아니거나 서버에 외부 잠금이 사용 가능한지 확인하여 테이블에 액세스하기 위해 myisamchk와 조정하기 위해 필요에 따라 테이블 파일을 잠글수 있는지 확인해야 합니다. MyISAM 테이블을 감싸기 위해 myisampack을 사용하는 경우에도 마찬가지입니다.

외부 잠금이 사용 가능한 상태에서 서버를 실행하는 경우 언제든지 myisamchk를 사용하여 테이블 점검과 같은 읽기 조작을 수행할 수 있습니다. 이 경우, 서버가 myisamchk가 사용중인 테이블을 업데이트하려고 하면 서버는 myisamchk가 완료되기를 기다렸다가 계속합니다.

테이블 복구 또는 최적화와 같은 쓰기 작업에 myisamchk를 사용하거나 myisampack을 사용하여 테이블을 감싸는 경우 항상 mysqld 서버가 테이블을 사용하지 않는지 확인해야합니다. mysqld를 중지하지 않으면 myisamchk를 실행하기 전에 최소한 mysqladmin flush-tables를 수행해야 합니다. 서버와 myisamchk가 동시에 테이블에 액세스하면 테이블이 손상될 수 있습니다.

 

외부 잠금이 적용되면 테이블에 액세스 해야하는 각 프로세스는 테이블에 액세스하기 전에 테이블 파일에 대한 파일 시스템 잠금을 획득합니다. 필요한 모든 잠금을 확보 할 수 없는 경우 잠금을 확보할 수 있을 때까지 (현재 잠금을 보유한 프로세스가 잠금을 해제 한 후) 프로세스가 테이블에 액세스하지 못하도록 차단됩니다.

 

외부 잠금은 서버가 테이블에 액세스하기 전에 다른 프로세스를 기다려야하므로 서버 성능에 영향을 미칩니다.

 

주어진 데이터 디렉토리 (일반적인 경우)에 액세스하기 위해 단일 서버를 실행하고 서버가 실행되는 동안 myisamchk와 같은 다른 프로그램이 테이블을 수정할 필요가 없는 경우 외부 잠금이 필요하지 않습니다. 다른 프로그램으로만 테이블을 읽는 경우 myisamchk가 테이블을 읽는 동안 서버가 테이블을 변경하면 myisamchk가 경고를 보고 할 수 있지만 외부 잠금은 필요하지 않습니다.

 

외부 잠금이 사용 불가능한 상태에서 myisamchk를 사용하려면 myisamchk가 실행되는 동안 서버를 중지하거나 myisamchk를 실행하기 전에 테이블을 잠그거나 플러시해야 합니다. (8.12.1 절“시스템 요소”참조)이 요구 사항을 피하려면 CHECK TABLE 및 REPAIR TABLE 문을 사용하여 MyISAM 테이블을 확인하고 복구합니다.

 

mysqld의 경우, 외부 잠금은 skip_external_locking 시스템 변수의 값에 의해 제어됩니다. 이 변수가 활성화되면 외부 잠금이 비활성화되고 그 반대도 마찬가지입니다. 외부 잠금은 기본적으로 비활성화되어 있습니다.

 

--external-locking 또는 --skip-external-locking 옵션을 사용하여 서버 시작시 외부 잠금 사용을 제어 할 수 있습니다.

 

외부 잠금 옵션을 사용하여 많은 MySQL 프로세스에서 MyISAM 테이블을 업데이트 할 수 있는 경우 다음 조건이 충족되는지 확인해야 합니다.

+ 다른 프로세스에서 업데이트한 테이블을 사용하는 쿼리에는 쿼리 캐시를 사용을 금합니다.

+ delay_key_write 시스템 변수를 ALL로 설정하여 서버를 시작하거나 공유 테이블에 대해 DELAY_KEY_WRITE = 1 테이블 옵션을 사용하면 안됩니다. 만약 사용시 인덱스 손상이 발생할 수 있습니다.

 

이러한 조건을 만족시키는 가장 쉬운 방법은 항상 --delay-key-write=OFF 및 --query-cache-size=0과 함께 --external-locking을 사용하는 것입니다. (많은 설정에서 앞의 옵션을 혼합하여 사용하는 것이 유용하기 때문에 이것은 기본적으로 수행되지 않습니다.)