[MySQL] Waiting for table metadata lock

Problem

DROP TABLE 쿼리를 실행했을 때 실행이 정상적으로 되지 않는 문제가 있었다.

SHOW FULL PROCESSLIST 로 확인해보니 해당 프로세스의 state가 Waiting for table metadata lock 인 채 계속해서 대기하는 것을 확인할 수 있었다.

 

---

Solution

1. information_schema.innodb_trx 테이블을 확인한다.

 SELECT *
 FROM information_schema.innodb_trx ;

다음과 같은 결과를 확인할 수 있다.

[root@localhost:mysql.sock] (mysql) 09:47> SELECT * FROM information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 421601021864688
                 trx_state: RUNNING
               trx_started: 2022-10-17 09:47:22
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 2381515
                 trx_query: (skip)
       trx_operation_state: starting index read
         trx_tables_in_use: 2
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 3239
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0

 

2. trx_state가 "RUNNING"인 것 중 trx_started가 비정상적으로 오래된 것을 찾는다. 여러 개라면 그 중 가장 오래된 것을 선택한다. 원인이 되는 프로세스의 trx_mysql_thread_id를 확인한다. 

       trx_mysql_thread_id: 2381515

 

대부분은 이 프로세스가 트랜잭션을 시작하고 테이블에 접근하여 metadata lock을 획득 한 채, Commit / Rollback 하지 않아 metadata lock이 해제되지 않은 상태로 방치한 경우일 것이다. 이 경우 이후에 들어온 DDL이 계속해서 lock을 대기하며 정상적으로 실행될 수 없다. 이 때 SQL 자체는 끝나 있는 상태에서 원인 프로세스가 Sleep 하고 있을 것이다.

 

드물지만 순수하게 긴 SQL이 실행 되는 중 "Waiting for table metadata lock"이 나온 경우는 끝나기를 기다리면 해소된다.

 

 

3. 해당 프로세스를 종료해도 되는지 확인한다.

SHOW FULL PROCESSLIST

나의 경우, 위에서 찾은 trx_mysql_thread_id 와 일치하는 ID를 가진 프로세스가 Sleep 상태라 종료해도 된다고 판단했다.

 

 

4. 원인이 되는 프로세스를 종료한다.

kill 2381515

 

원인이 되는 프로세스를 kill 하니 정상적으로 쿼리가 완료되었다.

 

쿼리가 아직도 대기중이라면 다른 프로세스가 원인이니 동일한 방법으로 다시 원인이 되는 프로세스의 ID를 찾는다.

 

 

---

참고

MySQL - Waiting for table metadata lock 대처 방법 - jacking75