lightdb for pg查看锁

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)