Last Updated on 7월 16, 2023 by Jade(정현호)
안녕하세요
이번 포스팅은 MySQL의 트랜잭션 과 잠금(Lock)에 대해서 확인 해보려고 합니다.
전체적인 내용은 Real MySQL 8.0 책 과 MySQL 문서를 정리 한 내용 으로 아래 포스팅에서 이어지는 글 입니다.
Contents
트랜잭션
트랜잭션은 작업의 완전성을 보장해 주는 것 입니다. 즉 논리적인 작업 셋을 모두 완벽하게 처리 하거나, 처리하지 못할 경우에는 모두 원 상태로 복구 하는 작업의 일부분만 적용되는 현상(Partial update) 이 발생하지 않도록 해주는 기능으로 트랜잭션 4가지 속성 중 Atomicity (원자성)에 해당하며 All or Nothing 형태를 의미 합니다.
잠금(Lock) 과 트랜잭션은 서로 비슷한 개념 같지만 잠금(Lock)은 동시성 제어를 하기 위한 기능이고, 트랜잭션은 데이터의 정합성을 보장하기 위한 기능 입니다.
잠금은 여러 세션이 동시에 동일한 레코드 나 테이블의 변경을 요청 할 경우 순서대로 한 시점에 하나의 세션만 변경할 수 있게 해주는 역할을 하게 됩니다.
격리 수준(isolation level)이라는 것은 하나의 트랜잭션 내에서 또는 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지를 결정하는 레벨을 의미하게 합니다.
MySQL 에서의 트랜잭션
트랜잭션 은 꼭 여러 개의 변경 작업을 묶어서 수행하는 경우만 의미 있는 개념은 아닙니다. 트랜잭션은 하나의 논리적인 작업 셋에 하나의 쿼리가 있거나 두 개 이상의 쿼리가 있거나 관계 없이 논리적인 작업 셋 자체가 100% 적용 되거나, 아무것도 적용되지 않아야 함을 보장 해주는 것 입니다
InnoDB 스토리지 엔진은 사용하기 복잡하고 번거롭다고 생각할 수도 있지만, MyISAM 이나 MEMORY 와 같은 트랜잭션을 지원하지 않는 스토리지 엔진의 테이블을 사용할 경우 더 많은 고민거리를 만들어 내게 됩니다.
간단하게 MyISAM 과 InnoDB 스토리지 엔진의 테이블의 트랜잭션 차이를 확인 해보도록 하겠습니다.
mysql> create table tb_tab_myisam(col int primary key) ENGINE=MyISAM; mysql> insert into tb_tab_myisam values (3); mysql> create table tb_tab_innodb(col int primary key) ENGINE=InnoDB; mysql> insert into tb_tab_innodb values (3); mysql> insert into tb_tab_myisam values(1),(2),(3); Error Code: 1062. Duplicate entry '3' for key 'tb_tab_myisam.PRIMARY' mysql> insert into tb_tab_innodb values(1),(2),(3); Error Code: 1062. Duplicate entry '3' for key 'tb_tab_innodb.PRIMARY' -- MyISAM mysql> select * from tb_tab_myisam; +-----+ | col | +-----+ | 1 | | 2 | | 3 | +-----+ -- InnoDB mysql> select * from tb_tab_innodb; +-----+ | col | +-----+ | 3 | +-----+
Insert 과정에서 2개의 구문 모두 1062 에러(PK 중복) 으로 에러가 발생 되기는 합니다 다만 2개의 테이블을 조회해보면 그 결과가 다르게 됩니다.
MyISAM 테이블은 PK 가 중복이 되어서 에러가 발생되었지만 1,2 는 INSERT 가 된 상태로 남아 있으며, MyISAM 은 INSERT 문 실행시 차례대로 1,2를 저장하고 그 다음 3을 저장 하려는 순간 에러가 발생된 것 이고 이미 Insert 된 1,2는 그대로 두고 3에서 에러가 발생되면서 쿼리가 그대로 종료가 된 것 입니다.
MyISAM 이러한 현상을 Partial Update 라고 표현하며 데이터 정합성을 맞추는데 많은 어려움을 만들어 내게 됩니다.
MyISAM 에서 이러한 현상을 방지 하기 위해서 IF..ELSE 등으로 프로그램 코드를 넣은 , 데이터에 대한 확인 및 클렌징 코드(Rollback) 까지를 준비해야 합니다.
하지만 InnoDB 는 아래와 같이 간단하게 로직 구현이 가능할 것 입니다.
try {
start transaction;
insert into tab_a ..;
insert into tab_b ..;
commit;
} catch(exception {
rollback;
}
트랜잭션 처리 주의 사항
트랜잭션 도 DBMS 커넥션과 유사하게 꼭 필요하고 최소한의 코드에만 적용하는 것이 좋습니다. 이 말은 프로그램 코드에서 트랜잭션의 범위를 최소화 하라는 의미가 되게 됩니다.
다음은 게시판에서 게시물 작성 완료 버튼을 눌렀을때 일련의 처리 로직 중 에서의 간략한 예시 입니다.
처리시작 - > 트랜잭션 시작 ->글쓰기 글자수 제한 확인 -> 사용자 글쓰기 권한 확인 -> 첨부된 파일의 업로드 가능 확장자 및 용량 확인 , 저장 -> 사용자 입력 내용(글 과 첨부파일 정보) 을 DB에 저장 -> 저장 된 내용 조회 및 추가 정보 DB에서 조회 -> 게시물에 대한 알림 메일 발송 -> 메일 발송 이력 DB에 저장 -> 트랜잭션 종료 -> 처리 완료
위의 내용에서 좋지 않은 부분은 시작 트랜잭션 부분과 트랜잭션 종료 사이의 많은 단계 중에서 실제로 DB 에 내용을 저장하는 것은 "사용자 입력 내용 DB 저장" 이라는 부분이라서 그 앞 단의 단계에서 DBMS 트랜잭션 절차에서 포함 하지 않아도 될 것 이며, 그 다음으로 가장 주요한 부분으로 "게시물 등록에 따른 이메일 발송" 부분 입니다.
작업 중간에 파일을 FTP/SFTP 등으로 전송하거나 파일을 생성 또는 파일에 기록 한다거나 위의 예시와 같이 이메일을 발송한다거나 하는 DB에서 처리와 무관한 로직이 DB 트랜잭션내 시작과 끝 사이에 있다는 것 입니다.
그래서 외부 로직의 문제가 발생되어서 Pending 이나 장시간 소요된다면 전체적인 처리가 매우 늦어지거나 문제가 될 수도 있게 됩니다.
업무의 특성과 구현이 필요한 로직 등에 따라서 트랜잭션 설계는 여러가지 면으로 작성될 수 있겠으나, 기본적으로 생각해야 할 부분으로 애플리케이션 코드에서 데이터베이스의 커넥션을 가지고 있는 범위 그리고 트랜잭션이 활성화 되어 있는 프로그램의 범위는 최소화 하는 부분이라는 것 입니다.
트랜잭션의 4가지 특성(ACID)
위에서 설명한 트랜잭션은 ACID 성질이라고 하는 다음의 네 가지 성질로 설명할 수 있습니다.
- Atomicity(원자성): 진행 중인 트랜잭션이 실패하게 될 경우 어느 단계는 적용 되고 어느 단계의 데이터는 완료 되지 않고 하는 경우가 발생해서는 안 되기 때문에 DBMS는 완료되지 않은 트랜잭션의 중간 상태를 데이터베이스에 반영해서는 안됩니다
즉, 트랜잭션의 모든 연산들이 정상적으로 수행 완료되거나 아니면 전혀 어떠한 연산도 수행되지 않은 상태를 보장해야 합니다. atomicity는 쉽게 'all or nothing' 특성으로 설명 할 수 있습니다. - Consistency(일관성): 트랜잭션이 성공적으로 수행된 후에도 데이터베이스가 일관성 있는 상태를 유지하는 것이며, 성공적으로 수행된 트랜잭션은 정당한 데이터들이 데이터베이스에 반영되었음을 의미합니다. 또는 일관된 상태에서 변경시에도 일관된 상태를 유지하면서 변경되어야 합니다.
보장되어야 하는 일관성에는 제약조건,Cascade,Trigger를 포함하여 모든 정의 된 조건에 맞게 데이터가 입력 및 변경 되어야 하며, 데이터베이스에서 데이터 입력/변경 시 사전에 설정되어 있는 룰(제약조건 등)에 맞지 않는 데이터가 입력 및 변경을 방지함을 의미 합니다.
트랜잭션의 일관성은 트랜잭션 실행이 성공적으로 완료하면 언제나 일관성 있는 데이터베이스 상태로 유지하는 것을 말합니다.
- Isolation(독립성/고립성): 여러 트랜잭션이 동시에 수행 되더라도 각각의 트랜잭션은 다른 트랜잭션의 수행에 영향을 받지 않고 독립적으로 수행되어야 함을 의미합니다. 즉 다수의 세션 또는 유저가 같은 시간에 같은 데이터에 접근하고 처리 중일 때 수행 중인 트랜잭션이 완료 될 때 까지 다른 트랜잭션이 끼어 들지 못하게 함으로써 데이터의 누락이나 데이터의 잘못된 데이터에 대한 방지를 하게 되는 것 입니다.
isolation 이 없을 경우 다수의 사용자에 의해 dirty read 나 lost update 등이 발생될 수 있게 됩니다. isolation level 설정에 따라 독립성의 높낮이 및 트랜잭션 진행간 발생될 수 있는 상황들도 달라지게 됩니다. 또한 각 DBMS 마다 기본으로 채택하고 있는 isolation level 이 각각 다르며 MySQL 에서는 REPEATABLE READ 을 기본 값으로 하고 있습니다.
격리 수준(isolation level)이 높다면 트랜잭션의 격리성/독립성은 높아지지만 동시 처리 성능이 떨어지며 시스템 자원도 많이 사용 되게 되며 레벨이 낮아지면 그와 반대로 되게 됩니다.
격리 수준(isolation level) 에 대한 자세한 내용은 아래에서 기술되어 있습니다.
- Durability(지속성): 트랜잭션이 성공적으로 완료되어 커밋되었다면, 해당 트랜잭션에 의한 반영된 모든 변경은 향후에 어떤 소프트웨어나 하드웨어 장애가 발생 되더라도 보존되어야 함을 의미 합니다.
커밋 성능을 위해서 지속성 일부를 포기하는 설정 방식도 존재 하기는 합니다.
MySQL 엔진의 잠금
MySQL 에서 사용 되는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있습니다. MySQL 엔진은 MySQL 서버에서 스토리지 엔진 영역을 제외한 나머지 부분을 의미 합니다.
MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치게 되지만, 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간 상호 영향을 미치지는 않습니다.
MySQL 엔진에서 테이블 데이터 동기화를 위한 테이블의 락 이에외에도 테이블 구조를 잠그는 메타데이터 락(Metadata Lock) 그리고 사용자의 필요에 따라 사용할 수 있는 네임드 락(Named Lock) 이라는 기능도 제공을 하고 있습니다.
글로벌 락
글로벌 락(Global Lock) 은 FLUSH TABLES WITH READ LOCK 명령으로 획득할 수 있으며 MySQL 제공하는 잠금 가운데 가장 범위가 큽니다.
한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT 를 제외한 대부분의 DDL 과 DML 문장은 해당 글로벌 락에 의해서 대기를 하게 됩니다.
글로벌 락이 영향을 미치는 범위는 MySQL 서버 전체 대상이며, 작업 대상 테이블이나 데이터베이스가 다르더라도 동일하게 영향을 미치게 됩니다.
MyISAM 이나 MEMORY 테이블에 대해서 mysqldump 유틸리티를 통해서 일관된 백업을 받아야 할 때 글로벌 락을 이용하게 됩니다.
글로벌 락을 실행하기 전에 테이블이나 레코드에 쓰기 잠금을 거는 SQL 이 실행 중이라면 해당 SQL 트랜잭션이 완료 될때 까지 기다려야 합니다.
또한 테이블에 읽기 잠금을 거는 과정에서 먼저 테이블을 FLUSH 해야 하기 때문에 테이블에 실행중인 모든 종류의 쿼리가 완료 되어야 합니다.
mysqldump 를 이용해 백업을 수행시 사용하는 옵션에 따라서 MySQL 서버에서 사용 되는 잠금이 다르게 되며 많이 사용 되는 옵션으로 --single-transaction 사용할 경우 InnoDB Storage 엔진의 테이블은 잠금을 걸지 않고 Isolation Level - repeatable read 를 사용한 MVCC 를 통해서 일관된 백업을 받게 됩니다
MySQL 8.0 부터는 InnoDB 스토리지 엔진이 기본으로 되었고, 조금 더 가벼운 글로벌 락의 필요성이 생기게 되었습니다. 그래서 Xtrabackup 또는 Enterprise Backup 과 같은 백업 툴에서 안정적인 실행을 위한 백업을 위한 Lock 이 추가 되었습니다.
mysql> LOCK INSTANCE FOR BACKUP; Query OK, 0 rows affected (0.00 sec) mysql> UNLOCK INSTANCE; Query OK, 0 rows affected (0.00 sec)
이 새로운 유형의 백업 잠금은 온라인 백업 중에 DML은 허용하는 동시에 일관성 없는 스냅샷이 생성될 수있는 있는 작업을 방지합니다.
새 백업 잠금은 LOCK INSTANCE FOR BACKUP 및 UNLOCK INSTANCE 구문을 통해서 사용할 수 있으며 이러한 명령어를 사용하기 위해서는 BACKUP_ADMIN 권한이 필요 합니다.
[참고] MySQL 8.0으로 인플레이스 업그레이드를 수행할 때 BACKUP_ADMIN 권한은 RELOAD 권한이 있는 사용자에게 자동으로 부여됩니다.
새로운 백업락을 사용하면 다음의 몇가지 작업은 변경 할 수 없지만, 테이블에 대한 변경은 허용되게 됩니다.
- 데이터베이스 및 테이블 등의 모든 객체 생성 및 객체 변경, 삭제
- REPAIR TABLE , OPTIMIZER TABLE 명령
- 사용자 관리 및 비밀번호 변경
위와 같은 작업 이외 테이블의 데이터 변경은 허용이 되게 됩니다.
일반적으로 백업을 Replica 인스턴스에서 수행하더라도 Xtrabackup 이나 Enterprise Backup 툴이 실행되는 도중에 스키마 변경이 발생되면 백업은 실패할 수 있게 되는데 장시간 수행되었던 백업이었다면 다시 수행하기 위해 또 많은 시간이 소요되고 필요하게 될 것 입니다.
그래서 8.0 버전에서 이런 목적으로 백업락이 도입이 되었으며, 정상적으로 복제는 이루어지지만 백업의 실패를 막기 위해서 DDL 명령이 실행되면 복제를 일시 중지하는 역할을 하게 됩니다.
테이블 락
테이블 락(Table Lock) 은 개별 테이블 단위로 설정되는 잠금이며, 명시적 또는 묵시적으로 특정 테이블 락을 획득할 수 있습니다.
명시적으로는 LOCK TABLES 테이블명 [read | write ] 명령으로 특정 테이블 락을 획득 할 수 있습니다.
테이블 락은 MyISAM 과 InnoDB 스토리지 엔진을 사용하는 테이블 모두 동일하게 설정할 수 있습니다.
명시적으로 획득한 잠금은 UNLOCK TABLES 명령으로 잠금을 해제를 할 수 있습니다.
특별한 경우가 아니라면 애플리케이션에서 사용할 필요는 없겠지만, 사용을 하게 된다면 글로벌 락과 동일하게 온라인 작업에서의 상당한 영향을 미칠수도 있습니다.
묵시적인 테이블 락은 MyISAM 이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생을 하게 됩니다.
사용자가 데이터를 변경하면 MySQL 서버는 테이블에 잠금을 설정하고 데이터를 변경한 한 후 잠금을 해제 하는 형태로 사용 됩니다.
즉, 묵시적인 테이블 락은 쿼리가 실행되는 동안 자동으로 획득 되었다가 쿼리가 완료된 후 자동 해제 됩니다.
InnoDB 스토리지 엔진 테이블의 경우 대부분의 데이터 변경(DML) 은 레코드 기반의 잠금을 사용하고 테이블 레벨의 잠금은 스키마 변경을 막기 위해 사용 됩니다.
네임드 락
네임드 락(Named Lock) 은 GET_LOCK() 함수를 이용하여 임의의 문자열을 지정하여 잠금을 설정할 수 있는 잠금 기능 입니다.
이 잠금의 특징은 대상 테이블이나 레코드 또는 AUTO_INCREMENT 와 같은 데이터베이스 객체가 아닌, 단순히 사용자가 지정한 문자열(String) 에 대해 잠금을 획득하고 반납(해제) 하는 잠금 기능 입니다.
사실 네임드 락은 자주 사용되지는 않습니다. 왜냐면 명확한 해당 업무 요건이나 로직이 필요할 경우 사용할 수 있으며 MySQL 에서의 네임드락을 사용 이외 대표적으로 Redis 나 ZooKeeper 와 같은 솔루션을 통한 분산락 구현을 할 수 있기 때문입니다.
그렇지만 MySQL 의 네임드 락을 사용하는 것은 별도의 솔루션이나 인프라 구축에 대한 비용(실제로 비용+관리적 Cost 등등) 을 줄이고 계속 사용중인 인프라 환경인 DB 시스템을 이용한다는 장점은 있습니다.
네임드락(GET_LOCK) 의 사용 용도는 주로 분산 락에서 사용되며 분산 락을 간단하게 표현하면 "분산 락은 데이터베이스 등 공통된 저장소를 이용하여 자원이 사용 중인지를 체크 하며 전체 서버에서 동기화된 처리를 구현 합니다"
그래서 이러한 분산 락으로 구현해서 사용하는 업무로는 보통 선착순 형태의 이벤트나 업무 또는 한 유저가 여러번 클릭으로 동일한 상태를 유지하려고 할 경우 등에서 사용할 수 있습니다.
그래서 간단하게 다시 살펴보면
- LOCK이 사용중이 아니라면, Lock을 획득
- LOCK이 사용중이라면 , Timeout 을 설정하고 대기 하였다가 Lock 을 획득
- Lock 획득을 위해 대기 하였으나 Timeout 시간이 지나서 Lock 획득 실패
이런 형태의 Lock 의 획득과 대기, Timeout 등을 통해서 분산락을 구현하여 사용할 수 있습니다. 위에서 설명한 것 처럼 일반적인 DB 객체(테이블이나 레코드 등) 에 잠금을 설정하는 것이 아닌 임의의 문자열을 통해서 잠금 설정을 사용하게 됩니다.
사용법은 다음과 같습니다.
-- jade-1 이라는 문자열로 잠금을 획득하며
-- 이미 잠금이 있다면 5초간 대기 합니다
mysql> SELECT GET_LOCK('jade-1',5);
+----------------------+
| GET_LOCK('jade-1',5) |
+----------------------+
| 1 |
+----------------------+
-- 문자열 'jade-1' 에 대해서 획득 가능한지를 확인 합니다.
mysql> SELECT IS_FREE_LOCK('jade-1');
+------------------------+
| IS_FREE_LOCK('jade-1') |
+------------------------+
| 0 |
+------------------------+
-- 문자열 jade-1 이 잠금이 설정되어 있는지 확인합니다.
mysql> SELECT IS_USED_LOCK('jade-1');
+------------------------+
| IS_USED_LOCK('jade-1') |
+------------------------+
| 8 |
+------------------------+
-- connection id 확인
mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
| 8 |
+-----------------+
-- 잠금해제
mysql> SELECT RELEASE_LOCK('jade-1');
+------------------------+
| RELEASE_LOCK('jade-1') |
+------------------------+
| 1 |
+------------------------+
사용된 함수에 대해서 조금 더 설명을 하면
GET_LOCK(STR, TIMEOUT)
입력받은 문자열로 잠금을 시도 합니다.
입력 받은 이름(STR)으로 TIMEOUT 초 동안 잠금 획득을 시도. (음수 입력시 무한 대기)
결과값은 1(성공), 0(TIMEOUT 초과), NULL(에러 발생)
IS_FREE_LOCK(STR)
입력한 이름(STR)에 해당하는 잠금이 획득 가능한지 확인합니다.
결과 값으로 1, 0, NULL 을 반환
1 : 입력한 이름의 잠금이 없을때
0 : 입력한 이름의 잠금이 있을때
NULL : 에러발생시(EX : 잘못된 인자)
IS_USED_LOCK(STR)
입력한 이름(STR)의 잠금이 사용중인지 확인합니다.
입력받은 이름(string)의 잠금이 존재하면 락을 보유하고 있는 CONNECTION ID 를 반환하고, 없으면 NULL 을 반환합니다.
RELEASE_LOCK(STR)
입력 받은 이름(STR)의 잠금을 해제 합니다.
결과값은 1(성공), 0(현재 세션의 LOCK이 아님), NULL(LOCK 없음)
RELEASE_ALL_LOCKS()
현재 세션의 모든 LOCK을 해제하고 해제한 LOCK 개수 반환 합니다.
그래서 먼저 한 세션에서 LOCK 을 획득 하고 다른 세션에서 락을 획득을 시도 할 경우 선행 세션의 락의 잠금이 완료 될때 까지 기다려서 획득을 하거나 Timeout 에 의해서 락 획득 실패를 하게 됩니다.
-- Session 1: 잠금 시도 -> 잠금 획득 완료
mysql> SELECT GET_LOCK('jade-1',5);
+----------------------+
| GET_LOCK('jade-1',5) |
+----------------------+
| 1 |
+----------------------+
<!!-- 반환값 1임으로 잠금을 획득 하였습니다.
-- Session 2 : 잠금 시도 -> 획득 실패
mysql> SELECT GET_LOCK('jade-1',5);
+----------------------+
| GET_LOCK('jade-1',5) |
+----------------------+
| 0 |
+----------------------+
1 row in set (5.01 sec)
<!!-- 수행시간은 5초로, 5초 동안 대기를 의미 합니다.
<!!-- 반환값이 0 임으로 잠금 획득에 실패 하였습니다.
-- Session 2 : 잠금 사용 확인
mysql> SELECT IS_USED_LOCK('jade-1');
+------------------------+
| IS_USED_LOCK('jade-1') |
+------------------------+
| 4 |
+------------------------+
<!!-- CONNECTION_ID 4 에서
<!!-- 잠금을 획득 중으로 확인 하였습니다.
-- Session 1 : CONNECTION_ID 확인
mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
| 4 |
+-----------------+
-- Session 1 : 잠금 해제
mysql> SELECT RELEASE_LOCK('jade-1');
+------------------------+
| RELEASE_LOCK('jade-1') |
+------------------------+
| 1 |
+------------------------+
<!!-- 반환값 1임으로 잠금 해제 완료 하였습니다.
-- Session 2 : 다시 잠금 시도
mysql> SELECT GET_LOCK('jade-1',5);
+----------------------+
| GET_LOCK('jade-1',5) |
+----------------------+
| 1 |
+----------------------+
1 row in set (0.00 sec)
<--!! 잠금 획득 성공
위와 같이 네임드 락을 통해서 분산락을 구현하여 사용할 수 있습니다
MySQL 5.6 버전과 My 5.7 의 차이가 있으며 MySQL 5.7 에서 부터는 동시에 여러개 잠금 획득 가능합니다.
MySQL 5.6 에서는 단일 동시 잠금만 획득할 수 있고 GET_LOCK()이 기존 잠금을 해제할 수 있습니다.
SELECT GET_LOCK('jade-1',5);
SELECT GET_LOCK('jade-2',5);
SELECT RELEASE_LOCK('jade-2');
SELECT RELEASE_LOCK('jade-1');
MySQL 5.6 에서는 두 번째 GET_LOCK('jade-2',5)이 실행 되면서 첫 번째 잠금을 해제하고('jade-1',5) 그리고 해제할 항목 이 없기 때문에 RELEASE_LOCK('jade-1') 실행시 반환 실패 결과값 인 NULL 을 받게 됩니다.
MySQL 5.7 이상에서 두 번째 GET_LOCK('jade-2',5)는 두 번째 잠금을 획득하고 두 RELEASE_LOCK() 호출 모두 1(성공)을 반환합니다.
메타데이터 락
메타데이터 락(Metadata Lock) 은 데이터베이스 객체(대표적으로 테이블이나 뷰 등)의 이름이나 구조를 변경 하는 경우에 획득하는 잠금 입니다. 메타데이터 락은 명시적으로 획득하거나 해제 할 수있는 것은 아님을 표현 하고 있습니다
다만 상황에 따라서 명시적으로 획득 이라고 볼수 있는 행동은 가능 합니다.
아래 와 같이 트랜잭션을 명시적으로 시작 후에 데이터를 조회 한다면 다른 세션에서 테이블의 구조를 변경하게 되면 Metadata Lock 으로 대기를 하게 됩니다.
-- Session 1
select CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
| 4 |
+-----------------+
<-- Session 1의 Process ID 는 4 로 확인됨
-- 트랜잭션 시작
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user_info;
+----+------+
| id | name |
+----+------+
| 1 | jade |
| 2 | Tom |
+----+------+
2 rows in set (0.00 sec)
-- Session 2 : 컬럼 추가 시도
mysql> alter table user_info add column col2 varchar(100);
<!!--- 락 획득 실패 및 락에 의해 대기중
-- Session 1 : Full processlist
mysql> show full processlist;
+----+------------+------+------+---------------------------------+----------------------------------------------------+
| Id | User | db | Time | State | Info |
+----+------------+------+------+---------------------------------+----------------------------------------------------+
| 2 | system user| NULL | 1321 | Connecting to master | NULL |
| 4 | root | npm | 0 | starting | show full processlist |
| 8 | root | npm | 10 | Waiting for table metadata lock | alter table user_info add column col2 varchar(100) |
+----+------------+------+------+---------------------------------+----------------------------------------------------+
<!!-- Metadata lock 에 의해 Session 2가 대기 하는 상황
"RENAME TABLE tb_old to tb_new; " 같이 테이블 이름을 변경하는 경우 자동으로 획득이 됩니다.
RENAME TABLE 명령의 경우 원본 이름과 변경될 이름 두개 모두 한꺼번에 잠금을 설정 합니다.
실시간으로 테이블을 RENAME 을 해야 할 요건의 경우에는 하나의 RENAME 명령문 안에서 같이 처리하는 방식이 더 도움이 되게 됩니다. 또한 2개 테이블을 RENAME 명령어 하나로 처리할 경우 "Table not found" 와 같은 상황은 발생되지 않게 됩니다.
하지만 매우 짧은 시간이라도 RENAME TABLE 구문을 순차적으로 별도로 실행 한다면 아주 짧은 시간에 해당 테이블을 쿼리하는 경우 해당 쿼리의 세션에서는 "Table not found ...." 오류를 받게 됩니다.
Metadata Lock 을 획득 하고 있는 Holder 에 정보는 performance_schema 의 metadata_locks 딕셔너리로 확인 할 수 있습니다.
MySQL 5.7에서는 setup_instruments 테이블의 값의 변경을 해야 metadata_locks 에 정보 및 Holder 정보를 확인 할 수 있습니다.
-- 데이터베이스 선택 use performance_schema; -- setup_consumers 변경 SELECT * FROM setup_consumers WHERE NAME = 'global_instrumentation'; -- 위의 조회결과가 NO 라면 아래 쿼리 실행 -- YES 라면 아래의 업데이트 문 생략 UPDATE setup_consumers SET ENABLED = 'YES' WHERE NAME = 'global_instrumentation'; SELECT * FROM setup_instruments WHERE NAME = 'wait/lock/metadata/sql/mdl'; -- 위의 쿼리에서 ENABLED 가 NO 면 아래 update 수행 -- YES 라면 업데이트 문 실행 생략 UPDATE setup_instruments SET ENABLED = 'YES',TIMED='YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';
Metadata Lock 에 대한 정보 확인이 가능하도록 설정이 완료 된 다음 Metadata Lock 으로 대기가 발생할 경우 아래와 같이 쿼리를 조회해보시면 여러 정보를 확인하실 수 있습니다.
mysql> select b.OBJECT_TYPE, b.OBJECT_SCHEMA, b.OBJECT_NAME, b.LOCK_TYPE, b.LOCK_STATUS, c.THREAD_ID, c.PROCESSLIST_ID -- ,c.PROCESSLIST_INFO from performance_schema.metadata_locks a join performance_schema.metadata_locks b on a.OWNER_THREAD_ID<>b.OWNER_THREAD_ID and a.OBJECT_NAME=b.OBJECT_NAME and a.LOCK_STATUS = 'PENDING' join performance_schema.threads c on b.OWNER_THREAD_ID = c.THREAD_ID; +-------------+---------------+-------------+-------------+-------------+-----------+----------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_STATUS | THREAD_ID | PROCESSLIST_ID | +-------------+---------------+-------------+-------------+-------------+-----------+----------------+ | TABLE | npm | user_info | SHARED_READ | GRANTED | 30 | 4 | +-------------+---------------+-------------+-------------+-------------+-----------+----------------+
MySQL 8.0에서는 setup_instruments 에서 "wait/lock/metadata/sql/mdl" 영역이 기본값으로 활성화(ENABLED) 임으로 별도의 변경없이 Metadata Lock 조회가 가능 합니다.
이어지는 다음글
Reference
Reference Book
• Real MySQL 8.0
Reference URL
• mysql.com/locking-functions
• hyos-dev-log.tistory.com/9
• azelcast.com/distributed-locks
관련 된 다른글
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
잘 보고 갑니다
안녕하세요
참조 하였던 책의 내용이 좋고 잘 정리 되어 있어서 블로그에 정리하는데
수월 했던 것 같습니다,
소중한 코멘트 감사합니다.
좋은 하루 되세요