Nguyên nhân và cách xử lý lỗi Lock wait timeout exceeded

Lỗi Lock wait timeout exceeded; try restarting transaction thường xuất hiện khi ứng dụng tương tác với cơ sở dữ liệu MySQL sử dụng engine InnoDB, đặc biệt trong các hệ thống có tần suất truy cập cao. Dưới đây là phân tích chi tiết về nguyên nhân và giải pháp hiệu quả.

Điều kiện phát sinh lỗi

• Thực hiện thao tác INSERT và UPDATE trên cùng một bản ghi trong một giao dịch (transaction).
• Nhiều máy chủ kết nối đến cùng một cơ sở dữ liệu.
• Xảy ra hiện tượng đỉnh điểm (spike) truy cập đồng thời.

Kết quả: Các thao tác thêm hoặc cập nhật bị rollback tự động, gây gián đoạn hoạt động nghiệp vụ.

Phân tích nguyên nhân Lỗi này khác biệt với deadlock:

• Lock wait timeout exceeded: Giao dịch chờ khóa vượt quá ngưỡng thời gian quy định mà chưa được cấp phát.
• Deadlock: Hai giao dịch chờ lẫn nhau, tạo vòng lặp vô hạn, hệ thống tự động hủy một trong số chúng.

Tham số quan trọng điều chỉnh hành vi này là innodb_lock_wait_timeout, mặc định là 50 giây. Khi một giao dịch phải chờ khóa vượt quá giá trị này, nó sẽ bị hủy và ném ngoại lệ.

Kiểm tra giá trị hiện tại:

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

Có thể thay đổi giá trị theo hai cách:

Cách 1 – Cấp độ session/global:

SET innodb_lock_wait_timeout = 120;
SET GLOBAL innodb_lock_wait_timeout = 120;

Lưu ý: Thay đổi GLOBAL chỉ áp dụng cho kết nối mới.

Cách 2 – Cấu hình trong file cấu hình:

[mysqld]
innodb_lock_wait_timeout = 300

Giá trị tối thiểu là 1s, tối đa lên tới ~34 năm (1073741824s), mặc định là 50s.

Phương pháp chẩn đoán và khắc phục Sử dụng các bảng thông tin trạng thái của InnoDB để xác định nguồn gốc tắc nghẽn:

• information_schema.innodb_trx: Hiển thị các giao dịch đang chạy.
• information_schema.innodb_locks: Thông tin về các khóa hiện tại.
• information_schema.innodb_lock_waits: Mối quan hệ giữa các giao dịch chờ và bị chặn.

Xem danh sách các giao dịch đang chờ khóa:

SELECT * FROM innodb_lock_waits;

Tìm các giao dịch bị chặn và khóa gây tắc:

SELECT 
    w.requesting_trx_id,
    w.blocking_trx_id,
    l.lock_id,
    t.trx_query
FROM innodb_lock_waits w
JOIN innodb_locks l ON w.blocking_lock_id = l.lock_id
JOIN innodb_trx t ON t.trx_id = w.blocking_trx_id;

Xác định ID luồng (thread) của giao dịch gây tắc:

SELECT trx_mysql_thread_id, trx_query 
FROM innodb_trx 
WHERE trx_state = 'LOCK WAIT';

Cuối cùng, hủy giao dịch gây tắc:

KILL <thread_id>;

Ngoài ra, kiểm tra trạng thái InnoDB toàn cục:

SHOW ENGINE INNODB STATUS;

Giải pháp lâu dài

• Tối ưu hóa logic nghiệp vụ: Tránh thực hiện nhiều thao tác DML trong cùng một giao dịch kéo dài.
• Tăng thời gian chờ nếu cần thiết, nhưng không nên tăng quá mức.
• Đảm bảo chỉ mục phù hợp để giảm thời gian khóa.
• Giảm tải bằng cách giới hạn số luồng truy cập song song.
• Sử dụng cơ chế retry logic trong ứng dụng khi gặp lỗi này.

InnoDB tự động phát hiện deadlock và lựa chọn giao dịch nhỏ nhất để rollback nhằm phá vỡ vòng lặp. Việc cấu hình đúng innodb_lock_wait_timeout giúp ứng dụng phản hồi kịp thời thay vì treo cứng.