Shared·Exclusive·Intention·Metadata Lock

Overview

Shared(S)와 Exclusive(X)는 작업의 호환성을, Intention(IS/IX)은 하위 Row Lock 의도를 상위 Table에 표시한다. Metadata Lock(MDL)은 Table 정의를 사용하는 Statement와 DDL이 충돌하지 않게 한다. 데이터 Row Lock과 Schema Lock은 대기 원인이 다르다.

핵심 용어

왜 필요한가

운영에서 ALTER TABLE이 “아무 일도 안 하는데” 기다리는 원인은 오래된 Transaction의 MDL일 수 있다. 반대로 Row Update 대기는 Data Lock이다. 둘을 구분해야 올바른 Session과 SQL을 찾는다.

핵심 원리와 내부 동작

Lock Manager는 Compatibility Matrix를 보고 요청을 부여하거나 기다린다. Intention Lock은 모든 Row를 훑지 않고 Table Lock과 Row Lock의 충돌을 판단하기 위한 표지다. MDL은 SQL이 참조하는 Object 정의가 실행 중 바뀌지 않게 하고 Transaction 종료까지 유지될 수 있다.

Example

-- 오래 열린 transaction이 table을 조회한 상태
BEGIN;
SELECT * FROM orders WHERE id = 1;
 
-- 다른 session의 DDL이 metadata lock을 기다릴 수 있음
ALTER TABLE orders ADD COLUMN memo varchar(100);

MySQL에서는 performance_schema.metadata_locks, data_locks, data_lock_waits와 Transaction 목록을 함께 확인한다. 제품 Version에 따라 View 이름과 제공 범위가 다르다.

실무에서 발생하는 문제와 Trade-off

Idle-in-transaction Session이 DDL 배포를 막는다. Online DDL도 시작·종료 시 MDL이 필요할 수 있다. Table Lock을 피하려 Row Lock을 써도 많은 Row를 잡으면 사실상 동시성이 낮아진다. Lock Upgrade와 서로 다른 획득 순서는 Deadlock을 만든다.

흔한 오해

• Intention Lock이 모든 Row를 직접 잠그는 강한 Lock은 아니다.
• 일반 MVCC SELECT의 읽기와 FOR SHARE는 같지 않다.
• “Online DDL”이 무대기·무영향을 뜻하지 않는다.
• Kill할 Session은 대기자가 아니라 실제 Blocker인지 확인해야 한다.

Production Considerations

DDL 전 Long Transaction, Connection Pool의 Auto-commit, Migration Lock Timeout을 점검한다. Blocker→Waiter Graph와 SQL, Transaction Age를 수집한다. 큰 Schema 변경은 Shadow Table이나 단계적 배포를 검토하고 Rollback 계획을 둔다.

다른 사람에게 설명한다면

30초: “S/X는 읽기와 쓰기의 호환성, IS/IX는 하위 Row Lock 의도, MDL은 Table 정의를 보호합니다. DDL 대기는 Row Lock이 아니라 오래 열린 Transaction의 Metadata Lock 때문일 수 있습니다.”

2분: Compatibility Matrix, Intention의 계층 최적화 목적, SELECT 후 열린 Transaction이 ALTER를 막는 예와 진단 View를 설명한다.

Interview Questions

1. S와 X Lock은 어떻게 호환되는가?
2. Intention Lock이 필요한 이유는?
3. Online DDL도 MDL을 기다릴 수 있는 이유는?
4. Blocker와 Waiter를 어떻게 구분하는가?

Related Topics

DB Lock 지도, Deadlock을 함께 본다.