Khắc phục 'Waiting for table metadata lock' khi ALTER TABLE hoặc DROP TABLE bị treo trong MySQL

Tình huống gặp phải

Bạn chạy lệnh ALTER TABLE hoặc DROP TABLE và nó cứ... đứng yên. Không có lỗi, không có tiến trình. SHOW PROCESSLIST xác nhận điều bạn đã nghi ngờ:

Waiting for table metadata lock

Mọi truy vấn tác động đến bảng đó bắt đầu xếp hàng phía sau. Bảng thực tế bị offline cho đến khi thứ đang giữ khóa được giải phóng.

Tại sao lại xảy ra

MySQL bảo vệ cấu trúc bảng bằng metadata lock (MDL). Lệnh ALTER TABLE cần một MDL độc quyền — nhưng nó sẽ không có được cho đến khi mọi transaction đang mở trên bảng đó hoàn tất. Điều này bao gồm cả các transaction đang bị treo nửa chừng chưa được commit hay rollback.

Các nguyên nhân thường gặp:

• Một câu SELECT bị bỏ quên đang mở trong tab MySQL client — idle nhiều giờ, chưa được commit
• Lỗi ứng dụng khi transaction bắt đầu nhưng không bao giờ commit (phổ biến với connection pool được cấu hình tắt autocommit)
• Một lệnh LOCK TABLES tường minh vẫn đang mở trong session khác
• Một lệnh FLUSH TABLES đang trong quá trình thực thi
• Trong MySQL 5.7+, một transaction idle chỉ cần đã chạm vào bảng vẫn giữ MDL

Bước 1 — Tìm thứ đang chặn

Bắt đầu bằng cách xem nhanh trạng thái hiện tại:

SHOW FULL PROCESSLIST;

Tìm các thread đang ở trạng thái Sleep với giá trị Time lớn. Thường như vậy là đủ để xác định thủ phạm. Nếu không, hãy đào sâu vào performance_schema:

-- Chỉ dành cho MySQL 5.6 / 5.7 — innodb_lock_waits đã bị xóa trong MySQL 8.0
SELECT
  r.trx_id              AS waiting_trx_id,
  r.trx_mysql_thread_id AS waiting_thread,
  r.trx_query           AS waiting_query,
  b.trx_id              AS blocking_trx_id,
  b.trx_mysql_thread_id AS blocking_thread,
  b.trx_query           AS blocking_query
FROM
  information_schema.innodb_lock_waits w
  JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
  JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

MDL không phải là row lock của InnoDB, nên truy vấn đó thường không trả về kết quả gì. Cách đáng tin cậy hơn — và là lựa chọn duy nhất trên MySQL 8.0+ — là truy vấn trực tiếp vào performance_schema:

-- MySQL 5.7+ / 8.0 — tìm ai đang giữ MDL trên bảng của bạn
SELECT
  waiting.THREAD_ID        AS waiting_thread_id,
  waiting.PROCESSLIST_ID   AS waiting_pid,
  waiting.PROCESSLIST_INFO AS waiting_query,
  blocking.THREAD_ID       AS blocking_thread_id,
  blocking.PROCESSLIST_ID  AS blocking_pid,
  blocking.PROCESSLIST_INFO AS blocking_query,
  ml.LOCK_STATUS
FROM
  performance_schema.metadata_locks ml
  JOIN performance_schema.threads blocking
    ON ml.OWNER_THREAD_ID = blocking.THREAD_ID
  JOIN performance_schema.threads waiting
    ON waiting.PROCESSLIST_INFO IS NOT NULL
WHERE
  ml.LOCK_STATUS = 'GRANTED'
  AND ml.OBJECT_SCHEMA = 'your_database'
  AND ml.OBJECT_NAME   = 'your_table';

Thay tên database và tên bảng thực tế của bạn vào.

Bước 2 — Xác định connection đang chặn

Truy vấn trên cho bạn một blocking_pid. Đối chiếu nó trong SHOW FULL PROCESSLIST. Dòng có vấn đề thường trông như thế này:

• Command: Sleep
• Time: một con số đáng xấu hổ — 300, 1800, hoặc thậm chí 7200 giây
• Info: NULL — truy vấn đã xong từ lâu, nhưng transaction chưa bao giờ được đóng

Connection đang ngủ đó đã giữ một metadata lock từ truy vấn cuối của nó và cứ... ngồi đó. Nó không hề biết mình đang chặn thay đổi schema của bạn.

Bước 3 — Kill tiến trình gây chặn

KILL <blocking_pid>;

Ví dụ:

KILL 42;

Lệnh ALTER TABLE của bạn sẽ được thực thi gần như ngay lập tức. Kiểm tra trạng thái:

SHOW PROCESSLIST;

Theo dõi trạng thái chuyển từ Waiting for table metadata lock sang copy to tmp table hoặc altering table. Như vậy là bạn đã qua được rồi.

Bước 4 — Xác nhận bản sửa lỗi

-- Xác nhận không còn MDL waiter nào trên bảng này
SELECT *
FROM performance_schema.metadata_locks
WHERE OBJECT_SCHEMA = 'your_database'
  AND OBJECT_NAME   = 'your_table';

Sau khi ALTER hoàn tất, truy vấn này trả về không có dòng nào. Trong quá trình thực thi bạn sẽ thấy một dòng GRANTED cho session của chính mình — đó là bình thường.

Kiểm tra lại để chắc chắn thay đổi schema đã được áp dụng đúng:

DESCRIBE your_table;
SHOW CREATE TABLE your_table\G

Nếu tiến trình gây chặn không có trong PROCESSLIST thì sao?

Connection đó có thể đã bị ngắt — client bị crash, mạng bị cắt, v.v. — nhưng MySQL chưa bao giờ rollback transaction của nó. wait_timeout và interactive_timeout sẽ dọn dẹp nó theo thời gian. Tuy nhiên với cài đặt mặc định 28800 giây (8 tiếng) của MySQL, "theo thời gian" có thể nghĩa là phải chờ rất lâu.

Buộc dọn dẹp bằng cách tạm thời giảm timeout:

-- Kiểm tra cài đặt timeout hiện tại
SHOW VARIABLES LIKE '%timeout%';

-- Tạm thời rút ngắn để xóa các connection stale (khôi phục ngay sau đó)
SET GLOBAL wait_timeout = 30;
SET GLOBAL interactive_timeout = 30;

Khôi phục về giá trị mặc định hợp lý sau khi các connection chết đã bị xóa:

SET GLOBAL wait_timeout = 28800;
SET GLOBAL interactive_timeout = 28800;

Đừng để nguyên 30 giây — nó sẽ ngắt kết nối các truy vấn chạy lâu hợp lệ.

Ngăn tình trạng này tái diễn

• Đặt timeout hợp lý. wait_timeout từ 60–300 giây phù hợp với hầu hết các kết nối ứng dụng. Tám tiếng hầu như không bao giờ là giá trị đúng.
• Dùng autocommit với connection pool. Hầu hết các ORM (Laravel, Django, Spring) mặc định bật autocommit. Nếu bạn đã tắt nó đi, mọi transaction đều cần một lệnh COMMIT hoặc ROLLBACK tường minh — không có ngoại lệ.
• Chạy DDL trong khung giờ ít traffic. Với các bảng lớn (50M+ dòng), hãy dùng gh-ost hoặc pt-online-schema-change. Cả hai đều tránh giữ MDL độc quyền trong suốt thời gian migration.
• Thêm DDL timeout. Đặt lock_wait_timeout theo session để lệnh ALTER của bạn thất bại nhanh thay vì chặn tất cả mọi thứ phía sau:

SET SESSION lock_wait_timeout = 10;
ALTER TABLE your_table ADD COLUMN new_col INT DEFAULT 0;

• Cảnh báo khi có transaction chạy lâu. Tích hợp vào hệ thống monitoring của bạn. Bất kỳ transaction nào mở quá 60 giây đều đáng xem xét:

SELECT *
FROM information_schema.innodb_trx
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60
ORDER BY trx_started ASC;

Tham khảo nhanh

-- 1. Tìm ai đang chặn
SELECT THREAD_ID, PROCESSLIST_ID, PROCESSLIST_TIME, PROCESSLIST_STATE, PROCESSLIST_INFO
FROM performance_schema.threads
WHERE PROCESSLIST_STATE IS NOT NULL
ORDER BY PROCESSLIST_TIME DESC
LIMIT 20;

-- 2. Kill tiến trình gây chặn
KILL <pid>;

-- 3. Xác nhận ALTER đang chạy
SHOW PROCESSLIST;