책너두 (Real MySQL 8.0 1권) 14일차 (~169p)

현재 책너두 1.5기 모집 링크 입니다 : https://breakbook.notion.site

요약

• MySQL 엔진에서 제공하는 잠금에 대한 종류와 각 기능을 이해하게 됨.
  • 글로벌 락, 테이블 락, 네임드 락, 메타데이터 락
• InnoDB 스토리지 엔진에서 제공하는 잠금에 대한 종류와 각 기능을 이해하게 됨.
  • 레코드 락, 갭 락, 넥스트 키 락, 자동 증가 락

메모

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 로 일관된 백업을 받아야 할 때 글로벌 락을 사용해야 한다.

FLUSH TABLES WITH READ LOCK 명령은 테이블에 읽기 잠금을 건다. 읽기 잠금을 걸기 전에 먼저 테이블을 플러시 수행한다. 그래서 이 명령을 수행하려면 테이블에 실행 중인 모든 종류의 쿼리가 완료되야 함.
→ 장시간 실행되는 쿼리가 있다면 MySQL 서버의 모든 테이블에 DML 쿼리가 오래동안 실행되지 않을 수 있음. 따라서 웹 서비스용 MySQL 서버에서는 가급적 사용하지 않는게 좋다.

• InnoDB 스토리지 엔진은 트랜잭션을 지원하기에 일관된 데이터 상태를 위해 모든 데이터 변경 작업을 멈출 필요는 없음.
  • 8.0 부터 InnoDB 가 기본 스토리지 엔진으로 채택되면서 좀 더 가벼운 글로벌 락의 필요성이 생김.
    • 8.0 부터 Xtrabackup 이나 Enterprise Backup 과 같은 백업 툴들의 안정적인 실행을 위해 백업 락이 도입 됨.

mysql> LOCK INSTANCE FOR BACKUP;
// ...
// 백업 실행
// ...
mysql > UNLOCK INSTANCE;

• 위 명령어와 같이 백업 락을 획득할 수 있다.
• 특정 세션에서 백업 락을 획득하면 모든 세션에서 다음과 같이 테이블의 스키마나 사용자 인증 관련 정보를 변경할 수 없다.
  • 데이터베이스 및 테이블 등 모든 객체 생성 및 변경, 삭제
  • REPAIR TABLE 과 OPTIMIZE TABLE 명령
  • 사용자 관리 및 비밀번호 변경
• 백업 락은 일반적인 테이블의 데이터 변경은 허용됨.
• 일반적인 MySQL 서버 구성은 소스 서버 (Source server) 와 레플리카 서버 (Replica server) 로 구성 됨.
  • 주로 백업은 레플리카 서버에서 실행 됨.
  • 백업을 위한 글로벌 락을 획득하면 복제 는 백업 시간만큼 지연될 수밖에 없음.
    • 만약, 레플리카 서버에서 백업 실행 중 소스 서버에 문제가 생기면 레플리카 서버의 데이터가 최신 상태가 될 때까지 서비스를 멈춰야 할 수 있음.
    • XtraBackup, Enterprise Backup 툴이 백업 도중 DDL 명령으로 인해 스키마 변경이 실행되면 백업이 실패함. → 다시 시간을 들여 백업해야함.
      • 이런 문제를 해결하기 위해 백업 락이 도입됨.

테이블 락

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

네임드 락

• GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있음.
• 대상이 테이블, 레코드, AUTO_INCREMENT 와 같은 데이터베이스 객체가 아님.
  • 단순히 사용자가 지정한 문자열에 대해 락을 획득, 해제하는 잠금임.
• 네임드 락은 자주 사용되지는 않음.

// "mylock" 이라는 문자열에 대해 잠금을 획득
// 이미 잠금을 사용중이면 2초동안 대기함 (2초 이후 자동 잠금해제됨)
mysql> SELECT GET_LOCK('mylock', 2);

// "mylock" 문자열에 대한 잠금 설정돼어있는지 확인
mysql> SELECT IS_FREE_LOCK('mylock');

// "mylock" 문자열에 대해 획득했던 잠금을 해제함
mysql> SELECT RELEASE_LOCK('mylock');

• 위 함수는 네임드 락에서 사용하는 함수들임.
  • 3개 함수 모두 정상적으로 락을 획득하거나 해제했다면 1을 반환한다.
    • 그렇지 않으면 NULL 이나 0을 반환한다.
• 네임드 락은 많은 레코드에 대해 복잡한 조건으로 레코드를 변경하는 트랜잭션에 유용하게 사용 가능함.
• 배치 프로그램으로 한꺼번에 많은 레코드를 변경하는 쿼리는 자주 데드락의 원인이 된다.
  • 각 프로그램의 실행 시간을 분산하거나 코드를 수정하여 데드락을 최소화 할 수 있음.
    • 이는 간단한 방법이 아니며, 최종 해결책이 될 수 없음.
  • 동일 데이터를 변경하거나 참조하는 프로그램끼리 분류해서 네임드 락을 걸고 쿼리를 실행하면 간단히 문제를 해결할 수 있음.
• 8.0 부터 네임드 락을 중첩해서 사용할 수 있음.
• 현재 세션에서 획득한 네임드 락을 한 번에 모두 해제하는 기능도 추가됨. (RELEASE_ALL_LOCKS() 이용)

메타데이터 락

• 데이터베이스 객체 (ex: 테이블, 뷰) 의 이름이나 구조를 변경하는 경우 획득하는 잠금
• 메타데이터 락은 명시적으로 획득, 해제할 수 없음.
  • “RENAME TABLE tab_a TO tab_b” 와 같이 테이블 이름을 변경할 때 자동으로 획득하는 잠금임.
  • RENAME TABLE 명령의 경우, 원본 이름과 변경될 이름 두 개 모두 한번에 잠금을 설정함.
    • 두 개의 rename 작업을 한번에 실행하는 것임. (Table not found 테이블이름 ← 과 같은 에러는 발생하지 않음)
  • 실시간으로 테이블을 바꿔하야하는 조건이 배치 프로그램에 많이 발생함.
• 메타 데이터 잠금과 InnoDB 트랜잭션을 동시에 사용해야 하는 경우도 있음.
  • ex) INSERT 만 실행되는 로그 테이블 (UPDATE, DELETE 가 없음)
    • 만약, 로그 테이블의 구조를 변경해야한다면 Online DDL 을 이용해 변경할 수도 있지만 시간이 너무 오래 걸린다면 언두 로그도 많아지고 Online DDL 실행되는 동안 누적인 Online DDL 버퍼 크기 들도 고민해야 함.
    • 또, MySQL 서버의 DDL 은 단일 스레드로 작동하므로 상당히 많은 시간이 소모됨.
      • 이 때, 새로운 구조의 테이블을 생성하고 최근 1시간, 또는 하루전 데이터까지 프라이머리 키인 id 값으로 범위를 나눠 여러 개의 스레드로 빠르게 복사하는 방법이 있음.
      • 범위를 나누고, 나머지 데이터 는 트랜잭션, 테이블 잠금, RENAME TABLE 명령으로 응용 프로그램 중단 없이 실행할 수 있음.
        • 대신, 남은 데이터 복사하는 시간동안은 테이블 잠금때문에 INSERT 할 수는 없음.
          • 가능한 미리 최근 데이터까지 복사해둬서 잠금 시간을 최소화 해야 서비스에 미치는 영향을 줄일 수 있음.

InnoDB 스토리지 엔진 잠금

• InnoDB 스토리지 엔진은 MySQL에서 제공하는 잠금과 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하고 있음.
  • 이 방식때문에 MyISAM 보다는 훨씬 뛰어난 동시성 처리를 제공함.
  • 하지만 이원화된 잠금 처리 때문에 InnoDB 스토리지 엔진에서 사용되는 잠금에 대한 정보는 MySQL 명령을 이용해 접근하기 까다로움.
  • 이전 버전에서는 MySQL 서버에서 InnoDB 잠금 정보를 진단하는 도구로 lock_monitor(innodb_lock_monitor) 이름의 InnoDB 테이블을 생성해서 InnoDB 의 잠금 정보를 덤프하는 방법과 SHOW ENGINE INNODB STATUS 명령이 전부였음.
    • 이마저도 어셈블리 코드를 보는 것 같아서 이해하기 상당히 어려ㅇ웠음.
      • 최근 버전에서 InnoDB의 트랜잭션과 잠금, 그리고 잠금 대기 중인 트랜잭션의 목록을 조회할 수 있는 방법이 도입됨.
        • MySQL 서버의 information_schema 데이터베이스에 존재하는 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 라는 테이블을 조인해서 조회하면 현재 어떤 트랜잭션이 잠금 대기하고 있고, 해당 잠금을 어느 트랜잭션이 가지고 있는지 확인할 수 있다.
        • 장시간 잠금을 가지고 있는 클라이언트를 찾아 종료할 수 도 있음.
        • MySQL 이 계속 업데이트 되면서 InnoDB 의 중요도가 높아지고 InnoDB 잠금에 대한 모니터링도 더 강화되면서 Performane Schema 를 이용하여 InnoDB 스토리지 엔진의 내부 잠금에 대한 모니터링 방법도 추가됨.

InnoDB 스토리지 엔진의 잠금

• 레코드 기반의 잠금 기능을 제공 → 잠금 정보가 상당히 작은 공간으로 관리되기 때문에 레코드 락이 페이지 락으로, 또는 테이블 락으로 레벨업(락 에스컬레이션) 되는 경우는 없음.
• 상용 DBMS 와 달리, InnoDB 스토리지 엔진에서는 레코드 락 뿐만 아니라 레코드와 레코드 사이의 간격을 잠그는 갭(GAP) 락이 존재함.

• 위 그림은 InnoDB 잠금의 종류임. (점선으로 된 부분의 레코드는 실제 존재하지 않는 레코드를 가정한 것임)

레코드 락

• 레코드 자체만 잠그는 것을 레코드 락(Record lock, Record only lock) 이라고 함.
• 다른 상용 DBMS 레코드 락과 동일한 역할을 함.
  • 차이점 이라면 InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다는 점임.
  • 인덱스가 하나도 없는 테이블이라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다.
    • 레코드 자체를 잠그느냐, 인덱스를 잠그느냐는 상당히 크고 중요한 차이를 만들어냄.
      • 추후 다시 예제로 살펴봄
• InnoDB 에서 대부분 보조 인덱스를 이용한 변경 작업은 다음에 나올 넥스트 키 락(Next key lock) 또는 갭 락(Gap lock) 을 사용한다.
  • 하지만 프라이머리 키 또는 유니크 인덱스에 의한 변경 작업에서는 갭(Gap) 에 대해 잠금되지 않고 레코드 자체에 대해서만 락을 건다.

갭 락

• 다른 DBMS 와의 차이가 바로 갭 락이다.
• 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만 잠그는 것을 의미함.
• 갭 락의 역할을 레코드와 레코드 사이의 간격에 새로운 레코드가 생성(INSERT)되는 것을 제어한다.
• 갭 락은 자체로 사용되기 보다는 넥스트 키 락의 일부로 자주 사용됨

넥스트 키 락

• 레코드 락과 갭 락을 합쳐 놓은 형태의 잠금이다.
• STATEMENT 포맷의 바이너리 로그를 사용하는 MySQL 서버에서는 REPEATABLE READ 격리 수준을 사용해야 함.
• innodb_locks_unsafe_binlog 시스템 변수가 비활성화 되면 (= 0) 변경을 위해 검색하는 레코드에는 넥스트 키 락 방식으로 잠금이 걸림.
  • InnoDB 의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주목적임.
• 넥스트 키 락과 갭 락으로 인해 데드락이 발생하거나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생함.
  • 가능하면 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는게 좋음.

5.5 버전까지는 ROW 포맷의 바이너리 로그가 도입된 지 오래되지 않아서 널리 사용되지 않음. 5.7 버전과 8.0 버전으로 업그레이드 되면서 ROW 포맷의 바이너리 로그에 대한 안정성이 높아짐. STATEMENT 포맷의 바이너리 로그가 가지는 단점을 많이 해결해줄 수 있게 되면서 MySQL 8.0 에서는 ROW 포맷의 바이너리 로그가 기본 설정으로 변경됨.

자동 증가 락

• MySQL에서 자동 증가하는 숫자 값을 추출하기 위해 AUTO_INCREMENT 라는 칼럼 속성을 제공한다.
  • AUTO_INCREMENT 칼럼이 사용된 테이블에 동시에 여러 레코드가 INSERT 되는 경우, 저장되는 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져야 함.
  • InnoDB 스토리지 엔진에서는 이를 내부적으로 AUTO_INCREMENT 락 (Auto increment lock) 이라고하는 테이블 수준의 잠금을 사용함.
• AUTO_INCREMENT 락은 INSERT 와 REPLACE 쿼리 문장과 같은 새로운 레코드를 저장하는 쿼리에서만 필요함.
  • UPDATE, DELETE 쿼리에서는 해당 락이 걸리지 않음.
• InnoDB 의 레코드 락, 넥스트 키 락과는 달리, AUTO_INCREMENT 락은 트랜잭션과 관계없이 INSERT 나 REPLACE 문장에서 AUTO_INCREMENT 값을 가져오는 순간만 락이 걸렸다가 즉시 해제된다.
  • 즉 해당 락은 아주 짧은 시간 동안 걸렸다가 해제되는 잠금이라 대부분의 경우 문제되지 않음.
• AUTO_INCREMENT 락은 테이블에 단 하나만 존재하기 떄문에 두 개의 INSERT 쿼리가 동시에 실행되는 경우, 하나의 쿼리가 AUTO_INCREMENT 락을 걸면 나머지 쿼리는 해당 락을 기다려야 함.
  • AUTO_INCREMENT 칼럼에 명시적으로 값을 설정하더라도 자동 증가 락을 걸게 됨)
• AUTO_INCREMENT 락을 명시적으로 획득, 해제하는 방법은 없음.
• 지금까지의 자동 증가 락에 대한 설명은 5.0 이하 버전에서 사용되던 방식임.
• 5.1 이상부터 innodb_autoinc_lock_mode 라는 시스템 변수를 이용해 자동 증가 락의 작동 방식을 변경할 수 있음.
  • innodb_autoinc_lock_mode=0
    • 5.0과 동일한 방식으로 모든 INSERT 문장은 자동 증가 락을 사용함.
  • innodb_autoinc_lock_mode=1
    • 단순히 한 건 또는 여러 건의 레코드를 INSERT 하는 SQL 중에서 MySQL 서버가 INSERT 되는 레코드의 건수를 정확히 예측할 수 있을 때는 자동 증가 락을 사용하지 않고 훨씬 가볍고 빠른 래치(뮤텍스) 를 이용해서 처리한다.
    • 개선된 래치는 자동 증가 락과 달리 아주 짧은 시간동안만 잠금을 걸고 필요한 자동 증가 값을 가져오면 즉시 잠금이 해제됨.
    • 하지만 INSERT … SELECT 와 같이 MySQL 서버가 건수를 (쿼리 실행하기전에) 예측할 수없을 떄는 5.0 과 같이 자동 증가 락을 사용함.
      • INSERT 문장이 완료되기 전까지 자동 증가 락은 해제되지 않아서 다른 커넥션에서는 INSERT 를 실행하지 못하고 대기함.
      • InnoDB 스토리지 엔진에서는 대량의 INSERT 가 수행될 때, 여러 개의 자동 증가 값을 한번에 할당받아서 INSERT 되는 레코드에 사용한다.
        • 따라서 대량으로 INSERT 되는 레코드는 자동 증가 값이 누락되지 않고 연속적으로 INSERT 됨.
        • 그런데, 한 번에 할당 받은 자동 증가 값을 너무 많이 받아서 남게되고 사용되지 못하면 폐기함.
          • 그래서 대량 INSERT 실행 이후에 INSERT 되는 레코드의 자동 증가 값이 누락되어 연속되지 않은 값을 가지게 됨.
          • 즉, 이 설정은 하나의 INSERT 문장으로 INSERT 되는 레코드는 연속된 자동 증가값을 가지게됨.
          • 이 설정 모드를 연속 모드(Consecutive mode) 라고도 함.
  • innodb_autoinc_lock_mode=2
    • InnoDB 스토리지 엔진은 절대 자동 증가락을 걸지 않고 경량화된 래치(뮤텍스)를 사용함.
      • 이 설정에서는 하나의 INSERT 문장으로 INSERT 되는 레코드 라도 연속된 자동 증가 값을 보장하지 않음.
      • 이 설정 모드를 인터리빙 모드(Interleaved mode) 라고도 함.
      • 이 설정에서 INSERT … SELECT 같은 대량의 INSERT 문장이 실행되는 도중, 다른 커넥션에서 INSERT 를 수행할 수 있으므로 동시 처리 성능이 높아짐.
      • 하지만 자동 증가 기능은 유니크한 값이 생성된다는 것만 보장함.
      • STATEMENT 포맷의 바이너리 로그를 사용하는 복제에서는 소스 서버와 레플리카 서버의 자동 증가 값이 달라질 수 있기 때문에 주의해야 함.
• 자동 증가 값이 한 번 증가하면 절 대 줄어들지 않는 이유는 AUTO_INCREMENT 잠금을 최소화 하기 위함이다.
  • INSERT 쿼리가 실패했더라도 한 번 증가된 AUTO_INCREMENT 값은 다시 줄어들지 않고 그대로 남는다.

5.7 버전까지 innodb_autoinc_lock_mode 기본 값이 1이었지만 8.0 부터는 기본 값이 2로 바뀜. 즉, 8.0 부터는 바이너리 로그 포맷이 STATEMENT 가 아니라 ROW 포맷이 기본값이 됐기 때문임. 만약 8.0 이 STATEMENT 포맷의 바이너리 로그를 사용한다면 innodb_autoinc_lock_mode 기본값을 2가아닌 1로 변경해서 사용할 것을 권장함.