select d.datname,c.relname,c.reltype,a.* from pg_locks a, pg_catalog.pg_database d,pg_catalog.pg_class c where d.oid = a.database
and c.oid = a.relation ;
当前数据库中的所有锁。注意是当前数据库，不是整个pg实例。虽然pg_locks本身是实例级的，但是pg_class是数据库级的，所以关联之后，其他数据库的锁会查询不到。
locktype |database|relation|page|tuple|virtualxid|transactionid|classid|objid|objsubid|virtualtransaction|pid |mode |granted|fastpath|
-------------+--------+--------+----+-----+----------+-------------+-------+-----+--------+------------------+-----+----------------+-------+--------+
relation | 57888| 12141| | | | | | | |17/504 |15487|AccessShareLock |true |true |
virtualxid | | | | |17/504 | | | | |17/504 |15487|ExclusiveLock |true |true |
relation | 107265| 335884| | | | | | | |16/118 | 8451|AccessShareLock |true |true |
relation | 107265| 335884| | | | | | | |16/118 | 8451|RowExclusiveLock|true |true |
relation | 107265| 335878| | | | | | | |16/118 | 8451|AccessShareLock |true |true |
relation | 107265| 335878| | | | | | | |16/118 | 8451|RowExclusiveLock|true |true |
virtualxid | | | | |16/118 | | | | |16/118 | 8451|ExclusiveLock |true |true |
page | 107265| 335884| 0| | | | | | |16/118 | 8451|ExclusiveLock |true |false |
transactionid| | | | | |12755336 | | | |16/118 | 8451|ExclusiveLock |true |false |
select * from pg_locks;
select * from pg_catalog.pg_database ;
select * from pg_catalog.pg_class where oid=335878; -- 查询不到
对于长时间的锁监控，可以查看postgresql.log，里面记录了获取锁超过参数设置的事务。
也可以查询一下看看当前正在执行的查询是否已经获取锁了：
SELECT
S.pid,
age(clock_timestamp(), query_start),
usename,
query,
L.mode,
L.locktype,
L.granted
FROM pg_stat_activity S
inner join pg_locks L on S.pid = L.pid
order by L.granted, L.pid DESC;
pid | age | usename | query | mode | locktype | granted
-------+-----------------+---------+-----------------------------------------+-----------------+------------+---------
20325 | 00:00:04.68855 | lightdb | select * from big_table for update; | RowShareLock | relation | f
20325 | 00:00:04.688562 | lightdb | select * from big_table for update; | ExclusiveLock | virtualxid | t
19801 | 00:00:32.412 | lightdb | lock table big_table in exclusive mode; | ExclusiveLock | relation | t
19801 | 00:00:32.412001 | lightdb | lock table big_table in exclusive mode; | ExclusiveLock | virtualxid | t
1054 | 00:00:00.002006 | lightdb | SELECT +| ExclusiveLock | virtualxid | t
| | | S.pid, +| | |
| | | age(clock_timestamp(), query_start), +| | |
| | | usename, +| | |
| | | query, +| | |
| | | L.mode, +| | |
| | | L.locktype, +| | |
| | | L.granted +| | |
| | | FROM pg_stat_activity S +| | |
| | | inner join pg_locks L on S.pid = L.pid +| | |
| | | order by L.granted, L.pid DESC; | | |
锁的兼容性如下：
所有的lock table table_name lock_mode都是表锁而不是行锁。只有select ... for update才能行锁。
各种语句对应的锁可参见13.3. Explicit Locking (light-pg.com)。
pgrowlocks扩展可以用来查看某个表上的行锁信息。如下：
postgres=# create extension pgrowlocks;
CREATE EXTENSION
postgres=# SELECT * FROM pgrowlocks('big_table');
locked_row | locker | multi | xids | modes | pids
------------+--------+-------+------+-------+------
(0 rows)