MySQL에서 사용되는 잠금

MySQL에서 사용되는 잠금은 크게 두 가지 레벨로 나눌 수 있다.

• 스토리지 엔진 레벨의 잠금
  • 스토리지 엔진 간 상호 영향을 미치지는 않는다.
• MySQL 엔진 레벨의 잠금
  • MySQL 엔진: MySQL 서버에서 스토리지 엔진을 제외한 나머지 부분
  • 모든 스토리지 엔진에 영향을 미친다.
  • 테이블 락 이외에도 메타데이터 락, 네임드 락도 제공한다.

Global Lock

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

백업 락

• FLUSH TABLES WITH READ LOCK 명령을 이용한 글로벌 락은 MySQL 서버의 모든 변경 작업을 멈춘다.
• MySQL 8.0부터 InnoDB가 기본 스토리지 엔진으로 채택되면서 조금 더 가벼운 글로벌 락의 필요성이 생겼다.
  • InnoDB 스토리지 엔진은 트랜잭션을 지원하기 때문에 일관된 데이터 상태를 위해 모든 데이터 변경 작업을 멈출 필요는 없다.
• Xtrabackup이나 Enterprise Backup과 같은 백업 툴들의 안정적인 실행을 위해 백업 락이 도입되었다.

LOCK INSTANCE FOR BACKUP;
-- // 백업실행
UNLOCK INSTANCE;

특정 세션에서 백업 락을 획득하면 모든 세션에서 다음과 같이 테이블의 스키마나 사용자의 인증 관련 정보를 변경할 수 없게 된다.

• 데이터베이스 및 테이블 등 모든 객체 생성 및 변경, 삭제
• REPAIR TABLE과 OPTIMIZE TABLE 명령
• 사용자 관리 및 비밀번호 변경

하지만 일반적인 테이블의 데이터 변경은 허용된다.

• 백업 락은 백업의 실패를 박기 위해 DDL 명령이 실행되면 복제를 일시 중지하는 역할을 한다.

Table Lock

• 테이블 락은 개별 테이블 단위로 설정되는 잠금이다.
• 명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있다.
• 명시적으로는 LOCK TABLES table_name [ READ | WRITE ] 명령으로 획득
  • 명시적으로 획득한 잠금은 UNLOCK TABLES 명령으로 잠금을 반납(해제)
  • 특별한 상황이 아니면 명시적인 테이블 락을 사용할 필요가 거의 없다.
  • 명시적인 테이블 락은 온라인 작업에 상당한 영향을 미친다.
• 묵시적으로는 MyISAM이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생한다.
  • 쿼리가 실행되는 동안 자동으로 획득했다가 쿼리가 완료된 후 자동 해제
  • MySQL 서버가 데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후, 즉시 잠금을 해제하는 형태로 사용
• InnoDB 테이블의 경우 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 단순 데이터 변경 쿼리로 인해 묵시적인 테이블 락이 설정되지는 않는다.
  • InnoDB 테이블에도 테이블 락이 생성되지만, 대부분의 데이터 변경(DML) 쿼리에서는 무시되고 스키마를 변경하는 쿼리(DDL)의 경우에만 영향을 미친다.

Named Lock

• 네임드 락은 GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있다.
  • RELEASE_LOCK() 함수로 반환
• 단순히 사용자가 지정한 문자열(String)에 대해 획득하고 반납(해제)하는 잠금이다.
  • 대상이 테이블이나 레코드 또는 AUTO_INCREMENT와 같은 데이터베이스 객체가 아니다.
• 자주 사용되지는 않는다.
  • 여러 클라이언트가 상호 동기화를 처리해야 할 때 사용하면 쉽게 해결할 수 있다.
  • 많은 레코드에 대해 복잡한 요건으로 레코드를 변경하는 트랜잭션에 유용하게 사용할 수 있다.
• MySQL 8.0부터는 네임드 락을 중첩해서 사용할 수 있으며, 현재 세션에서 획득한 네임드 락을 한 번에 모두 해제하는 기능(RELEASE_ALL_LOCKS() )도 추가됐다.

Metadata Lock

• 메타데이터 락은 데이터베이스 객체(대표적으로 테이블이나 뷰 등)의 이름이나 구조를 변경하는 경우에 획득하는 잠금이다.
• 명시적으로 획득하거나 해제할 수 없다.
• 테이블의 이름을 변경하는 경우 자동으로 획득된다.
  • 원본 이름과 변경될 이름 두 개 모두 한꺼번에 잠금 설정
• 메타데이터 락과 InnoDB의 트랜잭션을 동시에 사용해야 하는 경우도 있다.
  • 예를 들어, INSERT만 실행되는 로그 테이블의 구조를 변경해야 할 요건이 발생한 경우
  • 새로운 구조의 테이블을 생성
  • 기존 테이블에서 여러 개의 스레드로 최근(1시간 직전 또는 하루 전)의 데이터까지는 빠르게 복사
  • 나머지 데이터는 트랜잭션과 테이블 잠금, RENAME TABLE 명령으로 응용 프로그램의 중단 없이 실행

Reference

참고 서적

📔 Real MySQL 8.0