문제 상황
Trigger 생성 과정에서 오랜 시간동안 쿼리가 실행이 되지 않더니, 이내 갑자기 DB가 멈추는 상황이 발생했다.
디버깅을 위해서 DB 재시작 후, 동일한 문제 상황을 재현했다.
문제가 되는 쿼리를 다시 실행한 결과, DB에 연결된 thread의 수가 지속적으로 상승하고 있음을 확인할 수 있었다.
SHOW STATUS LIKE 'Threads_connected';
이후 스레드 정보를 정확하게 확인하기 위해서 아래 명령어를 입력해보니, 다음과 같은 에러 상황이 발생하고 있었다.
SHOW PROCESSLIST;| Id | User | Host | db | Command | Time | State | Info |
| 25800 | root | localhost:51282 | mydb | Sleep | 630 | Waiting for table metadata lock | select id, name |
| 25803 | root | localhost:51312 | mydb | Sleep | 652 | Waiting for table metadata lock | alter table |
| 25807 | root | localhost:51352 | mydb | Sleep | 7743 | NULL |
Metadata Lock?
Metadata Lock은 테이블이나 데이터베이스와 관련된 메타데이터를 보호하기 위해서 사용된다고 한다. 이를 통해 동시성 문제를 방지하고, 데이터의 무결성을 유지할 수 있다고 한다. 이러한 Lock은 주로 다음과 같은 상황에 발생한다고 한다.
- DDL과 DML의 충돌 : DDL 작업(CREATE, ALTER, DROP)이 실행되는 동안, DML(SELECT, INSERT, DELETE)이 동시에 수행될 경우
- 긴 실행 시간의 트랜잭션 : 트랜잭션이 작업 중인 테이블의 Lock을 계속 점유하고 있을 경우
Trigger 생성과 Metadata Lock의 연관성?
위에 언급한 2가지 경우로 나눠서 보면 다음과 같을 것이다.
- Trigger를 생성하려면 DDL(CREATE) 작업이 수행된다. → 이때 DML이 동시에 수행될 경우 Metadata Lock이 발생하며, 해당 테이블에 접근하려는 다른 Threads는 Lock이 해제될 때까지 대기 상태로 전환될 것이다. → 그 결과 Trigger 생성이 완료될 때까지 Transaction이 반환되지 않고, Lock이 장시간 유지되는 것이다
- 만약 Trigger 생성 쿼리의 실행 시간이 지나치게 길 경우에도, 트랜잭션을 계속 잡고 있기 때문에 Metadata Lock이 발생할 수 있다.
예상 원인과 해결 방법
프로세스 리스트를 보면 25807라는 Id의 Time이 7743이나 나온 것을 확인할 수 있다. 이를 통해 결국 해당 Id의 쿼리가 트랜잭션을 계속 잡고 있었기 때문에 Metadata Lock이 발생하게 된 것이라고 생각할 수 있다. 이러한 문제를 방지하기 위해선 세션이나 트랜잭션에 대한 타임아웃을 설정해야 할 것이다.
SET GLOBAL innodb_lock_wait_timeout = 50;
SET GLOBAL wait_timeout = 300;