Một tình huống hay gặp với Laravel: nhiều queue worker cùng chạy job dựng lại bảng thống kê, mỗi job xử lý một nhóm dữ liệu khác nhau, và thỉnh thoảng một job thất bại với lỗi 1213. Đó là deadlock MySQL: InnoDB phát hiện hai transaction chờ nhau thành vòng và huỷ một bên. Website không ngừng, job được chạy lại, nhưng một deadlock MySQL lặp lại trong job nền là dấu hiệu thiết kế có vấn đề. Bài này hướng dẫn đọc từng dòng phần LATEST DETECTED DEADLOCK của InnoDB, giải thích vì sao hai câu lệnh ghi hai bộ dữ liệu không trùng nhau vẫn khoá chết nhau, và vì sao cách giải thích quen thuộc "hai bên cùng nâng khoá S lên X" thường không khớp với log.

Triệu chứng

Tình huống điển hình: một job nhận một mã danh mục, dùng CTE đệ quy để lấy toàn bộ danh mục con, đếm bản ghi theo từng khu vực rồi ghi vào bảng cache bằng một câu INSERT ... SELECT ... ON DUPLICATE KEY UPDATE. Một job tổng dispatch job con cho mọi danh mục cùng lúc, với giả định "dữ liệu không trùng nhau nên chạy song song được". Khi deadlock MySQL xảy ra, câu lệnh bị huỷ trả về:

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying
to get lock; try restarting transaction

Job bắt exception, ghi log rồi ném lại; nếu khai báo $tries = 3, Laravel đưa nó vào hàng đợi chạy lại. Bảng đích có cấu trúc đơn giản: khoá chính id tự tăng, một unique key trên cặp (category_id, region_id) và vài index phụ.

Điều tra

Bước 1: tìm đúng nguồn thông tin khoá

Sai lầm thường gặp là tra performance_schema.metadata_locks. Bảng này chứa metadata lock, loại khoá cấp bảng điều phối giữa DDL và DML, không phải khoá dòng của InnoDB. Với deadlock MySQL ở mức dòng, nguồn đúng là SHOW ENGINE INNODB STATUS cho deadlock đã xảy ra, hoặc performance_schema.data_locks và data_lock_waits khi đang có transaction chờ.

Bước 2: lấy log deadlock MySQL từ InnoDB status

mysql --login-path=<database> -e "SHOW ENGINE INNODB STATUS\G" \
  | sed -n '/LATEST DETECTED DEADLOCK/,/^TRANSACTIONS$/p'

Một log mẫu đã rút gọn, tên bảng và giá trị được thay bằng tên trung tính:

------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 1002, ACTIVE 0 sec inserting
mysql tables in use 5, locked 5
LOCK WAIT ... lock struct(s), ... row lock(s), undo log entries 1
INSERT INTO stats_cache (category_id, region_id, item_count, updated_at)
  WITH RECURSIVE tree AS ( SELECT id FROM categories WHERE id = 31 UNION ALL ... )
  SELECT ... GROUP BY ...
  ON DUPLICATE KEY UPDATE item_count = VALUES(item_count), updated_at = VALUES(updated_at)

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS ... index PRIMARY of table `app`.`stats_cache` trx id 1002 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... index PRIMARY ... trx id 1002 lock_mode X insert intention waiting
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 1001, ACTIVE 0 sec inserting
INSERT ... (cùng câu lệnh, danh mục 30)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS ... index PRIMARY ... trx id 1001 lock_mode X
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... index PRIMARY ... trx id 1001 lock_mode X insert intention waiting
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

Bước 3: giải nghĩa từng dòng của deadlock MySQL

  • Hai transaction, hai danh mục: cùng một câu lệnh, khác tham số, gửi từ hai kết nối khác nhau. Vậy có ít nhất hai worker đang chạy song song.
  • index PRIMARY: khoá nằm trên khoá chính tự tăng, không nằm trên unique key (category_id, region_id).
  • heap no 1 và asc supremum: supremum là bản ghi giả đứng sau bản ghi lớn nhất của trang index. Next-key lock trên supremum thực chất chỉ khoá khoảng trống phía sau giá trị lớn nhất, tức là một gap lock ở cuối index.
  • lock_mode X ở phần HOLDS: mỗi bên giữ khoá X trên đúng khoảng trống đó. Gap lock không loại trừ nhau, nên hai bên cùng giữ được.
  • insert intention waiting: mỗi bên muốn chèn dòng mới vào khoảng trống đó và phải xin insert intention lock, loại khoá bị chặn bởi gap lock của transaction khác.

Ghép lại: (1) giữ gap, chờ (2) nhả gap để chèn; (2) giữ gap, chờ (1) nhả gap để chèn. Vòng chờ khép kín, InnoDB chọn huỷ transaction có chi phí rollback thấp hơn. Vì khoá chính tự tăng, mọi dòng mới của mọi danh mục đều được nối vào cuối index, nên hai job "độc lập" vẫn gặp nhau ở cùng một khoảng trống. Đây là deadlock MySQL kinh điển giữa gap lock và insert intention, được mô tả trong phần InnoDB Locking của tài liệu MySQL 8.0.

Bước 4: cách đọc sai deadlock MySQL dễ mắc nhất

Cách giải thích quen thuộc cho deadlock MySQL với ON DUPLICATE KEY UPDATE là: hai bên cùng giữ khoá chia sẻ S trên unique key, rồi cùng muốn nâng lên X để cập nhật. Câu chuyện đó có thật trong nhiều trường hợp, nhưng log mẫu ở trên bác bỏ nó ở ba điểm: không có khoá S nào trong vòng chờ, cả hai đều giữ lock_mode X; khoá nằm trên PRIMARY, không phải unique key; và thứ đang chờ là insert intention, không phải nâng cấp khoá. Hệ quả: dù hai danh mục không có khu vực nào trùng nhau, deadlock MySQL vẫn xảy ra, vì điểm tranh chấp là cuối khoá chính.

Khoá S vẫn có mặt, nhưng ở chỗ khác. Theo tài liệu Locks Set by Different SQL Statements, ở mức REPEATABLE READ mặc định, INSERT ... SELECT đặt next-key lock chia sẻ lên các dòng đọc từ bảng nguồn. Đó là lý do số row lock của mỗi transaction thường lớn và dòng mysql tables in use 5, locked 5 xuất hiện: bảng nguồn cũng bị khoá trong suốt câu lệnh.

Nguyên nhân gốc của deadlock MySQL

  • Nhiều writer song song vào cùng một bảng có khoá chính tự tăng: mọi dòng mới dồn về cuối index, nên các job tách theo danh mục thực ra tranh cùng một khoảng trống.
  • Mỗi job là một câu lệnh lớn: đọc đệ quy qua nhiều bảng, giữ khoá trên bảng nguồn và bảng đích tới khi câu lệnh xong, làm cửa sổ va chạm rộng ra.
  • Dispatch toàn bộ cùng lúc mà không giới hạn số job ghi bảng này chạy đồng thời.

Khắc phục deadlock MySQL

Tạm thời

InnoDB đã tự xử lý deadlock MySQL: huỷ một transaction để bên kia đi tiếp. Cách xử lý chính thức của MySQL là chạy lại transaction bị huỷ, như mô tả trong How to Minimize and Handle Deadlocks. Với Laravel, khai báo $tries và thêm $backoff vài giây để lần chạy lại không lao vào đúng lúc các job khác đang ghi. Giảm khối lượng mỗi câu lệnh (giới hạn độ sâu đệ quy, lọc bớt dữ liệu) giúp thu hẹp cửa sổ va chạm, nhưng không loại bỏ điều kiện gây deadlock MySQL.

Lâu dài

  • Xếp hàng các job ghi cùng bảng, cách chắc chắn nhất để không còn deadlock MySQL ở đây: đưa chúng vào một queue riêng chỉ có một worker, hoặc dùng middleware WithoutOverlapping với một khoá chung cho bảng, theo tài liệu queue của Laravel. Đổi lại là mất tính song song của nhóm job này.
  • Tách đọc và ghi: tính kết quả bằng một truy vấn đọc, rồi upsert theo lô nhỏ. Ghi càng ngắn, cửa sổ giữ gap lock càng hẹp.
  • Dựng bảng mới rồi hoán đổi: một tiến trình duy nhất dựng bảng tạm, xong thì RENAME TABLE đổi chỗ nguyên tử với bảng đang dùng.
  • Cân nhắc READ COMMITTED cho phiên của job: bảng nguồn được đọc nhất quán không khoá, giảm số row lock. Gap lock dùng cho kiểm tra trùng khoá vẫn còn, nên không thay thế được việc xếp hàng.
// Laravel: chỉ một job ghi stats_cache tại một thời điểm
public function middleware(): array
{
    return [(new WithoutOverlapping('stats_cache'))->releaseAfter(10)];
}

Phòng ngừa deadlock MySQL trong job nền

  • Bật innodb_print_all_deadlocks = ON để mọi deadlock MySQL được ghi vào error log, thay vì chỉ giữ cái gần nhất trong SHOW ENGINE INNODB STATUS.
  • Đếm lỗi 1213 trong log ứng dụng của các job nền, cảnh báo khi vượt ngưỡng, thay vì để $tries âm thầm che đi.
  • Review job nền theo câu hỏi: có bao nhiêu job cùng ghi một bảng tại một thời điểm, và khoá chính của bảng đó tăng theo kiểu gì.
  • Khi phân tích deadlock MySQL, đọc đúng ba thông tin trong log trước khi kết luận: tên index, loại khoá (X, S, gap, insert intention) và bản ghi bị khoá.

Bài học

"Mỗi job xử lý một phần dữ liệu riêng nên chạy song song an toàn" là giả định hợp lý trên giấy và sai trong InnoDB, vì khoá không chỉ đặt trên dòng dữ liệu mà còn trên khoảng trống giữa các dòng. Bài học thứ hai thuộc về cách điều tra: một deadlock MySQL có thể được kể lại bằng một câu chuyện rất thuyết phục mà không khớp với log. Ba dòng index PRIMARY, lock_mode X và insert intention waiting đủ để bác bỏ câu chuyện S lên X, nếu chịu đọc kỹ.

Cấu hình MySQL đa tenant có ở bài Tầng Database: MySQL đa tenant trên VPS cấu hình nhỏ, cách chạy queue worker bằng Supervisor ở bài Tăng tốc website với Laravel ResponseCache, Redis và Supervisor, và phương pháp chung ở bài Điều tra sự cố nhiều tầng: từ balancer tới database và Kubernetes.

Bài tiếp theo

OOM nginx trên balancer: khi một máy nhỏ gánh nhiều website. Bài sau quay về tầng balancer: điều gì xảy ra với các site phía sau khi tiến trình nginx ở cổng vào bị kernel kill vì hết RAM.