MySQL: Lock

※ MySQL: Lock.
 
안녕하세요. 듀스트림입니다.
 
오늘 포스팅은 MySQL의 락 관련 내용입니다.
 

MySQL :: MySQL 8.4 Reference Manual :: 17.7.1 InnoDB Locking

 
PostgreSQL처럼 MySQL InnoDB도 행 수준의 락을 사용하며, MySQL은 갭 락과 넥스트-키 락이 존재하는 게 다른 것 같습니다.

---

1. InnoDB 락 유형

1.1 공유 락 & 배타 락 (Shared and Exclusive Locks)

• 정의
  
  • 레코드 또는 테이블 수준에서 설정되는 기본적인 락 모드로, 트랜잭션의 읽기/쓰기 목적에 따라 SHARED (S) 또는 EXCLUSIVE (X) 락을 설정합니다.
• 예시

-- Shared Lock (읽기 공유 락)
SELECT * FROM users WHERE id = 10 LOCK IN SHARE MODE;

-- Exclusive Lock (쓰기 배타 락)
SELECT * FROM users WHERE id = 10 FOR UPDATE;

• 특징
  
  • SHARED (S): 다른 트랜잭션이 같은 레코드를 읽는 것은 허용, 수정은 차단
  • EXCLUSIVE (X): 해당 레코드에 대해 읽기와 쓰기 모두 차단
  • 일반적으로 InnoDB는 트랜잭션 격리 수준과 SQL 구문에 따라 자동으로 적절한 락을 적용
• 적용 예
  
  • FOR UPDATE → Exclusive Lock
  • LOCK IN SHARE MODE → Shared Lock

MySQL :: MySQL 8.0 Reference Manual :: 17.7.1 InnoDB Locking

---

1.2 레코드 락 (Record Locks)

• 정의
  
  • 인덱스에 존재하는 특정 레코드(Row)에 대한 잠금으로, 주로 SELECT ... FOR UPDATE 또는 UPDATE, DELETE 구문 실행 시 발생합니다.
• 예시

SELECT * FROM users WHERE id = 10 FOR UPDATE;
UPDATE users SET balance = balance - 100 WHERE id = 10;

• 특징
  
  • 해당 레코드에 대한 다른 트랜잭션의 읽기/쓰기 차단
  • 유니크 인덱스를 통한 정확한 행 지정 시 발생
  • 다른 레코드에는 영향 없음

MySQL :: MySQL 8.0 Reference Manual :: 17.7.1 InnoDB Locking

---

1.3 갭 락 (Gap Locks)

• 정의
  
  • 인덱스 레코드 사이의 "갭(범위)"에 대한 잠금으로, 삽입(insert)을 막기 위한 목적으로 사용됩니다. 해당 레코드 자체는 잠그지 않습니다.
• 예시

SELECT * FROM users WHERE balance BETWEEN 100 AND 200 FOR UPDATE;

• 특징
  
  • 범위 내에서 존재하지 않는 레코드의 삽입을 방지
  • 갭에만 적용되며, 레코드 자체에는 락 없음
  • REPEATABLE READ 격리 수준에서 기본 활성화
  • 팬텀 리드(phantom read) 방지 목적

MySQL :: MySQL 8.0 Reference Manual :: 17.7.1 InnoDB Locking

---

1.4 넥스트-키 락 (Next-Key Locks)

• 정의
  
  • 레코드 락 + 그 레코드 앞의 갭 락이 결합된 구조. 레코드 자체와 인접 범위까지 동시에 락을 거는 메커니즘입니다.
• 예시

SELECT * FROM users WHERE balance BETWEEN 100 AND 200 FOR UPDATE;

• 특징
  
  • 특정 레코드 + 그 앞뒤 범위까지 함께 잠금
  • 팬텀 리드 완전 차단
  • REPEATABLE READ 격리 수준에서 기본 동작
  • 주로 범위 조건 검색 시 자동 적용됨

MySQL :: MySQL 8.0 Reference Manual :: 17.7.1 InnoDB Locking

---

1.5 인서트 인텐션 락 (Insert Intention Locks)

• 정의
  
  • 인덱스의 특정 갭에 삽입하려는 의도가 있을 때 설정되는 특별한 유형의 갭 락입니다. 동시 삽입 시 충돌 방지를 위해 사용됩니다.
• 예시

INSERT INTO users (id, name) VALUES (25, 'Eve');

• 특징
  
  • 동일한 갭에 대해 여러 트랜잭션이 삽입 대기할 수 있음
  • 충돌은 없지만, 기존 갭 락과 충돌하면 대기 상태
  • 내부적으로 GAP 락과 다름

MySQL :: MySQL 8.0 Reference Manual :: 17.7.1 InnoDB Locking

---

1.6 인텐션 락 (Intention Locks: IS, IX)

• 정의
  
  • 테이블 수준에서 행 단위 락 의도를 표시하는 락입니다. IS, IX, SIX 등의 모드가 존재합니다.
• 예시

SELECT * FROM users WHERE id = 10 FOR SHARE;  -- IS 락
UPDATE users SET balance = balance + 100 WHERE id = 10;  -- IX 락

• 특징
  
  • 다중 트랜잭션이 동시에 테이블에 접근할 수 있도록 조정
  • 테이블 락과의 충돌 감지 목적으로 사용됨
  • 자체로는 데이터 접근을 막지 않음

MySQL :: MySQL 8.0 Reference Manual :: 17.7.1 InnoDB Locking

---

1.7 오토인크리먼트 락 (AUTO-INC Locks)

• 정의
  
  • AUTO_INCREMENT 컬럼에 대해 다중 레코드 삽입 시 일관된 번호 생성을 보장하기 위해 설정되는 테이블 수준 락입니다.
• 예시

INSERT INTO users (name) VALUES ('Eve'), ('Frank');

• 특징
  
  • MyISAM은 전역 테이블 락 사용, InnoDB는 논리적 락
  • bulk insert 시 트랜잭션 완료까지 번호 점유
  • innodb_autoinc_lock_mode로 조정 가능

MySQL :: MySQL 8.0 Reference Manual :: 17.7.1 InnoDB Locking

---

1.8 프레디케이트 락 (Predicate Locks for Spatial Indexes)

• 정의
  
  • 공간 인덱스 (SPATIAL INDEX) 를 사용하는 질의에 대해, predicate range 에 대한 논리적 락을 걸어 팬텀 리드를 방지하는 락입니다.
• 예시

CREATE TABLE geom_test (
  id INT PRIMARY KEY,
  shape GEOMETRY NOT NULL,
  SPATIAL INDEX(shape)
) ENGINE=InnoDB;

-- 공간 질의 (predicate lock 발생)
SELECT * FROM geom_test 
WHERE MBRContains(ST_GeomFromText('Polygon(...)'), shape) 
FOR UPDATE;

• 특징
  
  • 레코드나 갭이 아닌 "공간 조건(predicate)"에 대한 범위를 잠금
  • 공간 인덱스는 B-Tree 대신 R-Tree를 사용 → 일반적인 row/gap lock으로 제어 불가
  • 팬텀 리드를 방지하기 위해 별도로 Predicate Lock 이 도입됨
  • 내부적으로는 InnoDB가 predicate 표현식을 기억하고 삽입 차단에 사용
• 주의사항
  
  • FOR UPDATE, LOCK IN SHARE MODE 같은 잠금 구문을 사용해야 락이 발생함
  • 비공간 인덱스에서는 발생하지 않음

MySQL :: MySQL 8.0 Reference Manual :: 17.7.1 InnoDB Locking

---

1.9 메타데이터 락 (Metadata Locks, MDL)

• 정의
  
  • 테이블, 스키마, 트리거 등 DDL 및 객체 정의 관련 자원에 대한 락입니다. 쿼리 실행 중 테이블 구조가 변경되는 것을 방지합니다.
• 예시

-- 세션 1
START TRANSACTION;
SELECT * FROM users;

-- 세션 2 (대기 발생)
ALTER TABLE users ADD COLUMN tmp INT;

• 특징
  
  • DML 실행 시에도 자동으로 걸리는 시스템 락
  • EXCLUSIVE, SHARED 등 여러 모드 존재
  • performance_schema.metadata_locks로 확인 가능

MySQL :: MySQL 8.0 Reference Manual :: 10.11.4 Metadata Locking

---

요약 표

락 유형	설명
Record Lock	특정 인덱스 레코드에 대한 잠금
Gap Lock	인덱스 사이 간격에 대한 삽입 방지 락
Next-Key Lock	Record + Gap 결합, 팬텀 리드 방지
Insert Intention Lock	삽입 시도 시 갭에 설정되는 대기 락
Intention Lock	테이블 수준, 향후 행 락 의도 표시 (IS, IX)
Auto-Inc Lock	AUTO_INCREMENT 충돌 방지용 테이블 락
Metadata Lock	DDL, 테이블 정의에 대한 시스템 락
Shared / Exclusive	공유 또는 배타적인 레코드/테이블 접근 락
Predicate Lock	공간 인덱스에서 논리 범위 기반 잠금

---

2. 락 체크 쿼리

2.1 락 확인

SELECT 
  r.THREAD_ID, t.PROCESSLIST_ID, t.PROCESSLIST_INFO,
  r.OBJECT_SCHEMA, r.OBJECT_NAME,
  r.LOCK_TYPE, r.LOCK_MODE, r.LOCK_STATUS
FROM performance_schema.data_locks r
JOIN performance_schema.threads t ON r.THREAD_ID = t.THREAD_ID
--WHERE r.LOCK_TYPE IN ('RECORD','GAP','NEXT_KEY','INSERT_INTENTION')
;

---

2.2 락 홀더, 대기(Waiter) 확인

SELECT
  r.THREAD_ID      AS waiting_thread_id,
  rt.PROCESSLIST_ID AS waiting_conn_id,
  rt.PROCESSLIST_USER AS waiting_user,
  rt.PROCESSLIST_HOST AS waiting_host,
  rt.PROCESSLIST_DB AS waiting_db,
  r.OBJECT_SCHEMA  AS waiting_schema,
  r.OBJECT_NAME    AS waiting_table,
  r.LOCK_TYPE      AS waiting_lock_type,
  r.LOCK_MODE      AS waiting_lock_mode,
  r.LOCK_STATUS    AS waiting_status,
  b.THREAD_ID      AS blocking_thread_id,
  bt.PROCESSLIST_ID AS blocking_conn_id,
  bt.PROCESSLIST_USER AS blocking_user,
  bt.PROCESSLIST_HOST AS blocking_host,
  bt.PROCESSLIST_DB AS blocking_db,
  b.OBJECT_SCHEMA  AS blocking_schema,
  b.OBJECT_NAME    AS blocking_table,
  b.LOCK_TYPE      AS blocking_lock_type,
  b.LOCK_MODE      AS blocking_lock_mode,
  b.LOCK_STATUS    AS blocking_status
FROM
  performance_schema.data_lock_waits w
  JOIN performance_schema.data_locks r ON w.REQUESTING_ENGINE_LOCK_ID = r.ENGINE_LOCK_ID
  JOIN performance_schema.data_locks b ON w.BLOCKING_ENGINE_LOCK_ID = b.ENGINE_LOCK_ID
  LEFT JOIN performance_schema.threads rt ON r.THREAD_ID = rt.THREAD_ID
  LEFT JOIN performance_schema.threads bt ON b.THREAD_ID = bt.THREAD_ID
ORDER BY r.OBJECT_NAME;

---

2.3 메타데이터 락 확인 (테이블 락, DDL 락 등)

SELECT 
  OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME,
  LOCK_TYPE, LOCK_DURATION, LOCK_STATUS,
  OWNER_THREAD_ID, OWNER_EVENT_ID
FROM performance_schema.metadata_locks
WHERE OBJECT_TYPE='TABLE';

---

2.4 데드락 확인

SHOW ENGINE INNODB STATUS\G

---

3. 락 발생 테스트

+ 트랜잭션 격리 수준
MySQL의 InnoDB에서는 트랜잭션 격리 수준을 설정하여 동시성을 어떻게 처리할지 제어할 수 있습니다.

• READ COMMITTED: 다른 트랜잭션에서 커밋된 데이터를 읽을 수 있으며 행 수준 락이 적용됩니다.
• REPEATABLE READ: 트랜잭션이 시작된 시점의 데이터 스냅샷을 사용하며 갭 락과 넥스트-키 락이 사용되어 데이터 삽입을 방지합니다.
• SERIALIZABLE: 가장 높은 격리 수준으로 트랜잭션 간의 동시성을 완전히 제어합니다.

-- 세션 수준에서 트랜잭션 격리 수준을 READ COMMITTED로 설정
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 또는 전역적으로 설정 (MySQL 서버를 재시작하면 초기화됨)
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

---

▸ 테스트 데이터 생성

CREATE DATABASE IF NOT EXISTS lock_test;
USE lock_test;

DROP TABLE IF EXISTS t;
CREATE TABLE t (id INT PRIMARY KEY, val INT, INDEX(val)) ENGINE=InnoDB;

INSERT INTO t VALUES (10,100),(20,200),(30,300),(40,400);

---

3.1 일반 락 테스트

▸ 세션 1

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM t WHERE id BETWEEN 15 AND 35 FOR UPDATE;

• LOCK_MODE = 'IX': Intention Exclusive Lock 발생; 해당 테이블의 데이터를 수정할 것임을 나타내는 의도를 표시하는 락.
• LOCK_MODE = 'X,REC_NOT_GAP':  id값 20, 30에 대해 넥스트-키 락 발생 (레코드 락 + 갭 락).

---

▸ 세션 2 (동시 삽입 테스트)

START TRANSACTION;
INSERT INTO t (id,val) VALUES (25,250);

• LOCK_MODE = 'X,GAP,INSERT_INTENTION': 이전 세션이 해당 범위에 대해 익스클루시브 락을 잡았으므로, 현재 세션은 Wating 상태의 INSERT_INTENTION 락 발생.

---

3.2 데드락 테스트

▸ 세션 1

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;

UPDATE t SET val = 201 WHERE val = 200;

-- 세션 2가 300 락 잡은 후
UPDATE t SET val = 301 WHERE val = 300;

 
▸ 세션 2

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;

UPDATE t SET val = 301 WHERE val = 300;

-- 세션 1이 200에 락 잡은 상태
UPDATE t SET val = 201 WHERE val = 200;

---

3.3 메타락 테스트

▸ 세션 1

START TRANSACTION;

SELECT * FROM t;

 
▸ 세션 2

ALTER TABLE t ADD COLUMN tmp INT;

---

MySQL 꽤 똑똑하네요..
 
오늘은 여기까지~