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

大余道

目录

• 查看当前所有锁
• 查看特定表的锁
• 查看数据库级别的锁
• 查看事务锁的阻塞情况
• 解释
• 查看锁的等待时间
• 注意事项

查看当前所有锁

查询 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;

注意事项

• 锁是数据库操作的正常部分，但长时间的锁可能会影响性能。
• 如果发现锁竞争严重，可以考虑优化查询、索引或事务逻辑。
• 频繁的锁问题可能需要调整数据库的隔离级别或锁策略。