Giám sát khóa và xung đột

Hãy tưởng tượng bạn đang làm việc trong một văn phòng, nơi tất cả các cửa đều bị khóa chỉ vì một người quên chìa khóa trong phòng. Khóa trong PostgreSQL cũng hoạt động kiểu như vậy. Nếu một truy vấn hoặc transaction đã khóa một resource, thì các thao tác khác cố gắng truy cập cùng resource đó sẽ phải chờ nó xong. Tình huống này có thể gây ra delay, kịch bản xung đột và trong trường hợp xấu nhất là làm hệ thống đứng luôn.

Khi nào thì khóa xuất hiện?

Khóa (Locks) trong PostgreSQL được dùng để quản lý truy cập đồng thời vào dữ liệu. Chúng xuất hiện khi:

1. Khi thực hiện các thao tác ghi: UPDATE, DELETE, INSERT.
2. Khi dùng transaction giữ resource lâu hơn mức cần thiết.
3. Khi có xung đột giữa các transaction khác nhau cùng tranh chấp một resource.

Database thực tế là một "chiến trường" tranh giành resource, và kể cả khi bạn nghĩ hệ thống của mình chạy mượt, chỉ một transaction bất cẩn cũng có thể "đóng băng" mọi thứ, giống như một lần merge fail trong Git vậy.

Công cụ phân tích khóa: pg_locks

pg_locks là một view hệ thống của PostgreSQL, hiển thị các khóa hiện tại mà các transaction đang giữ hoặc đang chờ. Nó trả lời câu hỏi: "Ai đang giữ khóa và ai đang chờ?"

Các trường chính của pg_locks:

• locktype: loại khóa (ví dụ, relation, transaction, page, tuple).
• database: id của database.
• relation: id của bảng (nếu khóa liên quan đến bảng).
• mode: chế độ khóa (ví dụ, RowExclusiveLock, AccessShareLock).
• granted: flag cho biết khóa đã được cấp (true) hay transaction vẫn đang chờ (false).

Lưu ý: PostgreSQL áp dụng cái gọi là "chế độ khóa phân cấp". Nghĩa là các thao tác khác nhau có thể đặt các loại khóa ít nghiêm ngặt hơn (ví dụ AccessShareLock cho đọc dữ liệu) hoặc nghiêm ngặt hơn (ExclusiveLock cho sửa cấu trúc bảng).

Ví dụ: xem tất cả các khóa hiện tại

SELECT *
FROM pg_locks;

Nhưng nếu chỉ đơn giản là show hết pg_locks thì sẽ rất nhiều noise. Thử làm gì đó có ý nghĩa hơn nhé!

Ví dụ: các khóa chưa được cấp (tức là transaction đang chờ)

SELECT pid, locktype, relation::regclass AS table_name, mode, granted
FROM pg_locks
WHERE NOT granted;

Ở đây có gì?

• Mình lọc các record mà granted = false, tức là khóa chưa được cấp.
• relation::regclass chuyển id bảng thành tên bảng cho dễ đọc.

Kết quả có thể trông như sau:

Những truy vấn này sẽ giúp bạn xác định bảng/resource nào đang bị khóa và transaction nào có thể là nguyên nhân.

Phân tích xung đột: pg_blocking_pids()

Khóa thì vẫn còn nhẹ, nhưng nếu một transaction khóa transaction khác thì sao? PostgreSQL có một cách tiện để xác định "thủ phạm" bằng function pg_blocking_pids().

Function pg_blocking_pids() trả về danh sách id process (pid) đang khóa transaction hiện tại.

Ví dụ: tìm các transaction đang bị khóa bởi transaction khác

SELECT pid, pg_blocking_pids(pid) AS blocking_pids
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

Ở đây có gì?

• Mình dùng view pg_stat_activity để lấy các process đang active trong hệ thống.
• Function pg_blocking_pids(pid) trả về danh sách process đang khóa mỗi pid. Nếu danh sách không rỗng (độ dài lớn hơn 0), nghĩa là process đó đang bị khóa.

Ví dụ kết quả:

Transaction với pid = 4567 đang bị khóa bởi process 1234 và 5678. Đã tìm ra "thủ phạm" rồi nhé.

Kết thúc process đang khóa

Sau khi xác định được process đang khóa, bạn có thể dừng nó bằng function pg_terminate_backend():

SELECT pg_terminate_backend(1234); -- "Kill" process 1234

Nhưng cẩn thận nhé! Dừng process kiểu này có thể làm rollback dữ liệu trong transaction hiện tại. Dùng cái này như "nút hạt nhân" chỉ khi thực sự cần thiết thôi.

Ứng dụng thực tế: kịch bản phân tích khóa

Giả sử bạn có một database cho trường đại học với các bảng students và enrollments. Nhiều transaction cùng lúc ghi dữ liệu vào bảng enrollments, và bạn gặp phải tình trạng bị khóa.

1. Xác định khóa:

SELECT pid, locktype, relation::regclass AS table_name, mode, granted
FROM pg_locks
WHERE NOT granted;

2. Xác định process đang khóa:

SELECT pid, pg_blocking_pids(pid) AS blocking_pids
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

3. Giải quyết khóa:

Dừng cưỡng bức một trong các process xung đột:

SELECT pg_terminate_backend(1234); -- Dừng process 1234

Lưu ý: Trước khi "kill" process, hãy thử tìm hiểu nguyên nhân gây ra khóa. Có thể bạn nên xem lại logic transaction.

Lỗi phổ biến và cách tránh

Khóa thường xuất hiện do quản lý transaction không đúng cách. Ví dụ:

Lỗi: một transaction giữ khóa quá lâu mà không làm gì (trạng thái "idle in transaction").

Giải pháp: theo dõi trạng thái transaction bằng pg_stat_activity và kết thúc các transaction "treo".

SELECT pid, state, query
FROM pg_stat_activity
WHERE state = 'idle in transaction';

Lỗi: quên dùng index trong truy vấn, dẫn đến khóa ở cấp bảng.

Giải pháp: tối ưu truy vấn, thêm index cho các điều kiện hay dùng.

Bảng "ai đang chờ ai"

Để dễ chẩn đoán, bạn có thể dựng cây phụ thuộc, cho thấy transaction nào đang khóa transaction nào:

WITH RECURSIVE blocking_tree AS (
  SELECT pid, pg_blocking_pids(pid) AS blocked_by
  FROM pg_stat_activity
  WHERE cardinality(pg_blocking_pids(pid)) > 0
  UNION ALL
  SELECT a.pid, pg_blocking_pids(a.pid)
  FROM pg_stat_activity a
  JOIN blocking_tree b ON a.pid = ANY(b.blocked_by)
)
SELECT pid, blocked_by FROM blocking_tree;

Kết quả:

Ở đây bạn thấy process 5678 đang khóa process 1234, và process 1234 lại khóa 4567.