MySQL에서 사용되는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다.
스토리지 엔진레벨의 잠금은 스토리지 엔진 간 상호 영향을 미치지 않는다MySQL 엔진레벨의 잠금은 모든 스토리지 엔진에 영향을 미친다
MySQL 엔진 잠금
글로벌 락
- 글로벌 락은
FLUSH TABLES WITH READ LOCK명령으로 획득할 수 있고, MySQL에서 제공하는 잠금 가운데 가장 범위가 크다 - 한 세션에서
글로벌 락을 획득하면 다른 세션에서SELECT를 제외한 대부분의 DDL/DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 해당 문장이대기 상태로 남는다 글로벌 락이 영향을 미치는 범위는MySQL 서버 전체이며, 작업 대상 테이블이나 데이터베이스가 다르더라도 동일하게 영향을 미친다- 여러 데이터베이스에 존재하는 MyISAM이나 MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받아야 할 때는
글로벌 락을 사용해야 한다 - 하지만 MySQL 서버가 업그레이드하면서 MyISAM/MEMORY 스토리지 엔진보다는 InnoDB 스토리지 엔진의 사용이 일반화됐다
InnoDB 스토리지 엔진은 트랜잭션을 지원하기 때문에 일관된 데이터 상태를 위해 모든 데이터 변경 작업을 멈출 필요는 없다MySQL 8.0부터 InnoDB가 기본 스토리지 엔진으로 채택되면서 조금 더 가벼운 글로벌 락의 필요성이 생겼고, 백업 툴들의 안정적 실행을 위한백업 락이 도입됐다
백업 락
- 특정 세션에서 백업 락을 획득하면 모든 세션에서 다음과 같이 테이블의 스키마나 사용자의 인증 관련 정보를 변경할 수 없게 된다
- 데이터베이스 및 테이블 등 모든 객체 생성 및 변경, 삭제
REPAIR TABLE과OPTIMIZE TABLE명령- 사용자 관리 및 비밀번호 변경
- 하지만, 일반적인 테이블의 데이터 변경은 허용된다
- 일반적인 MySQL 서버 구성은 소스 서버와 레플리카 서버로 구성되고, 백업은 주로 레플리카 서버에서 실행된다
- 여기서 백업이
FLUSH TABLES WITH READ LOCK명령을 이용해 글로벌 락을 획득하면 복제는 백업 시간만큼 지연될 수 밖에 없다 - 레플리카 서버에서 백업을 실행하는 도중에 소스 서버에 문제가 생기면 레플리카 서버의 데이터가 최신 상태가 될 때까지 서비스를 멈춰야 할 수도 있다
- 백업 툴이 실행되는 도중에 스키마 변경이 실행되면 백업은 실패하게 된다
- 이처럼
정상적으로 복제는 실행되지만 백업의 실패를 막기 위해 DDL 명령이 실행되면 복제를 일시 중지 하는 역할을 한다
테이블 락
- 개별 테이블 단위로 설정되는 잠금
- 명시적/묵시적으로 특정 테이블의 락을 획득할 수 있음
LOCK TABLES table_name [ READ | WRITE ]- 명시적으로 획득한 잠금은
UNLOCK TABLES명령으로 잠금을 반납(해제)할 수 있음 - 특별한 상황이 아니면 사용할 필요가 없는데, 글로벌 락과 동일하게
온라인 작업에 상당한 영향을 미치기 때문이다 - 묵시적인 테이블 락은 MyISAM/MEMORY 테이블에 데이터를
변경하는 쿼리를 실행하면 발생한다 - MySQL 서버가 데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후,
즉시 잠금을 해제하는 형태로 사용된다 - 즉, 쿼리가 실행되는 동안
자동으로 획득됐다가 쿼리가 완료되면자동 해제된다 InnoDB의 경우 스토리지 엔진 차원에서레코드 기반 잠금을 제공해서, 단순 데이터 변경 쿼리로 인해묵시적 테이블 락이 설정되지는 않는다- 더 자세히는, InnoDB 테이블에도 락이 설정되지만, 대부분의
데이터 변경(DML)쿼리에서는 무시되고,스키마를 변경하는 쿼리(DDL)의 경우에만 영향을 미친다
네임드 락
GET_LOCK함수를 통해 임의의 문자열에 대해 잠금을 설정할 수 있다- 특징은 대상이 테이블이나 레코드 또는
AUTO_INCREMENT와 같은 데이터베이스 객체가 아니라는 것이다 - 단순히 사용자가 지정한
문자열에 대해 획득하고 반납(해제)하는 잠금이다 여러 클라이언트가 상호 동기화를 처리해야할 때네임드 락을 이용하면 쉽게 해결할 수 있다- 많은 레코드에 대해 복잡한 요건으로 레코드를 변경하는 트랜잭션에 유용하다
- 배치 프로그램처럼 한꺼번에
많은 레코드를 변경하는 쿼리는 자주데드락의 원인이 되곤 한다 - 각 프로그램의 실행 시간을 분산하거나 프로그램 코드를 수정해 데드락을 최소화할 수 있지만 이는 간단하지 않고, 완전한 해결책이 될 수 없다
- 이러한 경우에
동일 데이터를 변경하거나 참조하는 프로그램끼리 분류해,네임드 락을 걸고 쿼리를 실행하면 아주 간단히 해결할 수 있다 - MySQL 8.0 부터는 네임드 락을 중첩해서 사용할 수 있고, 현재 세션에서 획득한 네임드 락을 한 번에 모두 해제하는 기능도 추가됐다
메타데이터 락
- 데이터베이스 객체(테이블이나 뷰 등)의 이름이나 구조를 변경하는 경우에 획득하는 잠금
- 명시적으로 획득하거나 해제할 수 없고
RENAME TABLE tab_a TO tab_b같이 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금 - 테이블 서로 간
RENAME작업을 나눠서 실행하면 아주 짧은 순간이지만, RENAME하려는 테이블이 존재하지 않는 순간이 발생해 오류를 뱉는다 - 때로는, 메타데이터 잠금과 InnoDB의 트랜잭션을 동시에 사용해야 하는 경우도 있다
InnoDB 스토리지 엔진 잠금
InnoDB 스토리지 엔진은 MySQL에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하고 있다레코드 기반잠금 방식 덕분에 훨씬 뛰어난동시성 처리를 제공할 수 있다- 하지만,
이원화된 잠금처리 탓에 사용되는 잠금에 대한 정보는 MySQL 명령을 이용해 접근하기가 상당히 까다롭다 - 최근 버전에서 InnoDB의 트랜잭션과 잠금, 그리고 잠금 대기 중인 트랜잭션 목록을 조회할 수 있는 방법이 도입됐다
INN_DB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS- 위 테이블을 조회하면 현재 어떤 트랜잭션이 어떤 잠금을 대기하고 있는지, 장시간 잠금을 가지고 있는 클라이언트가 무엇인지 알 수 있다
InnoDB 스토리지 엔진 잠금 동작
- InnoDB 스토리지 엔진은
레코드기반의 잠금 기능을 제공 - 잠금 정보가 상당히 작은 공간으로 관리되기 때문에, 레코드 락이 페이지 락으로 또는 테이블락으로 레벨업 되는 경우(
락 에스컬레이션)은 없다 - 레코드 락뿐만 아니라 레코드와 레코드 사이의 간격을 잠그는
갭(GAP)락이라는 것이 존재한다
레코드 락
- 레코드 자체만을 잠그는 것을
레코드 락이라 한다 - 중요한 점은 InnoDB 스토리지 엔진은 레코드 자체가 아니라
인덱스의 레코드를 잠근다 - 인덱스가 하나도 없는 테이블이더라도 내부적으로
자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다 - 프라이머리 키 또는 유니크 인덱스에 의한 변경 작업에서는 레코드 자체에 대해서만 락을 건다
갭 락
- 레코드 자체가 아니라 레코드와 바로 인접한
레코드 사이의 간격만을 잠금 - 레코드와 레코드 사이의 간격에 새로운 레코드가
생성(INSERT)되는 것을 제어하는 역할 넥스트 키 락의 일부로 자주 사용됨
넥스트 키 락
- 레코드 락과 갭 락을 합쳐 놓은 형태의 잠금
REPEATABLE READ격리 수준을 사용해야 함innodb_locks_unsafe_for_binlog시스템 변수가 비활성화되면 변경을 위해 검색하는 레코드에는넥스트 키 락방식으로 잠금이 걸린다InnoDB의갭 락이나넥스트 키 락은 바이너리 로그에 기록되는 쿼리가레플리카 서버에서 실행될 때,소스 서버에서 만들어 낸 결과와동일한 결과를 만들어내도록 보장하는 것이 주목적- 하지만, 의외로 넥스트 키 락과 갭 락으로 인해
데드락이 발생하거나, 다른 트랜잭션을 기다리게 하는 일이 자주 발생한다 - 가능하다면
바이너리 로그 포맷을ROW형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋다
자동 증가(AUTO INCREMENT) 락
- MySQL에서는 자동 증가하는 숫자 값을 추출하기 위해
AUTO_INCREMENT라는 칼럼 속성을 제공한다 - 해당 속성이 적용된 테이블에
동시에 여러 레코드가INSERT되는 경우, 저장되는 각 레코드는중복되지 않고,저장된 순서대로 증가하는 일련번호 값을 가져야 한다 - InnoDB 스토리지 엔진에서는 이를 위해 내부적으로
AUTO_INCREMENT 락이라고 하는 테이블 수준의 잠금을 사용한다 - 트랜잭션과 관계없이
INSERT나REPLACE문장에서AUTO_INCREMENT값을 가져오는 순간만 락이 걸렸다가 즉시 해제된다 AUTO_INCREMENT락은 테이블에 단 하나만 존재하기 때문에, 두 개의INSERT쿼리가 동시에 실행되는 경우 하나의 쿼리가AUTO_INCREMENT락을 걸면 나머지 쿼리는AUTO_INCREMENT락을 기다려야한다AUTO_INCREMENT칼럼에 명시적으로 값을 설정해도 자동 증가 락을 걸게 된다- 대량
INSERT가 수행되면, InnoDB 스토리지 엔진은 여러 개의 자동 증가 값을 한 번에 할당받아서 INSERT되는 레코드에 사용한다 - 그래서, 대량 INSERT되는 레코드는 자동 증가 값이 누락되지 않고 연속되게 INSERT된다
매일 발전