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; | | |
锁的兼容性如下：
pgrowlocks扩展可以用来查看某个表上的行锁信息。如下：
postgres=# create extension pgrowlocks; CREATE EXTENSION postgres=# SELECT * FROM pgrowlocks('big_table'); locked_row | locker | multi | xids | modes | pids ------------+--------+-------+------+-------+------ (0 rows)