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 | <IDLE> 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 | <IDLE> 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中怎么查看锁信息并处理的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注亿速云行业资讯频道，感谢各位的阅读！
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
