[MariaDB][ADMIN] show processlist - Waiting for table metadata lock

[테스트 환경]

  - CentOS 7

  - MariaDB 10.1.12 

1. 문제 상황

  - Toad에서 대상 테이블을 조회하던 도중 사용자에 의한 중지

    - 중지가 되어도 실제 프로세스 상에는 세션이 쿼리를 조회하고 있음 

  - INDEX를 DROP 하는 중에 metadata lock이 걸려서 대기 상태에 빠진 상황

MariaDB [(none)]> show processlist;
+-----+---------+---------------------+-------+-------------+---------+-----------------------------------------------------------------------+---------------------------------------------------------+----------+
| Id  | User    | Host                | db    | Command     | Time    | State                                                                 | Info                                                    | Progress |
+-----+---------+---------------------+-------+-------------+---------+-----------------------------------------------------------------------+---------------------------------------------------------+----------+
|  50 | REPLUSR | 192.168.0.144:43430 | NULL  | Binlog Dump | 3043775 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL                                                    |    0.000 |
|  54 | root    | 192.168.0.144:43448 | NULL  | Sleep       |       0 |                                                                       | NULL                                                    |    0.000 |
|  80 | USR     | 192.168.0.145:44298 | ADB   | Sleep       |     224 |                                                                       | NULL                                                    |    0.000 |
| 637 | root    | 192.168.6.13:1725   | ADB   | Sleep       |    5232 |                                                                       | NULL                                                    |    0.000 |
| 638 | root    | 192.168.6.13:1726   | ADB   | Sleep       |    5516 |                                                                       | NULL                                                    |    0.000 |
| 639 | root    | 192.168.6.13:1727   | ADB   | Sleep       |    5514 |                                                                       | NULL                                                    |    0.000 |
| 640 | root    | 192.168.6.13:1729   | ESTDB | Sleep       |    5463 |                                                                       | NULL                                                    |    0.000 |
| 642 | root    | 192.168.3.5:58998   | ADB   | Query       |     634 | Sending data                                                          | SELECT   ~~ FROM ADB_USER_CONN                          |    0.000 |
| 643 | root    | 192.168.3.5:58999   | ADB   | Query       |     327 | Waiting for table metadata lock                                       | ALTER TABLE ADB_USER_CONN DROP INDEX ADB_USER_CONN_IDX01|    0.000 |
| 645 | root    | 192.168.3.3:50486   | ADB   | Sleep       |     793 |                                                                       | NULL                                                    |    0.000 |
| 646 | root    | 192.168.3.3:50487   | ADB   | Sleep       |     786 |                                                                       | NULL                                                    |    0.000 |
| 647 | root    | 192.168.3.5:61064   | NULL  | Sleep       |     476 |                                                                       | NULL                                                    |    0.000 |
| 648 | root    | localhost           | NULL  | Query       |       0 | init                                                                  | show processlist                                        |    0.000 |
+-----+---------+---------------------+-------+-------------+---------+-----------------------------------------------------------------------+---------------------------------------------------------+----------+
14 rows in set (0.00 sec)

2. 해결 방안

  - 사용자에 의해서 중지된 쿼리를 날리고 있는 세션을 KILL

--세션 확인
MariaDB [information_schema]> SELECT    a.trx_id, a.trx_state, a.trx_started, a.trx_query, a.trx_mysql_thread_id
    -> FROM      information_schema.INNODB_TRX a;
+--------+-----------+---------------------+------------------------------+---------------------+
| trx_id | trx_state | trx_started         | trx_query                    | trx_mysql_thread_id |
+--------+-----------+---------------------+------------------------------+---------------------+
| 42385  | RUNNING   | 2017-03-29 16:57:16 |SELECT   ~~ FROM ADB_USER_CONN|                642  |
+--------+-----------+---------------------+---------------+------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> KILL 642;
Query OK, 0 rows affected (0.00 sec)

3. 결과

  - INDEX DROP이 처리됨

MariaDB [ADB]> ALTER TABLE ADB_USER_CONN DROP INDEX ADB_USER_CONN_IDX01;
Query OK, 0 rows affected (13 min 44.90 sec)
Records: 0  Duplicates: 0  Warnings: 0


MariaDB [(none)]> show processlist;
+-----+---------+---------------------+-------+-------------+---------+-----------------------------------------------------------------------+---------------------------------------------------------+----------+
| Id  | User    | Host                | db    | Command     | Time    | State                                                                 | Info                                                    | Progress |
+-----+---------+---------------------+-------+-------------+---------+-----------------------------------------------------------------------+---------------------------------------------------------+----------+
|  50 | REPLUSR | 192.168.0.144:43430 | NULL  | Binlog Dump | 3043775 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL                                                    |    0.000 |
|  54 | root    | 192.168.0.144:43448 | NULL  | Sleep       |       4 |                                                                       | NULL                                                    |    0.000 |
|  80 | USR     | 192.168.0.145:44298 | ADB   | Sleep       |     194 |                                                                       | NULL                                                    |    0.000 |
| 637 | root    | 192.168.6.13:1725   | ADB   | Sleep       |    6701 |                                                                       | NULL                                                    |    0.000 |
| 638 | root    | 192.168.6.13:1726   | ADB   | Sleep       |    6986 |                                                                       | NULL                                                    |    0.000 |
| 639 | root    | 192.168.6.13:1727   | ADB   | Sleep       |    6984 |                                                                       | NULL                                                    |    0.000 |
| 640 | root    | 192.168.6.13:1729   | ESTDB | Sleep       |    6932 |                                                                       | NULL                                                    |    0.000 |
| 645 | root    | 192.168.3.3:50486   | ADB   | Sleep       |    2263 |                                                                       | NULL                                                    |    0.000 |
| 646 | root    | 192.168.3.3:50487   | ADB   | Sleep       |    2256 |                                                                       | NULL                                                    |    0.000 |
| 647 | root    | 192.168.3.5:61064   | NULL  | Sleep       |     930 |                                                                       | NULL                                                    |    0.000 |
| 648 | root    | localhost           | NULL  | Query       |       0 | init                                                                  | show processlist                                        |    0.000 |
+-----+---------+---------------------+-------+-------------+---------+-----------------------------------------------------------------------+---------------------------------------------------------+----------+