📌 MySQL 메타데이터 락 문제 정리

1. 문제 상황

• ATCH_FILE 테이블을 조회하려고 했는데 IntelliJ DB 콘솔이 계속 Connecting 상태에서 멈춤.
• 확인해보니 메타데이터 락(Metadata Lock) 때문에 SELECT와 ALTER TABLE 모두 대기 중.

---

2. 원인

1. 조인 쿼리 에러
   • performance_schema.processlist 테이블에서 PROCESSLIST_ID 컬럼을 조회하려다
   • 버전별 컬럼 불일치로 Unknown column 'p.PROCESSLIST_ID' 에러 발생.
2. 메타데이터 락 충돌
   • 세션 A (예: ID=2001): ALTER TABLE atch_file ADD COLUMN file_knd_cd ... 실행 → 테이블 독점(EXCLUSIVE) 락 요청.
   • 세션 B (예: ID=2010): IntelliJ 콘솔에서 조회 실행 → Waiting for table metadata lock.
   • 세션 C (예: ID=1995): Sleep 상태인데도 Auto-commit OFF라 SHARED_READ 락을 붙잡고 있음.
   • 결과적으로 세션 C가 락을 해제하지 않아, 세션 A·B가 모두 대기.

---

3. 해결 방법

A. 원인 세션 찾기 (MySQL 8 예시)

SELECT
  ml.OBJECT_SCHEMA, ml.OBJECT_NAME, ml.LOCK_TYPE, ml.LOCK_STATUS,
  th.PROCESSLIST_ID AS pid, pl.USER, pl.HOST, pl.INFO
FROM performance_schema.metadata_locks ml
JOIN performance_schema.threads th ON ml.OWNER_THREAD_ID = th.THREAD_ID
LEFT JOIN information_schema.PROCESSLIST pl ON pl.ID = th.PROCESSLIST_ID
WHERE ml.OBJECT_SCHEMA = 'example_db'
  AND ml.OBJECT_NAME   = 'atch_file';

B. 즉시 풀기

• DDL(ALTER)을 진행하고 싶다면
  1. KILL 1995; → Sleep 세션 종료
  2. KILL 2010; → 대기 중인 SELECT 세션도 종료(선택)
  3. 세션 2001(ALTER)이 자동으로 실행됨
• 조회만 하고 싶다면
  1. KILL 2001; → ALTER 세션 종료
  2. IntelliJ 콘솔 Auto-commit ON 설정
  3. 다시 SELECT 실행

C. 재발 방지

• IntelliJ Database Console → Auto-commit ON 유지
• DDL(ALTER TABLE)은 업무 시간 외 전용 세션에서 실행
• 가능하다면 컬럼 추가 시:→ 위치 지정(AFTER)을 생략하면 INSTANT 적용 가능 → 락 시간 최소화
• ALTER TABLE atch_file ADD COLUMN file_knd_cd VARCHAR(100) NULL COMMENT '파일 종류 코드' , ALGORITHM=INSTANT;
• 유휴 세션 정리:
• SET GLOBAL wait_timeout = 600; SET GLOBAL interactive_timeout = 600;

---

✅ 정리
문제는 Auto-commit 꺼진 Sleep 세션이 ATCH_FILE 메타데이터 락을 점유해서,
다른 ALTER TABLE과 SELECT가 모두 대기한 상황이었습니다.
불필요한 세션을 종료하거나 Auto-commit을 켜서 락을 조기에 해제하면 해결됩니다.

---

 현재 컬럼 구조를 보고 INSTANT ALTER 적용 가능 여부까지 체크하는 SQL 확인 가능