[MySQL] performance_schema로 현재 잠금 대기와 차단 세션 찾기

들어가며: 잠금 대기, 범인을 못 찾으면 못 풉니다

데이터베이스를 다루다 보면 갑자기 특정 쿼리가 멈춘 것처럼 응답하지 않는 상황을 자주 마주하게 됩니다. 애플리케이션 로그에는 타임아웃만 찍혀 있고, SHOW PROCESSLIST를 열어봐도 State: waiting for handler commit이나 Waiting for table metadata lock 같은 문구만 보일 뿐, 정작 어떤 세션이 어떤 세션을 막고 있는지는 알려주지 않습니다. 이때 실무에서 흔히 하는 실수가 감으로 의심 가는 세션을 KILL부터 해버리는 것입니다. 하지만 진짜 차단 세션(blocker)이 아니라 대기 세션(waiter)을 죽여버리면 문제는 그대로 남고, 반대로 이미 곧 커밋될 트랜잭션을 성급하게 종료시키면 애플리케이션에 예기치 않은 롤백을 유발할 수 있습니다. 이 글에서는 MySQL 8.0 이상에서 표준으로 자리잡은 performance_schema.data_locks와 performance_schema.data_lock_waits를 어떻게 조인해야 blocker와 waiter를 정확히 짚어낼 수 있는지, 그리고 결과를 보고 종료 여부를 어떻게 판단해야 하는지를 단계적으로 살펴보겠습니다.

1. 잠금 정보를 담고 있는 performance_schema 테이블들

MySQL 8.0부터는 InnoDB의 실제 로우 레벨 잠금 정보가 information_schema가 아니라 performance_schema 아래에 존재합니다. 잠금 대기를 진단하려면 최소 세 가지 테이블의 역할을 정확히 이해해야 합니다.

1-1. performance_schema.data_locks

현재 InnoDB가 보유하고 있거나 요청 중인 모든 잠금을 한 행씩 나열합니다. 각 행은 특정 트랜잭션이 특정 테이블(또는 인덱스 레코드)에 대해 어떤 종류의 잠금을 어떤 상태로 가지고 있는지를 보여줍니다. 핵심 컬럼은 다음과 같습니다.

• ENGINE_TRANSACTION_ID: InnoDB 내부 트랜잭션 식별자입니다.
• OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME: 잠금이 걸린 테이블과 인덱스입니다.
• LOCK_TYPE: RECORD(로우 단위) 또는 TABLE(테이블 단위)입니다.
• LOCK_MODE: X, S, IX, IS 같은 기본 모드에 GAP, REC_NOT_GAP, INSERT_INTENTION 같은 접미사가 붙습니다.
• LOCK_STATUS: GRANTED(이미 획득)인지 WAITING(대기 중)인지를 의미합니다.
• LOCK_DATA: 잠긴 실제 인덱스 값(대략적인 PK 값)입니다.

1-2. performance_schema.data_lock_waits

이 테이블이 이번 주제의 핵심입니다. 각 행이 정확히 하나의 대기 관계, 즉 "이 트랜잭션이 저 트랜잭션 때문에 기다리고 있다"는 사실 하나를 나타냅니다. 중요한 컬럼은 다음과 같습니다.

• REQUESTING_ENGINE_TRANSACTION_ID: 잠금을 요청했지만 아직 못 받은 쪽, 즉 waiter의 트랜잭션 ID입니다.
• BLOCKING_ENGINE_TRANSACTION_ID: 이미 잠금을 쥐고 있어서 상대를 막고 있는 쪽, 즉 blocker의 트랜잭션 ID입니다.
• REQUESTING_ENGINE_LOCK_ID, BLOCKING_ENGINE_LOCK_ID: data_locks의 ENGINE_LOCK_ID와 조인할 때 사용하는 키입니다.

즉 data_lock_waits는 이미 "누가 누구를 막는가"라는 관계 자체를 계산해서 제공합니다. 개발자가 락 호환성 규칙을 직접 계산할 필요가 없다는 뜻이며, 이 점이 이 테이블을 반드시 활용해야 하는 이유입니다.

1-3. performance_schema.threads와 세션 정보 연결

트랜잭션 ID만으로는 "누구를 KILL해야 하는지"를 알 수 없습니다. 트랜잭션 ID를 실제 커넥션(processlist ID)에 연결하려면 performance_schema.threads의 PROCESSLIST_ID 컬럼을 거쳐야 하며, 이는 data_locks.THREAD_ID와 조인됩니다. 여기에 더해 실제로 어떤 SQL 문이 세션을 잡고 있는지 확인하려면 information_schema.processlist(또는 SHOW FULL PROCESSLIST)의 INFO 컬럼까지 함께 조회해야 합니다.

2. 잠금 대기를 직접 재현해서 눈으로 확인하기

실전 진단 능력을 기르려면 반드시 로컬이나 테스트 환경에서 직접 잠금 대기 상황을 만들어보고, 아래 조인 쿼리가 실제로 어떤 결과를 뱉는지 확인하는 과정이 필요합니다. 다음 절차로 재현할 수 있습니다.

-- 준비: 테스트 테이블
CREATE TABLE lock_demo (
  id INT PRIMARY KEY,
  balance INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO lock_demo VALUES (1, 1000), (2, 2000);

세션 A(연결 1)에서 트랜잭션을 시작하고 커밋하지 않은 채로 둡니다.

-- 세션 A
START TRANSACTION;
SELECT * FROM lock_demo WHERE id = 1 FOR UPDATE;
-- 여기서 커밋하지 않고 대기 상태로 유지

세션 B(연결 2)에서 같은 행을 갱신하려고 시도하면 즉시 대기 상태에 들어갑니다.

-- 세션 B
START TRANSACTION;
UPDATE lock_demo SET balance = balance - 100 WHERE id = 1;
-- 이 시점에서 세션 B는 응답 없이 멈춘 것처럼 보임

이 상태에서 세션 C(모니터링용 별도 연결)를 열고 다음 절에서 제시하는 조인 쿼리를 실행하면, 세션 B가 세션 A에 의해 막혀 있다는 사실이 그대로 결과에 포함됩니다.

실무 팁: 재현 테스트는 반드시 운영 DB가 아닌 스크래치 스키마에서 진행하고, 세션 A의 ROLLBACK 또는 COMMIT을 잊지 않도록 주의가 필요합니다. 트랜잭션을 열어둔 채로 방치하면 실제 장애와 동일한 상황을 스스로 만들게 됩니다.

3. blocker와 waiter를 한 번에 찾는 조인 쿼리

가장 널리 쓰이는 정석 조인은 data_lock_waits를 기준으로 data_locks를 두 번(요청 측, 차단 측) 조인하고, 다시 threads를 두 번 조인해 각각의 processlist ID를 뽑아내는 형태입니다.

SELECT
  w.REQUESTING_ENGINE_TRANSACTION_ID AS waiter_trx_id,
  rt.PROCESSLIST_ID               AS waiter_pid,
  rl.OBJECT_SCHEMA                AS waiter_schema,
  rl.OBJECT_NAME                  AS waiter_table,
  rl.LOCK_MODE                    AS waiter_lock_mode,
  w.BLOCKING_ENGINE_TRANSACTION_ID AS blocker_trx_id,
  bt.PROCESSLIST_ID               AS blocker_pid,
  bl.LOCK_MODE                    AS blocker_lock_mode
FROM performance_schema.data_lock_waits w
JOIN performance_schema.data_locks rl
  ON rl.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks bl
  ON bl.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID
JOIN performance_schema.threads rt
  ON rt.THREAD_ID = rl.THREAD_ID
JOIN performance_schema.threads bt
  ON bt.THREAD_ID = bl.THREAD_ID;

여기에 실제 실행 중인 SQL 문까지 함께 보고 싶다면 information_schema.processlist를 PROCESSLIST_ID 기준으로 추가 조인하면 됩니다.

LEFT JOIN information_schema.processlist bp
  ON bp.ID = bt.PROCESSLIST_ID
LEFT JOIN information_schema.processlist wp
  ON wp.ID = rt.PROCESSLIST_ID

이 쿼리를 실행하면 blocker_pid 컬럼에 나온 값이 바로 KILL의 대상 후보이며, waiter_pid는 대기만 하고 있을 뿐 문제의 원인이 아니라는 점을 명확히 구분할 수 있습니다. evidenceNeeded 표시: 위 재현 절차대로 두 세션을 열어 실제로 이 쿼리를 실행한 결과 화면(컬럼별 실제 값)을 캡처해 본문에 삽입해야 합니다. 트랜잭션 ID와 PROCESSLIST_ID는 실행할 때마다 달라지므로 특정 숫자를 예시로 고정해서 적지 않았습니다.

4. 더 간단하게: sys 스키마의 innodb_lock_waits 뷰

매번 위와 같은 다중 조인 쿼리를 직접 작성하기 번거롭다면, MySQL에 기본 포함된 sys 스키마의 sys.innodb_lock_waits 뷰를 활용할 수 있습니다. 이 뷰는 위 조인을 이미 내부적으로 수행해두고, wait_started, wait_age, locked_table, waiting_query, blocking_query 같은 사람이 읽기 좋은 컬럼명으로 제공합니다.

SELECT wait_age, locked_table, locked_type,
       waiting_pid, waiting_query,
       blocking_pid, blocking_query
FROM sys.innodb_lock_waits
ORDER BY wait_age DESC;

운영 중 빠르게 상황을 파악해야 할 때는 이 뷰부터 조회하는 것이 더 안전합니다. 다만 sys 스키마 뷰는 내부적으로 performance_schema를 그대로 사용하므로, 근본 원리를 이해하려면 3절의 원본 조인 구조를 알고 있어야 합니다.

5. 결과 해석 시 흔히 하는 실수

조인 결과를 받아도 컬럼 의미를 잘못 읽으면 엉뚱한 세션을 종료시킬 수 있습니다. 주니어 개발자가 특히 자주 헷갈리는 지점을 정리합니다.

• blocker가 항상 오래된 트랜잭션은 아닙니다. 트랜잭션 시작 시각이 아니라 "해당 잠금을 먼저 획득했는가"가 기준이므로, 방금 시작한 짧은 트랜잭션이 blocker가 되는 경우도 흔합니다.
• LOCK_MODE의 GAP과 REC_NOT_GAP을 혼동하지 마십시오. X,GAP은 갭 락만 걸린 것이고 실제 레코드 값 자체에는 락이 없는 경우도 있어, 언뜻 충돌할 것 같지 않은 값 범위에서도 대기가 발생할 수 있습니다.
• 다중 blocker 체인이 존재할 수 있습니다. A가 B를 막고, B가 다시 C를 막는 대기 체인이 흔합니다. 이 경우 data_lock_waits 결과에 여러 행이 나오므로, 진짜 근본 원인(체인의 맨 앞)을 찾으려면 blocker_trx_id가 다른 행의 waiter_trx_id로 다시 등장하는지 확인해야 합니다.

6. 메타데이터 락(MDL)은 별개의 문제입니다

실무에서 자주 놓치는 함정 하나는, data_locks/data_lock_waits는 오직 InnoDB의 로우 레벨 잠금만 다룬다는 점입니다. DDL(ALTER TABLE 등)이나 LOCK TABLES로 인해 발생하는 메타데이터 락(Metadata Lock, MDL)은 여기 잡히지 않습니다. SHOW PROCESSLIST에서 Waiting for table metadata lock이 보인다면 대신 performance_schema.metadata_locks 테이블을 조회해야 하며, 이 테이블은 기본적으로 계측(instrument)이 꺼져 있는 경우가 있어 다음 설정으로 켜야 데이터가 채워집니다.

UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';

즉 "쿼리가 멈췄다"는 증상 하나에도 원인은 로우 락, 갭 락, 메타데이터 락, 혹은 단순한 오토커밋 미설정 등 여러 갈래로 나뉜다는 점을 반드시 구분해야 합니다.

7. 종료 판단 체크리스트: KILL을 눌러도 되는 순간

blocker를 정확히 찾았다고 해서 바로 KILL부터 실행하는 것은 위험합니다. 실행 전 다음 항목을 순서대로 확인하는 것이 안전합니다.

1. blocker_query가 정상적으로 진행 중인 트랜잭션인지, 아니면 애플리케이션 커넥션 누수로 오래 방치된 유휴(idle in transaction) 세션인지 확인합니다. information_schema.innodb_trx의 trx_started와 현재 시각의 차이, 그리고 trx_state가 RUNNING인지 확인합니다.
2. wait_age(대기 시간)가 애플리케이션의 타임아웃 임계값을 이미 넘었는지 확인합니다. 아직 여유가 있다면 자연스러운 커밋을 기다리는 편이 더 안전합니다.
3. blocker가 배치 작업이나 백업 스크립트처럼 의도된 장시간 트랜잭션은 아닌지 애플리케이션 팀에 반드시 확인합니다. 정상 배치를 강제 종료하면 데이터 정합성 문제를 새로 만들 수 있습니다.
4. KILL 대상은 항상 blocker_pid이며, waiter_pid를 죽이는 것은 근본 해결이 아니라는 점을 다시 확인합니다. waiter를 죽여도 다음 waiter가 즉시 같은 문제를 겪습니다.
5. KILL 실행 후에는 반드시 data_lock_waits를 재조회해 대기 체인이 실제로 해소되었는지, 혹은 다음 blocker가 새로 드러나는지 확인합니다.

8. 버전별 동작 차이: 5.7의 innodb_lock_waits는 왜 사라졌나

MySQL 5.7까지는 information_schema.innodb_locks와 information_schema.innodb_lock_waits를 사용해 유사한 진단을 했습니다. 하지만 이 두 테이블은 MySQL 8.0에서 완전히 제거되었으며, 그 기능은 performance_schema.data_locks와 performance_schema.data_lock_waits로 이관되었습니다. 이는 단순한 이름 변경이 아니라 구현 방식 자체가 달라진 것으로, 5.7 기준 스크립트를 8.0에 그대로 가져와 실행하면 "해당 테이블이 존재하지 않는다"는 에러를 만나게 됩니다. 따라서 8.0 이상 환경에서는 이 글에서 소개한 조인 구조를 기준으로 모니터링 스크립트를 새로 작성해야 하며, 5.7과 8.0을 함께 운영하는 조직이라면 버전별로 진단 쿼리를 분리 관리하는 것이 더 안전합니다. 또한 data_locks, data_lock_waits 조회는 performance_schema가 켜져 있어야(ON, 8.0 기본값) 결과가 채워진다는 점도 함께 확인해야 합니다.

정리해 드립니다

잠금 대기 진단의 핵심은 "증상을 본다"가 아니라 "관계를 조회한다"는 데 있습니다. data_lock_waits가 이미 blocker와 waiter의 관계를 계산해서 제공하므로, 여기에 data_locks와 threads만 정확히 조인하면 됩니다. 다만 결과를 받은 다음의 종료 판단은 기계적으로 할 수 없으며, 트랜잭션 상태와 대기 시간, 업무 맥락까지 함께 확인하는 절차가 반드시 필요합니다.