MySQL에서는 락(Lock)을 적용하는 범위에 따라 크게 글로벌 락(Global Lock), 테이블 락(Table Lock), 로우 락(Row Lock) 세 가지 유형으로 나눌 수 있습니다. 각 락의 특징과 사용법을 상세히 알아보겠습니다.

1. 글로벌 락 (Global Lock)

글로벌 락 사용법

글로벌 락을 사용하려면 다음 명령어를 실행합니다:

FLUSH TABLES WITH READ LOCK;

이 명령 실행 후 데이터베이스 전체가 읽기 전용 상태가 되며, 다른 스레드에서 다음과 같은 작업을 시도하면 모두 차단됩니다:

• 데이터 삽입, 수정, 삭제 작업 (INSERT, DELETE, UPDATE 등)
• 테이블 구조 변경 작업 (ALTER TABLE, DROP TABLE 등)

글로벌 락을 해제하려면 다음 명령어를 사용합니다:

UNLOCK TABLES;

세션이 종료되면 글로벌 락은 자동으로 해제됩니다.

글로벌 락의 활용 사례

글로벌 락은 주로 전체 데이터베이스 논리적 백업을 수행할 때 사용됩니다. 백업 도중 데이터나 테이블 구조가 변경되는 것을 방지하여 백업 파일의 데이터 일관성을 보장합니다.

예를 들어, 전자상거래 시스템에서 고객이 상품을 구매하는 시나리오를 생각해보겠습니다. 구매 로직은 일반적으로 여러 테이블(사용자 잔액 업데이트, 상품 재고 감소)을 동시에 변경합니다. 만약 백업 중 글로벌 락 없이 다음과 같은 순서로 작업이 진행된다면:

1. 사용자 테이블 백업 완료
2. 사용자가 상품 구매 작업 실행 (사용자 잔액 차감, 재고 감소)
3. 상품 테이블 백업 완료

이 경우 백업 파일에는 사용자 잔액이 차감되지 않은 상태로, 상품 재고는 감소된 상태로 저장됩니다. 결과적으로 데이터 복원 시 사용자는 돈을 지불하지 않고 상품을 받는 문제가 발생합니다. 글로벌 락을 사용하면 이러한 상황을 방지할 수 있습니다.

글로벌 락의 단점

글로벌 락을 적용하면 전체 데이터베이스가 읽기 전용이 되어, 백업이 진행되는 동안 애플리케이션은 데이터 읽기만 가능하고 쓰기 작업은 중단됩니다. 특히 대용량 데이터베이스의 경우 백업 시간이 길어져 서비스 중단 시간이 발생할 수 있습니다.

글로벌 락 대체 방법

InnoDB와 같이 반복 읽기(REPEATABLE READ) 격리 수준을 지원하는 트랜잭션 기반 스토리지 엔진을 사용한다면, 백업 전에 트랜잭션을 시작하고 Read View를 생성하여 트랜잭션 전체 기간 동안 일관된 데이터를 볼 수 있습니다. MVCC(Multi-Version Concurrency Control) 덕분에 백업 중에도 다른 트랜잭션에서 데이터를 업데이트할 수 있습니다.

MySQL의 백업 도구 mysqldump를 사용할 때 --single-transaction 옵션을 추가하면 백업 전에 자동으로 트랜잭션을 시작합니다. 이 방법은 반복 읽기 격리 수준을 지원하는 InnoDB에서만 유효합니다. MyISAM과 같이 트랜잭션을 지원하지 않는 엔진에서는 여전히 글로벌 락을 사용해야 합니다.

2. 테이블 락 (Table Lock)

MySQL의 테이블 레벨 락에는 다음과 같은 종류가 있습니다:

• 테이블 락 (Table Lock)
• 메타데이터 락 (MDL, Metadata Lock)
• 의도 락 (Intention Lock)
• AUTO-INC 락 (Auto-Increment Lock)

테이블 락 (Table Lock)

테이블에 직접 락을 걸기 위해 다음 명령어를 사용합니다:

-- 테이블 수준 공유 락 (읽기 락)
LOCK TABLES t_student READ;

-- 테이블 수준 배타 락 (쓰기 락)
LOCK TABLES t_student WRITE;

테이블 락은 다른 스레드의 읽기/쓰기뿐만 아니라, 락을 건 현재 스레드의 후속 작업도 제한합니다. 예를 들어, 현재 스레드가 t_student 테이블에 공유 락을 걸면, 이후 이 스레드가 동일 테이블에 쓰기 작업을 시도할 때 차단됩니다. 락 해제는 UNLOCK TABLES 명령어로 현재 세션의 모든 테이블 락을 해제하거나, 세션이 종료될 때 자동으로 해제됩니다.

InnoDB 엔진을 사용하는 테이블에서는 테이블 락 사용을 최대한 피해야 합니다. 테이블 락은 입도(Granularity)가 너무 커서 동시성 성능에 부정적인 영향을 미칩니다. InnoDB의 장점은 더 세밀한 로우 레벨 락을 구현한다는 점입니다.

메타데이터 락 (MDL, Metadata Lock)

MDL은 명시적으로 사용할 필요가 없으며, 테이블 작업 시 자동으로 추가됩니다:

• CRUD 작업: MDL 읽기 락 추가
• 테이블 구조 변경: MDL 쓰기 락 추가

MDL은 한 스레드가 테이블에 CRUD 작업을 수행하는 동안 다른 스레드가 테이블 구조를 변경하는 것을 방지합니다. SELECT 문 실행 중(읽기 락 보유) 다른 스레드가 테이블 구조 변경(쓰기 락 요청)을 시도하면, SELECT 문이 완료될 때까지 차단됩니다. 반대로, 테이블 구조 변경 중(쓰기 락 보유) 다른 스레드가 CRUD 작업(읽기 락 요청)을 시도하면 구조 변경이 완료될 때까지 차단됩니다.

MDL 해제 시점

MDL은 트랜잭션이 커밋된 후에야 해제됩니다. 즉, 트랜잭션이 실행되는 동안 MDL은 계속 유지됩니다. 따라서 장기 실행 트랜잭션(오랫동안 커밋되지 않은 트랜잭션)이 존재하는 상태에서 테이블 구조를 변경하려고 하면 예상치 못한 문제가 발생할 수 있습니다:

1. 스레드 A가 트랜잭션 시작 후 SELECT 문 실행 → 테이블에 MDL 읽기 락 추가
2. 스레드 B가 동일 SELECT 문 실행 → 읽기 락 간 충돌 없음, 정상 실행
3. 스레드 C가 테이블 필드 변경 시도 → 스레드 A의 읽기 락이 해제되지 않아 MDL 쓰기 락 획득 실패, 차단
4. 스레드 C가 차단된 후, 이후 동일 테이블에 대한 모든 SELECT 문이 차단 → 데이터베이스 연결 급증

MDL 쓰기 락 획득 대기 중일 때, 읽기 락 요청도 함께 차단되는 이유는 MDL 락 요청 큐에서 쓰기 락의 우선순위가 읽기 락보다 높기 때문입니다. 따라서 테이블 구조를 안전하게 변경하려면 먼저 데이터베이스의 장기 실행 트랜잭션이 있는지 확인하고, 있으면 해당 트랜잭션을 종료(kill)한 후 구조를 변경하는 것이 좋습니다.

의도 락 (Intention Lock)

InnoDB 테이블에서 특정 레코드에 락을 걸기 전에 테이블 레벨에 먼저 의도 락을 추가합니다:

• 레코드에 공유 락(S Lock)을 걸기 전 → 테이블에 의도 공유 락(IS Lock) 추가
• 레코드에 배타 락(X Lock)을 걸기 전 → 테이블에 의도 배타 락(IX Lock) 추가

INSERT, UPDATE, DELETE 작업을 실행할 때는 먼저 테이블에 의도 배타 락을 추가한 후, 해당 레코드에 배타 락을 추가합니다. 일반적인 SELECT 문은 MVCC를 사용한 일관성 읽기이므로 로우 락을 추가하지 않습니다. 하지만 SELECT 문에서 명시적으로 로우 락을 추가할 수도 있습니다:

-- 테이블에 의도 공유 락 + 레코드에 공유 락
SELECT ... LOCK IN SHARE MODE;

-- 테이블에 의도 배타 락 + 레코드에 배타 락
SELECT ... FOR UPDATE;

의도 공유 락과 의도 배타 락은 테이블 레벨 락으로, 로우 레벨의 공유/배타 락과 충돌하지 않습니다. 의도 락 간에도 충돌하지 않지만, 테이블 공유 락(LOCK TABLES ... READ)이나 테이블 배타 락(LOCK TABLES ... WRITE)과는 충돌할 수 있습니다.

의도 락이 없으면 테이블 배타 락을 추가할 때 테이블 내 모든 레코드를 순회하며 배타 락이 있는지 확인해야 하므로 성능이 저하됩니다. 의도 락을 사용하면 테이블에 의도 배타 락이 있는지만 확인하면 되므로 빠르게 판단할 수 있습니다. 의도 락의 목적은 테이블 내에 락이 걸린 레코드가 있는지 신속하게 판단하는 것입니다.

AUTO-INC 락 (Auto-Increment Lock)

테이블의 기본 키는 주로 자동 증가(AUTO_INCREMENT) 속성으로 설정됩니다. 데이터 삽입 시 기본 키 값을 지정하지 않으면 데이터베이스가 자동으로 증가하는 값을 할당하는데, 이때 AUTO-INC 락이 사용됩니다.

AUTO-INC 락은 특별한 테이블 락 메커니즘으로, 트랜잭션 커밋 시점이 아니라 삽입 문 실행 완료 직후에 해제됩니다. 데이터를 삽입할 때 AUTO_INCREMENT로 선언된 필드에 증가 값을 할당하기 위해 테이블 레벨의 AUTO-INC 락을 추가하며, 삽입 문 실행이 끝나면 바로 락을 해제합니다.

한 트랜잭션이 AUTO-INC 락을 보유하는 동안 다른 트랜잭션이 동일 테이블에 데이터를 삽입하려고 하면 차단되므로, 자동 증가 값의 연속성이 보장됩니다. 그러나 대량 데이터 삽입 시 AUTO-INC 락은 삽입 성능에 부정적인 영향을 미칩니다.

MySQL 5.1.22 버전부터 InnoDB는 경량 락(Lightweight Lock)을 제공하여 자동 증가 기능을 더 효율적으로 처리합니다. 데이터 삽입 시 자동 증가 필드에 경량 락을 추가하고 값을 할당한 후 즉시 락을 해제하며, 전체 삽입 문이 완료될 때까지 기다리지 않습니다.

innodb_autoinc_lock_mode 시스템 변수로 AUTO-INC 락과 경량 락 중 선택할 수 있습니다:

• 0: AUTO-INC 락 사용, 문장 실행 완료 후 해제
• 2: 경량 락 사용, 자동 증가 값 할당 후 즉시 해제
• 1:
  • 일반 INSERT 문: 자동 증가 값 할당 후 즉시 락 해제
  • INSERT ... SELECT 같은 대량 삽입 문: 문장 실행 완료 후 락 해제

innodb_autoinc_lock_mode = 2는 성능이 가장 높지만, binlog 로그 형식이 statement인 경우 주-종 복제 시 데이터 불일치 문제가 발생할 수 있습니다.

예를 들어, 세션 A가 테이블 t에 4행을 삽입하고 동일한 구조의 t2 테이블을 생성한 후, 두 세션이 동시에 t2에 데이터를 삽입하는 상황을 가정해보겠습니다. innodb_autoinc_lock_mode = 2이면 자동 증가 값 할당 후 즉시 락이 해제되므로 다음과 같은 순서가 가능합니다:

1. 세션 B: (1,1,1), (2,2,2) 삽입
2. 세션 A: id=3 할당, (3,5,5) 삽입
3. 세션 B: (4,3,3), (5,4,4) 삽입

이때 세션 B의 삽입 문으로 생성된 id가 연속적이지 않게 됩니다. 주 데이터베이스에서 이런 상황이 발생하면, binlog에는 두 세션의 INSERT 문이 순서대로 기록됩니다. binlog_format = statement이면 원본 SQL 문이 그대로 기록됩니다. 이 binlog를 종 데이터베이스에서 실행할 때는 순차적으로 실행되므로, 종 데이터베이스에서는 두 세션이 동시에 삽입하는 상황이 발생하지 않아 id가 연속적으로 생성됩니다. 따라서 주-종 데이터베이스 간 데이터 불일치가 발생합니다.

이 문제를 해결하려면 binlog_format = row로 설정해야 합니다. 이 경우 binlog에는 주 데이터베이스에서 할당한 실제 자동 증가 값이 기록되므로, 종 데이터베이스에서 동일한 id 값을 사용할 수 있습니다. 따라서 innodb_autoinc_lock_mode = 2와 binlog_format = row를 함께 사용하면 동시성을 높이면서 데이터 일관성 문제도 방지할 수 있습니다.

3. 로우 락 (Row Lock)

InnoDB 엔진은 로우 레벨 락을 지원하지만, MyISAM 엔진은 지원하지 않습니다. 일반적인 SELECT 문은 스냅샷 읽기이므로 레코드에 락을 추가하지 않습니다. SELECT 문에서 명시적으로 로우 락을 추가하려면 락 읽기(Locking Read)를 사용해야 하며, 이는 반드시 트랜잭션 내에서 수행되어야 합니다:

-- 레코드에 공유 락 추가
SELECT ... LOCK IN SHARE MODE;

-- 레코드에 배타 락 추가
SELECT ... FOR UPDATE;

트랜잭션이 커밋되면 락이 해제되므로, 위 두 문장은 BEGIN, START TRANSACTION 또는 SET autocommit = 0과 함께 사용해야 합니다. 공유 락(S Lock)은 읽기 공유, 읽기-쓰기 상호 배제를 만족하며, 배타 락(X Lock)은 쓰기-쓰기 및 읽기-쓰기 상호 배제를 만족합니다.

로우 레벨 락은 크게 세 가지 유형으로 나뉩니다:

• Record Lock (레코드 락): 단일 레코드만 잠금
• Gap Lock (갭 락): 특정 범위를 잠그지만 레코드 자체는 포함하지 않음
• Next-Key Lock (넥스트 키 락): Record Lock + Gap Lock의 조합, 범위와 레코드 자체를 모두 잠금

Record Lock

Record Lock은 한 개의 레코드를 잠그며, S型和 X型 두 가지가 있습니다:

• 트랜잭션이 레코드에 S형 레코드 락을 추가하면, 다른 트랜잭션도 동일 레코드에 S형 락을 추가할 수 있습니다(S형 간 호환). 하지만 X형 락은 추가할 수 없습니다(S형과 X형 불호환).
• 트랜잭션이 레코드에 X형 레코드 락을 추가하면, 다른 트랜잭션은 S형 또는 X형 락을 모두 추가할 수 없습니다(X형과 S형, X형 간 불호환).

예를 들어, 다음 문장을 실행하면 t_test 테이블에서 id=1인 레코드에 X형 레코드 락이 추가되어 다른 트랜잭션에서 해당 레코드를 수정할 수 없습니다:

BEGIN;
SELECT * FROM t_test WHERE id = 1 FOR UPDATE;

트랜잭션이 COMMIT을 실행하면 생성된 모든 락이 해제됩니다.

Gap Lock

Gap Lock은 반복 읽기(REPEATABLE READ) 격리 수준에서만 존재하며, 팬텀 리드(Phantom Read) 현상을 방지하기 위해 도입되었습니다. 예를 들어, 테이블에 id 범위 (3, 5)의 갭 락이 있으면 다른 트랜잭션이 id=4인 레코드를 삽입할 수 없습니다. 이를 통해 팬텀 리드를 효과적으로 방지합니다.

갭 락에는 X형과 S형이 있지만 실제로는 차이가 없습니다. 갭 락 간에는 호환되며, 두 트랜잭션이 동일한 갭 범위를 포함하는 갭 락을 동시에 보유할 수 있습니다. 이는 갭 락의 목적이 팬텀 레코드 삽입을 방지하는 것이기 때문입니다.

Next-Key Lock

Next-Key Lock은 Record Lock과 Gap Lock의 조합으로, 특정 범위와 해당 범위 내의 레코드 자체를 함께 잠급니다. 예를 들어, id 범위 (3, 5]의 next-key lock이 있으면 다른 트랜잭션은 id=4인 레코드를 삽입할 수 없고, id=5인 레코드를 수정할 수도 없습니다. 따라서 next-key lock은 레코드를 보호하면서도 해당 레코드 앞의 갭에 새로운 레코드가 삽입되는 것을 방지합니다.

Next-key lock은 갭 락과 레코드 락을 모두 포함하며, 한 트랜잭션이 X형 next-key lock을 보유하면 다른 트랜잭션이 동일 범위의 X형 next-key lock을 획득하려고 할 때 차단됩니다. 예를 들어, 한 트랜잭션이 범위 (1, 10]의 X형 next-key lock을 보유하면, 다른 트랜잭션이 동일 범위의 X형 next-key lock을 획득하려고 하면 차단됩니다. 동일 범위의 갭 락은 여러 트랜잭션 간 호환되지만, 레코드 락 부분에서는 X형과 S형 간의 관계가 적용되어 X형 레코드 락 간에는 충돌이 발생합니다.

삽입 의도 락 (Insert Intention Lock)

트랜잭션이 레코드를 삽입할 때, 삽입 위치가 다른 트랜잭션의 갭 락(next-key lock 포함)에 의해 잠겨 있는지 확인해야 합니다. 잠겨 있으면 삽입 작업이 차단되며, 갭 락을 보유한 트랜잭션이 커밋될 때까지(갭 락 해제 시점) 대기합니다. 이 대기 상태에서 삽입 의도 락이 생성되며, 이는 특정 구간에 새 레코드를 삽입하려는 트랜잭션이 있지만 현재는 대기 중임을 나타냅니다.

예를 들어, 트랜잭션 A가 테이블에 id 범위 (3, 5)의 갭 락을 추가한 상태에서, 트랜잭션 B가 id=4인 새 레코드를 삽입하려고 하면, 삽입 위치가 갭 락에 의해 잠겼음을 감지합니다. 이때 트랜잭션 B는 삽입 의도 락을 생성하고 락 상태를 대기로 설정합니다(MySQL에서 락은 먼저 락 구조를 생성한 후 상태를 설정하며, 대기 상태는 성공적인 락 획득을 의미하지 않습니다. 정상 상태여야 성공적인 획득입니다). 트랜잭션 A가 커밋될 때까지 트랜잭션 B는 차단됩니다.

삽입 의도 락은 이름에 '의도'가 포함되어 있지만 의도 락이 아닌 특별한 갭 락이며, 로우 레벨 락에 속합니다. 갭 락이 구간을 잠그는 반면, 삽입 의도 락은 특정 지점(Point)을 잠급니다. 이러한 관점에서 삽입 의도 락은 특별한 갭 락이라고 볼 수 있습니다.

삽입 의도 락과 갭 락의 중요한 차이점은 다음과 같습니다: 삽입 의도 락은 갭 락의 한 종류지만, 두 트랜잭션이 동시에 하나는 갭 락을, 다른 하나는 동일 갭 구간 내의 삽입 의도 락을 보유할 수 없습니다(물론 삽입 의도 락이 갭 락 구간 밖에 있다면 가능합니다).

---

참고 자료:

• MySQL技术内幕：InnoDB
• MySQL实战45讲
• 从根儿上理解MySQL