MySQL MDL(metadata_locks): insert 쿼리가 병목 상태에 빠지는 이유

최근에 DDL을 실행시키는 과정에서 모든 Insert 쿼리가 대기 상태에 빠져 밀리는 현상을 발견해서 정확한 원인 분석을 진행하고 그 내용을 기록합니다.

문제가 발생했던 상황

1. datagrip에서 무거운 select 쿼리 실행
2. datagrip에서 새로운 세션으로 ALTER TABLE 쿼리를 online DDL로 실행
3. server application에서 Insert 쿼리가 대기 상태에 빠짐

일반적으로 봤을 때는 오잉 datagrip 뭐가 문제지? 라는 생각이 들 수 있는데요. 자세한 내용은 좀 더 아래에서 풀어보겠습니다.

문제 원인은 다음과 같았어요

MySQL에서 ALTER TABLE 쿼리는 실행을 위해서 table metadata lock(=MDL)을 획득해야 하고 이 때, Exclusive Lock(배타적 락)을 필요로 하는데요.
위 상황에서 문제는 ALTER TABLE 쿼리가 *어떤 이유로 인해 Exclusive Lock을 획득하지 못해서 대기하는 상황이였고
이후에 들어오는 Insert 쿼리는 Shared Lock(공유 락)을 필요로 하는데, Exclusive Lock이 MySQL에서 처리 우선순위가 높기 때문에 대기 상태에 빠지게 되었습니다.

위에서 언급한 *어떤 이유의 정체는 "datagrip에서 무거운 select 쿼리 실행"이었고, 이 select 쿼리가 의도치 않게 Shared Lock을 잡고 있는 상황이여서 ALTER TABLE 쿼리가 Exclusive Lock을 획득할 수 없는 상황이라는 걸 알았습니다.

재연해보면 바로 아래와 같은 상황인 건데요 🤔
실제로 metadata lock 현황을 확인할 수 있는 performance_schema.metadata_locks 테이블을
살펴보면 SHARED_READ LOCK이 잡혀있는 걸 확인할 수 있었습니다.

 

 

그래서 의도치않게 오래 동작하고 있던 select 쿼리 프로세스를 KILL 하고 ALTER 쿼리를 실행해서 이슈를 해결할 수 있었습니다.

 

그런데 사실 SELECT 쿼리에서 Transaction을 명시적으로 실행한 적이 없습니다

사실 위 예시는 이해를 돕기 위해 일부러 명시적으로 Transaction을 시작했습니다.
그리고 실제 상황에서 "datagrip에서 무거운 select 쿼리 실행"할 때 Transacton을 실행할 이유도 없었고 명시적으로 실행한 적이 없었습니다.

그렇다면 Datagrip에서 자동으로 Transaction을 잡았다는 이야기가 되는데, 왜 그런 걸까요?

 

Transaction Mode : Auto

Datagrip에는 Transaction Mode 옵션이 존재하는데요.
여기서 Auto 옵션을 선택하면 (아마 기본값이었던 것 같습니다) Transaction commit을 Datagrip에서 관리하게 됩니다.

별다른 추가 설정 없이 Datagrip을 사용했었고 여기에 Isolation level이 REPEATABLE READ(이것도 기본값이었던 것 같습니다)로 잡혀있어서 SELECT 쿼리 실행 시에 SHARED_READ lock이 잡히게 된 것이었습니다.

이후 Action Item

위 이슈를 겪은 이후로 재발 방지도 중요하기 때문에 SHARED_READ lock을 오래 잡고 있는 쿼리를 감지하는 자동화된 모니터링을 위한 계획을 세워두었는데요. 자세한 내용은 다른 글에서 다뤄보겠습니다. 읽어주셔서 감사합니다 :)