溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
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進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
