Fix lỗi MySQL ERROR 1205: Lock wait timeout exceeded; try restarting transaction

Tình huống lỗi

Bạn thực thi lệnh UPDATE, INSERT, hoặc DELETE trong một transaction, chờ khoảng 50 giây, rồi nhận được:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

InnoDB khóa hàng, không phải bảng. Khi một transaction chỉnh sửa một hàng, nó giữ exclusive lock trên hàng đó cho đến khi commit hoặc rollback — không có gì khác có thể chạm vào hàng đó trong thời gian đó. Query của bạn bị timeout vì một transaction khác đã khóa cùng các hàng đó trước và giữ khóa quá thời hạn. Thời hạn đó là innodb_lock_wait_timeout, mặc định là 50 giây.

Tìm nguyên nhân đang chặn bạn

Đừng retry mù quáng. Hãy tìm hiểu ai đang giữ lock trước.

Hiển thị các lock wait và query đang chặn

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,
  b.trx_started     AS blocking_started
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_trx_id;

Kiểm tra tất cả các process đang chạy

SHOW FULL PROCESSLIST;

Tìm các kết nối ở trạng thái Sleep với giá trị Time lớn — khoảng 200+ giây. Đó là các transaction đang mở nhưng nằm idle trong khi giữ lock mà không có gì khác có thể acquire.

Cách xử lý nhanh: Kill Transaction đang chặn

Lấy giá trị blocking_thread từ query ở trên, sau đó kill nó:

KILL 42;  -- thay 42 bằng blocking thread ID thực tế của bạn

Thử lại transaction gốc của bạn ngay lập tức. Khi blocking thread đã bị kill, các lock được giải phóng và query của bạn có thể tiếp tục. Tuy nhiên, việc kill thread chỉ là giải pháp tạm thời — đọc các phần bên dưới để hiểu tại sao lỗi này cứ tái phát.

Nguyên nhân gốc rễ

Transaction mở trong thời gian dài — Một transaction bắt đầu, thực hiện công việc chậm (hoặc chỉ nằm đó bị quên), và không bao giờ commit. Các lock vẫn mở trong suốt thời gian đó.
Không có index trên cột WHERE — Nếu không có index, InnoDB sẽ khóa nhiều hàng hơn bạn dự định. Trên bảng 5 triệu hàng, điều đó có thể nghĩa là 5 triệu row lock thay vì chỉ vài hàng bạn thực sự cần cập nhật.
Code ứng dụng thực hiện I/O bên trong transaction — Một HTTP request, đọc file, hoặc thậm chí sleep 500 ms giữa các câu SQL giữ lock mở lâu hơn mức cần thiết.
Thứ tự lock vòng tròn — Transaction A chờ lock do Transaction B giữ, trong khi B chờ lock do A giữ. Cả hai đều không thể tiếp tục. Đây là vùng deadlock kinh điển, và nó cũng có thể xuất hiện dưới dạng ERROR 1205.

Cách khắc phục lâu dài 1: Giữ Transaction ngắn gọn

Quy tắc đơn giản: mở, ghi, commit — càng nhanh càng tốt. Đưa mọi thao tác chậm ra ngoài khối transaction.

-- XẤU: công việc chậm bên trong transaction
START TRANSACTION;
-- ... gọi API bên ngoài mất 3 giây ...
UPDATE orders SET status = 'processed' WHERE id = 123;
COMMIT;

-- TỐT: chuẩn bị mọi thứ trước, sau đó mở transaction nhanh
-- (thực hiện gọi API ở đây, trước khi bắt đầu transaction)
START TRANSACTION;
UPDATE orders SET status = 'processed' WHERE id = 123;
COMMIT;

Cách khắc phục lâu dài 2: Thêm Index trên Cột Lọc

Thiếu index buộc phải quét toàn bộ bảng. Thay vì khóa 3 hàng, InnoDB có thể khóa hàng nghìn hàng — đôi khi cả bảng. Kiểm tra các index hiện có:

SHOW INDEX FROM orders;

Nếu cột trong mệnh đề WHERE chưa được đánh index, hãy thêm:

ALTER TABLE orders ADD INDEX idx_status (status);
ALTER TABLE orders ADD INDEX idx_user_id (user_id);

Với index được thiết lập, cùng một lệnh UPDATE chỉ khóa các hàng khớp thay vì quét mọi hàng trong bảng.

Cách khắc phục lâu dài 3: Điều chỉnh Lock Wait Timeout

Giảm timeout không loại bỏ tranh chấp lock, nhưng làm cho các transaction thất bại nhanh hơn. Tài nguyên được giải phóng sau 10 giây thay vì tích tụ trong 50 giây.

Chỉ cho session hiện tại

SET innodb_lock_wait_timeout = 10;

Toàn cục (cho đến khi restart)

SET GLOBAL innodb_lock_wait_timeout = 10;

Lâu dài (thêm vào my.cnf / my.ini)

[mysqld]
innodb_lock_wait_timeout = 10

Sau đó restart MySQL:

sudo systemctl restart mysql

Cách khắc phục lâu dài 4: Sử dụng Thứ tự Lock Nhất quán

Bất cứ khi nào nhiều transaction chạm vào cùng các hàng, chúng phải acquire lock theo cùng một thứ tự. Thứ tự hỗn hợp tạo ra các vòng chờ. Đây là mẫu an toàn:

-- Cả Transaction A và B đều phải khóa hàng 1 TRƯỚC hàng 2
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- khóa hàng 1 trước
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;  -- sau đó khóa hàng 2
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Giả sử Transaction A khóa hàng 1 rồi cố lấy hàng 2, trong khi Transaction B khóa hàng 2 và cố lấy hàng 1. Cả hai đều không tiến lên được. Áp dụng thứ tự nhất quán phá vỡ hoàn toàn vòng lặp đó.

Cách khắc phục lâu dài 5: Bật Ghi Log Deadlock

Bật tùy chọn này một lần và để nó chạy. MySQL ghi mọi deadlock được phát hiện vào error log, giúp chẩn đoán các vấn đề lock trong tương lai dễ dàng hơn nhiều:

SET GLOBAL innodb_print_all_deadlocks = ON;

Để kiểm tra deadlock gần đây nhất ngay bây giờ:

SHOW ENGINE INNODB STATUS\G

Cuộn đến phần LATEST DETECTED DEADLOCK. Nó hiển thị các query, bảng và loại lock liên quan — đủ để truy ngược lại code gây ra vấn đề.

Các bước xác nhận

1. Xác nhận không có transaction mở lâu dài

SELECT trx_id, trx_started, trx_query,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS seconds_running
FROM information_schema.innodb_trx
ORDER BY seconds_running DESC;

Output lành mạnh: không có hàng nào, hoặc tất cả transaction dưới vài giây.

2. Xác nhận không có lock wait nào đang hoạt động

SELECT COUNT(*) FROM information_schema.innodb_lock_waits;

Nên trả về 0.

3. Thử lại query đang thất bại

Chạy transaction đang bị lỗi. Nó nên hoàn tất mà không có ERROR 1205.

4. Xác nhận giá trị timeout được đặt đúng

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

Tham khảo nhanh

Xác định blocker: query information_schema.innodb_lock_waits kết hợp với innodb_trx
Kill blocker: KILL <thread_id>
Transaction ngắn: commit nhanh, không có I/O chậm bên trong khối transaction
Thêm index: ngăn chặn row-range hoặc full-table lock không cần thiết
Thứ tự lock nhất quán: ngăn vòng chờ vòng tròn giữa các transaction đồng thời
Ghi log deadlock: SET GLOBAL innodb_print_all_deadlocks = ON