myql 메모

ALTER TABLE table ADD COLUMN column char(2),  ALGORITHM=INSTANT;

---

🔍 예시

SHOW PROCESSLIST;

📋 결과

+--------+-----------------+--------------------+------------+---------+---------+---------------------------------+------------------------------------------------------------------+
| Id     | User            | Host               | db         | Command | Time    | State                           | Info                                                             |
+--------+-----------------+--------------------+------------+---------+---------+---------------------------------+------------------------------------------------------------------+
|      5 | event_scheduler | localhost          | NULL       | Daemon  | 1375562 | Waiting on empty queue          | NULL                                                             |
| 129524 | root            | localhost          | db         | Query   |       3 | Waiting for table metadata lock | ALTER TABLE table ADD COLUMN column char(2),  ALGORITHM=INSTANT; |
| 129525 | user            | ip                 | db         | Sleep   |       7 |                                 | NULL                                                             |
+--------+-----------------+--------------------+------------+---------+---------+---------------------------------+------------------------------------------------------------------+

---

🔍 예시

SELECT 
    r.THREAD_ID AS waiting_thread_id,
    r.PROCESSLIST_ID AS waiting_conn_id,
    r.PROCESSLIST_USER AS waiting_user,
    r.PROCESSLIST_HOST AS waiting_host,
    r.PROCESSLIST_COMMAND AS waiting_command,
    r.PROCESSLIST_TIME AS waiting_time,
    r.PROCESSLIST_INFO AS waiting_query,
    b.PROCESSLIST_ID AS blocking_conn_id,
    b.PROCESSLIST_INFO AS blocking_query
FROM 
    performance_schema.data_lock_waits dw
JOIN 
    performance_schema.threads r ON dw.REQUESTING_THREAD_ID = r.THREAD_ID
JOIN 
    performance_schema.threads b ON dw.BLOCKING_THREAD_ID = b.THREAD_ID;

📋 결과

Empty set (0.01 sec)

---

🔍 예시

SELECT 
    ps.id, ps.user, ps.host, ps.db, ps.command, ps.time, ps.state, ps.info 
FROM 
    information_schema.processlist ps 
WHERE 
    ps.state LIKE '%metadata lock%' OR ps.info LIKE '%테이블명%';

📋 결과

+--------+------+-----------+------------+---------+------+-----------+---------+
| id     | user | host      | db         | command | time | state     | info    |
+--------+------+-----------+------------+---------+------+-----------+---------+
| 129670 | root | localhost | db         | Query   |    0 | executing | query   |
+--------+------+-----------+------------+---------+------+-----------+---------+