Greenplum中怎么查看锁信息并处理

Greenplum中怎么查看锁信息并处理，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

Greenplum提供了一个视图（gp_toolkit.gp_locks_on_relation）用来查看当前的锁信息情况，执行查询如下：

postgres=# select * from gp_toolkit.gp_locks_on_relation ; lorlocktype | lordatabase |         lorrelname          | lorrelation | lortransaction | lorpid |     lormode      | lorgranted |                 lorcurrentquery-------------+-------------+-----------------------------+-------------+----------------+--------+------------------+------------+------------------------------------------------- relation    |       12094 | gp_locks_on_relation        |       12016 |                |  11989 | AccessShareLock  | t          | select * from gp_toolkit.gp_locks_on_relation ; relation    |           0 | pg_authid                   |        1260 |                |  11989 | AccessShareLock  | t          | select * from gp_toolkit.gp_locks_on_relation ; relation    |           0 | pg_authid_oid_index         |        2677 |                |  11989 | AccessShareLock  | t          | select * from gp_toolkit.gp_locks_on_relation ; relation    |           0 | pg_authid_rolname_index     |        2676 |                |  11989 | AccessShareLock  | t          | select * from gp_toolkit.gp_locks_on_relation ; relation    |           0 | pg_authid_rolresgroup_index |        6440 |                |  11989 | AccessShareLock  | t          | select * from gp_toolkit.gp_locks_on_relation ; relation    |           0 | pg_authid_rolresqueue_index |        6029 |                |  11989 | AccessShareLock  | t          | select * from gp_toolkit.gp_locks_on_relation ; relation    |       12094 | pg_class                    |        1259 |                |  11989 | AccessShareLock  | t          | select * from gp_toolkit.gp_locks_on_relation ; relation    |       12094 | pg_class_oid_index          |        2662 |                |  11989 | AccessShareLock  | t          | select * from gp_toolkit.gp_locks_on_relation ; relation    |       12094 | pg_class_relname_nsp_index  |        2663 |                |  11989 | AccessShareLock  | t          | select * from gp_toolkit.gp_locks_on_relation ; relation    |           0 | pg_database                 |        1262 |                |  11989 | AccessShareLock  | t          | select * from gp_toolkit.gp_locks_on_relation ; relation    |           0 | pg_database_datname_index   |        2671 |                |  11989 | AccessShareLock  | t          | select * from gp_toolkit.gp_locks_on_relation ; relation    |           0 | pg_database_oid_index       |        2672 |                |  11989 | AccessShareLock  | t          | select * from gp_toolkit.gp_locks_on_relation ; relation    |       12094 | pg_locks                    |       11343 |                |  11989 | AccessShareLock  | t          | select * from gp_toolkit.gp_locks_on_relation ; relation    |       12094 | pg_stat_activity            |       11417 |                |  11989 | AccessShareLock  | t          | select * from gp_toolkit.gp_locks_on_relation ; relation    |       12094 | t1                          |       16384 |                |  12650 | RowExclusiveLock | t          |  in transaction(15 rows)

查到锁信息后，我们一般情况会根据当前的问题及业务逻辑定位出，是哪个进程卡住了。这里假设上面我们构造的针对t1表的事务插入引起了行级排他锁，那么我们此时为了恢复业务，需要将其处理掉。

首先我们通过活动查询视图来看一下该查询目前的状态：

postgres=# select * from pg_stat_activity; datid | datname  | procpid | sess_id | usesysid | usename |          current_query          | waiting |          query_start          |         backend_start   | client_addr | client_port | application_name |          xact_start           | waiting_reason | rsgid | rsgname | rsgqueueduration-------+----------+---------+---------+----------+---------+---------------------------------+---------+-------------------------------+-------------------------------+-------------+-------------+------------------+-------------------------------+----------------+-------+---------+------------------ 12094 | postgres |   11989 |      51 |       10 | gposs5  | select * from pg_stat_activity; | f       | 2020-09-09 21:16:17.514665+08 | 2020-09-09 21:12:06.797525+08 |             |          -1 | psql             | 2020-09-09 21:16:17.514665+08 |                |     0 | unknown | 12094 | postgres |   12650 |      52 |       10 | gposs5  |  in transaction           | f       | 2020-09-09 21:15:56.159363+08 | 2020-09-09 21:15:36.889396+08 |             |          -1 | psql             | 2020-09-09 21:15:48.16486+08  |                |     0 | unknown |(2 rows)

可以看出，procpid为12650的查询，与上面锁的lorpid是对应的，我们可以将这个进程停掉。采用如下两个函数即可：

postgres=# select pg_cancel_backend(12650); pg_cancel_backend------------------- t(1 row)postgres=# select pg_cancel_backend(12650); pg_cancel_backend------------------- t(1 row)postgres=# select pg_terminate_backend(12650); pg_terminate_backend---------------------- t(1 row)postgres=# select pg_terminate_backend(12650);WARNING:  PID 12650 is not a PostgreSQL server process pg_terminate_backend---------------------- f(1 row)

看完上述内容，你们掌握Greenplum中怎么查看锁信息并处理的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注行业资讯频道，感谢各位的阅读！