Cách khắc phục MySQL ERROR 1206: 'The total number of locks exceeds the lock table size'

Vấn đề: Một tác vụ dọn dẹp đơn giản bị lỗi

Gần đây tôi đã thử xóa dữ liệu cũ khỏi bảng log trên môi trường production. Bảng này có khoảng 40 triệu dòng và tôi muốn xóa mọi thứ cũ hơn sáu tháng. Công việc tưởng chừng đơn giản nên tôi đã chạy truy vấn này:

DELETE FROM activity_logs WHERE log_date < '2023-06-01';

Mọi thứ có vẻ ổn trong khoảng năm phút. Sau đó, thao tác đột ngột thất bại với lỗi sau:

ERROR 1206 (HY000): The total number of locks exceeds the lock table size

Cơ sở dữ liệu không bị sập, nhưng tác vụ dọn dẹp của tôi đã bị đình trệ hoàn toàn.

Tại sao lỗi này xảy ra

InnoDB xử lý việc khóa (locking) khác với nhiều database engine khác. Thay vì nâng cấp hàng nghìn khóa dòng (row locks) thành một khóa bảng (table lock) duy nhất để tiết kiệm bộ nhớ, InnoDB theo dõi từng khóa dòng riêng lẻ. Các khóa này nằm bên trong InnoDB Buffer Pool.

Mỗi khóa dòng tiêu tốn khoảng 150 byte bộ nhớ. Nếu bạn cố gắng xóa 10 triệu dòng cùng lúc, MySQL cần khoảng 1,5 GB dung lượng trống trong buffer pool chỉ để lưu trữ metadata của các khóa. Nếu innodb_buffer_pool_size của bạn được đặt ở giá trị nhỏ—như mặc định là 128MB—engine sẽ hết bộ nhớ và dừng tiến trình để bảo vệ bộ nhớ hệ thống.

Cách khắc phục nhanh: Tăng kích thước Buffer Pool

Nếu máy chủ của bạn còn dư RAM, cách nhanh nhất để giải quyết vấn đề này là cung cấp thêm không gian cho bảng khóa. Bạn thường có thể khắc phục điểm nghẽn tức thời bằng cách tăng kích thước buffer pool một cách linh hoạt.

1. Kiểm tra phân bổ hiện tại

SELECT @@innodb_buffer_pool_size / 1024 / 1024 AS size_mb;

2. Tăng kích thước trực tiếp (MySQL 5.7.5+)

Giả sử bạn có tổng cộng 16GB RAM và buffer pool chỉ ở mức 1GB. Bạn có thể tăng nó lên 4GB một cách an toàn mà không cần khởi động lại:

SET GLOBAL innodb_buffer_pool_size = 4294967296;

Hãy theo dõi mức sử dụng bộ nhớ của hệ điều hành. Nếu bạn đặt giá trị này cao hơn lượng RAM vật lý hiện có, trình Linux OOM killer có thể can thiệp và làm sập toàn bộ dịch vụ MySQL của bạn.

Giải pháp tốt hơn: Chia nhỏ dữ liệu (Batching)

Dùng thêm RAM chỉ là giải pháp tạm thời. Cách chuyên nghiệp để xử lý các tập dữ liệu lớn là chia nhỏ công việc thành các phần nhỏ hơn, dễ quản lý hơn. Việc chia nhỏ giúp ngăn bảng khóa bị đầy và giữ cho cơ sở dữ liệu luôn phản hồi tốt cho những người dùng khác.

Cách 1: Vòng lặp DELETE

Thay vì một lệnh DELETE khổng lồ, hãy sử dụng một vòng lặp xử lý 5.000 dòng mỗi lần. Điều này cho phép MySQL commit giao dịch và giải phóng các khóa sau mỗi đợt dữ liệu.

-- Chạy lệnh này trong SQL client hoặc script của bạn
REPEAT
    DELETE FROM activity_logs 
    WHERE log_date < '2023-06-01' 
    LIMIT 5000;
    
    SELECT ROW_COUNT() INTO @rows;
    -- Nghỉ một chút giúp ngăn chặn CPU tăng đột biến trên các máy chủ bận rộn
    DO SLEEP(0.05); 
UNTIL @rows = 0 END REPEAT;

Cách 2: Cập nhật theo dải khóa chính (Primary Key Range)

Nếu bạn đang cập nhật hàng triệu dòng, hãy tránh sử dụng LIMIT với offset lớn vì nó sẽ chậm dần theo thời gian. Thay vào đó, hãy nhắm vào các dải ID cụ thể. Cách này nhanh hơn đáng kể vì nó sử dụng trực tiếp clustered index.

-- Xử lý các dải ID 10.000 dòng
UPDATE users SET status = 'ngung_hoat_dong' WHERE last_login < '2023-01-01' AND id BETWEEN 1 AND 10000;
UPDATE users SET status = 'ngung_hoat_dong' WHERE last_login < '2023-01-01' AND id BETWEEN 10001 AND 20000;

Lưu thay đổi cấu hình vĩnh viễn

Để đảm bảo các cài đặt của bạn vẫn còn sau khi khởi động lại máy chủ, hãy cập nhật tệp my.cnf hoặc my.ini. Đối với một máy chủ cơ sở dữ liệu chuyên dụng, quy tắc chung là phân bổ 70-80% tổng dung lượng RAM cho buffer pool.

[mysqld]
# Ví dụ cho máy chủ có 8GB RAM
innodb_buffer_pool_size = 6G
# Sử dụng 1 instance cho mỗi GB để cải thiện tính đồng thời
innodb_buffer_pool_instances = 6

Xác minh

Làm thế nào để biết việc chia nhỏ dữ liệu có hiệu quả không? Hãy chạy lệnh này trong lần thao tác lớn tiếp theo của bạn:

SHOW ENGINE INNODB STATUS\G

Kiểm tra phần TRANSACTIONS. Bạn cần đảm bảo số lượng "row locks" luôn ở mức thấp (tương ứng với kích thước batch của bạn) thay vì tăng lên hàng triệu. Nếu con số này dưới 10.000, bạn đang ở vùng an toàn.

Danh sách kiểm tra tóm tắt

- **Giải pháp tạm thời:** Tăng `innodb_buffer_pool_size` nếu bạn có dư RAM.
- **Giải pháp thực sự:** Viết lại các truy vấn để xử lý dữ liệu theo từng đợt từ 5.000 đến 10.000 dòng.
- **Lưới an toàn:** Theo dõi `innodb_row_lock_current_waits` để phát hiện các điểm nghẽn về khóa trước khi chúng làm hỏng các truy vấn của bạn.