Greenplum中怎么查看鎖信息并處理，相信很多沒有經(jīng)驗的人對此束手無策，為此本文總結(jié)了問題出現(xiàn)的原因和解決方法，通過這篇文章希望你能解決這個問題。
創(chuàng)新互聯(lián)公司-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性價比樂平網(wǎng)站開發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫,直接使用。一站式樂平網(wǎng)站制作公司更省心,省錢,快速模板網(wǎng)站建設(shè)找我們，業(yè)務(wù)覆蓋樂平地區(qū)。費用合理售后完善，10多年實體公司更值得信賴。
Greenplum提供了一個視圖（gp_toolkit.gp_locks_on_relation）用來查看當前的鎖信息情況，執(zhí)行查詢?nèi)缦拢?/p>
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)
查到鎖信息后，我們一般情況會根據(jù)當前的問題及業(yè)務(wù)邏輯定位出，是哪個進程卡住了。這里假設(shè)上面我們構(gòu)造的針對t1表的事務(wù)插入引起了行級排他鎖，那么我們此時為了恢復業(yè)務(wù)，需要將其處理掉。
首先我們通過活動查詢視圖來看一下該查詢目前的狀態(tài)：
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是對應(yīng)的，我們可以將這個進程停掉。采用如下兩個函數(shù)即可：
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)
看完上述內(nèi)容，你們掌握Greenplum中怎么查看鎖信息并處理的方法了嗎？如果還想學到更多技能或想了解更多相關(guān)內(nèi)容，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝各位的閱讀！
重慶分公司
重慶分公司
