[DB] 락(잠금)

안녕하세요. 회사와 함께 성장하고 싶은 KOSE입니다.

 

이번 포스팅은 RealMySQL8.0의 트랜잭션과 잠금에 대해서 정리하는 글을 작성하도록 하겠습니다.!

 

 

1. MySQL 엔진의 잠금

• MySQL에서 사용되는 잠금은 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나뉩니다.
• MySQL 엔진 레벨의 잠금은 스토리지 엔진 레벨 잠금에 영향을 줄 수 있습니다.
• MySQL 엔젠 레벨은 클라이언트로부터 쿼리를 받아 처리하고 결과를 반환하는 등 클라이언트에게 직접적인 엔진 레벨이지만, 스토리지 엔진 레벨은 DB 저장 및 검색 관리 방식 등을 결정하는 엔진으로 디스크에 가까운 역할을 수행하고 있습니다.

 

 

2. 글로벌 락 

글로벌락은 다음과 같은 명령으로 획득할 수 있습니다. MySQL에서 제공하는 잠금 중 가장 큰 범위로 select를 제외한 대부분의 DDL 혹은 DML 문장을 실행하는 경우 글로벌 락이 해제될 때까지 해당 문장이 대기 상태로 남습니다.

 

FLUSH TABLES WITH READ LOCK

 

FLUSH TABLES WITH READ LOCK은 테이블에 읽기 잠금을 걸기 전에 먼저 테이블을 플러시 해야 하므로 테이블에 실행 중인 모든 종류의 쿼리가 완료되어야 합니다.

 

 

3. 백업 락

 

MySQL8.0 버전부터는 XtraBackup 혹은 Enterprise Backup과 같은 백업 툴들의 안정적인 실행을 위해 백업 락이 도입되었습니다. 

• XtraBackup: 오픈 소스로 도입되는 InnoDB 스토리지 엔진 전용의 무료 백업 도구로 물리적인 백업 방식을 사용합니다.
  XtraBackup은 인크리멘탈 백업 파일을 사용하는데, 이는 마지막 전체 백업 이후 변경된 데이터만 백업하는 방법을 의미합니다. 따라서 백업 파일의 크기를 줄이고 백업 속도를 향상할 수 있습니다.
• Enterprise Backup은 기업용 백업 솔루션으로 별도의 백업 서버에서 실행됩니다. 

백업 락의 실행 과정이 잘 이해가 안되는 부분이 있어서 제가 이해한 바를 하단에 정리하였는데 이 부분은 실제 내용과 다를 수 있습니다.!

• 소스 서버에 있는 데이터를 백업하기 위해 레플리카 서버가 활용되는데, 백업을 위해 소스 서버 전체를 글로벌 락으로 잠그는 데에는 효율적이지 않습니다.
• 일반적으로 백업하는 과정에서 DDL 요청이 수행되면 백업에 실패하게 됩니다.
• 이러한 문제를 해결하기 위해  레플리카 서버에 백업 락을 설정하여 소스 서버는 DDL이 처리될 때 레플리카 서버는 잠시 DDL 처리가 완료되기까지 락을 해제하고 대기합니다. DDL이 종료되면 백업을 진행하게 됩니다.

 

 

4. 테이블 락

 

테이블 락은 명시적 또는 묵시적 락으로 특정 테이블의 락을 획득할 수 있습니다.

명시적으로 획득한 잠금은 잠금을 반납할 수 잇는데, 특별한 상황이 아니라면 사용할 일이 많이 없습니다.

반면, 묵시적 테이블 락은 MyISAM 혹은 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생합니다.

InnoDB 엔진에서는 테이블 락이 설정되기는 하지만 DML 쿼리에서는 레코드 락이 설정되고 DDL의 경우 영향을 미칩니다.

 

 

5. 네임드 락

 

네임드락은 GET_LOCK() 함수를 이용해 임의의 문자열에 대한 잠금을 설정하비다.

네임드 락은 단순히 사용자가 지정한 문자열에 대해 획득하고 반납하는 잠금입니다. 이러한 잠금으로 동일한 요청에 대한 반복 수행 등을 방지하는 역할을 수행할 수 있습니다. 

 

네임드 락은 분산락과 비슷하지만 다른 용도로 사용됩니다.

네임드 락	분산 락
문자열(키)로 잠금	문자열(키)로 잠금
공유 자원 접근을 관리하되, 단일 데이터베이스에서 상호 동기화 처리에 용이	여러 데이터베이스 클러스터간 공유 자원 접근을 제어

 

 

6. InnoDB 스토리지 엔진 잠금

 

InnoDB 스토리지 엔진은 레코드 기반의 잠금 기능을 제공하며, 잠금 정보가 상당히 작은 공간으로 관리되기 때문에 레코드 락이 페이지 락으로 또는 테이블 락으로 레벨업 되는 경우는 없습니다.

 

InnoDB 스토리지 엔진은 레코드 락과 갭 락, 넥스트 키 락을 제공합니다.

사실 이 부분은 명확하게 이해하기가 너무 어려웠습니다. 따라서 먼저 이론적인 내용을 정리한 후, 실습을 통해 제가 이해한 바를 정리하도록 하겠습니다 (이 부분 역시 사실과 다를 수 있는 점 양해 부탁드립니다.)

 

<레코드 락>

• 레코드 자체를 잠그는 것을 레코드 락이라고 합니다. InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠급니다. 
• 인덱스가 없는 경우 자동 생성된 클러스터 인덱스를 이용해 잠금을 처리합니다.

 

<갭 락>

• 갭 락은 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미합니다.
• 갭 락의 역할은 레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어합니다.

 

<넥스트 키 락>

• 레코드 락과 갭 락을 합쳐 놓은 형태로 innodb_locks_unsafe_for_binlog 시스템 변수가 비활성화되면 변경을 위해 검색하는 레코드에는 넥스트 키 락 방식으로 잠금이 걸립니다.

 

 

<실습>

 

먼저, 실습 환경은 MySQL8.25 REPEATABLE READ 격리 수준입니다.

주어진 테이블의 인덱스를 확인하기 위해 아래의 명령어를 작성하여 프라이머리 키와 보조 인덱스를 확인하였습니다.

 

show index from employees;

 

 

이후, insert into 구문으로 새로운 데이터 2 개를 삽입하였습니다.

 

 

보조 인덱스로 검색할 경우, 보조 인덱스가 잘 적용되는지 확인하기 위해 실행 계획을 실행한 결과 올바르게 적용되었습니다.

 

 

먼저 1번 터미널에 start transaction을 작성하고, 2번 터미널에서도 start transaction을 적용하여 서로 격리된 트랜잭션을 적용하였습니다.

 

이 상태로, 1번 터미널에서 성별이 M이고 1997-01-01과 1997-01-03 사이에 업데이트를 위한 조회문을 작성하였습니다.

select * from employees where gender = 'M' and birth_date between '1997-01-01' and '1997-01-03' for update;

 

 

2번 터미널에서는  1997-01-02과 1997-01-03 사이인 1997-01-02일 값을 가지는 인서트문을 작성하였습니다.

 

insert into employees (emp_no, birth_date, first_name, last_name, gender, hire_date) values (1000003, '1997-01-02', 'Gose', 'Kose', 'M', '2023-09-01');

 

그 결과 갭 락이 적용되는 것을 확인할 수 있었습니다. 

 

 

사이값 뿐만 아니라, 시작점인 1997-01-01의 전날인 1996-12-31, 당일, 이후인 1997-01-04도 역시 갭 락이 적용되었습니다.

 

그렇다면 보조 인덱스가 아닌 프라이머리키를 적용할 때는 어떤 결과가 나올까요?

 

먼저 실행 계획으로 프라이머리키가 인덱스로 활용되는지 파악하였습니다.

 

<1번 터미널>

 

 

1000001 ~ 1000006번 사이에는 1000001, 1000002, 1000006번이 있습니다.

 

 

2번 터미널에서는 1000005를 pk로 가지는 임의의 값을 대입하였습니다 

insert into employees (emp_no, birth_date, first_name, last_name, gender, hire_date) values (1000005, '1996-12-31', 'Gose', 'Kose', 'M', '2023-09-01');

 

그 결과, 프라이머리키로 범위를 설정하였을 때도 갭 락이 적용되는 것을 확인할 수 있었습니다.

교재나 다른 글에서는 프라이머리 키 또는 유니크 인덱스에 의한 변경 작업에서 갭에 대해서는 잠그지 않고 레코드 자체에 대해서만 잠근다고 설명되어 있습니다. 

 

현재, LOCK_MODE에 GAP이 있지만 이 것이 어떠한 이유로 적용되었는지는 파악하기가 어려웠습니다. 제가 이해한 바로는 프라이머리 키 또는 유니크 인덱스에 의한 변경 작업은 직접적으로 pk를 이용하여 변경하므로 갭을 사용하지 않지만 위의 예시와 같이 범위를 설정하는 경우에는 갭락이 적용되는 것이라고 생각하게 되었습니다.

 

 

 

데이터베이스를 얕게 알 때는 몰랐지만, 공부를 하면 할수록 정말 어려운 것 같습니다.

락에 대해서 전 보다 깊게 배울 수 있었고 어떤 상황에는 어떠한 락이 적용되는지 파악할 수 있었습니다.

이상으로 포스팅을 마치겠습니다 감사합니다.!

 

 

참고자료: RealMySQL8.0 백은빈님 이성욱님 지음