1. 背景

正常情況下，PostgreSQL只要連上了就能愉快地使用了，但是在一些特別的場景，如壓測或者某些不可描述的異常，會出現(xiàn)數(shù)據(jù)庫連接異常的情況，比如鏈接數(shù)占滿了，無法釋放的情況，這時，我們會猜測是不是數(shù)據(jù)庫死鎖了？或者鏈接數(shù)達到最大值了？

2. 常用SQL語句

2.1. 查詢PostgreSQL設(shè)置的最大連接數(shù)

-- 查詢最大連接數(shù)
SHOW max_connections;

2.2. 查詢當(dāng)前使用中的連接數(shù)

-- 查詢當(dāng)前連接數(shù)
SELECT COUNT(*) FROM pg_stat_activity;

針對上述情況，我列舉了幾個常用的sql語句，用于排查“死鎖”問題

2.3. 查詢當(dāng)前所有連接數(shù)據(jù)庫用戶和用戶其連接數(shù)

-- 查詢當(dāng)前所有連接數(shù)據(jù)庫用戶和用戶其連接數(shù)（按用戶名分組統(tǒng)計）
SELECT usename, COUNT(*) FROM pg_stat_activity GROUP BY usename ORDER BY count DESC;
 
-- 查詢特定數(shù)據(jù)庫用戶當(dāng)前連接數(shù)(your_db_user_name替換成自己的數(shù)據(jù)庫名稱)
SELECT COUNT(*) FROM pg_stat_activity WHERE usename = 'your_db_user_name';

2.4. 查詢是否存在鎖表記錄

-- 顯示出沒有被授予的鎖，也就是等待的鎖
SELECT * FROM pg_locks WHERE granted = false;
-- 等價如下SQL
SELECT * FROM pg_locks WHERE granted = 'f';

注意：在PostgreSQL中，布爾類型的值可以用'true'，'false'，'t'，或者'f'來表示。

• 'true'或者't'表示真；
• 'false'或者'f'表示假。

所以當(dāng)你在查詢結(jié)果中看到't'，那就和'true'等價，都表示該進程已經(jīng)被授予了鎖。同樣的，'f'和'false'等價，都表示該進程仍在等待鎖。

2.5. 查詢詳細的鎖信息

-- 查詢更詳細的鎖信息，包括哪些語句在等待鎖
SELECT a.datname,
         l.relation::regclass,
         l.transactionid,
         l.mode,
         l.GRANTED,
         a.usename,
         a.query,
         a.query_start,
         age(now(), a.query_start) AS "age", 
         a.pid 
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
WHERE NOT l.GRANTED;

每一行都表示一個等待的鎖。大部分列都是自解釋的，但是這里要提一下age列：當(dāng)一個事務(wù)等待一個鎖，但是卻沒有進展時，會顯示這個進程已經(jīng)多長時間沒有作出響應(yīng)了。

2.6. 如何解鎖？

2.6.1. 定位并終止阻賽的事務(wù)

在一般情況下，解鎖最直接的方式就是終止導(dǎo)致鎖表的進程或事務(wù)。首先要確定持有鎖的事務(wù)，可以通過如下語句：

-- 確定持有鎖的事務(wù)
SELECT * FROM pg_stat_activity WHERE waiting = 't';

2.6.1.1 pg_locks和pg_stat_activity的區(qū)別？

這兩個視圖查詢的內(nèi)容其實是不同的。具體選擇用哪個視圖，取決于你需要查詢的信息。

SELECT * FROM pg_stat_activity;

pg_stat_activity視圖包含了所有連接到數(shù)據(jù)庫服務(wù)器的活躍進程的信息。

這個視圖通常用于查看正在運行的查詢以及其它會話信息。如果需要診斷阻塞指定進程的問題，該視圖是一個非常好的起點。在這個查詢中，waiting='t'的情況指的是，這個進程正在等待獲取一把被其它進程持有的鎖。

SELECT * FROM pg_locks WHERE granted = false;

• pg_locks視圖顯示了所有活躍的鎖以及等待鎖的進程。granted = false表示鎖尚未被授予，即有進程正在等待獲取此鎖，可能是由于有其它進程持有了這把鎖而未能立即獲取。
• 總的來說，如果你需要查看生成鎖的查詢，可能用pg_stat_activity會更有用。如果你想看哪些鎖正在被等待，pg_locks會更有用。兩者視圖的使用應(yīng)根據(jù)實際需求和場景來選擇。

2.6.2. 查找到pid后，按pid終止進程

-- 按PID終止進程
SELECT pg_terminate_backend(PID);

PID需要替換成實際的進程ID。

1. 如果不能直接終止導(dǎo)致鎖表的事務(wù)，就需要查找并修復(fù)導(dǎo)致阻賽的程序邏輯錯誤。

2. 如果是長期的阻礙，可能需要考慮數(shù)據(jù)庫的性能優(yōu)化或者硬件的升級等更深入的措施。

終止事務(wù)有可能會丟失數(shù)據(jù)或者讓數(shù)據(jù)狀態(tài)變得不一致。在大部分情況下，最好先找出為什么會發(fā)生鎖表情況，然后修復(fù)引發(fā)問題的原因，而不是簡單的直接終止事務(wù)。

2.6.2.1 pg_cancel_backend和pg_terminate_backend的區(qū)別？

pg_cancel_backend和pg_terminate_backend函數(shù)類似于UNIX中的SIGINT和SIGTERM信號。

• pg_cancel_backend(PID)：這個函數(shù)會發(fā)送一個請求來取消后端當(dāng)前的查詢。PID是你想要取消查詢的進程的PID。這個效果類似于UNIX的SIGINT信號，查詢會盡可能的安全取消，比如說在一個安全的點回滾到開始狀態(tài)。

• pg_terminate_backend(PID)：這個函數(shù)會中斷一個連接和該連接上的任何活動。PID是你想要終止的后端進程的PID。這個函數(shù)發(fā)送的是一個terminate(BSIGTERM)信號給指定的后端，使得后端進程立即退出。

總結(jié)一下，pg_cancel_backend試圖取消后端的執(zhí)行的查詢但是保持連接，而pg_terminate_backend則直接斷開指定的連接。

以上就是PostgreSQL排查連接鎖問題的常用SQL語句的詳細內(nèi)容，更多關(guān)于PostgreSQL排查連接鎖語句的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論