1. 背景

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

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;

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

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

-- 查詢當(dāng)前所有連接數(shù)據(jù)庫用戶和用戶其連接數(shù)（按用戶名分組統(tǒng)計(jì)）
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;
-- 等價(jià)如下SQL
SELECT * FROM pg_locks WHERE granted = 'f';

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

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

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

2.5. 查詢?cè)敿?xì)的鎖信息

-- 查詢更詳細(xì)的鎖信息，包括哪些語句在等待鎖
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;

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

2.6. 如何解鎖？

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

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

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

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

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

SELECT * FROM pg_stat_activity;

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

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

SELECT * FROM pg_locks WHERE granted = false;

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

2.6.2. 查找到pid后，按pid終止進(jìn)程

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

PID需要替換成實(shí)際的進(jìn)程ID。

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

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

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

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

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

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

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

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

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

最新評(píng)論