Description of PostgreSQL lock query and killing Process
Query the locks in the table.
Select a. locktype, a. database, a. pid, a. mode, a. relation, B. relname
From pg_locks
Join pg_class B on a. relation = B. oid
Where upper (B. relname) = 'table _ name ';
The preceding SQL statement is used to query whether a table has a lock.
The lock exists as follows:
Locktype | database | pid | mode | relation | relname
---------- + ------- + --------------- + ---------- + ---------
Relation | 439791 | 26752 | AccessShareLock | 2851428 | table_name
Relation | 439791 | 26752 | ExclusiveLock | 2851428 | table_name
Then, query the SQL statement corresponding to the lock in the pg_stat_activity table based on the pid found above:
Select usename, current_query, query_start, procpid, client_addr from pg_stat_activity where procpid = 17509;
As follows:
Usename | current_query | query_start | procpid | client_addr
----------- + Response + ----------------------------- + --------- + ----------------
Gpcluster | delete from TABLE_NAME where a = 1 | 09:35:47. 721173 + 08 | 17509 | 192.168.165.18
(1 row)
Through the above, we can find that the above lock keeps the statement hanging there. After the lock is completed, the application will soon run out.
Then, check the application code and find that no two transactions are committed in the code. After adding the submit operation, re-run the number, and soon run.
If you want to kill the statement, first confirm with the relevant personnel whether the statement is a key process.
Killing Method: query the pid of the process on the mydb server of the PostgreSQL database and Kill it.
> Ps-ef | grep 17509
Postgres 17509 4868 1 Nov18? 00:11:19 postgres: S mydb 192.168.165.18 (56059) SELECT
Postgres 30832 30800 0 00:00:00 pts/3 grep 17509
> Ps-ef | grep 17509
Postgres 17509 4868 1 Nov18? 00:11:19 postgres: S mydb 192.168.165.18 (56059) SELECT
Postgres 30838 30800 0 00:00:00 pts/3 grep 17509
> Kill-9 17509
------------------------------------ Lili split line ------------------------------------
Install PostgreSQL 6.3 on yum in CentOS 9.3
PostgreSQL cache details
Compiling PostgreSQL on Windows
Configuration and installation of LAPP (Linux + Apache + PostgreSQL + PHP) Environment in Ubuntu
Install and configure phppgAdmin on Ubuntu
Install PostgreSQL9.3 on CentOS
Configure a Streaming Replication cluster in PostgreSQL
How to install PostgreSQL 7/6 and phpPgAdmin in CentOS 5/6. 4
------------------------------------ Lili split line ------------------------------------
PostgreSQL details: click here
PostgreSQL: click here
This article permanently updates the link address: