Query MySQL Bị Treo: Cách Tìm, KILL Và Xử Lý Tận Gốc (Kèm AWS RDS)

Giới Thiệu

Bạn bấm nút "Tìm kiếm" trên màn hình danh sách. Loading quay mãi, 60 giây sau trang báo lỗi 504 Gateway Timeout. Bạn bấm lại, vẫn vậy. Mở MySQL client ra xem thì thấy có 3, 4 câu query giống hệt nhau đang chạy, câu lâu nhất đã chạy hơn 10 phút, dù request phía PHP/Node đã chết từ lâu.

Đây là tình huống mà gần như developer nào làm việc với MySQL cũng gặp, và gặp nhiều nhất trong lúc development và test: dữ liệu staging bắt đầu lớn dần, một màn hình tìm kiếm mới viết chạy ổn với 1.000 dòng nhưng "treo" với 1 triệu dòng, hoặc một transaction quên COMMIT khiến cả team không update được bảng.

Bài viết này là quy trình xử lý query bị treo từ đầu đến cuối:

1. Tìm query đang treo bằng một câu SQL duy nhất.
2. Phân loại: đang chạy thật, đang chờ lock, hay là "zombie query" mà client đã bỏ đi.
3. KILL đúng cách, kể cả trên AWS RDS, nơi lệnh KILL thông thường hay bị từ chối.
4. Hiểu 11 nguyên nhân phổ biến để sửa tận gốc, kèm một case study thực tế.
5. Phòng ngừa để lần sau query không thể treo hàng chục phút nữa.

Bài này là phần mở rộng của Troubleshooting MySQL. Nếu bạn cần quy trình xử lý sự cố tổng quát (CloudWatch, connection, deadlock, FLUSH), hãy đọc bài đó trước.

Query "Bị Treo" Thực Chất Là Gì?

Trong MySQL không có trạng thái nào tên là "treo". Khi developer nói "query bị treo", thực tế là một trong ba tình huống sau, và mỗi tình huống cần cách xử lý khác nhau:

Loại thứ ba là loại hay bị bỏ qua nhất. Nhiều developer nghĩ rằng khi request timeout thì query cũng dừng theo. Không phải vậy. MySQL chỉ phát hiện client đã ngắt kết nối khi nó cố gửi kết quả về. Trong suốt thời gian query còn đang tính toán, nó không biết và cũng không quan tâm client còn ở đó hay không.

Bước 1: Liệt Kê Query Đang Chạy Quá Lâu

Câu query dưới đây là "câu thần chú" nên lưu sẵn trong snippet của SQL client:

SELECT
  ID, USER, HOST, DB, TIME, STATE,
  LEFT(REPLACE(INFO, '\n', ' '), 150) AS INFO
FROM information_schema.PROCESSLIST
WHERE
  COMMAND <> 'Sleep'
  AND ID <> CONNECTION_ID()   -- bỏ qua chính session đang chạy câu lệnh này
  AND TIME > 30               -- chạy quá 30 giây
ORDER BY TIME DESC;

Giải thích:

• COMMAND <> 'Sleep': bỏ các connection đang rảnh trong connection pool. Chúng không chạy gì cả, chỉ làm nhiễu kết quả.
• ID <> CONNECTION_ID(): loại bỏ chính session của bạn, tránh nhầm lẫn và tránh tự kill mình.
• REPLACE(INFO, '\n', ' '): gộp câu SQL nhiều dòng (rất phổ biến khi query được sinh ra từ ORM hoặc query builder) thành một dòng.
• LEFT(..., 150): cắt bớt để kết quả không vỡ layout khi xem trên terminal. Khi cần câu SQL đầy đủ, dùng SHOW FULL PROCESSLIST hoặc bỏ LEFT.
• TIME > 30: ngưỡng nên điều chỉnh theo hệ thống. Với API online, 30 giây đã là bất thường. Với batch hoặc report, có thể nâng lên vài phút.

Với MySQL 8.0+, information_schema.PROCESSLIST đã được đánh dấu deprecated. Bạn có thể dùng performance_schema.processlist với cùng các cột, cách này không cần giữ mutex toàn cục khi đọc nên nhẹ hơn trên server đang bận:

SELECT ID, USER, HOST, DB, TIME, STATE, LEFT(INFO, 150) AS INFO
FROM performance_schema.processlist
WHERE COMMAND <> 'Sleep' AND TIME > 30
ORDER BY TIME DESC;

Phát Hiện Nhiều Bản Sao Của Cùng Một Query

Khi user bấm lại nhiều lần, hoặc queue tự retry, processlist sẽ có nhiều query giống hệt nhau. Gom nhóm theo đoạn đầu câu SQL để thấy ngay:

SELECT
  LEFT(REPLACE(INFO, '\n', ' '), 100) AS query_prefix,
  COUNT(*)   AS total,
  MAX(TIME)  AS max_time,
  MIN(TIME)  AS min_time
FROM information_schema.PROCESSLIST
WHERE COMMAND <> 'Sleep' AND ID <> CONNECTION_ID()
GROUP BY query_prefix
HAVING total > 1
ORDER BY total DESC;

Nếu một dạng query có total lớn, max_time và min_time chênh nhau nhiều, gần như chắc chắn đang có retry storm: bản sao sau được tạo ra trong khi bản trước chưa chạy xong.

Bước 2: Phân Loại Bằng Cột STATE

Đừng kill ngay. Hãy nhìn cột STATE trước, vì nó cho biết query đang làm việc hay đang chờ:

Với loại chờ lock, kill chính query đang chờ thường không giải quyết được gì: query tiếp theo sẽ lại xếp hàng chờ. Cần tìm session đang giữ lock (blocking transaction). Cách tìm được trình bày chi tiết ở phần Tìm Blocking Transaction trong bài Troubleshooting. Câu ngắn gọn nhất trên MySQL 8.0:

SELECT
  waiting_pid, waiting_query,
  blocking_pid, blocking_query,
  wait_age
FROM sys.innodb_lock_waits;

Nếu blocking_query là NULL, session đó đã chạy xong câu lệnh nhưng chưa COMMIT. Đây là thủ phạm cần kill, không phải các query đang chờ.

Trước khi sang bước 3, hãy ghi lại ID, USER, HOST và câu SQL đầy đủ. Sau khi kill, đây là dữ liệu duy nhất còn lại để tìm root cause.

Bước 3: KILL Query — Server Tự Host Và AWS RDS

Trên MySQL Tự Host

-- Chỉ dừng câu query đang chạy, giữ nguyên connection
KILL QUERY 9459;

-- Ngắt hẳn connection (dừng query và rollback transaction dở dang nếu có)
KILL 9459;          -- tương đương KILL CONNECTION 9459;

User có quyền CONNECTION_ADMIN (MySQL 8.0) hoặc SUPER (bản cũ hơn) có thể kill thread của bất kỳ user nào. User thường chỉ kill được thread của chính mình.

Trên AWS RDS / Aurora MySQL

Trên RDS, master user không có quyền SUPER. Vì vậy khi bạn đăng nhập bằng master user và kill query do user của application chạy, rất hay gặp lỗi:

ERROR 1095 (HY000): You are not owner of thread 9459

RDS cung cấp sẵn stored procedure để làm việc này:

-- Chỉ dừng câu query, giữ connection (nên ưu tiên dùng trước)
CALL mysql.rds_kill_query(9459);

-- Ngắt luôn cả connection
CALL mysql.rds_kill(9459);

Với query do chính user đang đăng nhập chạy, lệnh thông thường vẫn dùng được:

KILL QUERY 9459;

Bảng Tra Nhanh

Kill Query Hay Kill Connection?

• Bắt đầu bằng kill query (KILL QUERY / rds_kill_query). Cách này nhẹ nhàng hơn, connection vẫn còn để application tiếp tục dùng.
• Chuyển sang kill connection (KILL / rds_kill) khi:
  • Session đang giữ transaction mở và giữ lock (câu query đã xong nhưng chưa COMMIT). Kill query không có tác dụng vì không có query nào đang chạy, chỉ kill connection mới buộc transaction rollback và nhả lock.
  • Connection thuộc connection pool và bạn muốn pool tạo lại connection mới, sạch sẽ.

Kill Hàng Loạt

Khi có retry storm với hàng chục bản sao, kill từng ID bằng tay rất mất thời gian. Hãy để MySQL sinh ra câu lệnh, rồi kiểm tra trước khi chạy:

-- Sinh lệnh kill cho mọi query của user app chạy quá 5 phút
SELECT CONCAT('CALL mysql.rds_kill_query(', ID, ');') AS kill_cmd
FROM information_schema.PROCESSLIST
WHERE COMMAND <> 'Sleep'
  AND USER = 'app_user'
  AND TIME > 300
  AND INFO LIKE 'SELECT%';    -- chỉ kill SELECT, tránh kill nhầm UPDATE/DELETE đang chạy dở

Copy kết quả, đọc lại một lượt, rồi mới chạy. Trên server tự host, đổi CALL mysql.rds_kill_query( thành KILL QUERY .

Điều kiện INFO LIKE 'SELECT%' rất quan trọng. Kill một SELECT là an toàn vì không có gì cần rollback. Kill một UPDATE/DELETE lớn đang chạy dở sẽ kích hoạt rollback, và việc rollback có thể lâu hơn cả chính câu lệnh (xem phần dưới).

Sau Khi Kill: Xác Nhận Lại

Chạy lại câu query ở Bước 1. Có ba khả năng:

1. Session biến mất (với kill connection) hoặc COMMAND chuyển về Sleep (với kill query): xong.
2. COMMAND = Killed: InnoDB đang rollback các thay đổi. Session vẫn giữ lock cho đến khi rollback xong. Đừng restart server lúc này, vì khi khởi động lại InnoDB vẫn phải rollback tiếp, và server sẽ không nhận kết nối cho đến khi xong. Theo dõi tiến độ qua information_schema.innodb_trx (cột trx_rows_modified giảm dần).
3. Query mới giống hệt xuất hiện: application hoặc user đang retry. Kill chỉ là giảm triệu chứng, cần chặn ở phía application (xem phần Phòng ngừa).

11 Nguyên Nhân Phổ Biến Khiến Query Bị Treo

Kill chỉ là ứng cứu. Nếu không tìm ra lý do, vài giờ sau query đó sẽ treo lại. Dưới đây là các nguyên nhân hay gặp nhất:

Phần dưới đi sâu vào những nguyên nhân hay gặp nhất trong development.

#1 — Phân Bố Dữ Liệu Lệch Làm Optimizer Chọn Sai

MySQL optimizer ước lượng số dòng dựa trên statistics của index, và mặc định giả định dữ liệu phân bố đều. Nếu cột status có 3 giá trị, optimizer nghĩ mỗi giá trị chiếm khoảng 1/3 số dòng. Nhưng thực tế có thể là 99% dòng có status = 'DONE' và chỉ 1% là 'PENDING'. Khi đó, cùng một câu SQL, lọc 'PENDING' thì nhanh còn lọc 'DONE' thì treo, và optimizer có thể chọn plan tệ cho cả hai.

Kiểm tra mức độ lệch:

SELECT status, COUNT(*) AS cnt
FROM orders
GROUP BY status
ORDER BY cnt DESC;

Cách xử lý, từ nhẹ đến mạnh:

-- 1. Cập nhật statistics (nhanh, an toàn, nên thử đầu tiên)
ANALYZE TABLE orders;

-- 2. MySQL 8.0+: tạo histogram để optimizer biết phân bố thực tế
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 16 BUCKETS;

-- 3. Ép thứ tự join khi optimizer vẫn chọn sai
SELECT STRAIGHT_JOIN ...
FROM small_filtered_table s
JOIN big_table b ON b.s_id = s.id;

Đặc biệt chú ý key_len trong EXPLAIN. Nếu index có 3 cột nhưng key_len chỉ tương ứng với cột đầu tiên, MySQL chỉ dùng được cột đó để thu hẹp dữ liệu. Phần còn lại phải lọc từng dòng, và nếu cột đầu tiên lại là cột bị lệch phân bố, đây là công thức của một query treo (xem case study bên dưới).

#3 — Index Có Nhưng Không Dùng Được

Đây là lỗi rất phổ biến trong code mới viết, vì câu SQL nhìn hoàn toàn bình thường:

-- ❌ Dùng hàm trên cột: index trên created_at bị bỏ qua
SELECT * FROM orders WHERE DATE(created_at) = '2026-10-01';

-- ✅ Viết lại thành range
SELECT * FROM orders
WHERE created_at >= '2026-10-01' AND created_at < '2026-10-02';

-- ❌ So sánh cột VARCHAR với số: MySQL phải ép kiểu từng dòng
SELECT * FROM users WHERE phone = 0901234567;

-- ✅ So sánh cùng kiểu
SELECT * FROM users WHERE phone = '0901234567';

-- ❌ LIKE bắt đầu bằng wildcard: không dùng được B-tree index
SELECT * FROM products WHERE name LIKE '%áo%';

Một lỗi khó thấy hơn là lệch collation giữa hai cột join (ví dụ một bảng utf8mb4_general_ci, bảng kia utf8mb4_0900_ai_ci). MySQL phải convert từng giá trị, và index ở một phía sẽ không được dùng. Xem thêm ở bài Character Set và Collation.

#6 — Metadata Lock Từ Migration

Tình huống kinh điển trong development: một developer mở transaction trong SQL client, chạy vài câu SELECT, rồi đi họp mà quên đóng. Một developer khác chạy migration ALTER TABLE orders ADD COLUMN .... Migration chờ metadata lock. Và từ lúc đó, mọi query vào bảng orders, kể cả SELECT đơn giản, đều xếp hàng phía sau migration với STATE = Waiting for table metadata lock.

Tìm session đang giữ transaction lâu nhất:

SELECT trx_mysql_thread_id AS thread_id,
       trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_seconds,
       trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started
LIMIT 5;

Session có age_seconds lớn và trx_query = NULL gần như chắc chắn là thủ phạm. Để migration không treo cả bảng trong tương lai, đặt timeout ngắn cho chính session chạy DDL:

SET SESSION lock_wait_timeout = 10;   -- chờ metadata lock tối đa 10 giây rồi báo lỗi
ALTER TABLE orders ADD COLUMN note VARCHAR(255);

Migration fail sau 10 giây vẫn tốt hơn nhiều so với treo cả bảng trong 10 phút.

#9 — Zombie Query: Application Timeout Nhưng Query Vẫn Chạy

Cần hiểu rõ chuỗi sự kiện:

1. Request vào PHP-FPM / Node / Java, gửi query tới MySQL.
2. Query chạy lâu. Sau N giây, một tầng nào đó timeout: load balancer, nginx, PHP-FPM (request_terminate_timeout), hoặc HTTP client của frontend.
3. Worker của application bị kill hoặc bỏ request. Kết nối TCP tới MySQL có thể bị đóng.
4. MySQL vẫn chạy tiếp, vì nó chỉ phát hiện client mất khi cố gửi dữ liệu về.

Thêm một bẫy với PHP: trên Linux, max_execution_time của PHP không tính thời gian chờ database. Script có thể chờ một query 10 phút mà không bao giờ chạm max_execution_time. Còn PDO::ATTR_TIMEOUT với driver MySQL chỉ là timeout lúc kết nối, không phải timeout của query.

Cách chặn hiệu quả nhất là để MySQL tự huỷ query, bằng max_execution_time (đơn vị mili giây, chỉ áp dụng cho câu SELECT read-only):

-- Cho một câu query cụ thể (optimizer hint)
SELECT /*+ MAX_EXECUTION_TIME(60000) */ id, name
FROM orders
WHERE ...;

-- Cho cả session (đặt ngay sau khi kết nối, ví dụ trong config ORM)
SET SESSION max_execution_time = 60000;

-- Cho toàn server (trên RDS: đặt trong DB Parameter Group)
SET GLOBAL max_execution_time = 60000;

Khi bị huỷ, application nhận lỗi:

ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

Nguyên tắc chọn giá trị: max_execution_time nên bằng hoặc nhỏ hơn timeout của application một chút. Như vậy MySQL dừng query trước hoặc cùng lúc với lúc application bỏ cuộc, không còn ai chạy công vô ích.

Ví dụ cấu hình trong Laravel, đặt cho mọi connection:

// config/database.php
'mysql' => [
    // ...
    'options' => [
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET SESSION max_execution_time = 60000',
    ],
],

Không nên đặt giá trị global quá thấp nếu cùng database còn phục vụ batch/report chạy lâu hợp lệ. Trong trường hợp đó, đặt ở session cho connection của API online, và để connection của batch dùng giá trị khác.

#10 — Retry Storm

Retry storm biến một query chậm thành sự cố toàn hệ thống. Một query 2 phút không đáng sợ. Nhưng nếu 20 user cùng bấm lại 3 lần, bạn có 60 query nặng chạy song song, tranh nhau CPU và buffer pool, và mọi query khác trên database cũng chậm theo.

Chặn ở nhiều tầng:

• Frontend: disable nút submit khi request đang chạy, debounce ô tìm kiếm.
• Backend: retry với exponential backoff, không retry khi lỗi là timeout của query nặng.
• Database: giới hạn số connection của user dành cho tính năng nặng:

ALTER USER 'report_user'@'%' WITH MAX_USER_CONNECTIONS 3;

Case Study: Phân Bố Dữ Liệu Lệch + PHP Timeout = Query Treo 6–14 Phút

Đây là một sự cố thực tế trong quá trình phát triển một hệ thống quản lý kho (tên bảng đã được đổi cho dễ đọc). Màn hình danh sách nhập hàng (Receiving) có một query lấy danh sách ID theo trang. Query này join bảng style/SKU (product_styles) với bảng đơn đặt hàng (purchase_orders).

Bảng purchase_orders có index ix_po_active_po_no, cột đầu tiên là history_seq, và giá trị history_seq phân bố rất lệch: phần lớn bản ghi dồn vào một vài giá trị. Lỗi phân bố lệch của cột này đã từng xuất hiện ở 2 API khác trước đó, nhưng ở màn hình này nặng hơn nhiều.

Triệu Chứng

• Mọi kiểu tìm kiếm trên màn hình đều không xong trong 90 giây, kể cả khi chỉ lọc 1 style.
• Request PHP timeout ở 90 giây và trả lỗi cho user.
• Processlist trên RDS có nhiều query cùng dạng với TIME từ 6 đến 14 phút, chiếm CPU và connection của các API khác.

Phân Tích Bằng EXPLAIN

• Optimizer bắt đầu từ product_styles. Với mỗi SKU, nó tra purchase_orders qua ix_po_active_po_no.
• Nhưng key_len = 8 cho thấy chỉ cột history_seq (BIGINT, 8 byte) được dùng. Các cột phía sau của index bị bỏ qua.
• Vì history_seq lệch, mỗi lần tra như vậy quét khoảng 1 triệu dòng PO. Nhân với số SKU, tổng số dòng phải đọc vượt xa mọi ước lượng của optimizer.

Vì Sao Lại "Treo" 14 Phút Thay Vì 90 Giây?

Đây là sự kết hợp của 3 nguyên nhân trong bảng ở trên:

• #1 (plan sai do dữ liệu lệch) khiến query chạy rất lâu.
• #9 (zombie query): PHP timeout ở 90 giây, nhưng MySQL không biết client đã bỏ đi, nên query tiếp tục chạy đến khi xong, mất 6 đến 14 phút.
• #10 (retry storm): user thấy lỗi thì bấm tìm lại, tạo thêm query giống hệt. Các query tranh nhau tài nguyên nên càng chạy chậm hơn.

Xử Lý Theo 3 Tầng

-- Tầng 1, ứng cứu ngay: kill các query đang treo
CALL mysql.rds_kill_query(<id>);

-- Tầng 2, chặn zombie query: MySQL tự huỷ SELECT chạy quá 90 giây,
-- khớp với timeout của PHP
SET SESSION max_execution_time = 90000;

Tầng 3, sửa tận gốc: thêm composite index đúng thứ tự cột để lookup vào purchase_orders không phải dừng ở history_seq, hoặc viết lại query để optimizer bắt đầu từ bảng đã được lọc hẹp nhất. Sau đó kiểm tra lại bằng EXPLAIN ANALYZE: key_len phải tăng, và rows của mỗi lần lookup phải giảm từ khoảng 1 triệu xuống vài dòng.

Bài học lớn nhất: khi một cột phân bố lệch đã gây lỗi ở một API, hãy rà tất cả các query khác join qua cùng index đó và sửa luôn. Đừng đợi chúng lần lượt treo ở các màn hình khác.

Phòng Ngừa: Để Query Không Thể Treo Hàng Chục Phút

Điểm "test với dữ liệu gần giống production" đáng nhấn mạnh. Rất nhiều query chạy tốt ở môi trường dev chỉ vì dữ liệu dev nhỏ và phân bố đều. Lỗi #1 trong case study ở trên sẽ không bao giờ lộ ra nếu dữ liệu test chỉ có vài nghìn dòng với history_seq ngẫu nhiên.

Checklist Khi Gặp Query Bị Treo

• [ ] Chạy query ở Bước 1, liệt kê các query có TIME vượt ngưỡng.
• [ ] Gom nhóm theo câu SQL: có retry storm không?
• [ ] Đọc STATE: chạy thật hay chờ lock?
• [ ] Nếu chờ lock: tìm blocking transaction, kill thủ phạm chứ không phải query đang chờ.
• [ ] Ghi lại ID, USER, câu SQL đầy đủ, chạy EXPLAIN trước khi kill.
• [ ] Kill: KILL QUERY trên server tự host, CALL mysql.rds_kill_query() trên RDS. Chỉ dùng kill connection khi cần nhả transaction.
• [ ] Chạy lại Bước 1 để xác nhận. Nếu thấy Killed, chờ rollback, đừng restart.
• [ ] Đối chiếu bảng 11 nguyên nhân để tìm root cause.
• [ ] Đặt max_execution_time khớp với timeout của application.
• [ ] Sửa tận gốc (index, query, logic transaction) và rà các query tương tự.

Kết Luận

Query bị treo hiếm khi chỉ do một nguyên nhân. Thường đó là một query có plan tệ, cộng với việc application timeout nhưng database không biết, cộng với user bấm lại. Mỗi yếu tố riêng lẻ chỉ làm chậm, nhưng gộp lại thì có thể làm cả database đứng hình.

Quy trình cần nhớ chỉ có bốn bước: liệt kê → phân loại bằng STATE → kill đúng lệnh (đặc biệt trên RDS) → tìm root cause. Sau đó, dùng max_execution_time để đảm bảo dù query có tệ đến đâu, nó cũng không thể chạy lâu hơn thời gian application còn chờ kết quả.

Để đi sâu hơn: Tối ưu query MySQL với Index giúp sửa tận gốc các query chậm, EXPLAIN trong MySQL giúp đọc execution plan, và Troubleshooting MySQL là quy trình xử lý sự cố database tổng quát.