Posted inMySQL

Xử lý lỗi treo MySQL do Metadata Locking: Đừng để một lệnh ALTER làm sập hệ thống

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

Bối cảnh: Khi lệnh ALTER TABLE biến thành “cơn ác mộng” production

Tưởng tượng bạn vừa chạy một lệnh ALTER TABLE để thêm cột status vào bảng orders. Trên môi trường staging, lệnh này chạy mất chưa đến 1 giây. Nhưng ngay khi bạn nhấn Enter trên production, Slack bắt đầu nổ thông báo lỗi 504 Gateway Timeout. CPU database nhảy vọt lên 90%, và mọi truy vấn vào bảng orders bỗng dưng đứng hình.

Mình từng nếm trái đắng này với một database MySQL 8.0 nặng khoảng 50GB, chứa hơn 40 triệu record. Thủ phạm không phải là khóa dòng (row lock) thông thường, mà là Metadata Locking (MDL). Đây là cơ chế bảo vệ cấu trúc bảng của MySQL. Khi có một transaction đang đọc dữ liệu, MySQL sẽ chặn mọi thay đổi schema để đảm bảo tính nhất quán.

Nút thắt nằm ở đây: Chỉ cần một lệnh SELECT chạy ngầm quá lâu hoặc một transaction “quên” commit, lệnh ALTER TABLE của bạn sẽ phải xếp hàng chờ. Tệ hơn, lệnh ALTER này lại đứng chắn đầu hàng, khiến tất cả các truy vấn SELECT, INSERT đến sau cũng bị treo theo. Kết quả là toàn bộ ứng dụng bị tê liệt.

Tái hiện lỗi Metadata Locking trong 3 bước

Để trị được bệnh, chúng ta cần hiểu cách nó phát tác. Bạn có thể mô phỏng tình huống này ngay trên máy cá nhân với hai cửa sổ terminal.

Bước 1: Chuẩn bị dữ liệu

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
) ENGINE=InnoDB;

INSERT INTO users (name) VALUES ('An'), ('Bình'), ('Chi');

Bước 2: Tạo một transaction “treo” (Session 1)

Mở session đầu tiên, bắt đầu một transaction nhưng tuyệt đối không COMMIT.

START TRANSACTION;
SELECT * FROM users WHERE id = 1; 
-- Giữ nguyên session này, đừng tắt cũng đừng gõ thêm gì.

Bước 3: Chạy lệnh ALTER (Session 2)

Tại cửa sổ thứ hai, hãy thử thêm một cột mới.

ALTER TABLE users ADD COLUMN email VARCHAR(255);

Lúc này, Session 2 sẽ đứng im. Nếu bạn mở thêm Session 3 và chạy một lệnh SELECT đơn giản, nó cũng sẽ bị treo. Chào mừng bạn đến với thế giới của Metadata Locking.

Cơ chế hoạt động: Tại sao MySQL lại hành xử như vậy?

Từ bản 5.5.3, MySQL dùng MDL để quản lý quyền truy cập vào cấu trúc bảng. Có hai loại khóa bạn cần phân biệt:

  • Shared Metadata Lock (SU): Kích hoạt khi bạn đọc hoặc ghi dữ liệu (SELECT, INSERT…). Nhiều người có thể giữ khóa này cùng lúc.
  • Exclusive Metadata Lock (X): Kích hoạt khi thay đổi cấu trúc (ALTER, DROP…). Chỉ duy nhất một người được giữ khóa này.

Trong ví dụ trên, Session 1 đang giữ Shared Lock. Session 2 muốn lấy Exclusive Lock nên phải xếp hàng chờ. Oái oăm là khi Exclusive Lock đang chờ, nó sẽ ưu tiên chặn luôn tất cả các Shared Lock mới. Đây chính là hiệu ứng domino khiến hệ thống sập nhanh chóng.

Một sai lầm phổ biến là để giá trị lock_wait_timeout mặc định. MySQL để con số này là 31,536,000 giây (tức là 1 năm!). Điều này nghĩa là lệnh ALTER sẽ đợi đến khi nào server sập thì thôi. Mình khuyên bạn nên hạ xuống khoảng 60 giây.

-- Kiểm tra cấu hình hiện tại
SHOW VARIABLES LIKE 'lock_wait_timeout';

-- Giới hạn thời gian chờ xuống 60s để bảo vệ hệ thống
SET SESSION lock_wait_timeout = 60;

Cách “cứu net” khi Database bị treo

Khi thấy hệ thống bắt đầu chậm, đừng vội restart MySQL. Việc này chỉ làm mọi chuyện tệ hơn vì quá trình recovery sau khi crash sẽ rất lâu.

1. Tìm kiếm bằng SHOW PROCESSLIST

Kiểm tra xem có bao nhiêu connection đang ở trạng thái Waiting for table metadata lock.

SHOW FULL PROCESSLIST;

Lưu ý: Lệnh này chỉ cho thấy ai đang đợi, không chỉ ra được ai là người đang giữ khóa.

2. Dùng Performance Schema để tìm “thủ phạm”

Trên MySQL 5.7 trở lên, đây là công cụ mạnh mẽ nhất. Trước hết, hãy bật giám sát MDL:

UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME = 'wait/lock/metadata/sql/mdl';

Sau đó, chạy query này để tìm chính xác ID của thread đang chặn mọi thứ:

SELECT 
    OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, THREAD_ID, PROCESSLIST_ID
FROM performance_schema.metadata_locks
WHERE LOCK_STATUS = 'GRANTED';

3. Giải cứu nhanh với bảng sys

Nếu bạn lười gõ query dài, bảng sys có sẵn một view rất trực quan:

SELECT * FROM sys.schema_table_lock_waits;

Nhìn vào cột blocking_pid, tìm ID đó và KILL nó ngay lập tức để giải phóng bảng:

-- Ví dụ PID tìm được là 456
KILL 456;

Kinh nghiệm thực chiến: Phòng bệnh hơn chữa bệnh

Sau nhiều lần xử lý sự cố trên các hệ thống lớn, mình rút ra 4 quy tắc vàng:

  • Dùng công cụ Online Schema Change: Với bảng trên 10GB, đừng bao giờ dùng ALTER TABLE trực tiếp. Hãy dùng pt-online-schema-change của Percona hoặc gh-ost của GitHub. Chúng tạo bảng tạm và copy dữ liệu dần dần, không gây khóa bảng lâu.
  • Kiểm tra transaction dài: Trước khi migration, hãy kiểm tra xem có cronjob hay report nào đang chạy không. Một lệnh SELECT kéo dài 10 phút sẽ là ngòi nổ cho MDL lock.
  • Chọn giờ thấp điểm: Dù công cụ có tốt đến đâu, hãy thực hiện vào lúc ít traffic nhất (thường là 2-3 giờ sáng).
  • Thiết lập timeout ngắn: Luôn set lock_wait_timeout cho session chạy ALTER. Thà để lệnh migration thất bại còn hơn để nó làm treo cả website.

Xử lý Metadata Lock đòi hỏi sự bình tĩnh. Khi thấy hàng trăm kết nối bị treo, hãy nhớ: tìm đúng ID giữ khóa và xử lý nó, thay vì hoảng loạn restart server.

Share:
Tags: