Shulou(Shulou.com)06/02 Report--

Greenplum how to view lock information and deal with, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

Greenplum provides a view (gp_toolkit.gp_locks_on_relation) to view the current lock information. Execute the query as follows:

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)

After looking up the lock information, we will generally locate which process is stuck according to the current problem and business logic. Assuming that the transaction insertion for the T1 table we constructed above causes a row-level exclusive lock, then we need to dispose of it at this time in order to recover the business.

First, let's take a look at the current status of the query through the active query view:

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-21-16-14-665-08 | 2020-09-09 21-21-12-12-14-797525-08 | |-1 | psql | 2020-09-09 21-21 of the 16-14-665-08 | 0 | unknown | 12094 | postgres | 12650 | 52 | 10 | gposs5 | in transaction | f | 20-09-09 21: 15rows 56.1593631408 | 2020-09-09 21VAS 15VAS 36.8893961408 | |-1 | psql | 2020-09-021VAS 15VAS 48.164861408 | | 0 | unknown | (2 rows)

As you can see, the query with a procpid of 12650 corresponds to the lorpid locked above, and we can stop the process. You can use the following two functions:

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) after reading the above, do you know how to view lock information and deal with it in Greenplum? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.