[MySQL] 4. MySQL의 잠금

Preface

•

MySQL에서 사용되는 잠금

◦

스토리지 엔진 레벨 → 모든 스토리지 엔진에 영향

◦

MySQL 엔진 레벨 → 스토리지 엔진 간 상호 영향 X

1. MySQL 엔진의 잠금

1.1. 글로벌 락 (Global Lock)

•

개요

◦

MySQL 서버 전체에 영향을 미치는 잠금 유형

◦

FLUSH TABLES WITH READ LOCK 명령을 사용하여 획득 가능

◦

이 잠금은 서버 내 모든 테이블을 대상으로 함

◦

백업이나 복제를 위해 일관된 데이터 상태를 보장해야 할 때 사용

•

특징

◦

글로벌 락을 설정하면 다른 세션에서 SELECT를 제외한 대부분의 DDL(데이터 정의 언어) 및 DML(데이터 조작 언어) 문장은 글로벌 락이 해제될 때까지 대기 상태로 남음

◦

다중 데이터베이스의 MyISAM이나 MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받을 때 사용

◦

실행 중인 모든 종류의 쿼리가 완료되어야만 글로벌 락이 설정됨. 장시간 실행되는 쿼리가 있는 경우, 글로벌 락 설정까지 대기 시간이 길어질 수 있음

◦

일반적으로 웹 서비스용 MySQL 서버에서는 글로벌 락 사용을 권장하지 않으며, mysqldump 사용 시에도 글로벌 락을 내부적으로 사용할 수 있으므로 주의가 필요함

1.2. 테이블 락 (Table Lock)

•

개요

◦

테이블 락은 특정 테이블 단위로 설정되는 잠금

◦

명시적으로 LOCK TABLES table_name [READ WRITE] 명령을 사용해 획득하거나, 묵시적으로 데이터 변경 시 설정될 수 있음

•

명시적 테이블 락

◦

명시적으로 설정한 테이블 락은 UNLOCK TABLES 명령을 사용해 해제할 수 있음

◦

테이블 락은 성능에 큰 영향을 미칠 수 있기 때문에, 특별한 상황이 아니면 애플리케이션에서 명시적으로 사용하지 않는 것이 좋음

•

묵시적 테이블 락

◦

MyISAM이나 MEMORY

▪

데이터를 변경하는 쿼리를 실행하면 MySQL 서버는 자동으로 해당 테이블에 잠금을 설정한 후 데이터 변경 후 즉시 잠금을 해제하는 방식으로 동작

◦

InnoDB

▪

레코드 기반 잠금을 제공

▪

일반적인 데이터 변경(DML) 쿼리에서는 묵시적인 테이블 락이 설정되지 않음

▪

다만, 스키마 변경(DDL) 쿼리에서만 테이블 락이 설정될 수 있음

1.3. 네임드 락 (Named Lock)

•

개요

◦

특정 테이블이나 레코드와는 관계없이, 임의의 문자열에 대해 잠금을 설정할 수 있는 기능

•

특징

◦

네임드 락은 데이터베이스 객체가 아닌 단순히 사용자가 지정한 문자열(String)에 대해 설정되므로, 응용 프로그램이 특정 작업을 동기화하는 용도로 주로 사용

◦

여러 웹 서버가 하나의 데이터베이스 서버에 접속해 서비스하는 환경에서, 네임드 락을 사용하여 서로 다른 클라이언트가 같은 데이터를 동시에 수정하지 않도록 동기화할 수 있음

▪

분산락을 이용하여 동시성 해결하는 사례

◦

MySQL 8.0부터는 네임드 락을 중첩해서 사용할 수 있게 되었으며, 현재 세션에서 획득한 모든 네임드 락을 한꺼번에 해제하는 기능(RELEASE_ALL_LOCKS())도 추가

•

예시

-- "mylock"이라는 문자열에 대해 잠금을 획득합니다.
-- 이미 잠금이 사용 중인 경우, 2초 동안 대기합니다. (2초 후 자동으로 잠금 해제)
SELECT GET_LOCK('mylock', 2);

-- "mylock"이라는 문자열에 대해 잠금이 설정되어 있는지 확인합니다.
SELECT IS_FREE_LOCK('mylock');

-- "mylock"이라는 문자열에 대해 획득했던 잠금을 반납(해제)합니다.
SELECT RELEASE_LOCK('mylock');

-- 세 함수 모두 정상적으로 잠금을 획득하거나 해제한 경우 1을 반환합니다.
-- 그렇지 않으면 NULL이나 0을 반환합니다.
SQL
복사

1.4. 메타데이터 락 (Metadata Lock)

•

개요

◦

데이터베이스 객체(테이블, 뷰 등)의 이름이나 구조를 변경하는 경우 자동으로 획득되는 잠금

▪

예) RENAME TABLE - 메타데이터 락이 필요

•

특징

◦

명시적으로 획득하거나 해제할 수 없으며, 구조 변경 작업이 수행될 때 자동으로 됨

▪

예) 테이블의 이름을 변경하는 경우 

•

원래 이름과 변경될 이름 모두에 대해 메타데이터 락이 설정됨

→ 변경 중에 다른 트랜잭션이 해당 테이블에 접근하지 못하도록 보장

◦

실시간으로 테이블을 교체해야 하는 배치 프로그램에서 자주 사용 → 무중단 데이터를 갱신 가능

임시 테이블에 데이터를 준비

RENAME TABLE 명령을 사용해 기존 테이블을 새로운 테이블로 교체

◦

InnoDB 테이블에서 메타데이터 락과 트랜잭션을 동시에 사용할 수 있음

▪

복잡한 데이터 구조 변경이 필요한 상황에서도 일관성과 안정성을 유지

1.4.1. 예시

•

Insert 실행하는 로그 테이블 생성

CREATE TABLE access_log (
    id BIGINT NOT NULL AUTO_INCREMENT,
    client_ip INT UNSIGNED,
    access_dttm TIMESTAMP,
    PRIMARY KEY (id)
);
SQL
복사

•

테이블 구조 변경 요구사항 발생

◦

잠재 문제점

▪

Online DDL 사용가능하나, 오래 걸리는 경우 누적된 Online DDL 버퍼 크기 등 고려사항 많음

▪

MySQL DDL은 단일 스레드로 동작하여 소요시간 큼

◦

구현 방향

▪

새로운 구조 테이블 생성

▪

최근 데이터까지를 PK인 id 값을 범위별로 나누어 여러 스레드로 복사

-- 테이블의 압축을 적용하기 위해 KEY_BLOCK_SIZE=4 옵션을 추가하여 신규 테이블을 생성
CREATE TABLE access_log_new (
    id BIGINT NOT NULL AUTO_INCREMENT,
    client_ip INT UNSIGNED,
    access_dttm TIMESTAMP,
    PRIMARY KEY (id)
) KEY_BLOCK_SIZE=4;

-- 4개의 스레드를 이용해 id 범위별로 레코드를 신규 테이블로 복사
-- 첫 번째 스레드에서 id가 0 이상 10,000 미만인 레코드를 복사
mysql_thread1) INSERT INTO access_log_new 
               SELECT * FROM access_log 
               WHERE id >= 0 AND id < 10000;

-- 두 번째 스레드에서 id가 10,000 이상 20,000 미만인 레코드를 복사
mysql_thread2) INSERT INTO access_log_new 
               SELECT * FROM access_log 
               WHERE id >= 10000 AND id < 20000;
               
-- 세번째 네번째 스레드도 동일하게 진행 (생략)
SQL
복사

•

나머지 데이터는 트랜잭션, 테이블 잠금, RENAME TABLE 명령으로 무중단으로 실행 가능

◦

남은 데이터를 복사하는 시간 동안 테이블의 잠금으로 인해 INSERT 불가

◦

가능한 미리 아주 최근 데이터까지 복사해야 잠금 시간 최소화 → 서비스 영향 최소화

-- 트랜잭션을 autocommit 모드로 실행 (BEGIN이나 START TRANSACTION 사용하지 않음)
SET autocommit=0;

-- 작업 대상 테이블 두 개에 대해 테이블 쓰기 락을 획득
LOCK TABLES access_log WRITE, access_log_new WRITE;

-- 남은 데이터 복사
-- access_log 테이블에서 가장 큰 id 값을 @MAX_ID 변수에 저장
SELECT MAX(id) AS @MAX_ID FROM access_log;

-- MAX_ID보다 큰 id 값을 가진 레코드를 access_log_new 테이블로 복사
INSERT INTO access_log_new 
SELECT * FROM access_log 
WHERE id > @MAX_ID;

-- 트랜잭션 커밋
COMMIT;

-- 데이터 복사가 완료되면 테이블 이름을 바꾸어 새로운 테이블을 서비스로 사용
RENAME TABLE access_log TO access_log_old, access_log_new TO access_log;

-- 테이블 락 해제
UNLOCK TABLES;

-- 불필요한 테이블 삭제
DROP TABLE access_log_old;
SQL
복사

2. InnoDB 스토리지 엔진 잠금

•

InnoDB 스토리지 엔진은 MySQL에서 데이터의 무결성과 일관성을 유지하기 위해 여러 가지 잠금 메커니즘을 제공

◦

InnoDB의 고급 동시성 제어를 가능

◦

데이터베이스의 성능을 최적화하는 데 중요한 역할 수행

•

InnoDB 잠금 정보 조회

◦

아래 테이블을 조인하여 조회하여 어떤 트랜잭션이 어떤 잠금을 대기하고, 해당 잠금을 어느 트랜잭션이 갖고 있는지 확인 가능 

▪

informatinon_schema 

•

INNODB_TRX 

•

INNODB_LOCKS 

•

INNODB_LOCK_WAITS

2.1. InnoDB 스토리지 엔진의 잠금

•

InnoDB는 레코드 기반의 잠금 기능을 제공

•

 MyISAM보다 더 높은 동시성 처리를 지원

•

 InnoDB의 잠금은 여러 잠금 레벨로 구분됨

2.1.1. 레코드 락 (Record Lock)

•

개별 레코드에 대한 잠금 

•

다른 상용 DBMS와 달리 테이블의 물리적 레코드가 아닌 인덱스 레코드를 대상으로 잠금 진행

◦

레코드 자체 vs 인덱스 잠금에 따라 크고 중요한 차이를 만듦

•

인덱스가 없는 테이블도 내부적으로 생성된 클러스터링 인덱스를 통해 레코드 락 진행

•

주로 보조 인덱스가 있는 테이블의 변경 작업에서 사용

◦

 기본 키 또는 고유 인덱스가 있을 때는 해당 레코드만 잠그고, 레코드 사이의 간격(Gap)에 대해서는 잠금을 설정하지 않습니다.

2.1.2. 갭 락 (Gap Lock)

•

두 레코드 사이의 간격을 잠그는 잠금

•

새로운 레코드가 특정 간격에 삽입되는 것을 방지

•

주로 REPEATABLE READ 격리 수준에서 사용되며, 트랜잭션의 일관성을 유지하기 위해 필요

•

인덱스에 대한 범위 검색 시 사용되며, 인덱스의 레코드 사이의 간격에 새 레코드가 삽입되지 않도록 보장

•

갭 락 자체로 쓰이기 보다는 넥스트 키 락의 일부로 자주 사용됨

2.1.3. 넥스트 키 락 (Next Key Lock)

•

레코드 락과 갭 락을 결합한 형태

•

현재 레코드뿐만 아니라 그 레코드와 인접한 간격도 함께 잠금

•

바이너리 로그를 사용하는 MySQL 서버에서는 REPEATABLE READ 격리 수준을 사용해야함

◦

바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때, 소스 서버에서 만들어낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주 목적

•

의외로 데드락 발생의 주요 원인이 되기도 함

•

가능한 경우 넥스트 키 락을 줄이기 위해 ROW 기반의 바이너리 로그 포맷을 사용하도록 권장

2.1.4. 자동 증가 락 (Auto Increment Lock)

•

AUTO_INCREMENT 칼럼이 있는 테이블에서 중복되지 않는 연속된 숫자 값을 제공하기 위해 사용

•

INSERT와 REPLACE 명령에서만 사용되며, 매우 짧은 시간 동안 걸렸다가 해제됨

•

여러 INSERT 작업이 동시에 실행될 때 각 작업이 고유한 자동 증가 값을 받을 수 있도록 보장

•

innodb_autoinc_lock_mode 시스템 변수를 통해 잠금 모드를 조정 가능

2.2. 인덱스와 잠금

•

InnoDB의 잠금은 레코드를 직접 잠그는 것이 아니라 인덱스를 잠그는 방식으로 동작함

•

변경해야할 레코드를 찾기 위해 검색한 인덱스 레코드를 모두 락을 걸어야함

•

예시)

◦

특정 인덱스를 기반으로 업데이트 쿼리를 실행할 때, 해당 인덱스를 잠금으로 인해 다른 트랜잭션에서 접근할 수 없게 됨

▪

아래 예시에서 1건의 레코드를 업데이트하기 위해 253개의 레코드에 인덱스를 걸어야함

▪

first_name에만 인덱스가 있고, last_name에는 인덱스가 없음

▪

 first_name=’Georgi’ 인 레코드 253개가 업데이트 됨

•

적절한 인덱스가 준비 X → 많은 수의 레코드가 잠김 → 동시성 성능 저하 유발

◦

만약 인덱스가 하나도 없다면? 테이블 풀스캔 & 업데이트 → 테이블 내 모든 레코드 잠김

-- employees 테이블에는 first_name 컬럼에만 ix_firstname 인덱스가 적용되어있음
-- KEY ix_firstname (first_name)

-- employees 테이블에서 first_name이 'Georgi'인 사원은 전체 253명이 있다.
SELECT COUNT(*) FROM employees WHERE first_name = 'Georgi';

-- 결과:
-- +----------+
-- | 253      |
-- +----------+

-- first_name이 'Georgi'이고, last_name이 'Klassen'인 사원은 딱 1명만 있다.
SELECT COUNT(*) 
	FROM employees WHERE first_name = 'Georgi' AND last_name = 'Klassen';

-- first_name이 'Georgi'이고 last_name이 'Klassen'인 사원의 입사 일자를 오늘로 변경하는 쿼리
UPDATE employees 
SET hire_date = NOW() 
WHERE first_name = 'Georgi' AND last_name = 'Klassen';
SQL
복사

2.3. 레코드 수준의 잠금 확인 및 해제

•

레코드 수준의 잠금은 테이블 수준의 잠금보다 더 복잡

•

InnoDB는 여러 트랜잭션 간의 충돌을 방지하기 위해 이러한 잠금을 관리

2.3.1. 레코드 수준 잠금 확인

•

MySQL 5.1

◦

 information_schema의 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 테이블을 통해 현재 어떤 트랜잭션이 어떤 잠금을 기다리고 있는지 확인할 수  있음

•

MySQL 8.0

◦

 performance_schema의 data_locks와 data_lock_waits 테이블을 이용하여 잠금 상태를 모니터링할 수 있음

•

예시 코드

SELECT
    r.trx_id AS waiting_trx_id, 
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id, // 대기중인 트랜잭션이 차단하고 있는 트랜잭션 id
    b.trx_mysql_thread_id AS blocking_thread, // 차단 트랜잭션 스레드 id
    b.trx_query AS blocking_query // 차단 트랜잭션에서 실행중인 쿼리
FROM
    performance_schema.data_lock_waits w
    INNER JOIN information_schema.innodb_trx b
        ON b.trx_id = w.blocking_engine_transaction_id
    INNER JOIN information_schema.innodb_trx r
        ON r.trx_id = w.requesting_engine_transaction_id;
SQL
복사

•

예시 코드 실행 결과

waiting_trx_id	waiting_thread	waiting_query	blocking_trx_id	blocking_thread	blocking_query
11990	19	UPDATE employees ...	11989	18	UPDATE employees ...
11990	19	UPDATE employees ...	11984	17	NULL
11989	18	UPDATE employees ...	11984	17	NULL

•

스레드가 어떠한 잠금을 가지고 있는지 상세한 정보 확인 가능

◦

SELECT * FROM performance_schema.data_locks;

mysql> SELECT * FROM performance_schema.data_locks;

*************************** 1. row ***************************
ENGINE:                  INNODB
ENGINE LOCK ID:          4828335432:1157:140695376728800
ENGINE TRANSACTION ID:   11984
THREAD ID:               61
EVENT ID:                16028
OBJECT_SCHEMA:           employees
OBJECT_NAME:             employees
PARTITION NAME:          NULL
SUBPARTITION NAME:       NULL
INDEX NAME:              NULL
OBJECT INSTANCE BEGIN:   140695376728800
LOCK TYPE:               TABLE
LOCK MODE:               IX // X 락 갖고 있음
LOCK STATUS:             GRANTED
LOCK DATA:               NULL

*************************** 2. row ***************************
ENGINE:                  INNODB
ENGINE LOCK ID:          4828335432:8:298:25:140695394434080
ENGINE TRANSACTION ID:   11984
THREAD ID:               61
EVENT ID:                16048
OBJECT_SCHEMA:           employees
OBJECT_NAME:             employees
PARTITION NAME:          NULL
SUBPARTITION NAME:       NULL
INDEX NAME:              PRIMARY
OBJECT INSTANCE BEGIN:   140695394434080
LOCK TYPE:               RECORD
LOCK MODE:               X.REC_NOT_GAP // 갭이 포함되지 않은 순수 레코드만 잠금
LOCK STATUS:             GRANTED
LOCK DATA:               100001
SQL
복사

2.3.2. 레코드 수준 잠금 해제

•

잠금 충돌이나 긴 대기로 인해 문제가 발생할 경우, KILL 명령을 사용해 특정 트랜잭션을 강제로 종료함으로써 잠금을 해제할 수 있음