快捷導(dǎo)航

PostgreSQL查看是否鎖表的方法本步驟

更新時(shí)間：2025年06月16日 10:33:20 作者：面向生活編程

PostgreSQL使用pg_locks視圖查看鎖類(lèi)型、表OID、進(jìn)程ID及模式,結(jié)合pg_class過(guò)濾表,通過(guò)pg_stat_activity分析等待時(shí)間,檢查阻塞進(jìn)程與查詢(xún),優(yōu)化查詢(xún)和隔離級(jí)別解決鎖問(wèn)題

查看當(dāng)前所有鎖

查詢(xún) pg_locks 系統(tǒng)視圖可以查看當(dāng)前數(shù)據(jù)庫(kù)中的所有鎖信息：

SELECT
    locktype,
    database AS db_oid,
    relation AS rel_oid,
    page,
    tuple,
    virtualtransaction,
    pid,
    mode,
    granted
FROM pg_locks;

locktype：鎖的類(lèi)型，如表鎖、行鎖等。
relation：被鎖定的表的 OID，可以通過(guò) pg_class 視圖查詢(xún)表名。
pid：持有鎖的進(jìn)程 ID。
mode：鎖的模式，如 AccessShareLock、RowExclusiveLock 等。
granted：是否已經(jīng)獲得鎖。

查看特定表的鎖

如果你想查看特定表的鎖信息，可以結(jié)合 pg_class 視圖來(lái)過(guò)濾：

SELECT
    l.locktype,
    l.database AS db_oid,
    l.relation AS rel_oid,
    l.page,
    l.tuple,
    l.virtualtransaction,
    l.pid,
    l.mode,
    l.granted,
    c.relname AS table_name
FROM pg_locks l
JOIN pg_class c ON l.relation = c.oid
WHERE c.relname = 'your_table_name'; -- 替換為你的表名

查看數(shù)據(jù)庫(kù)級(jí)別的鎖

如果你懷疑數(shù)據(jù)庫(kù)級(jí)別的鎖，可以使用以下查詢(xún)：

SELECT
    l.locktype,
    l.database AS db_oid,
    l.relation AS rel_oid,
    l.page,
    l.tuple,
    l.virtualtransaction,
    l.pid,
    l.mode,
    l.granted,
    d.datname AS database_name
FROM pg_locks l
JOIN pg_database d ON l.database = d.oid;

查看事務(wù)鎖的阻塞情況

為了查看事務(wù)鎖的阻塞情況，可以使用以下查詢(xún)：

SELECT
    blocked_pid,
    blocking_pid,
    blocked_activity.query AS blocked_query,
    blocking_activity.query AS blocking_query,
    blocked_activity.pid AS blocked_pid,
    blocking_activity.pid AS blocking_pid
FROM
    (
        SELECT
            pid AS blocked_pid,
            pg_locks.locked_row.mode AS lock_mode,
            pg_locks.locked_row.relation AS relation,
            pg_locks.blocking_pid AS blocking_pid
        FROM
            pg_locks AS locked_row
            JOIN pg_locks AS blocking_lock ON
                locked_row.locktype = blocking_lock.locktype AND
                locked_row.database = blocking_lock.database AND
                locked_row.relation = blocking_lock.relation AND
                locked_row.page = blocking_lock.page AND
                locked_row.tuple = blocking_lock.tuple AND
                locked_row.virtualxid = blocking_lock.virtualxid AND
                locked_row.transactionid = blocking_lock.transactionid AND
                locked_row.classid = blocking_lock.classid AND
                locked_row.objid = blocking_lock.objid AND
                locked_row.objsubid = blocking_lock.objsubid AND
                locked_row.pid != blocking_lock.pid AND
                NOT locked_row.granted AND
                blocking_lock.granted
    ) AS blocked_locks
JOIN pg_stat_activity AS blocked_activity ON blocked_locks.blocked_pid = blocked_activity.pid
JOIN pg_stat_activity AS blocking_activity ON blocked_locks.blocking_pid = blocking_activity.pid;

解釋

blocked_pid：被阻塞的進(jìn)程 ID。
blocking_pid：阻塞其他進(jìn)程的進(jìn)程 ID。
blocked_query：被阻塞的查詢(xún)。
blocking_query：阻塞其他查詢(xún)的查詢(xún)。

查看鎖的等待時(shí)間

如果你想知道鎖的等待時(shí)間，可以結(jié)合 pg_stat_activity 視圖：

SELECT
    pid,
    query,
    state,
    wait_event_type,
    wait_event,
    now() - query_start AS waiting_time
FROM pg_stat_activity
WHERE state = 'active' AND wait_event_type IS NOT NULL;