Cách xử lý sự cố production database bị lock mà không cần restart service

DevOps rất rõ ràng là Dev + Ops tức là triển khai và quản trị. Với cá nhân tôi thấy thì quản trị khó hơn triển khai kha khá đấy, và trong đó quản trị liên quan database không phải lúc nào, công ty nào cũng có ông DBA riêng mà mình cũng kiêm luôn. Lúc thấy dashboard từ xanh chuyển sang đỏ, nhìn qua monitoring thấy CPU DB không cao, RAM ổn, nhưng latency tăng bất thường.

Đây cũng là phần kinh nghiệm mọi người xem thử nhé, dấu hiệu không lẫn vào đâu là Database bị lock. Khác với crash, tình huống DB bị lock sẽ die từ từ kiểu hệ thống đơ nhẹ, request trả chậm, rồi timeout hàng loạt. Bài toán đặt ra lúc đó là phải xử lý gấp mà tuyệt đối không được restart database hệ thống đang trong giai đoạn peak load, lưu lượng truy cập đang ở mức đỉnh điểm, gặp vài lần là nhớ ấy mà, nhưng chia sẻ cái này thì anh em biết cũng thêm 1 phần kiến thức đã biết : D

Trong bài này, tôi chia sẻ toàn bộ hành trình xử lý sự cố đó: từ lúc nhận diện, phân tích, đến cách gỡ lock đúng cách, rồi đảm bảo hệ thống vận hành ổn định trở lại.

1. Nhận diện vấn đề: DB bắt đầu đứng nhưng không sập

Anh em thường thấy một hệ thống bị lock thường biểu hiện rất rõ ràng là không crash, nhưng mọi thứ cực kỳ chậm.

API trả lỗi timeout: Ứng dụng không nhận được phản hồi từ DB kịp thời.
Connection pool ứng dụng đầy, không thể cấp phát connection mới.
Kiểm tra DB monitoring: Latency tăng bất thường, connection count tăng đều, nhưng slow query tăng đột biến. Dashboard DB spikes nhẹ phần wait event.

Đây là dấu hiệu rất rõ ràng của blocking locks.

Việc đầu tiên tôi làm là xác định xem có session nào đang giữ lock.

Với PostgreSQL, tôi chạy lệnh nhằm xác định các session đang hoạt động và status của nó

SELECT pid, usename, datname, state, query, xact_start, wait_event_type, wait_event
FROM pg_stat_activity
ORDER BY xact_start;

Kết quả: Dữ liệu trả về state = 'active', một vài session chạy hơn 20 phút nhưng không commit và và cột wait_event_type với wait_event báo chờ. Đó là nguyên nhân gây ra lock.

Để thấy rõ mối quan hệ blocking và blocked, tôi dùng thêm bảng thông tin về lock:

SELECT
    a.pid AS blocked_pid,
    a.query AS blocked_query,
    b.pid AS blocking_pid,
    b.query AS blocking_query
FROM pg_locks l1
JOIN pg_locks l2 ON l1.locktype = l2.locktype
    AND l1.database IS NOT DISTINCT FROM l2.database
    AND l1.relation IS NOT DISTINCT FROM l2.relation
    AND l1.page IS NOT DISTINCT FROM l2.page
    AND l1.tuple IS NOT DISTINCT FROM l2.tuple
    AND l1.virtualxid IS NOT DISTINCT FROM l2.virtualxid
    AND l1.transactionid IS NOT DISTINCT FROM l2.transactionid
    AND l1.classid IS NOT DISTINCT FROM l2.classid
    AND l1.objid IS NOT DISTINCT FROM l2.objid
    AND l1.objsubid IS NOT DISTINCT FROM l2.objsubid
    AND l1.pid <> l2.pid
JOIN pg_stat_activity a ON l1.pid = a.pid
JOIN pg_stat_activity b ON l2.pid = b.pid
WHERE l1.granted = false AND l2.granted = true;

Câu lệnh này sẽ cho ta thấy ngay blocking_pid và blocked_pid, cùng với query mà chúng đang chạy. Chạy xong là thấy ngay một session giữ lock trên một bảng lớn. Từ đây bắt đầu hành trình xử lý.

2. Phân tích nguyên nhân gốc: Tại sao lại có lock dài như vậy?

Trong các hệ thống transactional, có vài loại lock gây đau đầu:

Row-level lock
Table-level lock
Advisory lock
Lock từ các session idle in transaction
Migration giữ lock
Long-running query

Sự cố của tôi đến từ một batch job do ứng dụng chạy ngầm. Nó lấy dữ liệu theo từng đợt, update hàng loạt, nhưng vì code viết sơ sài nên một transaction mở nhưng không commit, dẫn đến giữ lock suốt 20 phút. Tệ hơn, job này không có timeout.

Nếu code viết kiểu thế này, lock là chuyện sớm muộn:

BEGIN;
UPDATE orders SET status = 'processed' WHERE status = 'pending';

-- thiếu COMMIT lock sẽ được giữ mãi mãi cho đến khi session bị ngắt

Một transaction mở mà quên commit giống như giữ chìa khóa phòng họp mà đi ăn trưa. Cả team ngồi chờ.

3. Xác định session gây lock: phải biết đúng thằng nào giữ mới xử lý được

Sau khi chạy câu lệnh ở mục 1, công việc tiếp theo là tìm đúng session đang blocking.

PostgreSQL: Tìm blockage

Câu lệnh này làm rõ vai trò của từng session trong chuỗi lock, tôi dùng lệnh tìm thằng blockage:

SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid,
       blocked_activity.query AS blocked_query,
       blocking_activity.query AS blocking_query
FROM pg_locks blocked_locks
JOIN pg_locks blocking_locks
    ON blocked_locks.locktype = blocking_locks.locktype
    AND blocked_locks.database = blocking_locks.database
    AND blocked_locks.relation = blocking_locks.relation
JOIN pg_stat_activity blocked_activity
    ON blocked_locks.pid = blocked_activity.pid
JOIN pg_stat_activity blocking_activity
    ON blocking_locks.pid = blocking_activity.pid
WHERE NOT blocked_locks.granted
  AND blocking_locks.granted;

Kết quả: blocking_pid = 12345 đang chạy một câu UPDATE hơn 20 phút. Nhìn là hiểu ngay vì sao các query khác bị xếp hàng dài.

Tham khảo cho MySQL (InnoDB)

Nếu anh em dùng MySQL, ta cần kiểm tra information_schema.innodb_lock_waits để xác định transaction nào đang chờ (requesting_trx_id) và transaction nào đang chặn (blocking_trx_id):

SELECT
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread
FROM information_schema.innodb_lock_waits w #
JOIN information_schema.innodb_trx b ON w.blocking_trx_id = b.trx_id
JOIN information_schema.innodb_trx r ON w.requesting_trx_id = r.trx_id;
# innodb_lock_waits: Chứa thông tin về các transaction đang chờ đợi lock.
# innodb_trx: Chứa thông tin chi tiết về các transaction

4. Xử lý lock không cần restart

Ở môi trường Production, việc restart database thường là phương án buộc phải tránh. Restart có thể khiến hệ thống gián đoạn, mất connection, thậm chí gây lỗi transaction.

Vậy cách làm đúng thường sẽ theo thứ tự:

Tìm session gây lock.
Đánh giá nó đang làm gì và có thể kill được không.
Kill session an toàn.
Xác minh hệ thống hồi phục ổn.

PostgreSQL: kill session `pg_terminate_backend()`

Đây là phương án hiệu quả nhất. Nó sẽ gửi tín hiệu yêu cầu terminate session. Transaction đang chạy sẽ tự động ROLLBACK và giải phóng lock.

SELECT pg_terminate_backend();

Sau khi thực hiện, lock được giải phóng và các session đang chờ sẽ được tiếp tục chạy, latency hệ thống sẽ giảm về mức bình thường chỉ trong vài giây.

MySQL: `kill query` hoặc `connection`

Không nên kill connection bừa vì có thể phá vỡ transaction quan trọng.

KILL QUERY ; -- Dùng Thread ID lấy từ innodb_trx. Ngắt query hiện tại.
KILL ;       -- Ngắt toàn bộ kết nối của thread đó.

5. Cách tránh tạo lock mới trong lúc đang xử lý

Trong khi session gây lock đang được kill và hệ thống đang hồi phục, tôi thực hiện thêm biện pháp tạm thời để ngăn chặn các session mới hoặc các job khác gây lock chồng chéo.

Tạm giảm connection pool limit của ứng dụng

Việc này hạn chế số lượng request mới đi vào DB. Nếu Connection Pool đầy sẽ gây tình trạng bottleneck, khiến nhiều request phải chờ, nhưng trong lúc DB đang quá tải thì việc giảm áp lực là cần thiết.

Đổi config tạm thời:

DB_POOL_MAX: 20 # Giảm từ 100 xuống 20
DB_POOL_MIN: 5

Tạm tắt các job nền

Các job nền thường chạy các truy vấn UPDATE hoặc INSERT lớn, là nguồn gây ra lock mới hoặc làm trầm trọng thêm tình trạng lock hiện tại.

Tạm thời pause hoặc disable các cron job hoặc batch service nghi ngờ có thể chạy các transaction nặng.

Chuyển bớt traffic read sang read replica

Giảm tải cho Primary DB, nơi đang chịu áp lực của các truy vấn WRITE bị chặn bởi lock, tạo không gian để Primary DB tập trung giải phóng lock.

Tạm thời chuyển hướng các truy vấn READ nặng (như báo cáo, thống kê) sang các Read Replica.

6. Sau khi xử lý: phải kiểm lại hệ thống

Việc kill session chỉ là bước chữa cháy. Hệ thống đã ổn định chưa? Phải kiểm tra lại ngay để đảm bảo mọi thứ đã hoàn toàn sạch sẽ.

Kiểm tra xem lock đã biến mất

Chạy lại câu lệnh này để xác nhận không còn session nào đang bị xếp hàng chờ nữa:

SELECT * FROM pg_locks WHERE NOT granted;

Kiểm tra xem có transaction lạ nào chạy lâu

Liệu có transaction nào khác chạy ngầm mà mình chưa để ý không?

SELECT pid, xact_start, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY xact_start;

Chạy analyze hoặc vacuum

Nếu lock kéo dài, các bảng bị ảnh hưởng có thể chứa nhiều dead tuple (dữ liệu đã bị xóa hoặc update nhưng chưa được dọn dẹp). Việc này giúp dọn dẹp và cập nhật thống kê cho DB, giúp các query sau chạy nhanh hơn:

VACUUM ANALYZE orders;

Kiểm tra slow query

Xem lại log hoặc bảng thống kê để đảm bảo không có query nào bị chậm một cách bất thường nữa:

SELECT * FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;

7. Ngăn sự cố tái diễn: phải chỉnh lại ứng dụng và hệ thống

Để tránh trường hợp tương tự tái diễn, tôi áp dụng thêm vài cấu hình:

idle_in_transaction_session_timeout

Đây là cấu hình quan trọng trong PostgreSQL để chống lại lỗi quên COMMIT:

ALTER SYSTEM SET idle_in_transaction_session_timeout = '2min';

Session nào mở transaction mà không làm gì trong 2 phút sẽ bị tự kill.

Thêm query timeout trên ứng dụng

Đừng chỉ dựa vào DB. Ứng dụng cũng phải tự đặt giới hạn cho query của mình. Ví dụ với Node.js dùng pg :

const client = new Client({
  statement_timeout: 30000,
});

Review logic ứng dụng

Nguyên tắc:

Không update bulk trong một transaction quá lớn
Tránh UPDATE hoặc DELETE hàng trăm ngàn bản ghi trong một transaction duy nhất
Phải dùng kỹ thuật tách batch nhỏ hơn (ví dụ: chia thành 10 block, mỗi block 10,000 bản ghi), sau mỗi block thì COMMIT để giải phóng lock tạm thời (pagination + commit theo block).

Thiết lập monitoring

Thiết lập cảnh báo sớm khi có dấu hiệu lock xuất hiện:

Số lượng locks & ngưỡng (Ví dụ: Có hơn 10 session đang chờ lock)
Query chạy & x giây (Ví dụ: Có query active nào chạy quá 10 giây)
Transactions active lâu bất thường: Cảnh báo khi một transaction đã mở quá 5 phút.

Tổng kết

Luôn tìm session gây lock trước khi đụng vào bất kỳ cấu hình nào.
Kill đúng session, đúng thời điểm, tránh ảnh hưởng transaction quan trọng.
Không xử lý lock chỉ bằng mệnh lệnh, phải hiểu root cause từ ứng dụng.
Chỉ tối ưu DB là chưa đủ, phải tối ưu logic nghiệp vụ và workflow của team.
Giám sát lock và transaction phải có alert sớm thay vì chờ người dùng kêu uy tín cũng dễ bị hạ :))