Description of PostgreSQL lock query and killing Process

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: