Chẩn đoán và khắc phục Deadlock trong MySQL: Phân tích log InnoDB và kỹ thuật chống khóa chéo - ITFROMZERO - Share tobe shared! Kinh nghiệm xử lý sự cố deadlock MySQL lúc 2 giờ sáng trên production. Hướng dẫn phân tích log SHOW ENGINE INNODB STATUS, cấu hình innodb_print_all_deadlocks và thiết kế transaction chống khóa chéo.

Table of Contents

Bối cảnh thực tế: Cơn ác mộng lúc nửa đêm

2 giờ sáng. Điện thoại rung bần bật trên bàn đầu giường vì chuông PagerDuty. Màn hình monitoring đỏ rực: API checkout đứt gánh, tỷ lệ lỗi HTTP 500 chạm mốc 35%, còn database connection pool (mức trần 200 connection) thì kịch kim.

Bật vội laptop SSH vào server production, log ứng dụng tuôn ra một loạt exception quen thuộc:

Deadlock found when trying to get lock; try restarting transaction (errno: 1213)

Nhiều bạn mới gặp lỗi này thường đoán MySQL bị crash hoặc câu lệnh SQL sai cú pháp. Thực tế hoàn toàn không phải. Deadlock phát sinh khi hai hoặc nhiều transaction giam giữ lock trên các tài nguyên khác nhau, đồng thời lại chờ tài nguyên của nhau theo chu trình khép kín (circular wait).

Hình dung đơn giản: Transaction 1 giữ bản ghi A và chờ bản ghi B. Cùng lúc, Transaction 2 giữ bản ghi B và chờ bản ghi A. Cả hai phía ghìm nhau, không bên nào chịu nhường. Để hệ thống không treo vĩnh viễn, InnoDB engine bắt buộc phải can thiệp. Nó chọn ‘trảm’ (rollback) transaction có chi phí hoàn tác nhỏ hơn—tức transaction có ít row bị thay đổi hơn dựa theo undo log.

Hồi bảng orders và wallets của team mình chạm mốc 15 triệu bản ghi, hệ thống phải gánh tải tầm 1.800 request/giây trong đợt sale. Khi lượng ghi đồng thời tăng vọt, chỉ cần hai luồng code gọi lock lệch thứ tự một mili-giây là database nghẽn cứng ngắc ngay lập tức.

Chẩn đoán nhanh tại hiện trường

Khi sự cố đang diễn ra, công cụ đầu tiên bạn cần dùng là bảng trạng thái nội bộ của InnoDB.

Đăng nhập vào MySQL server qua terminal:

mysql -u root -p

Chạy ngay lệnh sau để xem báo cáo hiện trạng:

SHOW ENGINE INNODB STATUS\G

Cuộn màn hình đến phần LATEST DETECTED DEADLOCK. Tại đây, InnoDB ghi lại chi tiết giải phẫu của vụ khóa chéo gần nhất:

(1) TRANSACTION & (2) TRANSACTION: Mã thread ID, thời gian active, số row đã lock và dung lượng undo log.
WAITING FOR THIS LOCK TO BE GRANTED: Câu lệnh SQL đang đứng chờ và dạng lock yêu cầu (thường là lock_mode X waiting – Exclusive lock).
HOLDS THE LOCK(S): Tài nguyên mà transaction đối ứng đang nắm giữ.
WE ROLLBACK TRANSACTION: Quyết định cuối cùng của InnoDB xem transaction nào bị hủy để cứu luồng còn lại.

Điểm trừ chí mạng: SHOW ENGINE INNODB STATUS chỉ lưu đúng một sự kiện deadlock gần nhất. Nếu hệ thống dính bão lỗi với hàng chục vụ khóa chéo mỗi phút, log mới sẽ đè log cũ liên tục. Đến sáng hôm sau ngồi truy vết, bạn sẽ không còn dấu vết gốc để điều tra.

Cấu hình chi tiết để gom log triệt để

Để không bỏ sót bất kỳ vụ xung đột nào trên production, ta phải bắt MySQL ghi nhận toàn bộ biến cố deadlock vào file error log thông thường.

1. Bật ngay trên runtime (không cần restart MySQL)

Chạy lệnh này trực tiếp trên production mà không làm gián đoạn traffic của khách hàng:

SET GLOBAL innodb_print_all_deadlocks = ON;

Kiểm tra lại xem biến hệ thống đã nhận giá trị mới chưa:

SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';

2. Lưu cấu hình vĩnh viễn vào my.cnf

Mở file cấu hình MySQL bằng quyền root:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Thêm hai dòng sau vào block [mysqld]:

[mysqld]
# Bật ghi log toàn bộ deadlock vào error log (/var/log/mysql/error.log)
innodb_print_all_deadlocks = 1

# Giảm thời gian chờ lock từ 50s mặc định xuống 5s-10s cho web app
innodb_lock_wait_timeout = 10

Vì sao nên giảm innodb_lock_wait_timeout? Mặc định MySQL cho phép transaction chờ lock tới 50 giây. Với ứng dụng web, giữ connection chờ 50 giây sẽ làm cạn sạch connection pool trong chớp mắt. Giảm xuống 10 giây giúp query fail fast, nhả tài nguyên cho request khác và trả lỗi rõ ràng về cho tầng client retry.

3. Kỹ thuật thiết kế Transaction chống khóa chéo

Sau khi đọc log từ vụ việc lúc 2 giờ sáng, nguyên nhân lộ diện: module nạp/chuyển tiền giữa hai ví điện tử chính là thủ phạm.

Khi User 10 chuyển cho User 20: code khóa bản ghi 10 rồi mới khóa 20. Cùng thời điểm đó, User 20 chuyển ngược tiền cho User 10: code lại khóa 20 trước rồi mới đến 10. Hai tiến trình đâm sầm vào nhau tạo thành deadlock.

Cách giải quyết triệt để nằm ở tầng code: Deterministic Lock Ordering (Khóa tài nguyên theo thứ tự ID cố định). Dù chuyển tiền theo chiều nào, ta luôn bắt buộc sắp xếp lock bản ghi có ID nhỏ hơn trước, ID lớn hơn sau.

Đoạn code Python chuẩn hóa luồng xử lý:

def transfer_funds(connection, from_account_id: int, to_account_id: int, amount: float):
    if from_account_id == to_account_id:
        raise ValueError("Tài khoản nguồn và đích không được trùng nhau")

    # Chuẩn hóa thứ tự lock: luôn khóa ID nhỏ trước, ID lớn sau
    first_id, second_id = sorted([from_account_id, to_account_id])

    cursor = connection.cursor()
    try:
        connection.begin()

        # 1. Lock tuần tự theo ID tăng dần để triệt tiêu circular wait
        cursor.execute("SELECT id, balance FROM accounts WHERE id = %s FOR UPDATE", (first_id,))
        acc1 = cursor.fetchone()
        cursor.execute("SELECT id, balance FROM accounts WHERE id = %s FOR UPDATE", (second_id,))
        acc2 = cursor.fetchone()

        # 2. Kiểm tra số dư tài khoản chuyển tiền
        sender_balance = acc1["balance"] if acc1["id"] == from_account_id else acc2["balance"]
        if sender_balance < amount:
            raise ValueError("Số dư khả dụng không đủ để giao dịch")

        # 3. Cập nhật số dư cả hai tài khoản
        cursor.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (amount, from_account_id))
        cursor.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (amount, to_account_id))

        connection.commit()
    except Exception as e:
        connection.rollback()
        raise e
    finally:
        cursor.close()

Hai nguyên tắc bắt buộc phải nhớ khi viết transaction:

Giữ transaction cực ngắn: Tuyệt đối không gọi third-party API (cổng thanh toán, gửi SMS/mail) hoặc tính toán nặng khi đang mở transaction. Hãy hoàn tất gọi API ngoài trước, rồi mới mở database transaction để ghi nhận.
Đảm bảo câu lệnh UPDATE/DELETE có index hỗ trợ: Nếu câu lệnh UPDATE ... WHERE ... không ăn index, InnoDB buộc phải quét toàn bảng. Hậu quả là nó sẽ đặt khóa toàn bộ các dòng và khoảng trống giữa các dòng (Gap Lock), làm nguy cơ nổ deadlock tăng vọt.

Kiểm tra & Monitoring dài hạn

Viết code chuẩn chỉ là bước một. Bạn cần hệ thống giám sát để biết bản vá thực sự phát huy tác dụng hay chưa.

1. Soi trực tiếp error log

Dùng lệnh tail để lọc riêng các dòng deadlock từ log của MySQL:

sudo tail -f /var/log/mysql/error.log | grep -A 20 "InnoDB: transactions deadlock detected"

2. Kiểm tra bộ đếm Deadlock qua metric status

Với Percona Server hoặc MariaDB, bạn có thể tra cứu nhanh:

SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks';

Nếu đang chạy MySQL bản chuẩn (Community 5.7/8.0+), biến status trên không có sẵn. Thay vào đó, hãy kích hoạt InnoDB Metrics table:

-- Bật bộ đếm metric deadlock nếu chưa bật
SET GLOBAL innodb_monitor_enable = 'lock_deadlocks';

-- Truy vấn tổng số lần deadlock từ khi bật server
SELECT name, count FROM information_schema.innodb_metrics WHERE name = 'lock_deadlocks';

3. Thiết lập cảnh báo Prometheus Alertmanager

Đừng chờ user réo tên mới biết hệ thống gặp sự cố. Hãy thiết lập alert rule trong Prometheus thông qua mysqld_exporter:

- alert: MySQLDeadlockSpike
  expr: rate(mysql_info_schema_innodb_metrics_lock_deadlocks_total[5m]) > 0
  for: 1m
  labels:
    severity: warning
  annotations:
    summary: "Deadlock xuất hiện trên MySQL {{ $labels.instance }}"
    description: "Tỷ lệ deadlock vượt mức 0 trong 5 phút qua. Cần kiểm tra error log ngay."

Deadlock là một phần tự nhiên của hệ cơ sở dữ liệu quan hệ khi chạy đa luồng. Việc bạn cần làm không phải là cầu nguyện nó không xảy ra, mà là kiểm soát thứ tự khóa chặt chẽ, rút ngắn thời gian giữ lock và thiết lập monitoring đủ nhạy để xử lý từ sớm.