MySQL의 Lock 종류와 동작 방식을 파헤쳐 보자

MySQL에서 사용되는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다.

MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치지만

스토리지 엔진 레벨의 잠금은 스토리지 엔진 간 상호 영향을 미치지 않는다.

각각의 엔진별로 어떠한 잠금들이 있는지 알아보자.

---

MySQL 엔진의 Lock

글로벌 락

MySQL에서 가장 큰 범위의 Lock으로 MySQL 서버 전체에 적용된다.

다음 명령으로 획득할 수 있다.

FLUSH TABLES WITH READ LOCK

한 세션에서 글로벌 락을 획득하면 다른 세션에서는 SELECT를 제외한 대부분의 DDL, DML이 글로벌 락 해제까지 대기 상태로 기다린다.

 

다음과 같은 상황에서 사용할 수 있다.

• 전체 DB의 물리적 백업이 필요할 때
• 모든 DB와 타 스토리지 엔진 테이블을 포함환 일관된 백업이 필요할 때
• 여러 DB간 동기화가 필요할 때

 

백업 락

글로벌 락 보다 가벼운 Lock으로 다음 명령으로 획득할 수 있다.

LOCK INSTANCE FOR BACKUP

특정 세션에서 백업 락을 획득하면 모든 세션에서 다음과 같은 작업이 차단/허용 된다.

• 데이터베이스/테이블 생성/삭제 등 스키마 변경 작업 차단
• REPAIR TABLE, OPTIMIZE TABLE 등의 테이블 관리 명령 차단
• 복제 관련 명령어(STOP SLAVE 등) 차단
• 사용자 관리 및 비밀번호 변경 차단
• 테이블의 변경 DML(INSERT, UPDATE, DELETE)은 허용

 

백업 중 테이블 구조가 변경되면 백업 파일의 일관성이 깨진다는 문제를 방지하기 위해 도입된 것으로

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

 

백업 락을 적용하기 좋은 문제 상황들을 보면 다음과 같다.

• 데이터베이스 백업 시 → 데이터 복사 중 테이블 구조가 변경되면 복제가 실패할 수 있음
• 복제 환경에서 새로운 슬레이브 구축 시 → 데이터 복사 중 테이블 구조가 변경되면 복제가 실패할 수 있음
• DB 서버 이전/마이그레이션 → 파일 복사 중 테이블 변경 등이 발생하면 문제가 될 수 있음

백업 락은 일반적인 데이터 변경은 허용하면서도, 물리적 구조 변경으로 인한 문제를 예방할 수 있어서

글로벌 락보다 범위가 더 작은 백업 락이 실용적인 선택이 될 수 있다.

 

테이블 락

개별 테이블 단위에 대한 잠금으로 명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있다.

 

명시적 테이블 락은 LOCK TABLES 명령으로 직접 설정하는 것이다.

-- 명시적 획득 : 읽기 락
LOCK TABLES table_name READ;

-- 명시적 획득 : 쓰기 락
LOCK TABLES table_name WRITE;

-- 락 반환
UNLOCK TABLES

명시적으로 테이블을 잠그는 것은 글로벌 락처럼 하나의 테이블이라는 큰 범위에 영향을 주기 때문에

특별한 상황이 아니라면 사용하지 않는 것을 권장한다.

 

묵시적 테이블 락은 MySQL이 데이터가 변경되는 테이블에 잠금을 자동으로 획득/반환한다.

하지만 InnoDB 스토리지 엔진의 경우 레코드 기반 잠금을 제공해

DML 실행 시에는 묵시적인 테이블 락이 설정되진 않고, DDL의 경우에 영향을 미친다.

 

테이블 락은 테이블 락을 이용한 데이터 마이그레이션 상황에서 유용하게 사용할 수 있다.

다음은 원본 테이블 access_log에서 새로운 access_log_new 테이블로 데이터를 마이그레이션하는 작업이다.

-- 트랜잭션 오토 커밋 false 설정
SET autocommit=0;

-- 테이블 락 획득
LOCK TABLES access_log WRITE, access_log_new WRITE;

-- 원본 테이블 데이터의 마지막 ID 확인
SELECT MAX(id) as @MAX_ID FROM access_log;

-- 새 테이블로 원본 테이블에 새로 추가된 최근 데이터까지 데이터 복사
INSERT INTO access_log_new 
SELECT * FROM access_log WHERE pk > @MAX_ID;

-- 커밋
COMMIT; 

RENAME TABLE 
    access_log TO access_log_old,      -- 기존 테이블 이름 변경
    access_log_new TO access_log;      -- 새 테이블을 기존 이름으로 변경
    
UNLOCK TABLES;                    -- 테이블 락 해제
DROP TABLE access_log_old;        -- 불필요한 테이블 삭제

이 경우 테이블 락을 사용해 데이터 복사 중에도 원본 테이블에 새로운 데이터를 안전하게 추가 가능하게 하면서

락이 필요한 작업에 대해서만 락을 걸어 긴 시간이 필요한 구조 변경 작업 중에도 서비스 영향 최소화할 수 있다.

 

네임드 락

임의의 문자열에 대해 잠금을 설정하는 것이다.

-- 네임 락 설정 (1번째 인자 : 락 키 이름, 2번째 인자 : 타임아웃)
SELECT GET_LOCK('str', 0);

-- 네임 락 반환
SELECT RELEASE_LOCK('str');

-- 모든 네임 락 해제
SELECT RELEASE_ALL_LOCKS();

잠금 대상이 테이블이나 레코드 가 아니라 사용자가 지정한 문자열에 대해 획득/반환하는 잠금이라는 것이 특징이다.

 

네임드 락은 다음과 같은 상황에서 유용하게 사용할 수 있다.

• 여러 서버의 데이터를 동기화 처리할 필요가 있을 때 MySQL을 사용한 분산락을 처리
• 동일 데이터에 대한 순차적 처리가 필요한 경우
• 배치 작업으로 많은 레코드를 한번에 변경해야할 경우 (배치 작업을 Lock 이름으로 구분해 동시성 제어)

 

메타데이터 락

DB 객체의 정의나 구조(테이블, 뷰, 내장 프로시저 등)를 보호하기 위한 잠금이다.

명시적으로 획득하거나 해제할 순 없고 객체를 변경할 자동으로 획득하는 잠금이다.

 

메타데이터 락은 SELECT 쿼리가 참조하는 DB 객체가 중간에 변경되는 것을 방지하는 역할을 한다.

---

InnoDB 스토리지 엔진 잠금

InnoDB 스토리지 엔진은 레코드 기반의 잠금 기능을 제공한다.

잠금의 범위가 MySQL 엔진 잠금에 비해 훨씬 작다.

어떤 락들을 제공하는지 알아보자.

레코드 락

우리가 일반적으로 아는 레코드 자체만을 잠그는 Lock이다.

중요한 점은 InnoDB는 레코드 자체가 아니라 인덱스의 레코드를 잠근다는 것이다.

인덱스가 하나도 없는 테이블이라면 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다.

잠금으로 정해지는 대상은 Where 절의 조건으로 확인할 수 있다.

 

주의할점은 인덱스가 없는 컬럼을 조건절로 사용해 잠금을 설정하면

인덱스가 없어서 테이블 전체 스캔 필요하고 모든 레코드에 대해 잠금이 걸려 테이블 락과 비슷환 효과가 발생하고

데드락이 발생할 확률이 높아지므로 주의할 필요가 있다.

 

아래는 Lock을 걸었다고 가정한 상황이다.

-- PK로 조회 또는 인덱스가 걸리지 않은 컬럼으로 조회 시
UPDATE users SET name = 'kaki' WHERE id = 1;  
=> id = 1인 레코드에만 정확히 락이 걸림

-- 세컨더리 인덱스로 검색
UPDATE users SET name = 'kaki' WHERE email = 'kaki@email.com'; 
=> email = 'kaki@email.com'인 레코드에 정확히 락이 걸림

-- 복합 인덱스로 검색 시
UPDATE users SET name = 'kaki' WHERE email = 'kaki@email.com' AND age = 28;
=> email = 'kaki@email.com' AND age = 28인 레코드에 락이 걸림

이를 통해 Where 절의 조건과 인덱스 설계가 레코드 락의 범위에 직접적인 영향을 끼친다는 것을 인지할 필요가 있다.

 

왜 레코드에 Lock을 거는 것이 아닌 인덱스에 락을 걸까?

아마 테이블 전체를 스캔하며 특정 레코드를 찾아 Lock을 걸고 반환하는 것이 아닌

B-Tree 구조를 사용하는 인덱스를 통해 특정 레코드를 빠르게 탐색하여 Lock을 걸고 반환하기 위함이 아닌가 싶다.

또한 필요한 범위에 대해 Lock을 걸고 싶은 상황이라면 항상 정렬된 인덱스에 Lock을 거는 것이 성능상 유리하기 위해서이기도 할 것이다.

 

갭 락

레코드 자체가 아닌 레코드와 인전합 레코드 사이의 간격만큼 잠그는 것이다.

아래 예시 코드는 age가 20-30 사이인 실제 레코드들에 대한 갭 락을 건 것이다.

-- users 테이블
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    age INT,
    INDEX idx_age (age)
);

-- users 테이블의 age 컬럼에 10, 20, 30이 있는 경우
SELECT * FROM users WHERE age BETWEEN 20 AND 30 FOR UPDATE;

중요한 점은 age가 20 ~ 30사이의 레코드 간격만 잠근 것이고 20, 30 레코드는 잠그지 않는다는 점이다.

즉, 레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어할 필요가 있다면 갭 락을 사용할 수 있다.

 

넥스트 키 락

레코드 락과 갭 락을 합쳐 놓은 형태이다.

-- users 테이블
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    age INT,
    INDEX idx_age (age)
);

-- users 테이블의 age 컬럼에 10, 20, 30이 있는 경우
SELECT * FROM salaries WHERE age = 20 FOR UPDATE;

위의 경우엔 넥스트 키 락은 age = 20인 레코드 자체를 잠그고

그 다음 키 값 age = 30까지의 갭을 같이 잠그는 것을 볼 수 있다.

즉, 20 ~ 30 범위를 잠그는데 20은 포함하고 30은 포함하지 않고 잠근다.

(”X” : 쓰기 락, “X,GAP” : 갭 락)

MySQL 기본 격리 수준인 REPEATABLE_READ을 사용하면 조건에 따라 넥스트 키 락을 사용한다.

READ_COMMITTED의 경우엔 기본적으로 레코드 락을 사용한다.

 

넥스트 키 락이 발생하는 상황

PK나 Unique 제약조건이 있어 결과가 1개임이 보장될 경우에는 레코드 락이 걸린다.

반면 결과 개수와 상관없이 Unique 제약조건이 없는 경우 넥스트 키 락이 걸린다.

또한 쿼리가 1개의 결과를 보장하지 않는 “복합 인덱스 중 일부 컬럼만 WHERE절 사용, BETWEEN 같은 연산자 사용” 같은 경우에도 넥스트 키 락이 걸린다.

 

REPEATABLE_READ(반복 가능한 읽기) 격리 레벨의 경우에는 Phantom Read(팬텀 리드) 문제가 있다.

InnoDB의 경우 REPEATABLE_READ 격리 레벨을 기본 값으로 사용하는데 이 팬텀 리드를 방지한다는 말을 들어본적이 있을 것이다.

바로 넥스트 키 락을 사용해 팬텀 리드를 방지한다.

넥스트 키 락이 걸리면 해당 범위에는 락 반환까지 다른 트랜잭션이 데이터를 추가할 수 없어 팬텀 리드가 방지되는 것이다.

즉, 넥스트 키 락은 "내가 보고 있는 범위에는 아무도 새로운 데이터를 넣을 수 없게 하자"라는 개념으로 팬텀 리드를 방지하는 것이다.

 

하지만 넥스트 키 락 조건에 해당하지 않으면 팬텀 리드가 방지되지 않나?라고 의문이 들었다.

넥스트 키 락 조건에 해당하지 않으면 트랜잭션 중간에 새로운 레코드가 추가는 되겠지만

결국 InnoDB에서는 MVCC를 사용한 일관된 읽기 기능으로 REPEATABLE_READ의 격리 레벨에서

트랜잭션 첫 조회시에 한 번 스냅샷을 저장하고 이후에 다른 트랜잭션에서 데이터가 변경되거나 추가되었다하더라도

다시 조회시엔 스냅샷이 생성된 시점 이후에 대한 데이터는 읽지 않아 팬텀 리드가 방지된다.

결국 InnoDB는 넥스트 키 락으로만 팬텀 리드를 방지한다기 보다는 MVCC와 넥스트 키 락을 사용하여 완벽히 팬텀 리드를 방지한다고 볼 수 있다.

 

 

 

여담이지만 innodb_locks_unsafe_for_binlog 시스템 변수로 넥스트 키 락 활성화 여부를 설정할 수 있었는데

8.0에서는 삭제되었고, 기본적으로 활성화되어 있다고 한다.

https://dev.mysql.com/worklog/task/?id=8894

 

자동 증가 락

AUTO_INCREMENT 컬럼이 사용된 테이블에 동시에 여러 레코드가 INSERT 될 경우

저장되는 레코드는 중복되지 않고 저장된 순데로 증가하는 일련번호 값을 가져야한다.

InnoDB 스토리지 엔진은 이를 위해 ‘자동 증가 락’이라는 테이블 수준의 잠금을 사용한다.

명시적으로 설정할 수 없으며 INSERT, REPLACE 같이 새로운 레코드를 추가하는 쿼리에만 자동으로 자동 증가 락이 걸린다.

아주 짧은 시간 동안 락이 걸렸다가 해제되는 잠금이라 대부분의 경우 문제가 되진 않는다.

자동 증가한 값이 한 번 증가하면 절대 줄어드지 않는 이유는 자동 증가 락의 잠금을 최소화하기 위해서이다.

---

인덱스와 잠금의 연관관계

위의 레코드 락에서도 설명했지만 InnoDB의 Lock과 인덱스는 상당한 연관 관계를 갖고 있다.

InnoDB는 레코드 잠금을 지원하는데 중요한 점은 레코드를 잠글 때 레코드 자체가 아니라 인덱스의 레코드를 잠근다는 것이다.

즉, 변경해야할 레코드를 찾기 위해 검색한 인덱스의 레코드에 모두 Lock을 건다.

 

각 트랜잭션이 어떤 잠금을 기다리고 있는지 아래 테이블의 정보로 확인할 수 있다.

SELECT * FROM performance_schema.data_locks;

 

salaries 테이블의 salary 컬럼에 인덱스가 설정되어 있는 상황이고 아래와 같이 쓰기 락을 설정했다.

SELECT * FROM salaries WHERE salary = 60000 FOR UPDATE;

결과는 다음과 같다.

(LOCK_DATE에 여러 데이터가 포함되어 있는데 salary = 60000에 해당하는 결과가 여러개다.)

salary = 60000에 해당하는 레코드들이 모두 잠긴 것을 볼 수 있다.

 

LOCK_MODE 용어 의미

• X : 쓰기 잠금 (베타적 락)
• S : 읽기 잠금 (공유 락)
• IX : 테이블의 일부분에 X lock을 걸겠다는 의도 표시
• IS : 테이블의 일부분에 S lock을 걸겠다는 의도 표시
• REC_NOT_GAP : 갭이 포함되지 않은 순수 레코드에 대해서만 잠금을 가지고 있음을 표시 (없다면 해당 레코드와 그 주변 갭에 대한 Next-Key Lock이 걸렸다는 의미)

 

IX, IS가 헷갈릴 수 있는데 테이블 레벨에서 "이 테이블의 일부 레코드들을 잠글 예정이에요"라고 표시하는 것이다.

다른 세션이 테이블 전체를 잠그려는 시도를 방지하기 위한 것이고 잠기지 않은 개별 레코드는 다른 세션이 사용 가능하다.

 

현재 세션에 IX, IS 락이 있을 때는 아래 호환성 표를 보고

다른 세션에서 해당 테이블에 락을 걸 수 있는지 여부를 확인할 수 있다.

http://www.innodbcluster.com/?depth=140203

 

잠금을 가진 상태에서 상당히 오랜 시간 멈춰 있다면 다음

2024.08.27 - [◼ CS 기초 지식/[데이터베이스]] - [데이터베이스/JPA] 낙관적 락, 비관적 락이란? 예시를 통해 쉽게 알아보자

과 같이 프로세스 리스트를 조회해 스레드를 강제 종료할 수 있다.

-- 실행 중인 프로세스 리스트 조회
SHOW PROCESSLIST;

-- 스레드 강제 종료
KILL 스레드_ID

---

낙관적, 비관적 Lock

낙관적 락과 비관적 락에 대해서는 아래 포스팅에서 설명한다.

2024.08.27 - [◼ CS 기초 지식/[데이터베이스]] - [데이터베이스/JPA] 낙관적 락, 비관적 락이란? 예시를 통해 쉽게 알아보자

[데이터베이스/JPA] 낙관적 락, 비관적 락이란? 예시를 통해 쉽게 알아보자

---

참고자료

• Real MySQL 8.0