PostgreSQL查看是否锁表的方法本步骤_MsSqlserver

查看当前所有锁

查询 pg_locks 系统视图可以查看当前数据库中的所有锁信息：

select
    locktype,
    database as db_oid,
    relation as rel_oid,
    page,
    tuple,
    virtualtransaction,
    pid,
    mode,
    granted
from pg_locks;

locktype：锁的类型，如表锁、行锁等。
relation：被锁定的表的 oid，可以通过 pg_class 视图查询表名。
pid：持有锁的进程 id。
mode：锁的模式，如 accesssharelock、rowexclusivelock 等。
granted：是否已经获得锁。

查看特定表的锁

如果你想查看特定表的锁信息，可以结合 pg_class 视图来过滤：

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'; -- 替换为你的表名

查看数据库级别的锁

如果你怀疑数据库级别的锁，可以使用以下查询：

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;

查看事务锁的阻塞情况

为了查看事务锁的阻塞情况，可以使用以下查询：

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：被阻塞的进程 id。
blocking_pid：阻塞其他进程的进程 id。
blocked_query：被阻塞的查询。
blocking_query：阻塞其他查询的查询。

查看锁的等待时间

如果你想知道锁的等待时间，可以结合 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;